1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
|
include/master-slave.inc
[connection master]
connection master;
set @max_binlog_cache_size = @@global.max_binlog_cache_size;
set @binlog_cache_size = @@global.binlog_cache_size;
set @@global.max_binlog_cache_size = 4096;
set @@global. binlog_cache_size = 4096;
#
# MDEV-35207 ignored error at binlogging by CREATE-TABLE-SELECT leads to assert
#
connect conn_err,localhost,root,,;
call mtr.add_suppression("Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage");
create table t engine=myisam select repeat ('a',4096*3) AS a;
ERROR HY000: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mariadbd variable and try again
create table t engine=innodb select repeat ('a',4096*3) AS a;
ERROR HY000: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mariadbd variable and try again
create table t (a int unique, b char) select 1 AS a, 'b' as b union select 1 as a, 'c' as b;
ERROR 23000: Duplicate entry '1' for key 'a'
select * from t;
ERROR 42S02: Table 'test.t' doesn't exist
disconnect conn_err;
connection master;
#
# MDEV-35499 errored CREATE-OR-REPLACE-SELECT does not DROP table in binlog
#
#
# Engine = innodb
#
set statement binlog_format=statement for create table t (a int) select 1 as a;
set statement binlog_format=row for create or replace table t (a int primary key, b char) engine=innodb select 1 AS a, 'b' as b union select 1 as a, 'c' as b;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
select * from t;
ERROR 42S02: Table 'test.t' doesn't exist
#
# Prove an expected lonely `DROP table t'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `test`.`t`/* Generated to handle failed CREATE OR REPLACE */
master-bin.000001 # Query # # ROLLBACK
set statement binlog_format=statement for create table t (a int) select 1 as a;
set statement binlog_format=row for create or replace table t (a text) engine=innodb select repeat ('a',1024) AS a union select repeat ('a',3*4096) AS a union select repeat ('a',3*4096) AS a;
ERROR HY000: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mariadbd variable and try again
select * from t;
ERROR 42S02: Table 'test.t' doesn't exist
#
# Prove an expected lonely `DROP table t'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `test`.`t`/* Generated to handle failed CREATE OR REPLACE */
master-bin.000001 # Query # # ROLLBACK
set statement binlog_format=statement for create table t (a int) select 1 as a;
set statement binlog_format=row for create or replace table t (a text) engine=innodb select repeat ('a',4096*3) AS a;;
ERROR HY000: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mariadbd variable and try again
select * from t;
ERROR 42S02: Table 'test.t' doesn't exist
#
# Prove an expected lonely `DROP table t'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `test`.`t`/* Generated to handle failed CREATE OR REPLACE */
master-bin.000001 # Query # # ROLLBACK
#
# Engine = myisam
#
set statement binlog_format=statement for create table t (a int) select 1 as a;
set statement binlog_format=row for create or replace table t (a int primary key, b char) engine=myisam select 1 AS a, 'b' as b union select 1 as a, 'c' as b;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
select * from t;
ERROR 42S02: Table 'test.t' doesn't exist
#
# Prove an expected lonely `DROP table t'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `test`.`t`/* Generated to handle failed CREATE OR REPLACE */
master-bin.000001 # Query # # ROLLBACK
set statement binlog_format=statement for create table t (a int) select 1 as a;
set statement binlog_format=row for create or replace table t (a text) engine=myisam select repeat ('a',1024) AS a union select repeat ('a',3*4096) AS a union select repeat ('a',3*4096) AS a;
ERROR HY000: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mariadbd variable and try again
select * from t;
ERROR 42S02: Table 'test.t' doesn't exist
#
# Prove an expected lonely `DROP table t'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `test`.`t`/* Generated to handle failed CREATE OR REPLACE */
master-bin.000001 # Query # # ROLLBACK
set statement binlog_format=statement for create table t (a int) select 1 as a;
set statement binlog_format=row for create or replace table t (a text) engine=myisam select repeat ('a',4096*3) AS a;;
ERROR HY000: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mariadbd variable and try again
select * from t;
ERROR 42S02: Table 'test.t' doesn't exist
#
# Prove an expected lonely `DROP table t'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `test`.`t`/* Generated to handle failed CREATE OR REPLACE */
master-bin.000001 # Query # # ROLLBACK
create table ti_pk (a int primary key) engine=innodb;
create table ta (a int) engine=aria;
create function f_ia(arg int)
returns integer
begin
insert into ti_pk set a=1;
insert into ta set a=1;
insert into ti_pk set a=arg;
return 1;
end |
set statement binlog_format = ROW for create table t_y (a int) engine=aria select f_ia(1 /* err */) as a;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
select * from t_y;
ERROR 42S02: Table 'test.t_y' doesn't exist
# correct execution: `ta` is modified and its new record is binlogged
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Table_map # # table_id: # (test.ta)
master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
master-bin.000001 # Query # # COMMIT
select * from ta;
a
1
select * from ti_pk;
a
connection slave;
include/diff_tables.inc [master:ta,slave:ta]
connection master;
delete from ta;
connection slave;
connection master;
set statement binlog_format = STATEMENT for create table t_y (a int) engine=aria select f_ia(1 /* err */) as a;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
select * from t_y;
ERROR 42S02: Table 'test.t_y' doesn't exist
# ***TODO: fix MDEV-36027***. As of now `ta` is modified but that's not binlogged
include/show_binlog_events.inc
select *,'on_master' from ta;
a on_master
1 on_master
select * from ti_pk;
a
connection slave;
select *,'on_slave' from ta;
a on_slave
connection master;
drop function f_ia;
drop table ti_pk, ta;
SET @@global.max_binlog_cache_size = @max_binlog_cache_size;
SET @@global. binlog_cache_size = @binlog_cache_size;
connection slave;
End of the tests
include/rpl_end.inc
|
