*summary:* Recently we hit lost tables during DDL for online products, and after some observersion, we found some interesting hehaviors if fil_rename_tablespace failed as retry > 250000. That might be another issue that not discussed here. THERE MUST BE SINGLE TABLESPACE
Here is the steps to show the behavior: *1. compile 5.1.48 source code.* CFLAGS="-O0 -g" CXX=gcc CXXFLAGS="-O0 -g -felide-constructors \ -fno-exceptions -fno-rtti" export CFLAGS CXXFLAGS ./configure --with-plugins=innodb_plugin make -j32 *2. debug with plugin innodb* 2.1 # attach a running pid (gdb) b fil_rename_tablespace Breakpoint 1 at 0x2aaaaabfed30: file fil/fil0fil.c, line 2435. (gdb) c Continuing. [Switching to Thread 0x4944a940 (LWP 7069)] Breakpoint 1, fil_rename_tablespace (old_name=0xeacdc20 "sbtest/sb1", id=51, new_name=0xea96fa0 "sbtest/#sql2-1b8b-1") at fil/fil0fil.c:2435 2435 { (gdb) c Continuing. Breakpoint 1, fil_rename_tablespace (old_name=0xeabb3f0 "sbtest/#sql-1b8b_1", id=52, new_name=0xea96fa0 "sbtest/sb1") at fil/fil0fil.c:2435 2435 { (gdb) return 0 Make fil_rename_tablespace return now? (y or n) y #0 0x00002aaaaabea8c4 in dict_table_rename_in_cache (table=0xeabb288, new_name=0xea96fa0 "sbtest/sb1", rename_also_foreigns=1) at dict/dict0dict.c:951 951 } else if (!fil_rename_tablespace(old_name, table->space, (gdb) (gdb) l 946 fputs(" (", stderr); 947 ut_print_filename(stderr, 948 table->dir_path_of_temp_table); 949 fputs(" )\n", stderr); 950 return(FALSE); 951 } else if (!fil_rename_tablespace(old_name, table->space, 952 new_name)) { 953 return(FALSE); 954 } 955 } (gdb) finish Run till exit from #0 0x00002aaaaabea8c4 in dict_table_rename_in_cache (table=0xeabb288, new_name=0xea96fa0 "sbtest/sb1", rename_also_foreigns=1) at dict/dict0dict.c:951 0x00002aaaaac6527e in row_rename_table_for_mysql (old_name=0xea97000 "sbtest/#sql-1b8b_1", new_name=0xea96fa0 "sbtest/sb1", trx=0xeacf728, commit=1) at row/row0mysql.c:3905 3905 if (!dict_table_rename_in_cache(table, new_name, Value returned is $1 = 0 (gdb) l 3900 trx->error_state = DB_SUCCESS; 3901 } else { 3902 /* The following call will also rename the .ibd data file if 3903 the table is stored in a single-table tablespace */ 3904 3905 if (!dict_table_rename_in_cache(table, new_name, 3906 !new_is_tmp)) { 3907 trx->error_state = DB_SUCCESS; 3908 trx_general_rollback_for_mysql(trx, NULL); 3909 trx->error_state = DB_SUCCESS; (gdb) l 3910 goto funct_exit; 3911 } 3912 3913 /* We only want to switch off some of the type checking in 3914 an ALTER, not in a RENAME. */ 3915 3916 err = dict_load_foreigns( 3917 new_name, !old_is_tmp || trx->check_foreigns); 3918 3919 if (err != DB_SUCCESS) { (gdb) finish Run till exit from #0 0x00002aaaaac6527e in row_rename_table_for_mysql (old_name=0xea97000 "sbtest/#sql-1b8b_1", new_name=0xea96fa0 "sbtest/sb1", trx=0xeacf728, commit=1) at row/row0mysql.c:3905 0x00002aaaaac14e0b in ha_innodb::rename_table () from /u01/mysql-5.1.48/storage/innodb_plugin/.libs/ha_innodb_plugin.so Value returned is $2 = 10 2.2 here is the key routines: storage/innodb_plugin/row/row0mysql.c: row_rename_table_for_mysql 3905 if (!dict_table_rename_in_cache(table, new_name, 3906 !new_is_tmp)) { 3907 trx->error_state = DB_SUCCESS; 3908 trx_general_rollback_for_mysql(trx, NULL); 3909 trx->error_state = DB_SUCCESS; 3910 goto funct_exit; 3911 } 2.3 client's output: root@sbtest 05:00:24>alter table sb1 add column d2 int; Query OK, 0 rows affected (9 min 47.97 sec) Records: 0 Duplicates: 0 Warnings: 0 root@sbtest 05:11:26>desc sb1; ERROR 1146 (42S02): Table 'sbtest.sb1' doesn't exist *3. different code between innodb and plugin* 3.1 innodb add err = DB_ERROR; before goto funct_exit; when dict_table_rename_in_cache failed. 3.2 test innodb work and result could be refered to modified innodb plugin just like innobase behavior, add err = DB_ERROR; before exit 3905 if (!dict_table_rename_in_cache(table, new_name, 3906 !new_is_tmp)) { 3907 trx->error_state = DB_SUCCESS; 3908 trx_general_rollback_for_mysql(trx, NULL); 3909 trx->error_state = DB_SUCCESS; 3910 err = DB_ERROR; 3911 goto funct_exit; 3912 } 3.3 re-debug with modified innodb plugin Breakpoint 1, fil_rename_tablespace (old_name=0x15b19620 "sbtest/sb2", id=53, new_name=0x15b26250 "sbtest/#sql2-1eb9-1") at fil/fil0fil.c:2435 2435 { (gdb) c Continuing. Breakpoint 1, fil_rename_tablespace (old_name=0x15ae4600 "sbtest/#sql-1eb9_1", id=54, new_name=0x15b26250 "sbtest/sb2") at fil/fil0fil.c:2435 2435 { (gdb) return 0 Make fil_rename_tablespace return now? (y or n) y #0 0x00002aaaaabea8c4 in dict_table_rename_in_cache (table=0x15ae4498, new_name=0x15b26250 "sbtest/sb2", rename_also_foreigns=1) at dict/dict0dict.c:951 951 } else if (!fil_rename_tablespace(old_name, table->space, (gdb) finish Run till exit from #0 0x00002aaaaabea8c4 in dict_table_rename_in_cache (table=0x15ae4498, new_name=0x15b26250 "sbtest/sb2", rename_also_foreigns=1) at dict/dict0dict.c:951 0x00002aaaaac6527e in row_rename_table_for_mysql (old_name=0x15b1a7a0 "sbtest/#sql-1eb9_1", new_name=0x15b26250 "sbtest/sb2", trx=0x15b25998, commit=1) at row/row0mysql.c:3905 3905 if (!dict_table_rename_in_cache(table, new_name, Value returned is $1 = 0 (gdb) finish Run till exit from #0 0x00002aaaaac6527e in row_rename_table_for_mysql (old_name=0x15b1a7a0 "sbtest/#sql-1eb9_1", new_name=0x15b26250 "sbtest/sb2", trx=0x15b25998, commit=1) at row/row0mysql.c:3905 0x00002aaaaac14e0b in ha_innodb::rename_table () from /u01/mysql-5.1.48/storage/innodb_plugin/.libs/ha_innodb_plugin.so Value returned is $2 = 11 (gdb) c Continuing. Breakpoint 1, fil_rename_tablespace (old_name=0x15b19770 "sbtest/#sql2-1eb9-1", id=53, new_name=0x15b1a7a0 "sbtest/sb2") at fil/fil0fil.c:2435 2435 { (gdb) c Continuing. c 3.4 client's output root@sbtest 05:15:17>create table sb2 (a int); Query OK, 0 rows affected (0.01 sec) root@sbtest 05:15:20>alter table sb2 add column d2 int; ERROR 1025 (HY000): Error on rename of './sbtest/#sql-1eb9_1' to './sbtest/sb2' (errno: -1) root@sbtest 05:16:28> *4. question:* why removed the err=DB_ERROR? It's a defect or intention?