mirror of
https://github.com/MariaDB/server.git
synced 2025-07-05 10:58:16 +02:00
22024da64e
This commit fixes a bug where Aria tables are used in (master->slave1->slave2) and a backup is taken on slave2. In this case it is possible that the replication position in the backup, stored in mysql.gtid_slave_pos, will be wrong. This will lead to replication errors if one is trying to use the backup as a new slave. Analyze: Replicated row events are committed with trans_commit_stmt() and thd->transaction->all.ha_list != 0. This means that backup_commit_lock is not taken for Aria tables, which means the rows are committed and binary logged on the slave under BLOCK_COMMIT which should not happen. This issue does not occur on the master as thd->transaction->all.ha_list is == 0 under AUTO_COMMIT, which sets 'is_real_trans' and 'rw_trans' which in turn causes backup_commit_lock to be taken. Fixed by checking in ha_check_and_coalesce_trx_read_only() if all handlers supports rollback and if not, then wait for BLOCK_COMMIT also for statement commit.
295 lines
8.7 KiB
Text
295 lines
8.7 KiB
Text
#
|
|
# Testing which locks we get from all stages
|
|
#
|
|
InnoDB 0 transactions not purged
|
|
BACKUP STAGE START;
|
|
SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info
|
|
WHERE TABLE_NAME NOT LIKE 'innodb_%_stats';
|
|
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
|
|
MDL_BACKUP_START Backup lock
|
|
BACKUP STAGE FLUSH;
|
|
SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info
|
|
WHERE TABLE_NAME NOT LIKE 'innodb_%_stats';
|
|
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
|
|
MDL_BACKUP_FLUSH Backup lock
|
|
BACKUP STAGE BLOCK_DDL;
|
|
SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info
|
|
WHERE TABLE_NAME NOT LIKE 'innodb_%_stats';
|
|
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
|
|
MDL_BACKUP_WAIT_DDL Backup lock
|
|
BACKUP STAGE BLOCK_COMMIT;
|
|
SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info
|
|
WHERE TABLE_NAME NOT LIKE 'innodb_%_stats';
|
|
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
|
|
MDL_BACKUP_WAIT_COMMIT Backup lock
|
|
BACKUP STAGE END;
|
|
SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info
|
|
WHERE TABLE_NAME NOT LIKE 'innodb_%_stats';
|
|
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
|
|
#
|
|
# testing BACKUP STAGE LOCK's
|
|
#
|
|
connect con1,localhost,root,,;
|
|
connect con2,localhost,root,,;
|
|
connection default;
|
|
#
|
|
# testing if BACKUP STAGE FLUSH causes deadlocks with ALTER TABLE
|
|
#
|
|
create table t1 (a int) stats_persistent= 0, engine=innodb;
|
|
InnoDB 0 transactions not purged
|
|
connection con2;
|
|
backup stage start;
|
|
connection default;
|
|
start transaction;
|
|
insert into t1 values (1);
|
|
connection con1;
|
|
alter table t1 add column (j int), algorithm copy;
|
|
connection con2;
|
|
backup stage flush;
|
|
SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info
|
|
WHERE TABLE_NAME NOT LIKE 'innodb_%_stats';
|
|
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
|
|
MDL_BACKUP_DDL Backup lock
|
|
MDL_BACKUP_FLUSH Backup lock
|
|
MDL_SHARED_WRITE Table metadata lock test t1
|
|
MDL_SHARED_UPGRADABLE Table metadata lock test t1
|
|
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
|
|
SET STATEMENT max_statement_time=1 FOR backup stage block_ddl;
|
|
ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
|
|
backup stage block_ddl;
|
|
connection default;
|
|
commit;
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
SET STATEMENT lock_wait_timeout=0 FOR INSERT INTO t1 values (2);
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
INSERT INTO t1 values (2,0);;
|
|
connection con2;
|
|
backup stage end;
|
|
connection con1;
|
|
connection default;
|
|
select * from t1;
|
|
a j
|
|
1 NULL
|
|
2 0
|
|
drop table t1;
|
|
# Test with inline alter table, which doesn't block block_commit
|
|
create table t1 (a int) engine=innodb;
|
|
start transaction;
|
|
insert into t1 values (1);
|
|
connection con1;
|
|
alter table t1 add column (j int);
|
|
connection con2;
|
|
backup stage start;
|
|
backup stage flush;
|
|
SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info
|
|
WHERE TABLE_NAME NOT LIKE 'innodb_%_stats';
|
|
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
|
|
MDL_BACKUP_ALTER_COPY Backup lock
|
|
MDL_BACKUP_FLUSH Backup lock
|
|
MDL_SHARED_WRITE Table metadata lock test t1
|
|
MDL_SHARED_UPGRADABLE Table metadata lock test t1
|
|
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
|
|
backup stage block_ddl;
|
|
backup stage block_commit;
|
|
connection default;
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
commit;
|
|
connection con2;
|
|
backup stage end;
|
|
connection con1;
|
|
connection default;
|
|
drop table t1;
|
|
#
|
|
# testing if BACKUP STAGE FLUSH causes deadlocks with DROP TABLE
|
|
#
|
|
create table t1 (a int)stats_persistent=0, engine=innodb;
|
|
InnoDB 0 transactions not purged
|
|
start transaction;
|
|
insert into t1 values (1);
|
|
connection con1;
|
|
SET STATEMENT lock_wait_timeout=0 FOR DROP TABLE t1;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
DROP TABLE t1;
|
|
connection con2;
|
|
backup stage start;
|
|
backup stage flush;
|
|
SET STATEMENT lock_wait_timeout=0 FOR SELECT * FROM t1;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
backup stage block_ddl;
|
|
SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info
|
|
WHERE TABLE_NAME NOT LIKE 'innodb_%_stats';
|
|
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
|
|
MDL_BACKUP_WAIT_DDL Backup lock
|
|
MDL_SHARED_WRITE Table metadata lock test t1
|
|
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
|
|
backup stage end;
|
|
connection default;
|
|
commit;
|
|
connection con1;
|
|
connection default;
|
|
#
|
|
# Check if backup stage block_dll + concurrent drop table blocks select
|
|
#
|
|
create table t1 (a int) engine=innodb;
|
|
InnoDB 0 transactions not purged
|
|
backup stage start;
|
|
backup stage block_ddl;
|
|
connection con1;
|
|
DROP TABLE t1;
|
|
connection con2;
|
|
connection con2;
|
|
SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info
|
|
WHERE TABLE_NAME NOT LIKE 'innodb_%_stats';
|
|
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
|
|
MDL_BACKUP_WAIT_DDL Backup lock
|
|
SELECT * FROM t1;
|
|
a
|
|
connection default;
|
|
backup stage end;
|
|
connection con1;
|
|
connection default;
|
|
#
|
|
# Check if backup stage block_dll overrides ddl lock for drop table
|
|
#
|
|
create table t1 (a int) engine=innodb;
|
|
start transaction;
|
|
insert into t1 values (1);
|
|
connection con1;
|
|
SET STATEMENT lock_wait_timeout=0 FOR DROP TABLE t1;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
DROP TABLE t1;
|
|
connection con2;
|
|
backup stage start;
|
|
backup stage flush;
|
|
backup stage block_ddl;
|
|
connection default;
|
|
commit;
|
|
connection con2;
|
|
backup stage end;
|
|
connection con1;
|
|
connection default;
|
|
#
|
|
# Check if BACKUP STAGE BLOCK_COMMIT blocks commit
|
|
#
|
|
create table t1 (a int) engine=innodb;
|
|
start transaction;
|
|
insert into t1 values (1);
|
|
connection con1;
|
|
backup stage start;
|
|
backup stage block_commit;
|
|
connection default;
|
|
commit;
|
|
connection con1;
|
|
backup stage end;
|
|
connection default;
|
|
select * from t1;
|
|
a
|
|
1
|
|
drop table t1;
|
|
#
|
|
# Check if BACKUP STAGE BLOCK_DDL blocks create view
|
|
#
|
|
create table t1 (a int) engine=innodb;
|
|
connection con1;
|
|
backup stage start;
|
|
backup stage block_ddl;
|
|
connection default;
|
|
create view v1 as select * from t1;;
|
|
connection con1;
|
|
select count(*) = 1 from information_schema.processlist;
|
|
count(*) = 1
|
|
0
|
|
backup stage end;
|
|
connection default;
|
|
drop table t1;
|
|
drop view v1;
|
|
disconnect con1;
|
|
disconnect con2;
|
|
#
|
|
# Test backup stage and flush tables
|
|
#
|
|
BACKUP STAGE START ;
|
|
BACKUP STAGE BLOCK_DDL ;
|
|
FLUSH TABLES;
|
|
CREATE TEMPORARY TABLE t12345678_tmp (col1 INT);
|
|
drop table t12345678_tmp;
|
|
BACKUP STAGE END;
|
|
#
|
|
# Test BACKUP STAGES with lock timeouts
|
|
#
|
|
SET GLOBAL lock_wait_timeout=0;
|
|
CREATE TABLE t_permanent_innodb (col1 INT) ENGINE = InnoDB;
|
|
CREATE TABLE t_permanent_myisam (col1 INT) ENGINE = MyISAM;
|
|
CREATE TABLE t_permanent_aria (col1 INT) ENGINE = Aria transactional=1;
|
|
CREATE TABLE t_permanent_aria2 (col1 INT) ENGINE = Aria transactional=0;
|
|
INSERT INTO t_permanent_innodb SET col1 = 1;
|
|
INSERT INTO t_permanent_myisam SET col1 = 1;
|
|
INSERT INTO t_permanent_aria SET col1 = 1;
|
|
INSERT INTO t_permanent_aria2 SET col1 = 1;
|
|
CREATE TABLE t_con1_innodb (col1 INT) ENGINE = InnoDB;
|
|
CREATE TABLE t_con1_myisam (col1 INT) ENGINE = MyISAM;
|
|
connect con1,localhost,root,,;
|
|
connection default;
|
|
BACKUP STAGE START;
|
|
BACKUP STAGE FLUSH;
|
|
BACKUP STAGE BLOCK_DDL;
|
|
BACKUP STAGE BLOCK_COMMIT;
|
|
connection con1;
|
|
SET AUTOCOMMIT = 1;
|
|
UPDATE t_permanent_aria SET col1 = 1;
|
|
UPDATE t_permanent_innodb SET col1 = 1;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
UPDATE t_permanent_innodb SET col1 = 8;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
UPDATE t_permanent_myisam SET col1 = 8;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
UPDATE t_permanent_aria SET col1 = 8;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
UPDATE t_permanent_aria2 SET col1 = 8;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
select * from t_permanent_innodb;
|
|
col1
|
|
1
|
|
select * from t_permanent_myisam;
|
|
col1
|
|
1
|
|
select * from t_permanent_aria;
|
|
col1
|
|
8
|
|
select * from t_permanent_aria2;
|
|
col1
|
|
1
|
|
SET AUTOCOMMIT = 0;
|
|
UPDATE t_permanent_innodb SET col1 = 9;
|
|
UPDATE t_permanent_aria SET col1 = 9;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
UPDATE t_permanent_myisam SET col1 = 9;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
UPDATE t_permanent_aria2 SET col1 = 9;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
DROP TABLE t_con1_innodb;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
DROP TABLE t_con1_myisam;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
connection default;
|
|
BACKUP STAGE END;
|
|
select * from t_permanent_innodb;
|
|
col1
|
|
1
|
|
select * from t_permanent_myisam;
|
|
col1
|
|
1
|
|
select * from t_permanent_aria;
|
|
col1
|
|
9
|
|
select * from t_permanent_aria2;
|
|
col1
|
|
1
|
|
DROP TABLE t_permanent_myisam, t_permanent_innodb, t_permanent_aria, t_permanent_aria2;
|
|
DROP TABLE t_con1_innodb, t_con1_myisam;
|
|
disconnect con1;
|
|
set global lock_wait_timeout=default;
|