drop table if exists t1, t2;
set debug_sync='RESET';
create table t1 (n1 int, n2 int, n3 int,
key (n1, n2, n3),
key (n2, n3, n1),
key (n3, n1, n2)) engine=myisam;
create table t2 (i int);
alter table t1 disable keys;
insert into t1 values (1, 2, 3);
reset master;
set debug_sync='alter_table_enable_indexes SIGNAL parked WAIT_FOR go';
alter table t1 enable keys;;
set debug_sync='now WAIT_FOR parked';
insert into t2 values (1);
insert into t1 values (1, 1, 1);;
set debug_sync='now SIGNAL go';
include/show_binlog_events.inc
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
binlog.000001	#	Query	#	#	BEGIN
binlog.000001	#	Query	#	#	use `test`; insert into t2 values (1)
binlog.000001	#	Xid	#	#	COMMIT /* XID */
binlog.000001	#	Query	#	#	use `test`; alter table t1 enable keys
binlog.000001	#	Query	#	#	BEGIN
binlog.000001	#	Query	#	#	use `test`; insert into t1 values (1, 1, 1)
binlog.000001	#	Query	#	#	COMMIT
drop tables t1, t2;
set debug_sync='RESET';
End of 5.0 tests
drop table if exists t1, t2, t3;
create table t1 (i int);
reset master;
set debug_sync='alter_table_before_main_binlog SIGNAL parked WAIT_FOR go';
alter table t1 change i c char(10) default 'Test1';;
set debug_sync='now WAIT_FOR parked';
insert into t1 values ();;
set debug_sync='now SIGNAL go';
select * from t1;
c
Test1
set debug_sync='alter_table_before_main_binlog SIGNAL parked WAIT_FOR go';
alter table t1 change c vc varchar(100) default 'Test2';;
set debug_sync='now WAIT_FOR parked';
rename table t1 to t2;;
set debug_sync='now SIGNAL go';
drop table t2;
create table t1 (i int);
set debug_sync='alter_table_before_main_binlog SIGNAL parked WAIT_FOR go';
alter table t1 change i c char(10) default 'Test3', rename to t2;;
set debug_sync='now WAIT_FOR parked';
insert into t2 values();;
set debug_sync='now SIGNAL go';
select * from t2;
c
Test3
alter table t2 change c vc varchar(100) default 'Test2', rename to t1;;
rename table t1 to t3;
drop table t3;
set debug_sync='alter_table_before_main_binlog SIGNAL parked WAIT_FOR go';
set debug_sync='RESET';
include/show_binlog_events.inc
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
binlog.000001	#	Query	#	#	use `test`; alter table t1 change i c char(10) default 'Test1'
binlog.000001	#	Query	#	#	BEGIN
binlog.000001	#	Query	#	#	use `test`; insert into t1 values ()
binlog.000001	#	Xid	#	#	COMMIT /* XID */
binlog.000001	#	Query	#	#	use `test`; alter table t1 change c vc varchar(100) default 'Test2'
binlog.000001	#	Query	#	#	use `test`; rename table t1 to t2
binlog.000001	#	Query	#	#	use `test`; DROP TABLE `t2` /* generated by server */
binlog.000001	#	Query	#	#	use `test`; create table t1 (i int)
binlog.000001	#	Query	#	#	use `test`; alter table t1 change i c char(10) default 'Test3', rename to t2
binlog.000001	#	Query	#	#	BEGIN
binlog.000001	#	Query	#	#	use `test`; insert into t2 values()
binlog.000001	#	Xid	#	#	COMMIT /* XID */
binlog.000001	#	Query	#	#	use `test`; alter table t2 change c vc varchar(100) default 'Test2', rename to t1
binlog.000001	#	Query	#	#	use `test`; rename table t1 to t3
binlog.000001	#	Query	#	#	use `test`; DROP TABLE `t3` /* generated by server */
End of 5.1 tests
#
# Additional coverage for WL#7743 "New data dictionary: changes
# to DDL-related parts of SE API".
#
# Killed ALTER TABLE on temporary table sometimes led to assertion
# failure on connection close.
connect  con1, localhost, root,,;
create temporary table t1 (i int) engine=innodb;
set debug= "+d,mysql_lock_tables_kill_query";
alter table t1 add index (i);
ERROR 70100: Query execution was interrupted
set debug= "-d,mysql_lock_tables_kill_query";
# The below disconnect should drop temporary table automagically.
disconnect con1;
connection default;
#
# Test coverage for new (since 8.0) behavior of ALTER TABLE RENAME
# under LOCK TABLES.
#
connect  con1, localhost, root,,;
SET @old_lock_wait_timeout= @@lock_wait_timeout;
connection default;
#
# 1) Simple ALTER TABLE RENAME.
#
# 1.1) Successfull ALTER TABLE RENAME.
#
CREATE TABLE t1 (i INT);
LOCK TABLES t1 WRITE;
ALTER TABLE t1 RENAME TO t2;
# Table is available under new name under LOCK TABLES.
SELECT * FROM t2;
i
connection con1;
# Access by new name from other connections should be blocked.
SET @@lock_wait_timeout= 1;
SELECT * FROM t2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
# But not for old table name.
SELECT * FROM t1;
ERROR 42S02: Table 'test.t1' doesn't exist
connection default;
UNLOCK TABLES;
#
# 1.2) ALTER TABLE RENAME in case when several tables are locked.
#
CREATE TABLE t1 (i INT);
LOCK TABLES t1 READ, t2 WRITE;
ALTER TABLE t2 RENAME TO t3;
# Table t1 should be still locked, and t2 should be available as t3
# with correct lock type.
SELECT * FROM t1;
i
INSERT INTO t3 values (1);
UNLOCK TABLES;
#
# 1.3) ALTER TABLE RENAME in case when same table locked more than once.
#
LOCK TABLES t1 READ, t3 WRITE, t3 AS a WRITE, t3 AS b READ;
ALTER TABLE t3 RENAME TO t4;
# Check that tables are locked under correct aliases and with modes.
SELECT * FROM t4 AS a, t4 AS b;
i	i
1	1
INSERT INTO t4 VALUES (2);
DELETE a FROM t4 AS a, t4 AS b;
DELETE b FROM t4 AS a, t4 AS b;
ERROR HY000: Table 'b' was locked with a READ lock and can't be updated
UNLOCK TABLES;
DROP TABLES t1, t4;
# 1.4) ALTER TABLE RENAME to different schema.
#
CREATE TABLE t1 (i INT);
CREATE DATABASE mysqltest;
LOCK TABLES t1 WRITE;
ALTER TABLE t1 RENAME TO mysqltest.t1;
# Table is available in new schema under LOCK TABLES.
SELECT * FROM mysqltest.t1;
i
connection con1;
# Access by new name from other connections should be blocked.
SET @@lock_wait_timeout= 1;
SELECT * FROM mysqltest.t1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
# But not to old schema and table name.
SELECT * FROM t1;
ERROR 42S02: Table 'test.t1' doesn't exist
# Also IX lock on new schema should be kept.
SET @@lock_wait_timeout= 1;
ALTER DATABASE mysqltest CHARACTER SET latin1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection default;
UNLOCK TABLES;
DROP DATABASE mysqltest;
#
# 1.5) ALTER TABLE RENAME which fails at the late stage for SEs
#      supporting and not supporting atomic DDL.
#
CREATE TABLE t1 (i INT) ENGINE=XENGINE;
CREATE TABLE t2 (i INT) ENGINE=MyISAM;
LOCK TABLES t1 WRITE, t2 WRITE;
SET @@debug='+d,injecting_fault_writing';
ALTER TABLE t1 RENAME TO t3;
ERROR HY000: Error writing file 'binlog' ((errno: #)
SET @@debug='-d,injecting_fault_writing';
# For SE supporting atomic DDL table still should be available under
# old name.
SELECT * FROM t1;
i
connection con1;
# New name should not be locked.
SELECT * FROM t3;
ERROR 42S02: Table 'test.t3' doesn't exist
connection default;
SET @@debug='+d,injecting_fault_writing';
ALTER TABLE t2 RENAME TO t4;
ERROR HY000: Error writing file 'binlog' ((errno: #)
SET @@debug='-d,injecting_fault_writing';
# For SE not supporting atomic DDL table will be
# removed from list of locked tables. And new
# name should not be added.
SELECT * FROM t2;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
SELECT * FROM t4;
ERROR HY000: Table 't4' was not locked with LOCK TABLES
connection con1;
# But metadata lock on old name can be still kept.
SET @@lock_wait_timeout= 1;
SELECT * FROM t2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
# New name should not be locked.
SELECT * FROM t4;
i
connection default;
UNLOCK TABLES;
DROP TABLES t1, t4;
#
# 2) ALTER TABLE INPLACE with RENAME clause.
#
# 2.1) Successful ALTER TABLE INPLACE with RENAME clause.
#
CREATE TABLE t1 (i INT);
LOCK TABLES t1 WRITE;
ALTER TABLE t1 ADD COLUMN j INT, RENAME TO t2, ALGORITHM=INPLACE;
# Table is available under new name under LOCK TABLES.
SELECT * FROM t2;
i	j
connection con1;
# Access by new name from other connections should be blocked.
SET @@lock_wait_timeout= 1;
SELECT * FROM t2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
# But not for old table name.
SELECT * FROM t1;
ERROR 42S02: Table 'test.t1' doesn't exist
connection default;
UNLOCK TABLES;
#
# 2.2) ALTER TABLE INPLACE with RENAME clause in case when several
#      tables are locked.
#
CREATE TABLE t1 (i INT);
LOCK TABLES t1 READ, t2 WRITE;
ALTER TABLE t2 ADD COLUMN k INT, RENAME TO t3, ALGORITHM=INPLACE;
# Table t1 should be still locked, and t2 should be available as t3
# with correct lock type.
SELECT * FROM t1;
i
INSERT INTO t3 values (1, 2, 3);
UNLOCK TABLES;
#
# 2.3) ALTER TABLE INPLACE with RENAME clause in case when same table
#      locked more than once.
#
LOCK TABLES t1 READ, t3 WRITE, t3 AS a WRITE, t3 AS b READ;
ALTER TABLE t3 ADD COLUMN l INT, RENAME TO t4, ALGORITHM=INPLACE;
# Check that tables are locked under correct aliases and with modes.
SELECT * FROM t4 AS a, t4 AS b;
i	j	k	l	i	j	k	l
1	2	3	NULL	1	2	3	NULL
INSERT INTO t4 VALUES (2, 3, 4, 5);
DELETE a FROM t4 AS a, t4 AS b;
DELETE b FROM t4 AS a, t4 AS b;
ERROR HY000: Table 'b' was locked with a READ lock and can't be updated
UNLOCK TABLES;
DROP TABLES t1, t4;
# 2.4) ALTER TABLE INPLACE with RENAME clause to different schema.
#
CREATE TABLE t1 (i INT);
CREATE DATABASE mysqltest;
LOCK TABLES t1 WRITE;
ALTER TABLE t1 ADD COLUMN k INT, RENAME TO mysqltest.t1, ALGORITHM=INPLACE;
# Table is available in new schema under LOCK TABLES.
SELECT * FROM mysqltest.t1;
i	k
connection con1;
# Access by new name from other connections should be blocked.
SET @@lock_wait_timeout= 1;
SELECT * FROM mysqltest.t1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
# But not to old schema and table name.
SELECT * FROM t1;
ERROR 42S02: Table 'test.t1' doesn't exist
# Also IX lock on new schema should be kept.
SET @@lock_wait_timeout= 1;
ALTER DATABASE mysqltest CHARACTER SET latin1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection default;
UNLOCK TABLES;
DROP DATABASE mysqltest;
#
# 2.5) ALTER TABLE INPLACE with RENAME clause fails at the late stage
#      for SEs supporting and not supporting atomic DDL.
#
CREATE TABLE t1 (i INT) ENGINE=XENGINE;
CREATE TABLE t2 (i INT) ENGINE=MyISAM;
LOCK TABLES t1 WRITE, t2 WRITE;
SET @@debug='+d,injecting_fault_writing';
ALTER TABLE t1 ADD COLUMN j INT, RENAME TO t3, ALGORITHM=INPLACE;
ERROR HY000: Error writing file 'binlog' ((errno: #)
SET @@debug='-d,injecting_fault_writing';
# For SE supporting atomic DDL table still should be available under
# old name.
SELECT * FROM t1;
i
connection con1;
# New name should not be locked.
SELECT * FROM t3;
ERROR 42S02: Table 'test.t3' doesn't exist
connection default;
SET @@debug='+d,injecting_fault_writing';
ALTER TABLE t2 RENAME COLUMN i TO j, RENAME TO t4, ALGORITHM=INPLACE;
ERROR HY000: Error writing file 'binlog' ((errno: #)
SET @@debug='-d,injecting_fault_writing';
# For SE not supporting atomic DDL table will be
# removed from list of locked tables. And new
# name should not be added.
SELECT * FROM t2;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
SELECT * FROM t4;
ERROR HY000: Table 't4' was not locked with LOCK TABLES
connection con1;
# But metadata lock on old name can be still kept.
SET @@lock_wait_timeout= 1;
SELECT * FROM t2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
# New name should not be locked.
SELECT * FROM t4;
j
connection default;
UNLOCK TABLES;
DROP TABLES t1, t4;
#
# 3) ALTER TABLE COPY with RENAME clause.
#
# 3.1) Successful ALTER TABLE COPY with RENAME clause.
#
CREATE TABLE t1 (i INT);
LOCK TABLES t1 WRITE;
ALTER TABLE t1 ADD COLUMN j INT, RENAME TO t2, ALGORITHM=COPY;
# Table is available under new name under LOCK TABLES.
SELECT * FROM t2;
i	j
connection con1;
# Access by new name from other connections should be blocked.
SET @@lock_wait_timeout= 1;
SELECT * FROM t2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
# But not for old table name.
SELECT * FROM t1;
ERROR 42S02: Table 'test.t1' doesn't exist
connection default;
UNLOCK TABLES;
#
# 3.2) ALTER TABLE COPY with RENAME clause in case when several
#      tables are locked.
#
CREATE TABLE t1 (i INT);
LOCK TABLES t1 READ, t2 WRITE;
ALTER TABLE t2 ADD COLUMN k INT, RENAME TO t3, ALGORITHM=COPY;
# Table t1 should be still locked, and t2 should be available as t3
# with correct lock type.
SELECT * FROM t1;
i
INSERT INTO t3 values (1, 2, 3);
UNLOCK TABLES;
#
# 3.3) ALTER TABLE COPY with RENAME clause in case when same table
#      locked more than once.
#
LOCK TABLES t1 READ, t3 WRITE, t3 AS a WRITE, t3 AS b READ;
ALTER TABLE t3 ADD COLUMN l INT, RENAME TO t4, ALGORITHM=COPY;
# Check that tables are locked under correct aliases and with modes.
SELECT * FROM t4 AS a, t4 AS b;
i	j	k	l	i	j	k	l
1	2	3	NULL	1	2	3	NULL
INSERT INTO t4 VALUES (2, 3, 4, 5);
DELETE a FROM t4 AS a, t4 AS b;
DELETE b FROM t4 AS a, t4 AS b;
ERROR HY000: Table 'b' was locked with a READ lock and can't be updated
UNLOCK TABLES;
DROP TABLES t1, t4;
# 3.4) ALTER TABLE COPY with RENAME clause to different schema.
#
CREATE TABLE t1 (i INT);
CREATE DATABASE mysqltest;
LOCK TABLES t1 WRITE;
ALTER TABLE t1 ADD COLUMN k INT, RENAME TO mysqltest.t1, ALGORITHM=COPY;
# Table is available in new schema under LOCK TABLES.
SELECT * FROM mysqltest.t1;
i	k
connection con1;
# Access by new name from other connections should be blocked.
SET @@lock_wait_timeout= 1;
SELECT * FROM mysqltest.t1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
# But not to old schema and table name.
SELECT * FROM t1;
ERROR 42S02: Table 'test.t1' doesn't exist
# Also IX lock on new schema should be kept.
SET @@lock_wait_timeout= 1;
ALTER DATABASE mysqltest CHARACTER SET latin1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection default;
UNLOCK TABLES;
DROP DATABASE mysqltest;
#
# 3.5) ALTER TABLE COPY with RENAME clause fails at the late stage
#      for SEs supporting and not supporting atomic DDL.
#
CREATE TABLE t1 (i INT) ENGINE=XENGINE;
CREATE TABLE t2 (i INT) ENGINE=MyISAM;
CREATE DATABASE mysqltest;
LOCK TABLES t1 WRITE, t2 WRITE;
SET @@debug='+d,injecting_fault_writing';
ALTER TABLE t1 ADD COLUMN j INT, RENAME TO t3, ALGORITHM=COPY;
ERROR HY000: Error writing file 'binlog' ((errno: #)
SET @@debug='-d,injecting_fault_writing';
# For SE supporting atomic DDL table still should be available under
# old name.
SELECT * FROM t1;
i
connection con1;
# New name should not be locked.
SELECT * FROM t3;
ERROR 42S02: Table 'test.t3' doesn't exist
connection default;
DROP TABLE t1;
SET @@debug='+d,injecting_fault_writing';
ALTER TABLE t2 RENAME COLUMN i TO j, RENAME TO t4, ALGORITHM=COPY;
ERROR HY000: Error writing file 'binlog' ((errno: #)
SET @@debug='-d,injecting_fault_writing';
# For SE not supporting atomic DDL table will be
# removed from list of locked tables. And new
# name should not be added.
SELECT * FROM t2;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
SELECT * FROM t4;
ERROR HY000: Table 't4' was not locked with LOCK TABLES
connection con1;
# Metadata locks on both old and new names are still kept.
SET @@lock_wait_timeout= 1;
SELECT * FROM t2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SELECT * FROM t4;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection default;
UNLOCK TABLES;
# Now test SE not supporting atomic DDL and different schema
# to improve code coverage.
connection default;
LOCK TABLE t4 WRITE;
SET @@debug='+d,injecting_fault_writing';
ALTER TABLE t4 RENAME COLUMN j TO i, RENAME TO mysqltest.t4, ALGORITHM=COPY;
ERROR HY000: Error writing file 'binlog' ((errno: #)
SET @@debug='-d,injecting_fault_writing';
# For SE not supporting atomic DDL table will be
# removed from list of locked tables. And new
# name should not be added.
SELECT * FROM t4;
ERROR HY000: Table 't4' was not locked with LOCK TABLES
SELECT * FROM mysqltest.t4;
ERROR HY000: Table 't4' was not locked with LOCK TABLES
connection con1;
# Metadata locks on both old and new names are still kept.
SET @@lock_wait_timeout= 1;
SELECT * FROM t4;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SELECT * FROM mysqltest.t4;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Also IX lock on new schema should be kept.
ALTER DATABASE mysqltest CHARACTER SET latin1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection default;
UNLOCK TABLES;
DROP DATABASE mysqltest;
#
# 3.6) Special case ALTER TABLE COPY with RENAME clause which
#      non-atomic, adds foreign keys and fails at the late stage.
#
CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=XENGINE;
CREATE TABLE t2 (fk INT) ENGINE=MyISAM;
LOCK TABLES t2 WRITE, t1 WRITE;
SET @@debug='+d,injecting_fault_writing';
ALTER TABLE t2 ADD FOREIGN KEY (fk) REFERENCES t1(pk), ENGINE=InnoDB, RENAME TO t3, ALGORITHM=COPY;
ERROR HY000: Error writing file 'binlog' ((errno: #)
SET @@debug='-d,injecting_fault_writing';
# Table should be removed from locked tables list and new
# table name should not be added.
SELECT * FROM t2;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
SELECT * FROM t3;
ERROR HY000: Table 't3' was not locked with LOCK TABLES
connection con1;
# However, metadata locks on both old and new names are still kept.
SET @@lock_wait_timeout= 1;
SELECT * FROM t2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SELECT * FROM t3;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET @@lock_wait_timeout= @old_lock_wait_timeout;
connection default;
# And delete from parent table is possible and doesn't cause asserts.
DELETE FROM t1;
UNLOCK TABLES;
DROP TABLES t3, t1;
connection con1;
disconnect con1;
connection default;
#
# Bug#24786075   FIND A WAY TO LIST #SQL... TABLE LEFT IN      
#                DATA DICTIONARY IN CASE ALTER FAILS.
# Test that we can see hidden temporary tables using ALTER TABLE.
# Test that we can delete the hidden temporary tables that were
# left by ALTER TABLE table failures in rare situations.
#
CREATE TABLE t1(a INT) ENGINE=MyISAM;
SET debug="+d,exit_after_alter_table_before_rename";
ALTER TABLE t1 modify column a varchar(30);
ERROR HY000: Unknown error
SET debug="-d,exit_after_alter_table_before_rename";
# Verify that #sql... tables are not seen by I_S and SHOW
SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='test' AND TABLE_NAME like '#sql%';
COUNT(TABLE_NAME)
0
SHOW TABLES FROM test;
Tables_in_test
t1
# The SHOW EXTENDED [FULL] syntax should show the hidden table.
SHOW EXTENDED TABLES FROM test;
Tables_in_test
#sql-xxxxx
t1
SHOW EXTENDED FULL TABLES FROM test;
Tables_in_test	Table_type
#sql-xxxxx	BASE TABLE
t1	BASE TABLE
# Dropping the temporary table.
DROP TABLE `#sql-xxxxx;
# Verify that the temporary table is dropped.
SHOW EXTENDED TABLES FROM test;
Tables_in_test
t1
SHOW EXTENDED FULL TABLES FROM test;
Tables_in_test	Table_type
t1	BASE TABLE
# clean-up
DROP TABLE t1;