Debian Codegraph @

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

raw
1 source include/have_innodb.inc;
2 source include/have_log_bin.inc;
3
4 SET BINLOG_FORMAT=MIXED;
5
6 RESET MASTER;
7
8 CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB;
9 INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
10
11 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
12 BEGIN;
13 # Should be logged as statement
14 UPDATE t1 SET b = 2*a WHERE a > 1;
15 COMMIT;
16
17 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
18 BEGIN;
19 # Should be logged as rows
20 UPDATE t1 SET b = a * a WHERE a > 3;
21 COMMIT;
22
23 # Check that errors are generated when trying to use READ COMMITTED
24 # transaction isolation level in STATEMENT binlog mode.
25
26 SET BINLOG_FORMAT=STATEMENT;
27
28 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
29 BEGIN;
30 error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE;
31 UPDATE t1 SET b = 1*a WHERE a > 1;
32 COMMIT;
33
34 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
35 BEGIN;
36 error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE;
37 UPDATE t1 SET b = 2*a WHERE a > 2;
38 COMMIT;
39
40 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
41 BEGIN;
42 UPDATE t1 SET b = 3*a WHERE a > 3;
43 COMMIT;
44
45 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
46 BEGIN;
47 UPDATE t1 SET b = 4*a WHERE a > 4;
48 COMMIT;
49
50 SET BINLOG_FORMAT=MIXED;
51
52 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
53 BEGIN;
54 UPDATE t1 SET b = 1*a WHERE a > 1;
55 COMMIT;
56
57 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
58 BEGIN;
59 UPDATE t1 SET b = 2*a WHERE a > 2;
60 COMMIT;
61
62 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
63 BEGIN;
64 UPDATE t1 SET b = 3*a WHERE a > 3;
65 COMMIT;
66
67 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
68 BEGIN;
69 UPDATE t1 SET b = 4*a WHERE a > 4;
70 COMMIT;
71
72 SET BINLOG_FORMAT=ROW;
73
74 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
75 BEGIN;
76 UPDATE t1 SET b = 1*a WHERE a > 1;
77 COMMIT;
78
79 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
80 BEGIN;
81 UPDATE t1 SET b = 2*a WHERE a > 2;
82 COMMIT;
83
84 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
85 BEGIN;
86 UPDATE t1 SET b = 3*a WHERE a > 3;
87 COMMIT;
88
89 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
90 BEGIN;
91 UPDATE t1 SET b = 4*a WHERE a > 4;
92 COMMIT;
93
94 source include/show_binlog_events.inc;
95
96 DROP TABLE t1;
97
98
99 #
100 # Let us test binlog_cache_use and binlog_cache_disk_use status vars.
101 # Actually this test has nothing to do with innodb per se, it just requires
102 # transactional table.
103 #
104 flush global status;
105 show status like "binlog_cache_use";
106 show status like "binlog_cache_disk_use";
107
108 create table t1 (a int) engine=innodb;
109
110 # Now we are going to create transaction which is long enough so its
111 # transaction binlog will be flushed to disk...
112 let $1=2000;
113 disable_query_log;
114 begin;
115 while ($1)
116 {
117 eval insert into t1 values( $1 );
118 dec $1;
119 }
120 commit;
121 enable_query_log;
122 show status like "binlog_cache_use";
123 show status like "binlog_cache_disk_use";
124
125 # Transaction which should not be flushed to disk and so should not
126 # increase binlog_cache_disk_use.
127 begin;
128 delete from t1;
129 commit;
130 show status like "binlog_cache_use";
131 show status like "binlog_cache_disk_use";
132 drop table t1;
133
134 #
135 # Bug#27716 multi-update did partially and has not binlogged
136 #
137
138 CREATE TABLE `t1` (
139 `a` int(11) NOT NULL auto_increment,
140 `b` int(11) default NULL,
141 PRIMARY KEY (`a`)
142 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
143
144 CREATE TABLE `t2` (
145 `a` int(11) NOT NULL auto_increment,
146 `b` int(11) default NULL,
147 PRIMARY KEY (`a`)
148 ) ENGINE=INNODB DEFAULT CHARSET=latin1 ;
149
150 # A. testing multi_update::send_eof() execution branch
151 insert into t1 values (1,1),(2,2);
152 insert into t2 values (1,1),(4,4);
153 reset master;
154 --error ER_DUP_ENTRY
155 UPDATE t2,t1 SET t2.a=t1.a+2;
156 # check
157 select * from t2 /* must be (3,1), (4,4) */;
158 --echo # There must no UPDATE in binlog;
159 source include/show_binlog_events.inc;
160
161 # B. testing multi_update::send_error() execution branch
162 delete from t1;
163 delete from t2;
164 insert into t1 values (1,2),(3,4),(4,4);
165 insert into t2 values (1,2),(3,4),(4,4);
166 reset master;
167 --error ER_DUP_ENTRY
168 UPDATE t2,t1 SET t2.a=t2.b where t2.a=t1.a;
169 --echo # There must be no UPDATE query event;
170 source include/show_binlog_events.inc;
171
172 # cleanup bug#27716
173 drop table t1, t2;
174
175 --echo *** MDEV-11937: InnoDB flushes redo log too often ***
176
177 # Count number of log fsyncs reported by InnoDB per commit.
178 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
179
180 SET @old_flush = @@GLOBAL.innodb_flush_log_at_trx_commit;
181 SET GLOBAL innodb_flush_log_at_trx_commit=1;
182
183 --let $syncs1 = query_get_value(SHOW STATUS LIKE 'Innodb_data_fsyncs', Value, 1)
184 --let $ROWS = 100
185 --disable_query_log
186 let $count = $ROWS;
187 while ($count) {
188 eval INSERT INTO t1 VALUES ($count);
189 dec $count;
190 }
191 --let $syncs2 = query_get_value(SHOW STATUS LIKE 'Innodb_data_fsyncs', Value, 1)
192 eval SET @num_sync = $syncs2 - $syncs1;
193 --enable_query_log
194
195 # Allow a bit of slack, in case some background process or something
196 # is introducing a few more syncs.
197 eval SELECT IF(@num_sync < $ROWS*1.5, "OK",
198 CONCAT("ERROR: More than 1 fsync per commit (saw ", @num_sync/$ROWS, ")")) AS status;
199
200 DROP TABLE t1;
201 SET GLOBAL innodb_flush_log_at_trx_commit=@old_flush;
202
203
204 --echo End of tests