2410 lines
63 KiB
Plaintext
2410 lines
63 KiB
Plaintext
################# t/transactional_ddl_locking.test ###################
|
|
# #
|
|
# Tests ensure that concurrent DDL operations should not be allowed #
|
|
# on the table that are already in use inside an active transaction. #
|
|
# #
|
|
# In order to ensure serialization in binary log we will need full #
|
|
# mirrored replication setup and master and slave will contain the #
|
|
# same schema/database structure. #
|
|
# #
|
|
# Contents of slave and master should be the same ie result sets are #
|
|
# correct and slave does not fail with any error like row not found. #
|
|
# #
|
|
# Creation: #
|
|
# 2012-10-08 shipjain Implement this test as a part of WL4986 #
|
|
# Transactional DDL locking #
|
|
# #
|
|
######################################################################
|
|
|
|
--source include/big_test.inc
|
|
--source include/have_myisam.inc
|
|
--source include/no_ps_protocol.inc
|
|
--source include/master-slave.inc
|
|
|
|
--echo # Prepare playground for test by creating tables
|
|
|
|
CREATE TABLE t1 (a INT) ;
|
|
CREATE TABLE t2 (a INT PRIMARY KEY, b INT);
|
|
CREATE TABLE t3 (a INT);
|
|
CREATE TABLE t4 (a INT);
|
|
CREATE TABLE t1part (id INT, year_col INT);
|
|
CREATE TABLE t2part (id INT, year_col INT)
|
|
PARTITION BY RANGE (year_col) (
|
|
PARTITION p0 VALUES LESS THAN (1991),
|
|
PARTITION p1 VALUES LESS THAN (1995),
|
|
PARTITION p2 VALUES LESS THAN (2013));
|
|
CREATE TABLE t3part (id INT, year_col INT)
|
|
PARTITION BY HASH(year_col)
|
|
PARTITIONS 4;
|
|
CREATE TABLE t4part (id INT, year_col INT)
|
|
PARTITION BY RANGE (year_col) (
|
|
PARTITION p0 VALUES LESS THAN (1970),
|
|
PARTITION p1 VALUES LESS THAN (1995),
|
|
PARTITION p2 VALUES LESS THAN (2013));
|
|
CREATE TABLE t1prim (a INT, b INT PRIMARY KEY);
|
|
CREATE TABLE t1opt (id INT , content TEXT, author_id INT,
|
|
article_title VARCHAR(120), article_hash INT);
|
|
CREATE TABLE t1bin (c INT);
|
|
CREATE TABLE t2bin SELECT * FROM t1bin;
|
|
CREATE TABLE t1myisam (c INT) ENGINE = MYISAM;
|
|
CREATE TABLE t1innodb (c INT) ENGINE=XENGINE;
|
|
CREATE TABLE t3967_1 (id INT(11) NOT NULL AUTO_INCREMENT,
|
|
b INT(11) DEFAULT NULL, PRIMARY KEY (id))
|
|
ENGINE=XENGINE;
|
|
CREATE TABLE t3967_2 (id INT(11) NOT NULL AUTO_INCREMENT,
|
|
b INT(11) DEFAULT NULL, PRIMARY KEY (id))
|
|
ENGINE=XENGINE;
|
|
CREATE TABLE `t1_30138` (id INT) ENGINE=XENGINE;
|
|
COMMIT;
|
|
|
|
connect (master2,localhost,root,,);
|
|
connect (master3,localhost,root,,);
|
|
|
|
# Concurrent execution of DROP TABLE and INSERT inside an active transaction.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (1);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
|
|
--echo send statement and reap result later ---
|
|
--send DROP TABLE t1;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status= `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'DROP TABLE t1'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous ---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of ALTER TABLE RENAME TO and INSERT inside an active transaction
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (1);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t1 RENAME TO t1new;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t1 RENAME TO t1new'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of ALTER TABLE DROP COLUMN and INSERT inside an active transaction
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
INSERT INTO t2 VALUES (1,1);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t2 DROP COLUMN a;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t2 DROP COLUMN a'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t2 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t2 ;
|
|
|
|
--let $diff_tables= master:t2, slave:t2
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of ALTER TABLE ADD COLUMN and UPDATE inside an active transaction
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
UPDATE t1 SET a = 2;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t1 ADD COLUMN b INT;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t1 ADD COLUMN b INT'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of RENAME TABLE and INSERT inside an active transaction
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (1);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send RENAME TABLE t1 to t2new;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'RENAME TABLE t1 to t2new'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of ALTER TABLE ADD COMMENT and INSERT inside an active transaction
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (1);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t1 MODIFY a bigINT COMMENT 'first';
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t1 MODIFY a bigINT COMMENT \'first\''`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of ALTER TABLE ADD CONSTRAINT and UPDATE inside an active transaction
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
UPDATE t1 SET a = 1;
|
|
|
|
--echo --- connection master1 --
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t1 ADD PRIMARY KEY (a);
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t1 ADD PRIMARY Key (a)'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of ALTER TABLE ADD CONSTRAINT and UPDATE inside an active transaction
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
UPDATE t1 SET a = 1;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t2 (a) ;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t2 (a) '`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of ALTER TABLE ALTER COLUMN and UPDATE inside an active transaction
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
UPDATE t1 SET a = 1;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t1 ALTER a SET DEFAULT 1;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t1 ALTER a SET DEFAULT 1'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of ALTER TABLE CHANGE COLUMN and UPDATE inside an active transaction
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
UPDATE t1 SET a = 1;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t1 CHANGE a b smallINT;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t1 CHANGE a b smallINT'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of ALTER TABLE MODIFY COLUMN and UPDATE inside an active transaction
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
INSERT INTO t1 VALUES(1);
|
|
BEGIN;
|
|
UPDATE t1 SET a = 1;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t1 MODIFY a INTEGER;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t1 MODIFY a INTEGER'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of ALTER TABLE DROP PRIMARY KEY and UPDATE inside an active transaction
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
INSERT INTO t1prim VALUES (1,1);
|
|
INSERT INTO t1prim VALUES (2,2);
|
|
COMMIT;
|
|
BEGIN;
|
|
UPDATE t1prim SET b = 4 WHERE a = 1;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t1prim DROP PRIMARY KEY;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t1prim DROP PRIMARY KEY'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1prim ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1prim ;
|
|
|
|
--let $diff_tables= master:t1prim, slave:t1prim
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of ALTER TABLE DISABLE KEY and UPDATE inside an active transaction
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
|
|
BEGIN;
|
|
UPDATE t1 SET a = 1;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t1 disable KEYS;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t1 disable KEYS'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of ALTER TABLE PARTITION BY and INSERT inside an active transaction
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
INSERT INTO t1part VALUES(1, '1998');
|
|
INSERT INTO t1part VALUES(2, '2012');
|
|
|
|
BEGIN;
|
|
INSERT INTO t1part SELECT * FROM t1part;
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t1part PARTITION BY HASH(id) PARTITIONS 2 ;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status= `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t1part PARTITION BY HASH(id) PARTITIONS 2'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1part;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1part;
|
|
|
|
--let $diff_tables= master:t1part, slave:t1part
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of INSERT and ALTER TABLE ADD PARTITION inside an active transaction.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
INSERT INTO t2part VALUES (1, '1988');
|
|
BEGIN;
|
|
INSERT INTO t2part VALUES (2, '1998');
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t2part ADD PARTITION (PARTITION p3 VALUES LESS THAN (2015));
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t2part ADD PARTITION (PARTITION p3 VALUES LESS THAN (2015))'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t2part;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t2part;
|
|
|
|
--let $diff_tables= master:t2part, slave:t2part
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of INSERT and ALTER TABLE DROP PARTITION inside an active transaction.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
INSERT INTO t2part VALUES (1, '1912');
|
|
BEGIN;
|
|
INSERT INTO t2part VALUES (2, '1978');
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t2part Drop PARTITION p1 ;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t2part Drop PARTITION p1'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t2part;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t2part;
|
|
|
|
--let $diff_tables= master:t2part, slave:t2part
|
|
--source include/diff_tables.inc
|
|
|
|
# Concurrent execution of INSERT and ALTER TABLE COALESCE PARTITION inside an active transaction.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
INSERT INTO t3part VALUES (1, '1902');
|
|
BEGIN;
|
|
INSERT INTO t3part VALUES (2, '1965');
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t3part COALESCE PARTITION 2 ;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t3part COALESCE PARTITION 2'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t3part;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t3part;
|
|
|
|
--let $diff_tables= master:t3part, slave:t3part
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of INSERT on a partitioned table and REORGANIZE PARTITION inside an active transaction.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
INSERT INTO t4part VALUES (1, '1932');
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
INSERT INTO t4part VALUES (2, '1998');
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t4part REORGANIZE PARTITION p0,p1 INTO (PARTITION s0 VALUES LESS THAN (1995));
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t4part
|
|
REORGANIZE PARTITION p0 INTO (PARTITION s0 VALUES LESS THAN (1960),
|
|
PARTITION s1 VALUES LESS THAN (1970))'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t4part ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t4part ;
|
|
|
|
--let $diff_tables= master:t4part, slave:t4part
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of INSERT and ALTER TABLE ANALYSE/CHECK PARTITION inside an active transaction.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
INSERT INTO t2part VALUES (3, '1892');
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
INSERT INTO t2part VALUES (4, '2012');
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t2part CHECK PARTITION ALL;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t2part CHECK PARTITION ALL'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t2part ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t2part ;
|
|
|
|
--let $diff_tables= master:t2part, slave:t2part
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of INSERT and ALTER TABLE ANALYSE PARTITION inside an active transaction.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
INSERT INTO t2part VALUES (5, '1975');
|
|
BEGIN;
|
|
INSERT INTO t2part VALUES (6, '1980');
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t2part CHECK PARTITION ALL;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t2part ANALYSE PARTITION ALL'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t2part ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t2part ;
|
|
|
|
--let $diff_tables= master:t2part, slave:t2part
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of INSERT and ALTER TABLE OPTIMIZE PARTITION inside an active transaction.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
INSERT INTO t2part VALUES (7, '1912');
|
|
BEGIN;
|
|
INSERT INTO t2part VALUES (8, '1998');
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t2part OPTIMIZE PARTITION ALL;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t2part OPTIMIZE PARTITION ALL'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t2part ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t2part ;
|
|
|
|
--let $diff_tables= master:t2part, slave:t2part
|
|
--source include/diff_tables.inc
|
|
|
|
# Concurrent execution of INSERT and ALTER TABLE REBUILD PARTITION inside an active transaction.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
INSERT INTO t2part VALUES (9, '2011');
|
|
BEGIN;
|
|
INSERT INTO t2part VALUES (10, '1982');
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t2part REBUILD PARTITION ALL;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t2part REBUILD PARTITION ALL'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t2part ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t2part ;
|
|
|
|
--let $diff_tables= master:t2part, slave:t2part
|
|
--source include/diff_tables.inc
|
|
|
|
# Concurrent execution of INSERT and ALTER TABLE REPAIR PARTITION inside an active transaction.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
INSERT INTO t2part VALUES (11, '1912');
|
|
BEGIN;
|
|
INSERT INTO t2part VALUES (12, '1999');
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t2part REPAIR PARTITION ALL;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t2part REPAIR PARTITION ALL'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t2part ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t2part ;
|
|
|
|
--let $diff_tables= master:t2part, slave:t2part
|
|
--source include/diff_tables.inc
|
|
|
|
# Concurrent execution of INSERT and ALTER TABLE REPAIR PARTITION inside an active transaction.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
INSERT INTO t2part VALUES (13, '1923');
|
|
BEGIN;
|
|
INSERT INTO t2part VALUES (14, '1981');
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t2part REMOVE PARTITIONING ;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t2part REMOVE PARTITIONING' `;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t2part ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t2part ;
|
|
|
|
--let $diff_tables= master:t2part, slave:t2part
|
|
--source include/diff_tables.inc
|
|
|
|
# Concurrent execution of INSERT and ALTER TABLE ALGORITHM COPY inside an active transaction.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (1);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t1 ALGORITHM = COPY;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t1 ALGORITHM = COPY;'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of INSERT and ALTER TABLE ALGORITHM INPLACE inside an active transaction.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (1);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t1 ALGORITHM = INPLACE;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t1 ALGORITHM = INPLACE;'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
# Meta data locking for HANDLER statement
|
|
|
|
CREATE TABLE t1hand (a INT, KEY a(a));
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
BEGIN;
|
|
SELECT * FROM t1hand;
|
|
HANDLER t1hand OPEN;
|
|
HANDLER t1hand READ a NEXT;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
let $lock_status = `SELECT COUNT(*) FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'HANDLER t1hand READ a NEXT'`;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send DROP TABLE t1hand;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $wait_condition = SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST
|
|
WHERE STATE = "Waiting for table"
|
|
AND INFO = "DROP TABLE t1hand";
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
HANDLER t1hand CLOSE;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1hand ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1hand ;
|
|
|
|
--let $diff_tables= master:t1hand, slave:t1hand
|
|
--source include/diff_tables.inc
|
|
DROP TABLE t1hand;
|
|
|
|
# Triggers - Tables from different engines cause full table lock
|
|
|
|
USE test;
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
CREATE TABLE t1trig(c INT) ENGINE=XENGINE;
|
|
CREATE TABLE t2trig(c INT) ENGINE = MYISAM;
|
|
DELIMITER |;
|
|
CREATE TRIGGER trig AFTER UPDATE ON t1trig FOR EACH ROW
|
|
BEGIN
|
|
INSERT INTO t2trig(c) VALUES (1);
|
|
END|
|
|
DELIMITER ;|
|
|
INSERT INTO t1trig(c) VALUES (1);
|
|
--disable_warnings
|
|
UPDATE t1trig SET c = 2 WHERE c = 1;
|
|
--enable_warnings
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
INSERT INTO t1trig VALUES (9);
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1trig;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1trig;
|
|
|
|
--let $diff_tables= master:t1trig, slave:t1trig
|
|
--source include/diff_tables.inc
|
|
|
|
# Concurrent execution of INSERT and REVOKE PRIVILEGES inside an active transaction.
|
|
|
|
USE test;
|
|
CREATE USER 'user1'@'localhost' ;
|
|
GRANT ALL ON test.t1 TO user1@localhost;
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (1);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send REVOKE ALL ON test.t1 FROM user1@localhost;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'REVOKE ALL ON test.t1 FROM user1@localhost'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
# Four way deadlock - Waiting for locks and hang
|
|
USE test;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
CREATE TABLE tz (s1 INT UNIQUE) ENGINE=XENGINE;
|
|
INSERT INTO tz VALUES (1);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
INSERT INTO tz VALUES (2);
|
|
INSERT INTO tz VALUES (3);
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
DROP TABLE tz;
|
|
|
|
--echo --- connection master3 ---
|
|
connection master3;
|
|
--error ER_BAD_TABLE_ERROR
|
|
--echo send statement and reap result later ---
|
|
--send DROP TABLE tz;
|
|
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
--sync_slave_with_master
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
# Concurrent execution of ROLLBACK and INSERT inside an active transaction.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (1);
|
|
INSERT INTO t1 VALUES (5);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ROLLBACK;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ROLLBACK;'`;
|
|
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of Multiple DML statement on different tables with DROP TABLE on other connection.
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (1);
|
|
INSERT INTO t2 VALUES (2,3);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send DROP TABLE t2;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'DROP TABLE t2;'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of Multiple DML statement on different tables with RENAME on other connection.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (1);
|
|
INSERT INTO t2 VALUES (3,4);
|
|
INSERT INTO t3 VALUES (2);
|
|
INSERT INTO t4 VALUES (1);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send RENAME TABLE t1 to t2, t2 to t3, t3 to t4, t4 to t1;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'RENAME TABLE t1 to t2, t2 to t3, t3 to t4, t4 to t1;'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
SELECT COUNT(*) FROM t2 ;
|
|
SELECT COUNT(*) FROM t3 ;
|
|
SELECT COUNT(*) FROM t4 ;
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
SELECT COUNT(*) FROM t2 ;
|
|
SELECT COUNT(*) FROM t3 ;
|
|
SELECT COUNT(*) FROM t4 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--let $diff_tables= master:t2, slave:t2
|
|
--let $diff_tables= master:t3, slave:t3
|
|
--let $diff_tables= master:t4, slave:t4
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Rename tables on innodb tables with pending clause slave data issue
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
INSERT INTO t3967_1 (b) VALUES (1),(2),(3);
|
|
BEGIN;
|
|
INSERT INTO t3967_1(b) VALUES (4);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send RENAME TABLE t3967_1 to t1_backup, t3967_2 to t3967_1;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'RENAME TABLE t3967_1 to t1_backup, t3967_2 to t3967_1;'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t3967_1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t3967_1 ;
|
|
|
|
--let $diff_tables= master:t3967_1, slave:t3967_1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# WITH CONSISTENT SNAPSHOT does not isolate against TABLE CHANGES.
|
|
|
|
INSERT INTO t1_30138 VALUES (1),(2),(3);
|
|
SET AUTOCOMMIT = 0;
|
|
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
|
|
START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
SELECT COUNT(*) FROM t1_30138;
|
|
CREATE TABLE t1_30138_new (id INT) ENGINE=XENGINE;
|
|
INSERT INTO t1_30138_new VALUES (4),(5),(6),(7);
|
|
INSERT INTO t1_30138 VALUES(8);
|
|
RENAME TABLE t1_30138 TO t1_30138_old, t1_30138_new TO t1_30138;
|
|
SELECT COUNT(*) FROM t1_30138;
|
|
SELECT COUNT(*) FROM t1_30138_old;
|
|
--sync_slave_with_master
|
|
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1_30138 ;
|
|
SELECT COUNT(*) FROM t1_30138_old ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1_30138 ;
|
|
SELECT COUNT(*) FROM t1_30138_old ;
|
|
|
|
--let $diff_tables= master:t1_30138, slave:t1_30138
|
|
--let $diff_tables= master:t1_30138_old, slave:t1_30138_old
|
|
--source include/diff_tables.inc
|
|
|
|
# Concurrent execution of Multi UPDATE statements on transactional (INNODB) and non transactional tables(MyISAM).
|
|
CALL mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT');
|
|
--echo --- connection master ---
|
|
connection master;
|
|
INSERT INTO t1innodb (c) VALUES (1);
|
|
INSERT INTO t1myisam (c) VALUES (1);
|
|
COMMIT;
|
|
BEGIN;
|
|
--disable_warnings
|
|
UPDATE t1myisam, t1innodb SET t1myisam.c = 10, t1innodb.c = NULL;
|
|
--enable_warnings
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send DROP TABLE t1myisam;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'DROP TABLE t1myisam'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1myisam ;
|
|
SELECT COUNT(*) FROM t1innodb ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1myisam ;
|
|
SELECT COUNT(*) FROM t1innodb ;
|
|
|
|
--let $diff_tables= master:t1myisam, slave:t1myisam
|
|
--let $diff_tables= master:t1innodb, slave:t1innodb
|
|
--source include/diff_tables.inc
|
|
|
|
# Binlogging of "CREATE SELECT" which unfold into two or more events in RBL mode.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send DROP TABLE t1;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'DROP TABLE t1'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of DROP DATABASE and CREATE FUNCTION inside in an active transaction.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
CREATE DATABASE db1;
|
|
USE db1;
|
|
CREATE TABLE db1.t1 (a INT);
|
|
BEGIN;
|
|
CREATE FUNCTION db1.f1() RETURNS INT RETURN 1;
|
|
INSERT INTO db1.t1 VALUES (f1());
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send DROP DATABASE db1;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'DROP database db1'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
USE db1;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
DROP TABLE db1.t1;
|
|
DROP FUNCTION db1.f1;
|
|
|
|
# Concurrent execution of DML statements that uses stored FUNCTION and DROP/MODIFY the same FUNCTION.
|
|
|
|
USE test;
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE FUNCTION f1() RETURNS INT RETURN 1;
|
|
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (f1());
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send DROP FUNCTION f1;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'DROP FUNCTION f1';`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
USE test;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of DML statements that uses STORED PROCEDURE and DROP the same.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
CREATE PROCEDURE p1() INSERT INTO t1 SELECT * FROM t3;
|
|
BEGIN;
|
|
CALL p1();
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send DROP PROCEDURE p1;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'DROP PROCEDURE p1';`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of DML statements that uses STORED PROCEDURE and DROP the same.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
DELIMITER |;
|
|
CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND STARTS NOW() DO
|
|
BEGIN
|
|
DECLARE EXIT HANDLER FOR 1136 BEGIN
|
|
INSERT INTO t3 VALUES (1);
|
|
END; -- ER_WRONG_VALUE_COUNT_ON_ROW
|
|
INSERT INTO t2 SELECT * FROM t1;
|
|
END|
|
|
DELIMITER ;|
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send DROP EVENT e1;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock' AND info = 'DROP EVENT e1';`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t2 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t2 ;
|
|
|
|
--let $diff_tables= master:t2, slave:t2
|
|
--source include/diff_tables.inc
|
|
|
|
# Concurrent execution of INSERT and ALTER on VIEW inside an active transaction.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
CREATE VIEW v1 AS SELECT * FROM t1;
|
|
BEGIN;
|
|
INSERT INTO v1 VALUES (11);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER VIEW v1 AS SELECT * FROM t2;
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER VIEW v1 AS SELECT * FROM t2'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM v1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM v1;
|
|
|
|
--let $diff_tables= master:v1, slave:v1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of INSERT and DROP on VIEW inside an active transaction.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
DROP VIEW v1;
|
|
CREATE VIEW v1 AS SELECT * FROM t1;
|
|
BEGIN;
|
|
INSERT INTO v1 VALUES (12);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send DROP VIEW v1;
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'DROP VIEW v1'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM v1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM v1 ;
|
|
|
|
--let $diff_tables= master:v1, slave:v1
|
|
--source include/diff_tables.inc
|
|
|
|
# Concurrent execution of ALTER and UPDATE on VIEW inside an active transaction.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
DROP VIEW v1;
|
|
CREATE VIEW v1 AS SELECT a, 1 AS col2 FROM t1;
|
|
BEGIN;
|
|
UPDATE v1 SET a = 0;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER VIEW v1 AS SELECT a,1 AS col2 FROM t2;
|
|
--echo --- connection master ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER VIEW v1 AS SELECT a,1 AS col2 FROM t2'`;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM v1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM v1 ;
|
|
|
|
--let $diff_tables= master:v1, slave:v1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of INSERT and CREATE REPLACE ON VIEW inside an active transaction.
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
DROP VIEW v1;
|
|
CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1;
|
|
BEGIN;
|
|
INSERT INTO v1 VALUES(13);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER VIEW v1 AS SELECT * FROM t2;
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER VIEW v1 AS SELECT * FROM t2'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM v1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM v1 ;
|
|
|
|
--let $diff_tables= master:v1, slave:v1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of OPTIMIZE TABLE and DROP TABLE
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
INSERT INTO t1opt VALUES (1,'Mysql locking',20,'Transactional locking',10);
|
|
INSERT INTO t1opt SELECT * FROM t1opt;
|
|
BEGIN;
|
|
--disable_result_log
|
|
OPTIMIZE TABLE t1opt;
|
|
--enable_result_log
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send DROP TABLE t1opt;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'DROP TABLE t1opt'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
|
|
--let $diff_tables= master:v1, slave:v1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of OPTIMIZE TABLE and DROP TABLE
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
CREATE TABLE t1opt (id INT , content TEXT, author_id INT,
|
|
article_title VARCHAR(120), article_hash INT);
|
|
INSERT INTO t1opt VALUES (2,'Mysql locking1',22,'Transactional locking1',11);
|
|
INSERT INTO t1opt SELECT * FROM t1opt;
|
|
BEGIN;
|
|
--disable_result_log
|
|
OPTIMIZE TABLE t1opt;
|
|
--enable_result_log
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t1opt RENAME TO t1opt;
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'ALTER TABLE t1opt RENAME TO t1opt'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1opt ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1opt ;
|
|
|
|
--let $diff_tables= master:t1opt, slave:t1opt
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of INSERT and DROP using more than one table
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (15);
|
|
INSERT INTO t3 VALUES (10);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send DROP TABLE t1,t3;
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'DROP TABLE t1,t3'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
SELECT COUNT(*) FROM t3 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1 ;
|
|
SELECT COUNT(*) FROM t3 ;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--let $diff_tables= master:t3, slave:t3
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of DELETE and DROP using more than one table
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
BEGIN;
|
|
DELETE t3.*, t4.* FROM t3, t4 WHERE t3.a = t4.a AND t3.a = 1;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send DROP TABLE t3, t4;
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status = `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Waiting for TABLE metadata lock'
|
|
AND info = 'DROP TABLE t3, t4'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
SELECT COUNT(*) FROM t3 ;
|
|
SELECT COUNT(*) FROM t4 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t3 ;
|
|
SELECT COUNT(*) FROM t4 ;
|
|
|
|
--let $diff_tables= master:t3, slave:t3
|
|
--let $diff_tables= master:t4, slave:t4
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
# Concurrent execution of DROP DATABASE and INSERT in an active transaction
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
DROP DATABASE db1;
|
|
CREATE DATABASE db1;
|
|
USE db1;
|
|
CREATE TABLE db1.t1(a INT, b INT);
|
|
BEGIN;
|
|
INSERT INTO db1.t1 VALUES (1, 1);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send DROP DATABASE db1;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status= `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Table lock' AND info = 'DROP DATABASE db1'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
USE db1;
|
|
SELECT COUNT(*) FROM t1;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
DROP TABLE db1.t1;
|
|
|
|
# Concurrent execution of ALTER DATABASE and INSERT in an active transaction
|
|
USE test;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
DROP DATABASE db1;
|
|
CREATE DATABASE db1;
|
|
USE db1;
|
|
CREATE TABLE db1.t1(a INT, b INT);
|
|
BEGIN;
|
|
INSERT INTO db1.t1 VALUES (1,1);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER DATABASE db1 CHARACTER SET big5;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status= `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Table lock' AND info = 'ALTER DATABASE db1 CHARACTER SET big5;'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
USE db1;
|
|
SELECT COUNT(*) FROM t1;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1;
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
DROP TABLE db1.t1;
|
|
|
|
USE test;
|
|
# Select inside FUNCTION takes a shared lock
|
|
--echo --- connection master ---
|
|
connection master;
|
|
USE test;
|
|
CREATE TABLE tab1(x INT PRIMARY KEY, y INT) ENGINE=XENGINE;
|
|
INSERT tab1 VALUES (1, 0), (2, 0), (3, 0), (4, 0), (5, 0);
|
|
DELIMITER |;
|
|
CREATE FUNCTION func(z INT) RETURNS INT
|
|
READS SQL DATA
|
|
RETURN (SELECT x FROM tab1 WHERE x = z)
|
|
|
|
|
DELIMITER ;|
|
|
BEGIN;
|
|
SELECT func(1);
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
USE test;
|
|
BEGIN;
|
|
SELECT func(1);
|
|
UPDATE tab1 SET y = 1 WHERE x = 1;
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
COMMIT;
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
USE test;
|
|
SELECT COUNT(*) FROM tab1 ;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM tab1;
|
|
|
|
|
|
--let $diff_tables= master:tab1, slave:tab1
|
|
--source include/diff_tables.inc
|
|
|
|
DROP FUNCTION func;
|
|
DROP TABLE tab1;
|
|
|
|
# Exclusive locking on table
|
|
|
|
USE test;
|
|
CREATE TABLE t1lock (a INT);
|
|
CREATE TABLE t2lock (a INT);
|
|
--echo --- connection master ---
|
|
connection master;
|
|
LOCK TABLE t1lock WRITE, t2lock WRITE;
|
|
DROP TABLE t1lock;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t2lock RENAME t1lock;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status= `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Table lock' AND info = 'ALTER TABLE t1lock RENAME t2lock;'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
USE test;
|
|
SELECT COUNT(*) FROM t2lock;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t2lock;
|
|
|
|
--let $diff_tables= master:t2lock, slave:t2lock
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
UNLOCK TABLES;
|
|
CREATE TABLE t1lock (a INT);
|
|
LOCK TABLE t1lock WRITE;
|
|
UNLOCK TABLES;
|
|
DROP TABLE t2lock;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
--echo send statement and reap result later ---
|
|
--send ALTER TABLE t1lock RENAME t2lock;
|
|
|
|
--echo --- connection master2 ---
|
|
connection master2;
|
|
let $lock_status= `SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE state = 'Table lock' and info = 'ALTER TABLE t1lock RENAME t2lock;'`;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
--echo reap result of previous---
|
|
|
|
--reap
|
|
|
|
--echo --- connection master ---
|
|
connection master;
|
|
COMMIT;
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
USE test;
|
|
SELECT COUNT(*) FROM t2lock;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t2lock;
|
|
|
|
--let $diff_tables= master:t2lock, slave:t2lock
|
|
--source include/diff_tables.inc
|
|
|
|
DROP TABLE t2lock;
|
|
|
|
# Waiting for lock and hangs
|
|
CREATE TABLE t1lock1 (a INT, b INT);
|
|
CREATE TABLE t2lock1 (a INT, b INT);
|
|
--echo --- connection master ---
|
|
connection master;
|
|
LOCK TABLES t1lock1 WRITE;
|
|
# InnoDB shows: 'OK' ,whereas, MyISAM shows: 'Table is already up to date'
|
|
--replace_result OK "Table is already up to date"
|
|
ANALYZE TABLE t1lock1;
|
|
UNLOCK TABLES;
|
|
|
|
--echo --- connection master1 ---
|
|
connection master1;
|
|
SET SESSION lock_wait_timeout = 1;
|
|
LOCK TABLE t2lock1 WRITE;
|
|
FLUSH TABLE t2lock1;
|
|
UNLOCK TABLES;
|
|
|
|
--sync_slave_with_master
|
|
|
|
--echo --- connection slave ---
|
|
connection slave;
|
|
USE test;
|
|
SELECT COUNT(*) FROM t1lock1;
|
|
--echo --- connection master ---
|
|
connection master;
|
|
SELECT COUNT(*) FROM t1lock1;
|
|
|
|
--let $diff_tables= master:t1lock1, slave:t1lock1
|
|
--source include/diff_tables.inc
|
|
|
|
# Cleanup
|
|
DROP TABLE t1lock1,t2lock1;
|
|
DROP TABLE t1,t2,t3,t4;
|
|
DROP TABLE t1part,t2part,t3part,t4part;
|
|
DROP TABLE t1prim,t1opt;
|
|
DROP TABLE t1trig,t2trig;
|
|
DROP TABLE t1bin,t2bin,t1myisam,t1innodb;
|
|
DROP TABLE t3967_1,t3967_2,t1_30138,t1_30138_old;
|
|
DROP FUNCTION f1;
|
|
DROP PROCEDURE p1;
|
|
DROP VIEW v1;
|
|
DROP USER user1@localhost;
|
|
DROP DATABASE db1;
|
|
|
|
--sync_slave_with_master
|
|
--source include/rpl_end.inc
|
|
connection slave;
|
|
disconnect slave;
|
|
--source include/wait_until_disconnected.inc
|
|
connection master3;
|
|
disconnect master3;
|
|
--source include/wait_until_disconnected.inc
|
|
connection master2;
|
|
disconnect master2;
|
|
--source include/wait_until_disconnected.inc
|
|
connection master1;
|
|
disconnect master1;
|
|
--source include/wait_until_disconnected.inc
|
|
connection master;
|
|
disconnect master;
|
|
--source include/wait_until_disconnected.inc
|
|
|