Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.4.34
-
None
-
None
Description
In either ROW and STATEMENT binlog format a failing at *commit* as
--error ER_GTID_STRICT_OUT_OF_ORDER
|
create table t_x (a int) select f(/* function modifies a non-transaction table */) as a; |
CREATE-TABLE-SELECT query is not binlogged at all even when a function may have left
side effects such as updates to non-transactional tables.
How to reproduce
There are two types of the error occurring either in the commit time or and the select time.
The first find in the section 4.B of the following tests from MDEV-35506
# Tests of commit time failures.
|
# At committing of an auto-commit statement a failure to commit in its
|
# binlog branch should rollback at least a transactional part of the statement.
|
#
|
# References:
|
# MDEV-35506 |
|
source include/have_innodb.inc;
|
source include/have_log_bin.inc;
|
|
set @@session.gtid_domain_id=1; |
set @save_gtid_stric_mode=@@global.gtid_strict_mode; |
|
create table ta (a int) engine=aria; |
create table ti (a int) engine=innodb; |
create table ti_pk (a int primary key) engine=innodb; |
create table t (a int) engine=innodb; |
delimiter |;
|
create function f_i()
|
returns integer
|
begin
|
insert into ti set a=1; |
return 1; |
end |
|
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 |
|
delimiter ;|
|
|
call mtr.add_suppression("Error writing file"); |
|
# Naturally all empty now
|
select count(*) as zero from t;
|
select count(*) as zero from ta;
|
select count(*) as zero from ti;
|
|
# Force manual value assignement to gtid::seq_no while in the strict mode |
# so that the value is rejected. Despite the errorred out statement
|
# being at its commit phase it will eventually be rolled back.
|
# Side effects of non-transactional engines, like Aria, are displayed.
|
--echo # 1. simple Innodb test |
set @@global.gtid_strict_mode=0; set @@session.gtid_seq_no=1; |
set @@global.gtid_strict_mode=1; |
# mask possible allowed seq_no shift
|
--replace_regex /GTID 1-1-[0-9]+/GTID VALUE/ |
--error ER_GTID_STRICT_OUT_OF_ORDER
|
insert into t set a=1; |
|
--echo # observe effective rollback
|
select count(*) as zero from t;
|
|
--echo # 2. simple Aira test |
set @@global.gtid_strict_mode=0; set @@session.gtid_seq_no=1; |
set @@global.gtid_strict_mode=1; |
--replace_regex /GTID 1-1-[0-9]+/GTID VALUE/ |
--error ER_GTID_STRICT_OUT_OF_ORDER
|
insert into ta values (1),(2); |
|
--echo # note no rollback
|
select count(*) as '*NON-zero*' from ta; |
# local cleanup
|
delete from ta;
|
|
--echo # 3. multi-engine test |
# A. non-transactional top-level
|
set @@global.gtid_strict_mode=0; set @@session.gtid_seq_no=1; |
set @@global.gtid_strict_mode=1; |
--replace_regex /GTID 1-1-[0-9]+/GTID VALUE/ |
--error ER_GTID_STRICT_OUT_OF_ORDER
|
insert into ta set a=f_i();
|
--echo # note no rollback..
|
select count(*) as one from ta;
|
--echo # ..incl transactional engine
|
select count(*) as one from ti;
|
delete from ti;
|
delete from ta;
|
|
# B. non-transactional in the leaf
|
set @@global.gtid_strict_mode=0; set @@session.gtid_seq_no=1; |
set @@global.gtid_strict_mode=1; |
--replace_regex /GTID 1-1-[0-9]+/GTID VALUE/ |
--error ER_GTID_STRICT_OUT_OF_ORDER
|
insert into t set a=f_ia(0); |
|
--echo # note no rollback..
|
select count(*) as one from ta;
|
--echo # ..incl transactional engine
|
select count(*) as one from t;
|
select count(*) as one from ti;
|
delete from ti;
|
delete from ta;
|
|
--echo # 4. create-table-select-f() |
--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1) |
--let $binlog_start = query_get_value(SHOW MASTER STATUS, Position, 1) |
# 4.A. two phase commit branch |
set @@global.gtid_strict_mode=0; set @@session.gtid_seq_no=1; |
set @@global.gtid_strict_mode=1; |
--replace_regex /GTID 1-1-[0-9]+/GTID VALUE/ |
--error ER_GTID_STRICT_OUT_OF_ORDER
|
create table f_x (a int) select f_i() as a; |
--echo # rollback indeed takes place in the pure transactional case |
select count(*) as zero from ti;
|
|
# *** MDEV-36027 *** |
|
# # 4.B. one phase commit branch |
--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1) |
--let $binlog_start = query_get_value(SHOW MASTER STATUS, Position, 1) |
set @@global.gtid_strict_mode=0; set @@session.gtid_seq_no=1; |
set @@global.gtid_strict_mode=1; |
--replace_regex /GTID 1-1-[0-9]+/GTID VALUE/ |
--error ER_GTID_STRICT_OUT_OF_ORDER
|
create table t_x (a int) engine=aria select f_ia(0) as a; |
--error ER_NO_SUCH_TABLE
|
select * from t_x;
|
|
--echo # bug: in EITHER binlog_format no logging for create-table-select took place |
--source include/show_binlog_events.inc
|
--echo # .. but non-transactional `ta` (and `t_x` sic!) are modified
|
select count(*) as one from ta;
|
select count(*) as zero from ti;
|
|
delete from ta;
|
--echo #.
|
|
# cleanup
|
|
set @@global.gtid_strict_mode=@save_gtid_stric_mode; |
drop function f_i;
|
drop function f_ia;
|
drop table t, ta, ti, ti_pk;
|
|
The 2nd error type takes place with STATEMENT binlog format when an error occurs with SELECT's execution, like a duplicate entry error is met inside the function here
--error ER_DUP_ENTRY
|
set statement binlog_format = STATEMENT for create table t_y (a int) engine=aria select f_ia(1 /* err */) as a; |
see the whole test in the # MDEV-36027 section of rpl_create_select_row.test of MDEV-35207 fixes.
In the ROW format case the errored-in-select CREATE-SELECT is logged correctly which suggests
a *workaround*.
How to fix
It is expected that when the non-transactional ta got affected its updates must be
present in binlog.
E.g for the ROW binlog format a non-transactonal group of events could be still logged
headed with an automatic GTID value.
In the STATEMENT format case, it seems an Incident event has to be logged.
Attachments
Issue Links
- relates to
-
MDEV-35207 ignored error at binlogging by CREATE-TABLE-SELECT leads to assert
-
- Stalled
-
-
MDEV-35499 Errorred-out CREATE-or-REPLACE-SELECT does not log DROP table into binlog
-
- Stalled
-
-
MDEV-35502 Failed at ROW-format binlogging CREATE-TABLE-SELECT should not generate Incident event
-
- Stalled
-