ALTER TABLE temporary tables not listed in INFORMATION_SCHEMA.TEMPORARY_TABLES

[Laurynas Biveinis]

The temporary tables that are created for ALTER TABLE execution are not listed in INFORMATION_SCHEMA.TEMPORARY_TABLES or GLOBAL_TEMPORARY_TABLES tables.

To verify, add a debug sync point that stops the ALTER TABLE thread when temporary table is in use, e.g. at the start of handler::ha_write_row(). Then the following partial testcase

CREATE TABLE t1 (a INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1), (2), (3);

SET DEBUG_SYNC= 'start_ha_write_row SIGNAL write_in_progress WAIT_FOR i_s_completed';
send ALTER TABLE t1 ADD COLUMN b VARCHAR(10);

connect (conn2,localhost,root,,);
connection conn2;

SET DEBUG_SYNC= 'now WAIT_FOR write_in_progress';
SELECT COUNT(*) FROM INFORMATION_SCHEMA.GLOBAL_TEMPORARY_TABLES;

will return 0 for the temp table count even though it should be 1.

The cause for this is that the temp table is created with open_temporary_table() call in mysql_alter_table() with link_in_list==FALSE, thus the table won't be added to thd->temporary_tables list.

This was fixed in 5.5 by the 2875.107.1 revision (backport from 6.0 WL#3726 "DDL locking for all metadata objects")

Test case and fix will be together with bug 951588.

[Laurynas Biveinis (laurynas-biveinis)]

IMHO not worth to fix in 5.1, let's document the limitation instead.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2763