Debian Codegraph @

mysql-test/suite/binlog/t/binlog_spurious_ddl_errors.test

raw
1 ################################################################################
2 # BUG#50479 DDL stmt on row-only/stmt-only tables generate spurious
3 # binlog_format errors
4 #
5 # In the fix of BUG#39934 in 5.1-rep+3, errors are generated when
6 # binlog_format=row and a statement modifies a table restricted to
7 # statement-logging (ER_BINLOG_ROW_MODE_AND_STMT_ENGINE); or if
8 # binlog_format=statement and a statement modifies a table limited to
9 # row-logging (ER_BINLOG_STMT_MODE_AND_ROW_ENGINE).
10 #
11 # In this test case, we check if some DDL statements that lock tables do not
12 # trigger errors as they do not generate rows events and as such are harmless
13 # from the point of view of conflicts between the engine's supported logging
14 # format and the value of binlog_format.
15 #
16 # In particular, we check if:
17 # 1 - ALTER TABLE, CREATE INDEX and CREATE TRIGGER do not generate either
18 # ER_BINLOG_STMT_MODE_AND_ROW_ENGINE or ER_BINLOG_STMT_MODE_AND_ROW_ENGINE
19 #
20 # 2 - CREATE TABLE ... SELECT generates an error because the command can
21 # generate row events but CREATE TABLE without SELECT does not generate
22 # an error.
23 ################################################################################
24 --source include/have_innodb.inc
25 --source include/have_example_plugin.inc
26 --source include/have_log_bin.inc
27
28 SET @old_binlog_format= @@global.binlog_format;
29 INSTALL PLUGIN example SONAME 'ha_example';
30
31 --echo ################################################################################
32 --echo # Verifies if ER_BINLOG_STMT_MODE_AND_ROW_ENGINE happens by setting the binlog
33 --echo # format to STATEMENT and the transaction isolation level to READ COMMITTED as
34 --echo # such changes force Innodb to accept changes in the row format.
35 --echo #
36 --echo # When CREATE TABLE, ALTER TABLE, CREATE INDEX and CREATE TRIGGER are executed
37 --echo # any error should be triggered.
38 --echo #
39 --echo # In contrast, CREATE TABLE ... SELECT should trigger the following error:
40 --echo # ER_BINLOG_STMT_MODE_AND_ROW_ENGINE.
41 --echo ################################################################################
42 SET binlog_format = STATEMENT;
43 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
44 CREATE TABLE t_row (a VARCHAR(100)) ENGINE = InnoDB;
45
46 ALTER TABLE t_row ADD COLUMN b INT;
47
48 CREATE TRIGGER trig_row BEFORE INSERT ON t_row FOR EACH ROW INSERT INTO t_stmt VALUES (1);
49
50 CREATE INDEX i ON t_row(a);
51
52 --error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE
53 CREATE TABLE t_row_new ENGINE = InnoDB SELECT * FROM t_row;
54
55 DROP TABLE t_row;
56
57 --echo
58 --echo
59
60 --echo ################################################################################
61 --echo # Verifies if ER_BINLOG_ROW_MODE_AND_STMT_ENGINE happens by setting the binlog
62 --echo # format to ROW and using a engine, i.e. EXAMPLE, that only supports STATEMENT.
63 --echo #
64 --echo # When CREATE TABLE, ALTER TABLE, CREATE INDEX and CREATE TRIGGER are executed
65 --echo # the error ER_BINLOG_ROW_MODE_AND_STMT_ENGINE is not triggered. Note that other
66 --echo # errors are triggered due to restrictions in the engine.
67 --echo #
68 --echo # In contrast, CREATE TABLE ... SELECT should trigger the following error:
69 --echo # ER_BINLOG_ROW_MODE_AND_STMT_ENGINE.
70 --echo ################################################################################
71 SET binlog_format = ROW;
72 CREATE TABLE t_stmt (a VARCHAR(100)) ENGINE = EXAMPLE;
73
74 ALTER TABLE t_stmt ADD COLUMN b INT;
75
76 CREATE TRIGGER trig_stmt BEFORE INSERT ON t_stmt FOR EACH ROW INSERT INTO t_stmt VALUES (1);
77
78 --error ER_TOO_LONG_KEY
79 CREATE INDEX i ON t_stmt(a);
80
81 --error ER_BINLOG_ROW_MODE_AND_STMT_ENGINE
82 CREATE TABLE t_stmt_new ENGINE = EXAMPLE SELECT * FROM t_stmt;
83
84 DROP TABLE t_stmt;
85
86 --echo
87 --echo
88
89 --echo ################################################################################
90 --echo # CLEAN UP #
91 --echo ################################################################################
92 flush tables;
93 UNINSTALL PLUGIN example;
94 SET @@global.binlog_format = @old_binlog_format;
95 SET @@session.binlog_format = @old_binlog_format;