Debian Codegraph @

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

raw
1 # === Purpose ===
2 #
3 # This test verifies that changes to the SQL mode are also replicated
4 # ensuring the replica has the same data as the source
5 #
6 # ==== Requirements ====
7 #
8 # R1. When use row based replication, the SQL mode from the source should also
9 # be used on the replica
10 #
11 # === Implementation ====
12 #
13 # 1. Create a table where a generated column has the type TIME
14 # 2. Change the source SQL mode to 'TIME_TRUNCATE_FRACTIONAL'
15 # 3. Insert data on the source being the value of the generated column dependent on the SQL mode
16 # 4. Delete the row on the source and verify it was deleted on the replica
17 # 5. Cleanup
18 #
19 # === References ===
20 #
21 # Bug #33945038: SQL mode is sometimes over-written in row based replication
22 #
23
24 --source include/have_binlog_format_row.inc
25 --source include/master-slave.inc
26
27 --echo
28 --echo ##############################################################
29 --echo # 1. Create a table where a generated column has the type TIME
30
31 --source include/rpl_connection_master.inc
32
33 CREATE TABLE t1 (
34 first DOUBLE,
35 gen_col TIME(1) GENERATED ALWAYS AS (`first`) VIRTUAL,
36 KEY (gen_col)
37 ) ENGINE=InnoDB;
38
39
40 --echo
41 --echo ##############################################################
42 --echo # 2. Change the source SQL mode to 'TIME_TRUNCATE_FRACTIONAL'
43
44 #
45 # To understand what is the effect of this mode, here is the docs example
46 #
47 # CREATE TABLE t (id INT, tval TIME(1));
48 # SET sql_mode='';
49 # INSERT INTO t (id, tval) VALUES(1, 1.55);
50 # SET sql_mode='TIME_TRUNCATE_FRACTIONAL';
51 # INSERT INTO t (id, tval) VALUES(2, 1.55);
52 #
53 # mysql> SELECT id, tval FROM t ORDER BY id;
54 # +------+------------+
55 # | id | tval |
56 # +------+------------+
57 # | 1 | 00:00:01.6 |
58 # | 2 | 00:00:01.5 |
59 # +------+------------+
60 #
61
62 SET sql_mode='TIME_TRUNCATE_FRACTIONAL';
63
64 --echo
65 --echo ##############################################################
66 --echo # 3. Insert data on the source being the value of the generated column dependent on the SQL mode
67
68 INSERT INTO t1 (first) VALUES(1.55);
69
70 --echo
71 --echo ##############################################################
72 --echo # 4. Delete the row on the source and verify it was deleted on the replica
73
74 DELETE FROM t1 WHERE gen_col="00:00:01.5";
75
76 --source include/sync_slave_sql_with_master.inc
77
78 --source include/rpl_connection_slave.inc
79
80 #
81 # To understand what divergences in SQL mode would do:
82 # When inserted in the replica the row will be indexed to the value of `gen_col`
83 #`When deleted, the replica will search for the value of "00:00:01.5", but
84 # the table index would have the value "00:00:01.6" if the sql mode was different.
85 # The delete would not find the row and no data would be removed in the replica.
86 #
87
88 --let $table_data_count = `SELECT COUNT(*) FROM t1`
89 --let $assert_text= The data on the replica was deleted
90 --let $assert_cond= $table_data_count = 0
91 --source include/assert.inc
92
93 --echo
94 --echo ##############################################################
95 --echo # 5. Cleanup
96
97 --source include/rpl_connection_master.inc
98
99 DROP TABLE t1;
100
101 --source include/rpl_end.inc