Debian Codegraph @

mysql-test/suite/rpl_nogtid/r/rpl_binlog_format_errors.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 ==== Initialize ====
7 [on slave]
8 [connection slave]
9 SET @old_binlog_format= @@global.binlog_format;
10 INSTALL PLUGIN example SONAME 'ha_example.so';
11 [on master]
12 [connection master]
13 SET @old_binlog_format= @@global.binlog_format;
14 INSTALL PLUGIN example SONAME 'ha_example.so';
15 CREATE TABLE t (a VARCHAR(100)) ENGINE = MYISAM;
16 CREATE TABLE t_row (a VARCHAR(100)) ENGINE = INNODB;
17 CREATE TABLE t_stmt (a VARCHAR(100)) ENGINE = EXAMPLE;
18 CREATE TABLE t_slave_stmt (a VARCHAR(100)) ENGINE = MYISAM;
19 CREATE TABLE t_autoinc (a INT KEY AUTO_INCREMENT) ENGINE = MYISAM;
20 CREATE TABLE t_double_autoinc (a INT KEY AUTO_INCREMENT) ENGINE = MYISAM;
21 CREATE TRIGGER trig_autoinc BEFORE INSERT ON t_autoinc FOR EACH ROW BEGIN INSERT INTO t_stmt VALUES ('x'); END;
22 CREATE TRIGGER trig_double_autoinc BEFORE INSERT ON t_double_autoinc FOR EACH ROW BEGIN INSERT INTO t_autoinc VALUES (NULL); END;
23 CREATE DATABASE other;
24 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
25 [on slave]
26 include/sync_slave_sql_with_master.inc
27 DROP TABLE t_slave_stmt;
28 CREATE TABLE t_slave_stmt (a INT) ENGINE = EXAMPLE;
29 [on master]
30 [connection master]
31 BINLOG '
32 1gRVSg8BAAAAZgAAAGoAAAABAAQANS4xLjM2LWRlYnVnLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAA
33 AAAAAAAAAAAAAAAAAADWBFVKEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
34 ';
35 ==== Test ====
36 ---- binlog_format='row' ----
37 * TC1: Modify both row-only and stmt-only table
38 CREATE TRIGGER trig_2 AFTER INSERT ON t_stmt FOR EACH ROW BEGIN INSERT INTO t_row VALUES(1); END;
39 INSERT INTO t_stmt VALUES (1);
40 ERROR HY000: Cannot execute statement: impossible to write to binary log since both row-incapable engines and statement-incapable engines are involved.
41 SELECT * FROM t_stmt /* should be empty */;
42 a
43 SELECT * FROM t_row /* should be empty */;
44 a
45 DROP TRIGGER trig_2;
46 * TC2: Stmt-only table and binlog_format='row'
47 INSERT INTO t_stmt VALUES (1);
48 ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = ROW and at least one table uses a storage engine limited to statement-based logging.
49 SELECT * FROM t_stmt /* should be empty */;
50 a
51 * TC3: Row injection and stmt-only table: in slave sql thread
52 INSERT INTO t_slave_stmt VALUES (1);
53 [on slave]
54 [connection slave]
55 include/wait_for_slave_sql_error_and_skip.inc [errno=1664]
56 Last_SQL_Error = 'Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and at least one table uses a storage engine limited to statement-based logging.''
57 [connection slave]
58 SELECT * FROM t_slave_stmt /* should be empty */;
59 a
60 [on master]
61 [connection master]
62 * TC4: Row injection and stmt-only table: use BINLOG statement
63 BINLOG '
64 1gRVShMBAAAALwAAAEABAAAAABcAAAAAAAAABHRlc3QABnRfc3RtdAABDwJkAAE=
65 1gRVShcBAAAAIAAAAGABAAAQABcAAAAAAAEAAf/+ATE=
66 ';
67 ERROR HY000: Cannot execute statement: impossible to write to binary log since statement is in row format and at least one table uses a storage engine limited to statement-based logging.
68 SELECT * FROM t_stmt /* should be empty */;
69 a
70 ---- binlog_format=mixed ----
71 [on slave]
72 include/sync_slave_sql_with_master.inc
73 include/stop_slave.inc
74 SET @@global.binlog_format = MIXED;
75 include/start_slave.inc
76 [on master]
77 [connection master]
78 SET @@global.binlog_format = MIXED;
79 SET @@session.binlog_format = MIXED;
80 * TC5: Unsafe statement and stmt-only engine
81 INSERT INTO t_stmt VALUES (UUID());
82 ERROR HY000: Cannot execute statement: impossible to write to binary log since statement is unsafe, storage engine is limited to statement-based logging, and BINLOG_FORMAT = MIXED. Statement is unsafe because it uses a system function that may return a different value on the replica.
83 SELECT * FROM t_stmt;
84 a
85 /* should be empty */;
86 * TC6: Multi-unsafe statement and stmt-only engine
87 INSERT INTO t_double_autoinc SELECT CONCAT(UUID(), @@hostname, NULL) FROM mysql.general_log LIMIT 1;
88 ERROR HY000: Cannot execute statement: impossible to write to binary log since statement is unsafe, storage engine is limited to statement-based logging, and BINLOG_FORMAT = MIXED. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
89 SELECT * FROM t_double_autoinc;
90 a
91 /* should be empty */;
92 ---- binlog_format=statement ----
93 [on slave]
94 include/sync_slave_sql_with_master.inc
95 include/stop_slave.inc
96 SET @@global.binlog_format = STATEMENT;
97 include/start_slave.inc
98 [on master]
99 [connection master]
100 SET @@global.binlog_format = STATEMENT;
101 SET @@session.binlog_format = STATEMENT;
102 * TC7: Row-only engine and binlog_format=statement: innodb-specific message
103 INSERT INTO t_row VALUES (1);
104 ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
105 SELECT * FROM t_row /* should be empty */;
106 a
107 * TC8: Same statement, but db filtered out - no error
108 USE other;
109 INSERT INTO test.t_row VALUES (1);
110 USE test;
111 SELECT * FROM t_row /* should contain the value 1 */;
112 a
113 1
114 USE other;
115 DELETE FROM test.t_row;
116 USE test;
117 * TC9: Row-only engine and binlog_format=statement: generic message
118 SET @@session.debug= '+d,no_innodb_binlog_errors';
119 INSERT INTO t_row VALUES (1);
120 ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging.
121 SELECT * FROM t_row /* should be empty */;
122 a
123 * TC10: Same statement, but db filtered out - no error
124 USE other;
125 INSERT INTO test.t_row VALUES (1);
126 USE test;
127 SET @@session.debug= '';
128 SELECT * FROM t_row /* should contain the value 1 */;
129 a
130 1
131 * TC11: Row injection and binlog_format=statement: BINLOG statement
132 BINLOG '
133 cNpVShMBAAAAKgAAADYBAAAAABcAAAAAAAAABHRlc3QAAXQAAQ8CZAAB
134 cNpVShcBAAAAIAAAAFYBAAAQABcAAAAAAAEAAf/+ATE=
135 ';
136 ERROR HY000: Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.
137 SELECT * FROM t /* should be empty */;
138 a
139 * TC13: Unsafe statement and binlog_format=statement
140 INSERT INTO t VALUES (COALESCE(1, UUID()));
141 Warnings:
142 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the replica.
143 SELECT * FROM t /* should contain the value 1 */;
144 a
145 1
146 DELETE FROM t;
147 * TC14: Same statement, but db filtered out - no message
148 USE other;
149 INSERT INTO test.t VALUES (COALESCE(1, UUID()));
150 USE test;
151 SELECT * FROM t /* should contain the value 1 */;
152 a
153 1
154 DELETE FROM t;
155 ---- master: binlog_format=mixed, slave: binlog_format=statement ----
156 SET @@global.binlog_format = MIXED;
157 SET @@session.binlog_format = MIXED;
158 * TC15: Row injection and binlog_format=statement: in slave sql thread
159 INSERT INTO t VALUES (COALESCE(1, UUID()));
160 [on slave]
161 [connection slave]
162 include/wait_for_slave_sql_error_and_skip.inc [errno=1666]
163 Last_SQL_Error = 'Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.''
164 [connection slave]
165 SELECT * FROM t /* should be empty */;
166 a
167 [on master]
168 [connection master]
169 ==== Clean up ====
170 DROP TRIGGER trig_autoinc;
171 DROP TRIGGER trig_double_autoinc;
172 DROP TABLE t, t_row, t_stmt, t_slave_stmt, t_autoinc, t_double_autoinc;
173 DROP DATABASE other;
174 SET @@global.binlog_format = @old_binlog_format;
175 SET @@session.binlog_format = @old_binlog_format;
176 UNINSTALL PLUGIN example;
177 [on slave]
178 include/sync_slave_sql_with_master.inc
179 include/stop_slave.inc
180 SET @@global.binlog_format = @old_binlog_format;
181 SET @@session.binlog_format = @old_binlog_format;
182 include/start_slave.inc
183 UNINSTALL PLUGIN example;
184 include/rpl_end.inc