1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
|
############################################################################################
# #
# The aim of this test is to set up replication between a master and slave and #
# test the modified behaviour of bitwise operations which was brought by WL#8699: #
# Bit-wise operations on binary data types. #
# #
# Creation Date: 2016-03-02 #
# Author: Deepa Dixit #
# #
############################################################################################
--source include/master-slave.inc
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
--echo # Create table with columns of binary data types and insert values
--connection master
CREATE TABLE bitwise_table(id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, vbin1 VARBINARY(32), vbin2 VARBINARY(32));
INSERT INTO bitwise_table(vbin1, vbin2) VALUES
(x'59', x'6a'),
(x'5939', x'6ac3'),
(x'5939a998', x'6ac35d2a'),
(x'5939a99861154f35', x'6ac35d2a3ab34bda'),
(x'5939a99861154f3587d5440618e9b28b', x'6ac35d2a3ab34bda8ac412ea0141852c'),
(x'5939a99861154f3587d5440618e9b28b166181c5ca514ab1b8e9c970ae5e421a', x'6ac35d2a3ab34bda8ac412ea0141852c3c8e38bb19934a7092a40bb19db13a80'),
(x'5939a99861154f3587d5440618e9b28b', x'8ac412ea0141852c'),
(x'5939a99861154f35', x'6ac35d2a3ab34bda8ac412ea0141852c');
--disable_warnings
INSERT INTO bitwise_table(vbin1, vbin2)
SELECT (vbin1 & vbin2) , (vbin1 | vbin2) FROM bitwise_table WHERE id <= 6;
INSERT INTO bitwise_table(vbin1, vbin2)
SELECT (vbin1 ^ vbin2) , ( ~ vbin1 ) FROM bitwise_table WHERE id <= 6;
INSERT INTO bitwise_table(vbin1, vbin2)
SELECT (vbin1 >> 4 ) , ( vbin2 << 3 ) FROM bitwise_table WHERE id <= 6;
INSERT INTO bitwise_table(vbin1, vbin2)
SELECT (vbin1 ^ x'6ac3'), (vbin2 & x'5939') FROM bitwise_table WHERE id = 2;
--enable_warnings
--error ER_INVALID_BITWISE_OPERANDS_SIZE
INSERT INTO bitwise_table(vbin1, vbin2)
SELECT (vbin1 & vbin2) , (vbin1 | vbin2) FROM bitwise_table WHERE id = 7;
--source include/sync_slave_sql_with_master.inc
--echo [Connection Slave]
--echo # check whether table was created and values were inserted on slave
SELECT HEX(vbin1), BIT_COUNT(vbin1), HEX(vbin2), BIT_COUNT(vbin2) FROM bitwise_table;
SELECT (vbin1 | NULL ), (vbin2 & NULL) FROM bitwise_table;
SELECT HEX(vbin1), HEX(BIT_AND(vbin1)), HEX(BIT_OR(vbin1)), HEX(BIT_XOR(vbin1)), BIT_COUNT(vbin1) FROM bitwise_table GROUP BY id;
SELECT HEX(vbin2), HEX(BIT_AND(vbin2)), HEX(BIT_OR(vbin2)), HEX(BIT_XOR(vbin2)), BIT_COUNT(vbin2) FROM bitwise_table GROUP BY id;
--let $diff_tables = master:bitwise_table, slave:bitwise_table
--source include/diff_tables.inc
--echo [Connection Master]
--connection master
--echo # Update rows inside the table
UPDATE bitwise_table SET vbin1 = (vbin1 | vbin2) WHERE id <= 6;
UPDATE bitwise_table SET vbin2 = (vbin1 & vbin2) WHERE id <= 6;
--source include/sync_slave_sql_with_master.inc
--echo [Connection Slave]
--echo # Check whether rows are updated
SELECT HEX(vbin1), HEX(vbin2) FROM bitwise_table WHERE id <= 6;
--let $diff_tables = master:bitwise_table, slave:bitwise_table
--source include/diff_tables.inc
--echo [Connection Master]
--connection master
UPDATE bitwise_table SET vbin1 = (vbin1 ^ vbin2) WHERE id <= 6;
UPDATE bitwise_table SET vbin2 = (~ vbin1) WHERE id <= 6;
--source include/sync_slave_sql_with_master.inc
--echo [Connection Slave]
--echo # Check whether rows are updated
SELECT HEX(vbin1), HEX(vbin2) FROM bitwise_table WHERE id <= 6;
--let $diff_tables = master:bitwise_table, slave:bitwise_table
--source include/diff_tables.inc
--echo [Connection Master]
--connection master
--echo # Delete rows from the table
DELETE FROM bitwise_table WHERE BIT_COUNT(vbin1) <= 8;
--source include/sync_slave_sql_with_master.inc
--echo [Connection Slave]
--echo # check whether the rows are deleted
SELECT HEX(vbin1), HEX(vbin2) FROM bitwise_table;
--let $diff_tables = master:bitwise_table, slave:bitwise_table
--source include/diff_tables.inc
--echo [Connection Master]
--connection master
--echo # Drop tables and clean up
DROP TABLE bitwise_table;
--source include/sync_slave_sql_with_master.inc
--echo [Connection Slave]
--echo # Check whether table is dropped
--error ER_NO_SUCH_TABLE
SELECT * FROM bitwise_table;
--source include/rpl_end.inc
|
