Debian Codegraph @

mysql-test/suite/binlog_nogtid/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_example_plugin.inc
25 --source include/have_log_bin.inc
26 --source include/no_ps_protocol.inc
27
28 SET @old_binlog_format= @@global.binlog_format;
29 --replace_regex /\.dll/.so/
30 eval INSTALL PLUGIN example SONAME '$EXAMPLE_PLUGIN';
31
32 --echo ################################################################################
33 --echo # Verifies if ER_BINLOG_STMT_MODE_AND_ROW_ENGINE happens by setting the binlog
34 --echo # format to STATEMENT and the transaction isolation level to READ COMMITTED as
35 --echo # such changes force Innodb to accept changes in the row format.
36 --echo #
37 --echo # When CREATE TABLE, ALTER TABLE, CREATE INDEX and CREATE TRIGGER are executed
38 --echo # any error should be triggered.
39 --echo #
40 --echo # In contrast, CREATE TABLE ... SELECT should trigger the following error:
41 --echo # ER_BINLOG_STMT_MODE_AND_ROW_ENGINE.
42 --echo ################################################################################
43 SET binlog_format = STATEMENT;
44 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
45 CREATE TABLE t_row (a VARCHAR(100)) ENGINE = InnoDB;
46
47 ALTER TABLE t_row ADD COLUMN b INT;
48
49 CREATE TRIGGER trig_row BEFORE INSERT ON t_row FOR EACH ROW INSERT INTO t_stmt VALUES (1);
50
51 CREATE INDEX i ON t_row(a);
52
53 --error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE
54 CREATE TABLE t_row_new ENGINE = InnoDB SELECT * FROM t_row;
55
56 DROP TABLE t_row;
57
58 --echo
59 --echo
60
61 --echo ################################################################################
62 --echo # Verifies if ER_BINLOG_ROW_MODE_AND_STMT_ENGINE happens by setting the binlog
63 --echo # format to ROW and using a engine, i.e. EXAMPLE, that only supports STATEMENT.
64 --echo #
65 --echo # When CREATE TABLE, ALTER TABLE, CREATE INDEX and CREATE TRIGGER are executed
66 --echo # the error ER_BINLOG_ROW_MODE_AND_STMT_ENGINE is not triggered. Note that other
67 --echo # errors are triggered due to restrictions in the engine.
68 --echo #
69 --echo # In contrast, CREATE TABLE ... SELECT should trigger the following error:
70 --echo # ER_BINLOG_ROW_MODE_AND_STMT_ENGINE.
71 --echo ################################################################################
72 SET binlog_format = 'ROW';
73 CREATE TABLE t_stmt (a VARCHAR(100)) ENGINE = EXAMPLE;
74
75 --error ER_NOT_SUPPORTED_YET
76 ALTER TABLE t_stmt ADD COLUMN b INT;
77
78 CREATE TRIGGER trig_stmt BEFORE INSERT ON t_stmt FOR EACH ROW INSERT INTO t_stmt VALUES (1);
79
80 --error ER_TOO_MANY_KEYS
81 CREATE INDEX i ON t_stmt(a);
82
83 --error ER_BINLOG_ROW_MODE_AND_STMT_ENGINE
84 CREATE TABLE t_stmt_new ENGINE = EXAMPLE SELECT * FROM t_stmt;
85
86 DROP TABLE t_stmt;
87
88 --echo
89 --echo
90
91 --echo ################################################################################
92 --echo # CLEAN UP #
93 --echo ################################################################################
94 flush tables;
95 UNINSTALL PLUGIN example;
96 SET @@global.binlog_format = @old_binlog_format;
97 SET @@session.binlog_format = @old_binlog_format;