135 lines
4.7 KiB
Plaintext
135 lines
4.7 KiB
Plaintext
############################################################################################
|
|
# #
|
|
# 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
|
|
|