Debian Codegraph @

mysql-test/suite/rpl/r/rpl_func_uuid.result

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 # Create table with columns of binary data types and insert uuid values
7 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 );
8 INSERT INTO uuid_table VALUES( NULL, UUID_TO_BIN('12345678123456781234567812345678'),
9 UUID_TO_BIN('12345678123456781234567812345678'),
10 UUID_TO_BIN('12345678123456781234567812345678'),
11 UUID_TO_BIN('12345678123456781234567812345678'),
12 UUID_TO_BIN('12345678123456781234567812345678'));
13 INSERT INTO uuid_table VALUES( NULL, UUID_TO_BIN('12345678-1234-5678-1234-567812345678'),
14 UUID_TO_BIN('12345678-1234-5678-1234-567812345678'),
15 UUID_TO_BIN('12345678-1234-5678-1234-567812345678'),
16 UUID_TO_BIN('12345678-1234-5678-1234-567812345678'),
17 UUID_TO_BIN('12345678-1234-5678-1234-567812345678'));
18 INSERT INTO uuid_table VALUES( NULL, UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}'),
19 UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}'),
20 UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}'),
21 UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}'),
22 UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}'));
23 INSERT INTO uuid_table(bin) VALUES ( UUID_TO_BIN('{c8eb4b15cb09-48bb-bbb2-e6a0b6b4d5c7}'));
24 ERROR HY000: Incorrect string value: '{c8eb4b15cb09-48bb-bbb2-e6a0b6b4d5c7}' for function uuid_to_bin
25 INSERT INTO uuid_table(bin) VALUES ( UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c76}'));
26 ERROR HY000: Incorrect string value: '{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c76}' for function uuid_to_bin
27 INSERT INTO uuid_table(bin) VALUES ( UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c}'));
28 ERROR HY000: Incorrect string value: '{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c}' for function uuid_to_bin
29 INSERT INTO uuid_table(bin) VALUES ( UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2e6-a0b6b4d5c7}'));
30 ERROR HY000: Incorrect string value: '{c8eb4b15-cb09-48bb-bbb2e6-a0b6b4d5c7}' for function uuid_to_bin
31 INSERT INTO uuid_table(bin) VALUES ( UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d}5c7'));
32 ERROR HY000: Incorrect string value: '{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d}5c7' for function uuid_to_bin
33 include/sync_slave_sql_with_master.inc
34 [Connection Slave]
35 # check whether table was created and uuids were inserted on slave
36 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
37 FROM uuid_table
38 WHERE pkey <= 2;
39 a b c d e
40 12345678-1234-5678-1234-567812345678 12345678-1234-5678-1234-567812345678 12345678-1234-5678-1234-567812345678 12345678-1234-5678-1234-567812345678 12345678-1234-5678-1234-567812345678
41 12345678-1234-5678-1234-567812345678 12345678-1234-5678-1234-567812345678 12345678-1234-5678-1234-567812345678 12345678-1234-5678-1234-567812345678 12345678-1234-5678-1234-567812345678
42 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
43 FROM uuid_table
44 WHERE pkey = 3;
45 a b c d e
46 CB0948BB4B15C8EBBBB2E6A0B6B4D5C7 cb0948bb-4b15-c8eb-bbb2-e6a0b6b4d5c7 CB0948BB4B15C8EBBBB2E6A0B6B4D5C7 cb0948bb-4b15-c8eb-bbb2-e6a0b6b4d5c7 C8EB4B15CB0948BBBBB2E6A0B6B4D5C7
47 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
48 FROM uuid_table
49 WHERE pkey <= 3;
50 a b c d e
51 1 1 1 1 1
52 1 1 1 1 1
53 1 1 1 1 1
54 include/diff_tables.inc [master:uuid_table, slave:uuid_table]
55 [Connection Master]
56 # Update values in table
57 UPDATE uuid_table SET bin = (UUID_TO_BIN('c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7', IS_UUID('12345678-1234-5678-1234-567812345678')))
58 WHERE pkey = 1;
59 include/sync_slave_sql_with_master.inc
60 [Connection Slave]
61 # Check whether value is updated on slave
62 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)))
63 FROM uuid_table
64 WHERE pkey = 1;
65 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)))
66 48BBCB09C8EB4B15BBB2E6A0B6B4D5C7 12345678-1234-5678-1234-567812345678 12345678123456781234567812345678 12345678-1234-5678-1234-567812345678 12345678123456781234567812345678
67 include/diff_tables.inc [master:uuid_table, slave:uuid_table]
68 [Connection Master]
69 # Delete a row from the table
70 DELETE FROM uuid_table WHERE blb = UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}');
71 include/sync_slave_sql_with_master.inc
72 [Connection Slave]
73 # Check whether row has been deleted
74 SELECT * FROM uuid_table WHERE pkey = 3;
75 pkey bin vbn tbl ttx blb
76 include/diff_tables.inc [master:uuid_table, slave:uuid_table]
77 [Connection Master]
78 # Create a table and insert invalid values of uuid
79 CREATE TABLE invalid_uuid ( pkey INT NOT NULL PRIMARY KEY AUTO_INCREMENT, col VARCHAR(50) );
80 INSERT into invalid_uuid VALUES ( NULL, '1234567812345678123456781234567' ),
81 ( NULL, '1234-5678-1234-567812345678-12345678' ),
82 ( NULL, '123456781234567812345678123456789' ),
83 ( NULL, '12345678-1234-5678-1234567812345678' ),
84 ( NULL, '{123456781234567812345678123456}78' );
85 include/sync_slave_sql_with_master.inc
86 [Connection Slave]
87 # Check whether table has been created on slave
88 SELECT BIN_TO_UUID(UNHEX(col)) FROM invalid_uuid;
89 ERROR HY000: Incorrect string value: '\x01#Eg\x81#Eg\x81#Eg\x81#Eg\x89' for function bin_to_uuid
90 include/diff_tables.inc [master:invalid_uuid, slave:invalid_uuid]
91 [Connection Master]
92 # create table with generated column
93 CREATE TABLE uuid_gencol (col1 VARCHAR(100), gcol2 BINARY(16) AS (UUID_TO_BIN(col1)) VIRTUAL, INDEX(gcol2));
94 INSERT INTO uuid_gencol (col1) VALUES
95 ('{12345678-1234-5678-1234-567812345678}'),
96 ('12345679123456781234567812345678'),
97 ('12345670-1234-5678-1234-567812345678');
98 include/sync_slave_sql_with_master.inc
99 [Connection Slave]
100 # Check whether table is created
101 EXPLAIN SELECT * FROM uuid_gencol WHERE gcol2=x'12345679123456781234567812345678';
102 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
103 1 SIMPLE uuid_gencol NULL ref gcol2 gcol2 17 const 1 100.00 Using where
104 Warnings:
105 Note 1003 /* select#1 */ select `test`.`uuid_gencol`.`col1` AS `col1`,`test`.`uuid_gencol`.`gcol2` AS `gcol2` from `test`.`uuid_gencol` where (`test`.`uuid_gencol`.`gcol2` = 0x12345679123456781234567812345678)
106 SELECT col1, BIN_TO_UUID(gcol2) FROM uuid_gencol WHERE gcol2=x'12345679123456781234567812345678';
107 col1 BIN_TO_UUID(gcol2)
108 12345679123456781234567812345678 12345679-1234-5678-1234-567812345678
109 EXPLAIN SELECT * FROM uuid_gencol WHERE UUID_TO_BIN(col1)=x'12345679123456781234567812345678';
110 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
111 1 SIMPLE uuid_gencol NULL ref gcol2 gcol2 17 const 1 100.00 Using where
112 Warnings:
113 Note 1003 /* select#1 */ select `test`.`uuid_gencol`.`col1` AS `col1`,`test`.`uuid_gencol`.`gcol2` AS `gcol2` from `test`.`uuid_gencol` where (`test`.`uuid_gencol`.`gcol2` = 0x12345679123456781234567812345678)
114 SELECT col1, HEX(UUID_TO_BIN(BIN_TO_UUID(gcol2))) FROM uuid_gencol WHERE UUID_TO_BIN(col1)=x'12345679123456781234567812345678';
115 col1 HEX(UUID_TO_BIN(BIN_TO_UUID(gcol2)))
116 12345679123456781234567812345678 12345679123456781234567812345678
117 include/diff_tables.inc [master:uuid_gencol, slave:uuid_gencol]
118 [Connection Master]
119 # Drop tables and clean up
120 DROP TABLE uuid_table;
121 DROP TABLE invalid_uuid;
122 DROP TABLE uuid_gencol;
123 include/sync_slave_sql_with_master.inc
124 [Connection Slave]
125 # Check whether table is dropped
126 SELECT * FROM uuid_table;
127 ERROR 42S02: Table 'test.uuid_table' doesn't exist
128 include/rpl_end.inc