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
|
include/master-slave.inc
Warnings:
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
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.
[connection master]
# Create table with columns of binary data types and insert uuid values
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 );
INSERT INTO uuid_table VALUES( NULL, UUID_TO_BIN('12345678123456781234567812345678'),
UUID_TO_BIN('12345678123456781234567812345678'),
UUID_TO_BIN('12345678123456781234567812345678'),
UUID_TO_BIN('12345678123456781234567812345678'),
UUID_TO_BIN('12345678123456781234567812345678'));
INSERT INTO uuid_table VALUES( NULL, UUID_TO_BIN('12345678-1234-5678-1234-567812345678'),
UUID_TO_BIN('12345678-1234-5678-1234-567812345678'),
UUID_TO_BIN('12345678-1234-5678-1234-567812345678'),
UUID_TO_BIN('12345678-1234-5678-1234-567812345678'),
UUID_TO_BIN('12345678-1234-5678-1234-567812345678'));
INSERT INTO uuid_table VALUES( NULL, UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}'),
UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}'),
UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}'),
UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}'),
UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}'));
INSERT INTO uuid_table(bin) VALUES ( UUID_TO_BIN('{c8eb4b15cb09-48bb-bbb2-e6a0b6b4d5c7}'));
ERROR HY000: Incorrect string value: '{c8eb4b15cb09-48bb-bbb2-e6a0b6b4d5c7}' for function uuid_to_bin
INSERT INTO uuid_table(bin) VALUES ( UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c76}'));
ERROR HY000: Incorrect string value: '{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c76}' for function uuid_to_bin
INSERT INTO uuid_table(bin) VALUES ( UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c}'));
ERROR HY000: Incorrect string value: '{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c}' for function uuid_to_bin
INSERT INTO uuid_table(bin) VALUES ( UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2e6-a0b6b4d5c7}'));
ERROR HY000: Incorrect string value: '{c8eb4b15-cb09-48bb-bbb2e6-a0b6b4d5c7}' for function uuid_to_bin
INSERT INTO uuid_table(bin) VALUES ( UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d}5c7'));
ERROR HY000: Incorrect string value: '{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d}5c7' for function uuid_to_bin
include/sync_slave_sql_with_master.inc
[Connection Slave]
# check whether table was created and uuids were inserted on slave
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
FROM uuid_table
WHERE pkey <= 2;
a b c d e
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
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
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
FROM uuid_table
WHERE pkey = 3;
a b c d e
CB0948BB4B15C8EBBBB2E6A0B6B4D5C7 cb0948bb-4b15-c8eb-bbb2-e6a0b6b4d5c7 CB0948BB4B15C8EBBBB2E6A0B6B4D5C7 cb0948bb-4b15-c8eb-bbb2-e6a0b6b4d5c7 C8EB4B15CB0948BBBBB2E6A0B6B4D5C7
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
FROM uuid_table
WHERE pkey <= 3;
a b c d e
1 1 1 1 1
1 1 1 1 1
1 1 1 1 1
include/diff_tables.inc [master:uuid_table, slave:uuid_table]
[Connection Master]
# Update values in table
UPDATE uuid_table SET bin = (UUID_TO_BIN('c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7', IS_UUID('12345678-1234-5678-1234-567812345678')))
WHERE pkey = 1;
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether value is updated on slave
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)))
FROM uuid_table
WHERE pkey = 1;
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)))
48BBCB09C8EB4B15BBB2E6A0B6B4D5C7 12345678-1234-5678-1234-567812345678 12345678123456781234567812345678 12345678-1234-5678-1234-567812345678 12345678123456781234567812345678
include/diff_tables.inc [master:uuid_table, slave:uuid_table]
[Connection Master]
# Delete a row from the table
DELETE FROM uuid_table WHERE blb = UUID_TO_BIN('{c8eb4b15-cb09-48bb-bbb2-e6a0b6b4d5c7}');
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether row has been deleted
SELECT * FROM uuid_table WHERE pkey = 3;
pkey bin vbn tbl ttx blb
include/diff_tables.inc [master:uuid_table, slave:uuid_table]
[Connection Master]
# Create a table and insert invalid values of uuid
CREATE TABLE invalid_uuid ( pkey INT NOT NULL PRIMARY KEY AUTO_INCREMENT, col VARCHAR(50) );
INSERT into invalid_uuid VALUES ( NULL, '1234567812345678123456781234567' ),
( NULL, '1234-5678-1234-567812345678-12345678' ),
( NULL, '123456781234567812345678123456789' ),
( NULL, '12345678-1234-5678-1234567812345678' ),
( NULL, '{123456781234567812345678123456}78' );
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether table has been created on slave
SELECT BIN_TO_UUID(UNHEX(col)) FROM invalid_uuid;
ERROR HY000: Incorrect string value: '\x01#Eg\x81#Eg\x81#Eg\x81#Eg\x89' for function bin_to_uuid
include/diff_tables.inc [master:invalid_uuid, slave:invalid_uuid]
[Connection Master]
# create table with generated column
CREATE TABLE uuid_gencol (col1 VARCHAR(100), gcol2 BINARY(16) AS (UUID_TO_BIN(col1)) VIRTUAL, INDEX(gcol2));
INSERT INTO uuid_gencol (col1) VALUES
('{12345678-1234-5678-1234-567812345678}'),
('12345679123456781234567812345678'),
('12345670-1234-5678-1234-567812345678');
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether table is created
EXPLAIN SELECT * FROM uuid_gencol WHERE gcol2=x'12345679123456781234567812345678';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE uuid_gencol NULL ref gcol2 gcol2 17 const 1 100.00 Using where
Warnings:
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)
SELECT col1, BIN_TO_UUID(gcol2) FROM uuid_gencol WHERE gcol2=x'12345679123456781234567812345678';
col1 BIN_TO_UUID(gcol2)
12345679123456781234567812345678 12345679-1234-5678-1234-567812345678
EXPLAIN SELECT * FROM uuid_gencol WHERE UUID_TO_BIN(col1)=x'12345679123456781234567812345678';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE uuid_gencol NULL ref gcol2 gcol2 17 const 1 100.00 Using where
Warnings:
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)
SELECT col1, HEX(UUID_TO_BIN(BIN_TO_UUID(gcol2))) FROM uuid_gencol WHERE UUID_TO_BIN(col1)=x'12345679123456781234567812345678';
col1 HEX(UUID_TO_BIN(BIN_TO_UUID(gcol2)))
12345679123456781234567812345678 12345679123456781234567812345678
include/diff_tables.inc [master:uuid_gencol, slave:uuid_gencol]
[Connection Master]
# Drop tables and clean up
DROP TABLE uuid_table;
DROP TABLE invalid_uuid;
DROP TABLE uuid_gencol;
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether table is dropped
SELECT * FROM uuid_table;
ERROR 42S02: Table 'test.uuid_table' doesn't exist
include/rpl_end.inc
|
