Debian Codegraph @

mysql-test/suite/rpl/t/rpl_func_uuid.test

raw
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