| 1 |
############################################################################################# |
| 2 |
# # |
| 3 |
# The aim of this test is to set up replication between a master # |
| 4 |
# and slave and test the functions UUID_TO_BIN, BIN_TO_UUID, # |
| 5 |
# and IS_UUID which were added as part of the WL#8920: Improve usability of UUID # |
| 6 |
# manipulations. # |
| 7 |
# # |
| 8 |
# Tables with binary data types are created and the newly introduced functions # |
| 9 |
# UUID_TO_BIN, BIN_TO_UUID, and IS_UUID are used on the uuid values inserted # |
| 10 |
# into the table. # |
| 11 |
# # |
| 12 |
# Creation Date : 2015-2-10 # |
| 13 |
# Author : Deepa Dixit # |
| 14 |
# # |
| 15 |
############################################################################################# |
| 16 |
|
| 17 |
--source include/master-slave.inc |
| 18 |
|
| 19 |
|
| 20 |
--echo # Create table with columns of binary data types and insert uuid values |
| 21 |
|
| 22 |
--connection master |
| 23 |
|
| 24 |
CREATE TABLE uuid_table ( pkey INT NOT NULL PRIMARY KEY AUTO_INCREMENT, bin BINARY(16), vbn VARBINARY(16), tbl TINYBLOB, ttx TINYTEXT CHARACTER SET binary, blb BLOB ); |
| 25 |
|
| 26 |
# Valid values of uuid |
| 27 |
|
| 28 |
INSERT INTO uuid_table VALUES( NULL, UUID_TO_BIN('12345678123456781234567812345678'), |
| 29 |
UUID_TO_BIN('12345678123456781234567812345678'), |
| 30 |
UUID_TO_BIN('12345678123456781234567812345678'), |
| 31 |
UUID_TO_BIN('12345678123456781234567812345678'), |
| 32 |
UUID_TO_BIN('12345678123456781234567812345678')); |
| 33 |
|
| 34 |
INSERT INTO uuid_table VALUES( NULL, UUID_TO_BIN('12345678-1234-5678-1234-567812345678'), |
| 35 |
UUID_TO_BIN('12345678-1234-5678-1234-567812345678'), |
| 36 |
UUID_TO_BIN('12345678-1234-5678-1234-567812345678'), |
| 37 |
UUID_TO_BIN('12345678-1234-5678-1234-567812345678'), |
| 38 |
UUID_TO_BIN('12345678-1234-5678-1234-567812345678')); |
| 39 |
|
| 40 |
INSERT INTO uuid_table VALUES( NULL, UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}'), |
| 41 |
UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}'), |
| 42 |
UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}'), |
| 43 |
UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}'), |
| 44 |
UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}')); |
| 45 |
|
| 46 |
# Invalid values cannot be inserted using the function |
| 47 |
|
| 48 |
--error ER_WRONG_VALUE_FOR_TYPE |
| 49 |
INSERT INTO uuid_table(bin) VALUES ( UUID_TO_BIN('{c8eb4b15cb09-48bb-bbb2-e6a0b6b4d5c7}')); |
| 50 |
--error ER_WRONG_VALUE_FOR_TYPE |
| 51 |
INSERT INTO uuid_table(bin) VALUES ( UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c76}')); |
| 52 |
--error ER_WRONG_VALUE_FOR_TYPE |
| 53 |
INSERT INTO uuid_table(bin) VALUES ( UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c}')); |
| 54 |
--error ER_WRONG_VALUE_FOR_TYPE |
| 55 |
INSERT INTO uuid_table(bin) VALUES ( UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2e6-a0b6b4d5c7}')); |
| 56 |
--error ER_WRONG_VALUE_FOR_TYPE |
| 57 |
INSERT INTO uuid_table(bin) VALUES ( UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d}5c7')); |
| 58 |
|
| 59 |
--source include/sync_slave_sql_with_master.inc |
| 60 |
--echo [Connection Slave] |
| 61 |
|
| 62 |
--echo # check whether table was created and uuids were inserted on slave |
| 63 |
|
| 64 |
SELECT BIN_TO_UUID(bin) AS a, BIN_TO_UUID(vbn) AS b, BIN_TO_UUID(tbl) AS c, BIN_TO_UUID(ttx) AS d, BIN_TO_UUID(blb) AS e |
| 65 |
FROM uuid_table |
| 66 |
WHERE pkey <= 2; |
| 67 |
|
| 68 |
SELECT HEX(UUID_TO_BIN(BIN_TO_UUID(bin, TRUE))) AS a, BIN_TO_UUID(vbn, TRUE) AS b, HEX(UUID_TO_BIN(BIN_TO_UUID(tbl, TRUE))) AS c, BIN_TO_UUID(ttx, IS_UUID(BIN_TO_UUID(ttx))) AS d, HEX(UUID_TO_BIN(BIN_TO_UUID(blb, FALSE))) AS e |
| 69 |
FROM uuid_table |
| 70 |
WHERE pkey = 3; |
| 71 |
|
| 72 |
SELECT IS_UUID(BIN_TO_UUID(bin)) AS a, IS_UUID(BIN_TO_UUID(vbn)) AS b, IS_UUID(BIN_TO_UUID(tbl)) AS c, IS_UUID(BIN_TO_UUID(ttx)) AS d, IS_UUID(BIN_TO_UUID(blb)) AS e |
| 73 |
FROM uuid_table |
| 74 |
WHERE pkey <= 3; |
| 75 |
|
| 76 |
|
| 77 |
--let $diff_tables = master:uuid_table, slave:uuid_table |
| 78 |
--source include/diff_tables.inc |
| 79 |
|
| 80 |
--echo [Connection Master] |
| 81 |
--connection master |
| 82 |
|
| 83 |
--echo # Update values in table |
| 84 |
|
| 85 |
UPDATE uuid_table SET bin = (UUID_TO_BIN('c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7', IS_UUID('12345678-1234-5678-1234-567812345678'))) |
| 86 |
WHERE pkey = 1; |
| 87 |
|
| 88 |
--source include/sync_slave_sql_with_master.inc |
| 89 |
--echo [Connection Slave] |
| 90 |
|
| 91 |
--echo # Check whether value is updated on slave |
| 92 |
|
| 93 |
SELECT HEX(UUID_TO_BIN(BIN_TO_UUID(bin))), BIN_TO_UUID(vbn), HEX(UUID_TO_BIN(BIN_TO_UUID(tbl))), BIN_TO_UUID(ttx), HEX(UUID_TO_BIN(BIN_TO_UUID(blb))) |
| 94 |
FROM uuid_table |
| 95 |
WHERE pkey = 1; |
| 96 |
|
| 97 |
--let $diff_tables = master:uuid_table, slave:uuid_table |
| 98 |
--source include/diff_tables.inc |
| 99 |
|
| 100 |
--echo [Connection Master] |
| 101 |
--connection master |
| 102 |
|
| 103 |
--echo # Delete a row from the table |
| 104 |
|
| 105 |
DELETE FROM uuid_table WHERE blb = UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}'); |
| 106 |
|
| 107 |
--source include/sync_slave_sql_with_master.inc |
| 108 |
--echo [Connection Slave] |
| 109 |
|
| 110 |
--echo # Check whether row has been deleted |
| 111 |
|
| 112 |
SELECT * FROM uuid_table WHERE pkey = 3; |
| 113 |
|
| 114 |
--let $diff_tables = master:uuid_table, slave:uuid_table |
| 115 |
--source include/diff_tables.inc |
| 116 |
|
| 117 |
--echo [Connection Master] |
| 118 |
--connection master |
| 119 |
|
| 120 |
--echo # Create a table and insert invalid values of uuid |
| 121 |
|
| 122 |
CREATE TABLE invalid_uuid ( pkey INT NOT NULL PRIMARY KEY AUTO_INCREMENT, col VARCHAR(50) ); |
| 123 |
|
| 124 |
INSERT into invalid_uuid VALUES ( NULL, '1234567812345678123456781234567' ), |
| 125 |
( NULL, '1234-5678-1234-567812345678-12345678' ), |
| 126 |
( NULL, '123456781234567812345678123456789' ), |
| 127 |
( NULL, '12345678-1234-5678-1234567812345678' ), |
| 128 |
( NULL, '{123456781234567812345678123456}78' ); |
| 129 |
|
| 130 |
|
| 131 |
--source include/sync_slave_sql_with_master.inc |
| 132 |
--echo [Connection Slave] |
| 133 |
|
| 134 |
--echo # Check whether table has been created on slave |
| 135 |
|
| 136 |
--error ER_WRONG_VALUE_FOR_TYPE |
| 137 |
SELECT BIN_TO_UUID(UNHEX(col)) FROM invalid_uuid; |
| 138 |
|
| 139 |
--let $diff_tables = master:invalid_uuid, slave:invalid_uuid |
| 140 |
--source include/diff_tables.inc |
| 141 |
|
| 142 |
--echo [Connection Master] |
| 143 |
--connection master |
| 144 |
|
| 145 |
--echo # create table with generated column |
| 146 |
|
| 147 |
CREATE TABLE uuid_gencol (col1 VARCHAR(100), gcol2 BINARY(16) AS (UUID_TO_BIN(col1)) VIRTUAL, INDEX(gcol2)); |
| 148 |
|
| 149 |
INSERT INTO uuid_gencol (col1) VALUES |
| 150 |
('{12345678-1234-5678-1234-567812345678}'), |
| 151 |
('12345679123456781234567812345678'), |
| 152 |
('12345670-1234-5678-1234-567812345678'); |
| 153 |
|
| 154 |
--source include/sync_slave_sql_with_master.inc |
| 155 |
--echo [Connection Slave] |
| 156 |
|
| 157 |
--echo # Check whether table is created |
| 158 |
|
| 159 |
EXPLAIN SELECT * FROM uuid_gencol WHERE gcol2=x'12345679123456781234567812345678'; |
| 160 |
SELECT col1, BIN_TO_UUID(gcol2) FROM uuid_gencol WHERE gcol2=x'12345679123456781234567812345678'; |
| 161 |
|
| 162 |
EXPLAIN SELECT * FROM uuid_gencol WHERE UUID_TO_BIN(col1)=x'12345679123456781234567812345678'; |
| 163 |
SELECT col1, HEX(UUID_TO_BIN(BIN_TO_UUID(gcol2))) FROM uuid_gencol WHERE UUID_TO_BIN(col1)=x'12345679123456781234567812345678'; |
| 164 |
|
| 165 |
--let $diff_tables = master:uuid_gencol, slave:uuid_gencol |
| 166 |
--source include/diff_tables.inc |
| 167 |
|
| 168 |
--echo [Connection Master] |
| 169 |
--connection master |
| 170 |
|
| 171 |
--echo # Drop tables and clean up |
| 172 |
|
| 173 |
DROP TABLE uuid_table; |
| 174 |
DROP TABLE invalid_uuid; |
| 175 |
DROP TABLE uuid_gencol; |
| 176 |
|
| 177 |
|
| 178 |
--source include/sync_slave_sql_with_master.inc |
| 179 |
--echo [Connection Slave] |
| 180 |
|
| 181 |
--echo # Check whether table is dropped |
| 182 |
|
| 183 |
--error ER_NO_SUCH_TABLE |
| 184 |
SELECT * FROM uuid_table; |
| 185 |
|
| 186 |
--source include/rpl_end.inc |
| 187 |
|