diff options
Diffstat (limited to 'mysql-test/main/lock_multi_bug38499.test')
| -rw-r--r-- | mysql-test/main/lock_multi_bug38499.test | 228 |
1 files changed, 228 insertions, 0 deletions
diff --git a/mysql-test/main/lock_multi_bug38499.test b/mysql-test/main/lock_multi_bug38499.test new file mode 100644 index 00000000000..b812984e516 --- /dev/null +++ b/mysql-test/main/lock_multi_bug38499.test @@ -0,0 +1,228 @@ +# Bug38499 flush tables and multitable table update with derived table cause crash +# MySQL >= 5.0 +# + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +SET @odl_sync_frm = @@global.sync_frm; +SET @@global.sync_frm = OFF; + +connect (locker,localhost,root,,); +connect (writer,localhost,root,,); + +--connection default +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +CREATE TABLE t1( a INT, b INT ); +CREATE TABLE t2( a INT, b INT ); +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4); +INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4); + +--echo # 1. test regular tables +--echo # 1.1. test altering of columns that multiupdate doesn't use +--echo # 1.1.1. normal mode + +--disable_query_log +let $i = 100; +while ($i) { +--dec $i + +--connection writer + send UPDATE t1, (SELECT 1 FROM t2 t1i) d SET a = 0 WHERE 1=0; + +--connection locker + ALTER TABLE t1 ADD COLUMN (c INT); + ALTER TABLE t1 DROP COLUMN c; + +--connection writer +--reap +} + +--echo # 1.1.2. PS mode + +--connection writer +PREPARE stmt FROM 'UPDATE t1, (SELECT 1 FROM t2 t1i) d SET a = 0 WHERE 1=0'; + +let $i = 100; +while ($i) { +--dec $i + +--connection writer +--send EXECUTE stmt + +--connection locker + ALTER TABLE t1 ADD COLUMN (c INT); + ALTER TABLE t1 DROP COLUMN c; + +--connection writer +--reap +} +--enable_query_log + +--echo # 1.2. test altering of columns that multiupdate uses +--echo # 1.2.1. normal mode + +--connection default + +--disable_query_log +let $i = 100; +while ($i) { + dec $i; + +--connection locker +--error 0,ER_DUP_FIELDNAME + ALTER TABLE t1 ADD COLUMN a int(11) unsigned default NULL; + UPDATE t1 SET a=b; + +--connection writer +--send UPDATE t1, (SELECT 1 FROM t2 t1i) d SET a = 0 WHERE 1=0; + +--connection locker +--error 0,ER_CANT_DROP_FIELD_OR_KEY + ALTER TABLE t1 DROP COLUMN a; + +--connection writer +--error 0,ER_BAD_FIELD_ERROR # unknown column error +--reap +} +--enable_query_log + +--echo # 1.2.2. PS mode + +--disable_query_log +let $i = 100; +while ($i) { + dec $i; + +--connection locker +--error 0,ER_DUP_FIELDNAME + ALTER TABLE t1 ADD COLUMN a INT; + UPDATE t1 SET a=b; + +--connection writer + PREPARE stmt FROM 'UPDATE t1, (SELECT 1 FROM t2 t1i) d SET a = 0 WHERE 1=0'; +--send EXECUTE stmt + +--connection locker +--error 0,ER_CANT_DROP_FIELD_OR_KEY + ALTER TABLE t1 DROP COLUMN a; + +--connection writer +--error 0,ER_BAD_FIELD_ERROR # Unknown column 'a' in 'field list' +--reap +} +--enable_query_log +--connection default +ALTER TABLE t1 ADD COLUMN a INT; + +--echo # 2. test UNIONs +--echo # 2.1. test altering of columns that multiupdate doesn't use +--echo # 2.1.1. normal mode + +--disable_query_log +let $i = 100; +while ($i) { +--dec $i + +--connection writer + send UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0; + +--connection locker + ALTER TABLE t1 ADD COLUMN (c INT); + ALTER TABLE t1 DROP COLUMN c; + +--connection writer +--reap +} + +--echo # 2.1.2. PS mode + +--connection writer +PREPARE stmt FROM 'UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0'; + +let $i = 100; +while ($i) { +--dec $i + +--connection writer +--send EXECUTE stmt + +--connection locker + ALTER TABLE t1 ADD COLUMN (c INT); + ALTER TABLE t1 DROP COLUMN c; + +--connection writer +--reap +} +--enable_query_log + +--echo # 2.2. test altering of columns that multiupdate uses +--echo # 2.2.1. normal mode + +--connection default + +--disable_query_log +let $i = 100; +while ($i) { + dec $i; + +--connection locker +--error 0,ER_DUP_FIELDNAME + ALTER TABLE t1 ADD COLUMN a int(11) unsigned default NULL; + UPDATE t1 SET a=b; + +--connection writer +--send UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0; + +--connection locker +--error 0,ER_CANT_DROP_FIELD_OR_KEY + ALTER TABLE t1 DROP COLUMN a; + +--connection writer +--error 0,ER_BAD_FIELD_ERROR # Unknown column 'a' in 'field list' +--reap +} +--enable_query_log + +--echo # 2.2.2. PS mode + +--disable_query_log +let $i = 100; +while ($i) { + dec $i; + +--connection locker +--error 0,ER_DUP_FIELDNAME + ALTER TABLE t1 ADD COLUMN a INT; + UPDATE t1 SET a=b; + +--connection writer + PREPARE stmt FROM 'UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0'; +--send EXECUTE stmt + +--connection locker +--error 0,ER_CANT_DROP_FIELD_OR_KEY + ALTER TABLE t1 DROP COLUMN a; + +--connection writer +--error 0,ER_BAD_FIELD_ERROR # Unknown column 'a' in 'field list' +--reap +} +--enable_query_log +--connection default +DROP TABLE t1,t2; + + +# Close connections +--disconnect locker +--disconnect writer + +SET @@global.sync_frm = @odl_sync_frm; + +# End of 5.0 tests + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + |