Debian Codegraph @

mysql-test/suite/binlog/r/flashback.result

raw
1 ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
2 #
3 # Preparatory cleanup.
4 #
5 DROP TABLE IF EXISTS t1;
6 #
7 # We need a fixed timestamp to avoid varying results.
8 #
9 SET timestamp=1000000000;
10 # < CASE 1 >
11 # Delete all existing binary logs.
12 #
13 RESET MASTER;
14 CREATE TABLE t1 (
15 c01 tinyint,
16 c02 smallint,
17 c03 mediumint,
18 c04 int,
19 c05 bigint,
20 c06 char(10),
21 c07 varchar(20),
22 c08 TEXT
23 ) ENGINE=InnoDB;
24 # < CASE 1 >
25 # Insert data to t1
26 #
27 INSERT INTO t1 VALUES(0,0,0,0,0,'','','');
28 INSERT INTO t1 VALUES(1,2,3,4,5, "abc", "abcdefg", "abcedfghijklmnopqrstuvwxyz");
29 INSERT INTO t1 VALUES(127, 32767, 8388607, 2147483647, 9223372036854775807, repeat('a', 10), repeat('a', 20), repeat('a', 255));
30 # < CASE 1 >
31 # Update t1
32 #
33 UPDATE t1 SET c01=100 WHERE c02=0 OR c03=3;
34 # < CASE 1 >
35 # Clear t1
36 #
37 DELETE FROM t1;
38 FLUSH LOGS;
39 # < CASE 1 >
40 # Show mysqlbinlog result without -B
41 #
42 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
43 /*!40019 SET @@session.max_delayed_threads=0*/;
44 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
45 DELIMITER /*!*/;
46 # at #
47 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Start: binlog v 4, server v #.##.## created 010909 9:46:40 at startup
48 ROLLBACK/*!*/;
49 # at #
50 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Gtid list []
51 # at #
52 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Binlog checkpoint master-bin.000001
53 # at #
54 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX GTID 0-1-1 ddl thread_id=#
55 /*M!100101 SET @@session.skip_parallel_replication=0*//*!*/;
56 /*M!100001 SET @@session.gtid_domain_id=0*//*!*/;
57 /*M!100001 SET @@session.server_id=1*//*!*/;
58 /*M!100001 SET @@session.gtid_seq_no=1*//*!*/;
59 # at #
60 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Query thread_id=# exec_time=# error_code=0 xid=<xid>
61 use `test`/*!*/;
62 SET TIMESTAMP=1000000000/*!*/;
63 SET @@session.pseudo_thread_id=#/*!*/;
64 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_for_timestamp=1, @@session.system_versioning_insert_history=0/*!*/;
65 SET @@session.sql_mode=1411383296/*!*/;
66 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
67 /*!\C latin1 *//*!*/;
68 SET @@session.character_set_client=X,@@session.collation_connection=X,@@session.collation_server=X/*!*/;
69 SET @@session.lc_time_names=0/*!*/;
70 SET @@session.collation_database=DEFAULT/*!*/;
71 CREATE TABLE t1 (
72 c01 tinyint,
73 c02 smallint,
74 c03 mediumint,
75 c04 int,
76 c05 bigint,
77 c06 char(10),
78 c07 varchar(20),
79 c08 TEXT
80 ) ENGINE=InnoDB
81 /*!*/;
82 # at #
83 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX GTID 0-1-2 trans thread_id=#
84 /*M!100001 SET @@session.gtid_seq_no=2*//*!*/;
85 START TRANSACTION
86 /*!*/;
87 # at #
88 # at #
89 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Annotate_rows:
90 #Q> INSERT INTO t1 VALUES(0,0,0,0,0,'','','')
91 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Table_map: `test`.`t1` mapped to number #
92 # at #
93 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Write_rows: table id # flags: STMT_END_F
94 ### INSERT INTO `test`.`t1`
95 ### SET
96 ### @1=0 /* TINYINT meta=0 nullable=1 is_null=0 */
97 ### @2=0 /* SHORTINT meta=0 nullable=1 is_null=0 */
98 ### @3=0 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
99 ### @4=0 /* INT meta=0 nullable=1 is_null=0 */
100 ### @5=0 /* LONGINT meta=0 nullable=1 is_null=0 */
101 ### @6='' /* STRING(10) meta=65034 nullable=1 is_null=0 */
102 ### @7='' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
103 ### @8='' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
104 # Number of rows: 1
105 # at #
106 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Xid = #
107 COMMIT/*!*/;
108 # at #
109 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX GTID 0-1-3 trans thread_id=#
110 /*M!100001 SET @@session.gtid_seq_no=3*//*!*/;
111 START TRANSACTION
112 /*!*/;
113 # at #
114 # at #
115 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Annotate_rows:
116 #Q> INSERT INTO t1 VALUES(1,2,3,4,5, "abc", "abcdefg", "abcedfghijklmnopqrstuvwxyz")
117 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Table_map: `test`.`t1` mapped to number #
118 # at #
119 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Write_rows: table id # flags: STMT_END_F
120 ### INSERT INTO `test`.`t1`
121 ### SET
122 ### @1=1 /* TINYINT meta=0 nullable=1 is_null=0 */
123 ### @2=2 /* SHORTINT meta=0 nullable=1 is_null=0 */
124 ### @3=3 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
125 ### @4=4 /* INT meta=0 nullable=1 is_null=0 */
126 ### @5=5 /* LONGINT meta=0 nullable=1 is_null=0 */
127 ### @6='abc' /* STRING(10) meta=65034 nullable=1 is_null=0 */
128 ### @7='abcdefg' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
129 ### @8='abcedfghijklmnopqrstuvwxyz' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
130 # Number of rows: 1
131 # at #
132 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Xid = #
133 COMMIT/*!*/;
134 # at #
135 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX GTID 0-1-4 trans thread_id=#
136 /*M!100001 SET @@session.gtid_seq_no=4*//*!*/;
137 START TRANSACTION
138 /*!*/;
139 # at #
140 # at #
141 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Annotate_rows:
142 #Q> INSERT INTO t1 VALUES(127, 32767, 8388607, 2147483647, 9223372036854775807, repeat('a', 10), repeat('a', 20), repeat('a', 255))
143 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Table_map: `test`.`t1` mapped to number #
144 # at #
145 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Write_rows: table id # flags: STMT_END_F
146 ### INSERT INTO `test`.`t1`
147 ### SET
148 ### @1=127 /* TINYINT meta=0 nullable=1 is_null=0 */
149 ### @2=32767 /* SHORTINT meta=0 nullable=1 is_null=0 */
150 ### @3=8388607 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
151 ### @4=2147483647 /* INT meta=0 nullable=1 is_null=0 */
152 ### @5=9223372036854775807 /* LONGINT meta=0 nullable=1 is_null=0 */
153 ### @6='aaaaaaaaaa' /* STRING(10) meta=65034 nullable=1 is_null=0 */
154 ### @7='aaaaaaaaaaaaaaaaaaaa' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
155 ### @8='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
156 # Number of rows: 1
157 # at #
158 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Xid = #
159 COMMIT/*!*/;
160 # at #
161 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX GTID 0-1-5 trans thread_id=#
162 /*M!100001 SET @@session.gtid_seq_no=5*//*!*/;
163 START TRANSACTION
164 /*!*/;
165 # at #
166 # at #
167 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Annotate_rows:
168 #Q> UPDATE t1 SET c01=100 WHERE c02=0 OR c03=3
169 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Table_map: `test`.`t1` mapped to number #
170 # at #
171 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Update_rows: table id # flags: STMT_END_F
172 ### UPDATE `test`.`t1`
173 ### WHERE
174 ### @1=0 /* TINYINT meta=0 nullable=1 is_null=0 */
175 ### @2=0 /* SHORTINT meta=0 nullable=1 is_null=0 */
176 ### @3=0 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
177 ### @4=0 /* INT meta=0 nullable=1 is_null=0 */
178 ### @5=0 /* LONGINT meta=0 nullable=1 is_null=0 */
179 ### @6='' /* STRING(10) meta=65034 nullable=1 is_null=0 */
180 ### @7='' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
181 ### @8='' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
182 ### SET
183 ### @1=100 /* TINYINT meta=0 nullable=1 is_null=0 */
184 ### @2=0 /* SHORTINT meta=0 nullable=1 is_null=0 */
185 ### @3=0 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
186 ### @4=0 /* INT meta=0 nullable=1 is_null=0 */
187 ### @5=0 /* LONGINT meta=0 nullable=1 is_null=0 */
188 ### @6='' /* STRING(10) meta=65034 nullable=1 is_null=0 */
189 ### @7='' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
190 ### @8='' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
191 ### UPDATE `test`.`t1`
192 ### WHERE
193 ### @1=1 /* TINYINT meta=0 nullable=1 is_null=0 */
194 ### @2=2 /* SHORTINT meta=0 nullable=1 is_null=0 */
195 ### @3=3 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
196 ### @4=4 /* INT meta=0 nullable=1 is_null=0 */
197 ### @5=5 /* LONGINT meta=0 nullable=1 is_null=0 */
198 ### @6='abc' /* STRING(10) meta=65034 nullable=1 is_null=0 */
199 ### @7='abcdefg' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
200 ### @8='abcedfghijklmnopqrstuvwxyz' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
201 ### SET
202 ### @1=100 /* TINYINT meta=0 nullable=1 is_null=0 */
203 ### @2=2 /* SHORTINT meta=0 nullable=1 is_null=0 */
204 ### @3=3 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
205 ### @4=4 /* INT meta=0 nullable=1 is_null=0 */
206 ### @5=5 /* LONGINT meta=0 nullable=1 is_null=0 */
207 ### @6='abc' /* STRING(10) meta=65034 nullable=1 is_null=0 */
208 ### @7='abcdefg' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
209 ### @8='abcedfghijklmnopqrstuvwxyz' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
210 # Number of rows: 2
211 # at #
212 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Xid = #
213 COMMIT/*!*/;
214 # at #
215 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX GTID 0-1-6 trans thread_id=#
216 /*M!100001 SET @@session.gtid_seq_no=6*//*!*/;
217 START TRANSACTION
218 /*!*/;
219 # at #
220 # at #
221 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Annotate_rows:
222 #Q> DELETE FROM t1
223 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Table_map: `test`.`t1` mapped to number #
224 # at #
225 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Delete_rows: table id # flags: STMT_END_F
226 ### DELETE FROM `test`.`t1`
227 ### WHERE
228 ### @1=100 /* TINYINT meta=0 nullable=1 is_null=0 */
229 ### @2=0 /* SHORTINT meta=0 nullable=1 is_null=0 */
230 ### @3=0 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
231 ### @4=0 /* INT meta=0 nullable=1 is_null=0 */
232 ### @5=0 /* LONGINT meta=0 nullable=1 is_null=0 */
233 ### @6='' /* STRING(10) meta=65034 nullable=1 is_null=0 */
234 ### @7='' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
235 ### @8='' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
236 ### DELETE FROM `test`.`t1`
237 ### WHERE
238 ### @1=100 /* TINYINT meta=0 nullable=1 is_null=0 */
239 ### @2=2 /* SHORTINT meta=0 nullable=1 is_null=0 */
240 ### @3=3 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
241 ### @4=4 /* INT meta=0 nullable=1 is_null=0 */
242 ### @5=5 /* LONGINT meta=0 nullable=1 is_null=0 */
243 ### @6='abc' /* STRING(10) meta=65034 nullable=1 is_null=0 */
244 ### @7='abcdefg' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
245 ### @8='abcedfghijklmnopqrstuvwxyz' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
246 ### DELETE FROM `test`.`t1`
247 ### WHERE
248 ### @1=127 /* TINYINT meta=0 nullable=1 is_null=0 */
249 ### @2=32767 /* SHORTINT meta=0 nullable=1 is_null=0 */
250 ### @3=8388607 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
251 ### @4=2147483647 /* INT meta=0 nullable=1 is_null=0 */
252 ### @5=9223372036854775807 /* LONGINT meta=0 nullable=1 is_null=0 */
253 ### @6='aaaaaaaaaa' /* STRING(10) meta=65034 nullable=1 is_null=0 */
254 ### @7='aaaaaaaaaaaaaaaaaaaa' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
255 ### @8='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
256 # Number of rows: 3
257 # at #
258 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Xid = #
259 COMMIT/*!*/;
260 # at #
261 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Rotate to master-bin.000002 pos: 4
262 DELIMITER ;
263 # End of log file
264 ROLLBACK /* added by mysqlbinlog */;
265 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
266 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
267 # < CASE 1 >
268 # Show mysqlbinlog result with -B
269 #
270 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
271 /*!40019 SET @@session.max_delayed_threads=0*/;
272 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
273 DELIMITER /*!*/;
274 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Start: binlog v 4, server v #.##.## created 010909 9:46:40 at startup
275 ROLLBACK/*!*/;
276 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Gtid list []
277 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Binlog checkpoint master-bin.000001
278 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Annotate_rows:
279 #Q> INSERT INTO t1 VALUES(0,0,0,0,0,'','','')
280 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Table_map: `test`.`t1` mapped to number #
281 # Number of rows: 1
282 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Annotate_rows:
283 #Q> INSERT INTO t1 VALUES(1,2,3,4,5, "abc", "abcdefg", "abcedfghijklmnopqrstuvwxyz")
284 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Table_map: `test`.`t1` mapped to number #
285 # Number of rows: 1
286 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Annotate_rows:
287 #Q> INSERT INTO t1 VALUES(127, 32767, 8388607, 2147483647, 9223372036854775807, repeat('a', 10), repeat('a', 20), repeat('a', 255))
288 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Table_map: `test`.`t1` mapped to number #
289 # Number of rows: 1
290 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Annotate_rows:
291 #Q> UPDATE t1 SET c01=100 WHERE c02=0 OR c03=3
292 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Table_map: `test`.`t1` mapped to number #
293 # Number of rows: 2
294 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Annotate_rows:
295 #Q> DELETE FROM t1
296 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Table_map: `test`.`t1` mapped to number #
297 # Number of rows: 3
298 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Rotate to master-bin.000002 pos: 4
299 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Xid = #
300 START TRANSACTION/*!*/;
301 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Write_rows: table id # flags: STMT_END_F
302 ### INSERT INTO `test`.`t1`
303 ### SET
304 ### @1=127 /* TINYINT meta=0 nullable=1 is_null=0 */
305 ### @2=32767 /* SHORTINT meta=0 nullable=1 is_null=0 */
306 ### @3=8388607 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
307 ### @4=2147483647 /* INT meta=0 nullable=1 is_null=0 */
308 ### @5=9223372036854775807 /* LONGINT meta=0 nullable=1 is_null=0 */
309 ### @6='aaaaaaaaaa' /* STRING(10) meta=65034 nullable=1 is_null=0 */
310 ### @7='aaaaaaaaaaaaaaaaaaaa' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
311 ### @8='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
312 ### INSERT INTO `test`.`t1`
313 ### SET
314 ### @1=100 /* TINYINT meta=0 nullable=1 is_null=0 */
315 ### @2=2 /* SHORTINT meta=0 nullable=1 is_null=0 */
316 ### @3=3 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
317 ### @4=4 /* INT meta=0 nullable=1 is_null=0 */
318 ### @5=5 /* LONGINT meta=0 nullable=1 is_null=0 */
319 ### @6='abc' /* STRING(10) meta=65034 nullable=1 is_null=0 */
320 ### @7='abcdefg' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
321 ### @8='abcedfghijklmnopqrstuvwxyz' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
322 ### INSERT INTO `test`.`t1`
323 ### SET
324 ### @1=100 /* TINYINT meta=0 nullable=1 is_null=0 */
325 ### @2=0 /* SHORTINT meta=0 nullable=1 is_null=0 */
326 ### @3=0 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
327 ### @4=0 /* INT meta=0 nullable=1 is_null=0 */
328 ### @5=0 /* LONGINT meta=0 nullable=1 is_null=0 */
329 ### @6='' /* STRING(10) meta=65034 nullable=1 is_null=0 */
330 ### @7='' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
331 ### @8='' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
332 COMMIT
333 /*!*/;
334 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Xid = #
335 START TRANSACTION/*!*/;
336 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Update_rows: table id # flags: STMT_END_F
337 ### UPDATE `test`.`t1`
338 ### WHERE
339 ### @1=100 /* TINYINT meta=0 nullable=1 is_null=0 */
340 ### @2=2 /* SHORTINT meta=0 nullable=1 is_null=0 */
341 ### @3=3 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
342 ### @4=4 /* INT meta=0 nullable=1 is_null=0 */
343 ### @5=5 /* LONGINT meta=0 nullable=1 is_null=0 */
344 ### @6='abc' /* STRING(10) meta=65034 nullable=1 is_null=0 */
345 ### @7='abcdefg' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
346 ### @8='abcedfghijklmnopqrstuvwxyz' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
347 ### SET
348 ### @1=1 /* TINYINT meta=0 nullable=1 is_null=0 */
349 ### @2=2 /* SHORTINT meta=0 nullable=1 is_null=0 */
350 ### @3=3 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
351 ### @4=4 /* INT meta=0 nullable=1 is_null=0 */
352 ### @5=5 /* LONGINT meta=0 nullable=1 is_null=0 */
353 ### @6='abc' /* STRING(10) meta=65034 nullable=1 is_null=0 */
354 ### @7='abcdefg' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
355 ### @8='abcedfghijklmnopqrstuvwxyz' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
356 ### UPDATE `test`.`t1`
357 ### WHERE
358 ### @1=100 /* TINYINT meta=0 nullable=1 is_null=0 */
359 ### @2=0 /* SHORTINT meta=0 nullable=1 is_null=0 */
360 ### @3=0 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
361 ### @4=0 /* INT meta=0 nullable=1 is_null=0 */
362 ### @5=0 /* LONGINT meta=0 nullable=1 is_null=0 */
363 ### @6='' /* STRING(10) meta=65034 nullable=1 is_null=0 */
364 ### @7='' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
365 ### @8='' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
366 ### SET
367 ### @1=0 /* TINYINT meta=0 nullable=1 is_null=0 */
368 ### @2=0 /* SHORTINT meta=0 nullable=1 is_null=0 */
369 ### @3=0 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
370 ### @4=0 /* INT meta=0 nullable=1 is_null=0 */
371 ### @5=0 /* LONGINT meta=0 nullable=1 is_null=0 */
372 ### @6='' /* STRING(10) meta=65034 nullable=1 is_null=0 */
373 ### @7='' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
374 ### @8='' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
375 COMMIT
376 /*!*/;
377 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Xid = #
378 START TRANSACTION/*!*/;
379 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Delete_rows: table id # flags: STMT_END_F
380 ### DELETE FROM `test`.`t1`
381 ### WHERE
382 ### @1=127 /* TINYINT meta=0 nullable=1 is_null=0 */
383 ### @2=32767 /* SHORTINT meta=0 nullable=1 is_null=0 */
384 ### @3=8388607 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
385 ### @4=2147483647 /* INT meta=0 nullable=1 is_null=0 */
386 ### @5=9223372036854775807 /* LONGINT meta=0 nullable=1 is_null=0 */
387 ### @6='aaaaaaaaaa' /* STRING(10) meta=65034 nullable=1 is_null=0 */
388 ### @7='aaaaaaaaaaaaaaaaaaaa' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
389 ### @8='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
390 COMMIT
391 /*!*/;
392 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Xid = #
393 START TRANSACTION/*!*/;
394 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Delete_rows: table id # flags: STMT_END_F
395 ### DELETE FROM `test`.`t1`
396 ### WHERE
397 ### @1=1 /* TINYINT meta=0 nullable=1 is_null=0 */
398 ### @2=2 /* SHORTINT meta=0 nullable=1 is_null=0 */
399 ### @3=3 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
400 ### @4=4 /* INT meta=0 nullable=1 is_null=0 */
401 ### @5=5 /* LONGINT meta=0 nullable=1 is_null=0 */
402 ### @6='abc' /* STRING(10) meta=65034 nullable=1 is_null=0 */
403 ### @7='abcdefg' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
404 ### @8='abcedfghijklmnopqrstuvwxyz' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
405 COMMIT
406 /*!*/;
407 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Xid = #
408 START TRANSACTION/*!*/;
409 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Delete_rows: table id # flags: STMT_END_F
410 ### DELETE FROM `test`.`t1`
411 ### WHERE
412 ### @1=0 /* TINYINT meta=0 nullable=1 is_null=0 */
413 ### @2=0 /* SHORTINT meta=0 nullable=1 is_null=0 */
414 ### @3=0 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
415 ### @4=0 /* INT meta=0 nullable=1 is_null=0 */
416 ### @5=0 /* LONGINT meta=0 nullable=1 is_null=0 */
417 ### @6='' /* STRING(10) meta=65034 nullable=1 is_null=0 */
418 ### @7='' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
419 ### @8='' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
420 COMMIT
421 /*!*/;
422 #010909 9:46:40 server id 1 end_log_pos # CRC32 XXX Query thread_id=# exec_time=# error_code=0 xid=<xid>
423 use `test`/*!*/;
424 SET TIMESTAMP=1000000000/*!*/;
425 SET @@session.pseudo_thread_id=#/*!*/;
426 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_for_timestamp=1, @@session.system_versioning_insert_history=0/*!*/;
427 SET @@session.sql_mode=1411383296/*!*/;
428 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
429 /*!\C latin1 *//*!*/;
430 SET @@session.character_set_client=X,@@session.collation_connection=X,@@session.collation_server=X/*!*/;
431 SET @@session.lc_time_names=0/*!*/;
432 SET @@session.collation_database=DEFAULT/*!*/;
433 COMMIT
434 /*!*/;
435 DELIMITER ;
436 # End of log file
437 ROLLBACK /* added by mysqlbinlog */;
438 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
439 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
440 # < CASE 1 >
441 # Insert data to t1
442 #
443 TRUNCATE TABLE t1;
444 INSERT INTO t1 VALUES(0,0,0,0,0,'','','');
445 INSERT INTO t1 VALUES(1,2,3,4,5, "abc", "abcdefg", "abcedfghijklmnopqrstuvwxyz");
446 INSERT INTO t1 VALUES(127, 32767, 8388607, 2147483647, 9223372036854775807, repeat('a', 10), repeat('a', 20), repeat('a', 60));
447 # < CASE 1 >
448 # Delete all existing binary logs.
449 #
450 RESET MASTER;
451 SELECT * FROM t1;
452 c01 c02 c03 c04 c05 c06 c07 c08
453 0 0 0 0 0
454 1 2 3 4 5 abc abcdefg abcedfghijklmnopqrstuvwxyz
455 127 32767 8388607 2147483647 9223372036854775807 aaaaaaaaaa aaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
456 # < CASE 1 >
457 # Operate some data
458 #
459 UPDATE t1 SET c01=20;
460 UPDATE t1 SET c02=200;
461 UPDATE t1 SET c03=2000;
462 DELETE FROM t1;
463 FLUSH LOGS;
464 # < CASE 1 >
465 # Flashback & Check the result
466 #
467 SELECT * FROM t1;
468 c01 c02 c03 c04 c05 c06 c07 c08
469 127 32767 8388607 2147483647 9223372036854775807 aaaaaaaaaa aaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
470 1 2 3 4 5 abc abcdefg abcedfghijklmnopqrstuvwxyz
471 0 0 0 0 0
472 RESET MASTER;
473 # < CASE 2 >
474 # UPDATE multi-rows in one event
475 #
476 BEGIN;
477 UPDATE t1 SET c01=10 WHERE c01=0;
478 UPDATE t1 SET c01=20 WHERE c01=10;
479 COMMIT;
480 FLUSH LOGS;
481 # < CASE 2 >
482 # Flashback & Check the result
483 #
484 SELECT * FROM t1;
485 c01 c02 c03 c04 c05 c06 c07 c08
486 127 32767 8388607 2147483647 9223372036854775807 aaaaaaaaaa aaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
487 1 2 3 4 5 abc abcdefg abcedfghijklmnopqrstuvwxyz
488 0 0 0 0 0
489 DROP TABLE t1;
490 # < CASE 3 >
491 # Self-referencing foreign keys
492 #
493 CREATE TABLE t1 (a INT PRIMARY KEY, b INT, FOREIGN KEY my_fk(b) REFERENCES t1(a)) ENGINE=InnoDB;
494 BEGIN;
495 INSERT INTO t1 VALUES (1, NULL);
496 INSERT INTO t1 VALUES (2, 1), (3, 2), (4, 3);
497 COMMIT;
498 SELECT * FROM t1;
499 a b
500 1 NULL
501 2 1
502 3 2
503 4 3
504 RESET MASTER;
505 DELETE FROM t1 ORDER BY a DESC;
506 FLUSH LOGS;
507 # < CASE 3 >
508 # Flashback & Check the result
509 #
510 SELECT * FROM t1;
511 a b
512 1 NULL
513 2 1
514 3 2
515 4 3
516 DROP TABLE t1;
517 # < CASE 4 >
518 # Trigger
519 #
520 CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
521 CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
522 BEGIN;
523 INSERT INTO t1 VALUES (1, NULL);
524 INSERT INTO t1 VALUES (2, 1), (3, 2), (4, 3);
525 INSERT INTO t2 VALUES (6, 7), (7, 8), (8, 9);
526 COMMIT;
527 SELECT * FROM t1;
528 a b
529 1 NULL
530 2 1
531 3 2
532 4 3
533 SELECT * FROM t2;
534 a b
535 6 7
536 7 8
537 8 9
538 CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW DELETE FROM t2 WHERE a = NEW.b;
539 RESET MASTER;
540 INSERT INTO t1 VALUES (5, 6), (7, 8);
541 SELECT * FROM t1;
542 a b
543 1 NULL
544 2 1
545 3 2
546 4 3
547 5 6
548 7 8
549 SELECT * FROM t2;
550 a b
551 7 8
552 FLUSH LOGS;
553 # < CASE 4 >
554 # Flashback & Check the result
555 #
556 SELECT * FROM t1;
557 a b
558 1 NULL
559 2 1
560 3 2
561 4 3
562 SELECT * FROM t2;
563 a b
564 6 7
565 7 8
566 8 9
567 DROP TRIGGER trg1;
568 DROP TABLE t1;
569 DROP TABLE t2;
570 # < CASE 5 >
571 # REPLCAE Queries
572 #
573 CREATE TABLE t1 (a INT PRIMARY KEY, b INT, UNIQUE uk(b)) ENGINE=InnoDB;
574 BEGIN;
575 INSERT INTO t1 VALUES (1, NULL);
576 INSERT INTO t1 VALUES (2, 1), (3, 2), (4, 3);
577 INSERT INTO t1 VALUES (5, 4), (6, 5), (7, 6);
578 COMMIT;
579 SELECT * FROM t1;
580 a b
581 1 NULL
582 2 1
583 3 2
584 4 3
585 5 4
586 6 5
587 7 6
588 RESET MASTER;
589 REPLACE INTO t1 VALUES (3, 100);
590 REPLACE INTO t1 SET a=4, b=200;
591 SELECT * FROM t1;
592 a b
593 1 NULL
594 2 1
595 5 4
596 6 5
597 7 6
598 3 100
599 4 200
600 REPLACE INTO t1 VALUES (5,5);
601 SELECT * FROM t1;
602 a b
603 1 NULL
604 2 1
605 5 5
606 7 6
607 3 100
608 4 200
609 FLUSH LOGS;
610 # < CASE 5 >
611 # Flashback & Check the result
612 #
613 SELECT * FROM t1;
614 a b
615 1 NULL
616 2 1
617 3 2
618 4 3
619 5 4
620 6 5
621 7 6
622 DROP TABLE t1;
623 # < CASE 6 >
624 # Test Case from MDEV-21067
625 #
626 CREATE DATABASE world;
627 CREATE TABLE world.city (
628 ID INT AUTO_INCREMENT PRIMARY KEY,
629 Name VARCHAR(64),
630 CountryCode VARCHAR(64),
631 District VARCHAR(64),
632 Population INT
633 ) ENGINE=InnoDB;
634 CREATE TABLE test.test (
635 ID INT AUTO_INCREMENT PRIMARY KEY,
636 REC VARCHAR(64),
637 ts TIMESTAMP
638 ) ENGINE=InnoDB;
639 INSERT INTO world.city VALUES (NULL, 'Davenport', 'USA', 'Iowa', 100);
640 INSERT INTO world.city VALUES (NULL, 'Boulder', 'USA', 'Colorado', 1000);
641 INSERT INTO world.city VALUES (NULL, 'Gweru', 'ZWE', 'Midlands', 10000);
642 RESET MASTER;
643 CHECKSUM TABLE world.city;
644 Table Checksum
645 world.city 563256876
646 INSERT INTO test.test VALUES (NULL, 'Good record 1', CURRENT_TIMESTAMP());
647 INSERT INTO world.city VALUES (NULL, 'Wrong value 1', '000', 'Wrong', 0);
648 INSERT INTO world.city VALUES (NULL, 'Wrong value 2', '000', 'Wrong', 0) , (NULL, 'Wrong value 3', '000', 'Wrong', 0);
649 INSERT INTO test.test VALUES (NULL, 'Good record 2', CURRENT_TIMESTAMP());
650 UPDATE world.city SET Population = 99999999 WHERE ID IN (1, 2, 3);
651 INSERT INTO test.test VALUES (NULL, 'Good record 3', CURRENT_TIMESTAMP());
652 DELETE FROM world.city WHERE ID BETWEEN 1 AND 2;
653 INSERT INTO test.test VALUES (NULL, 'Good record 5', CURRENT_TIMESTAMP());
654 REPLACE INTO world.city VALUES (4074, 'Wrong value 4', '000', 'Wrong', 0);
655 REPLACE INTO world.city VALUES (4078, 'Wrong value 5', '000', 'Wrong', 0), (NULL, 'Wrong value 6', '000', 'Wrong', 0);
656 INSERT INTO test.test VALUES (NULL, 'Good record 6', CURRENT_TIMESTAMP());
657 INSERT INTO world.city
658 SELECT NULL, Name, CountryCode, District, Population FROM world.city WHERE ID BETWEEN 2 AND 10;
659 INSERT INTO test.test VALUES (NULL, 'Good record 7', CURRENT_TIMESTAMP());
660 INSERT INTO test.test VALUES (NULL, 'Good record 8', CURRENT_TIMESTAMP());
661 DELETE FROM world.city;
662 INSERT INTO test.test VALUES (NULL, 'Good record 9', CURRENT_TIMESTAMP());
663 FLUSH LOGS;
664 # < CASE 6 >
665 # Flashback & Check the result
666 #
667 SELECT * FROM world.city;
668 ID Name CountryCode District Population
669 1 Davenport USA Iowa 100
670 2 Boulder USA Colorado 1000
671 3 Gweru ZWE Midlands 10000
672 SELECT * FROM test.test;
673 ID REC ts
674 1 Good record 1 2001-09-09 09:46:40
675 2 Good record 2 2001-09-09 09:46:40
676 3 Good record 3 2001-09-09 09:46:40
677 4 Good record 5 2001-09-09 09:46:40
678 5 Good record 6 2001-09-09 09:46:40
679 6 Good record 7 2001-09-09 09:46:40
680 7 Good record 8 2001-09-09 09:46:40
681 8 Good record 9 2001-09-09 09:46:40
682 CHECKSUM TABLE world.city;
683 Table Checksum
684 world.city 563256876
685 DROP TABLE test.test;
686 DROP TABLE world.city;
687 DROP DATABASE world;
688 # < CASE 7 >
689 # Test Case for MDEV-17260
690 #
691 RESET MASTER;
692 CREATE TABLE t1 ( f INT PRIMARY KEY ) ENGINE=innodb;
693 INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6);
694 # 6- Rows must be present
695 SELECT COUNT(*) FROM t1;
696 COUNT(*)
697 6
698 FLUSH LOGS;
699 DELETE FROM t1;
700 FLUSH LOGS;
701 # 0- Rows must be present
702 include/assert.inc [Table t1 should have 0 rows.]
703 # 6- Rows must be present upon restoring from flashback
704 include/assert.inc [Table t1 should have six rows.]
705 DROP TABLE t1;
706 #
707 # MDEV-30698 Cover missing test cases for mariadb-binlog options
708 # --raw [and] --flashback
709 #
710 #
711 # < CASE 8 >
712 # Verify flashback works well for binlog_row_image full_nodup mode
713 #
714 CREATE TABLE t1 (
715 c01 TINYINT PRIMARY KEY,
716 c02 SMALLINT,
717 c03 MEDIUMINT,
718 c04 INT,
719 c05 BIGINT,
720 c06 CHAR(10),
721 c07 VARCHAR(20),
722 c08 TEXT,
723 c09 ENUM('one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight'),
724 c10 SET('black', 'white', 'red', 'yellow'),
725 c11 TIMESTAMP(3),
726 c12 DATETIME(3)
727 ) ENGINE = InnoDB;
728 INSERT INTO t1 VALUES (1, 1, 1, 1, 1, 'A', 'A', 'A', 'one', 'black',
729 '2023-11-26 10:00:00.123', '2023-11-26 10:00:00');
730 INSERT INTO t1 VALUES (2, 1, 1, 1, 1, 'A', 'A', 'A', 'one', 'black',
731 '2023-11-26 10:00:00.123', '2023-11-26 10:00:00');
732 INSERT INTO t1 VALUES (3, 1, NULL, 1, 1, 'A', 'A', 'A', 'one', 'black',
733 '2023-11-26 10:00:00.123', NULL);
734 INSERT INTO t1 VALUES (4, 1, NULL, 1, 1, 'A', 'A', 'A', 'one', 'black',
735 '2023-11-26 10:00:00.123', NULL);
736 FLUSH BINARY LOGS;
737 # The update includes the cases that
738 # Value -> Value
739 # Value -> NULL
740 # NULL -> value
741 # and the changed null bits in both first and second null byte
742 UPDATE t1 SET c02 = NULL, c03 = 2, c09 = 'two',
743 c10 = NULL, c12 = '2023-11-26 11:00:00';
744 FLUSH BINARY LOGS;
745 #
746 # Data before flashback
747 #
748 SELECT * FROM t1;
749 c01 c02 c03 c04 c05 c06 c07 c08 c09 c10 c11 c12
750 1 NULL 2 1 1 A A A two NULL 2023-11-26 10:00:00.123 2023-11-26 11:00:00.000
751 2 NULL 2 1 1 A A A two NULL 2023-11-26 10:00:00.123 2023-11-26 11:00:00.000
752 3 NULL 2 1 1 A A A two NULL 2023-11-26 10:00:00.123 2023-11-26 11:00:00.000
753 4 NULL 2 1 1 A A A two NULL 2023-11-26 10:00:00.123 2023-11-26 11:00:00.000
754 #
755 # Data after flashback
756 #
757 SELECT * FROM t1;
758 c01 c02 c03 c04 c05 c06 c07 c08 c09 c10 c11 c12
759 1 1 1 1 1 A A A one black 2023-11-26 10:00:00.123 2023-11-26 10:00:00.000
760 2 1 1 1 1 A A A one black 2023-11-26 10:00:00.123 2023-11-26 10:00:00.000
761 3 1 NULL 1 1 A A A one black 2023-11-26 10:00:00.123 NULL
762 4 1 NULL 1 1 A A A one black 2023-11-26 10:00:00.123 NULL
763 DROP TABLE t1;
764 SET binlog_format=statement;
765 Warnings:
766 Warning 1105 MariaDB Galera and flashback do not support binlog format: STATEMENT
767 SET GLOBAL binlog_format=statement;
768 ERROR HY000: Flashback does not support binlog_format STATEMENT
769 ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;