MySQL Bugs: #57586: unknown table when trying to drop a table and no available undo slots left

Bug #57586	unknown table when trying to drop a table and no available undo slots left
Submitted:	19 Oct 2010 22:45	Modified:	27 Jan 2012 12:33
Reporter:	Chris Calender	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.51	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	1024, max transactions, undo slots, unknown table

Description:
If you attempt to drop an existing InnoDB table, but you do not have any available undo slots open, then you will receive an "unknown table" error.

mysql> DROP TABLE test.innodb_table_monitor;
ERROR 1051 (42S02): Unknown table 'innodb_table_monitor'

How to repeat:
<first> make sure max_connections > 1024 </first>

use test;
create table t (id int) engine=innodb;
create table innodb_table_monitor (id int) engine=innodb;

Run following script:

<?
$db_arr=array();
for ($i = 1; $i <= 1024; $i++) {
	$db=$db_arr[$i]=mysql_connect("localhost:3307","root","pass",1);
        mysql_select_db("test",$db);
        mysql_query("BEGIN",$db) or die(mysql_error());
        mysql_query("INSERT INTO t VALUES ($i)",$db) or die(mysql_error());
}
$db=$db_arr[$i]=mysql_connect("localhost:3307","root","pass",1);
mysql_select_db("test",$db);
mysql_query("DROP TABLE test.innodb_table_monitor",$db) or die(mysql_error());
sleep(60);
?>

After the above script, it will throw the following error:

Unknown table 'innodb_table_monitor'

Or, if you omit the DROP from the script, and execute it from the CLI, after the 1024 transactions have been started and are sleeping, then you would get:

mysql> DROP TABLE test.innodb_table_monitor;
ERROR 1051 (42S02): Unknown table 'innodb_table_monitor'

And you can see the table does indeed exist (executed directly after the above command):

mysql> show create table innodb_table_monitor\G
*************************** 1. row ***************************
       Table: innodb_table_monitor
Create Table: CREATE TABLE `innodb_table_monitor` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Suggested fix:
Please print a more accurate error message in this case instead of reporting it is an "unknown table".

For reference, this is somewhat similar to the following bug:

http://bugs.mysql.com/bug.php?id=32476

However, that bug throws the "unknown table" when the number of open files limit has been hit, whereas this one throws the same error when there are no open undo slots available.

Additional notes/comments:

If the 1025th transaction is the DROP TABLE command, then you will receive the "unknown table" error.

On the other hand, if the 1025th connection/transaction is an INSERT (same as the first 1024), then you get the expected "Too many active concurrent transactions" error.

In addition, I also tested using UPDATEs & DELETEs instead of INSERTs in the body of the for() loop, to see if the type of undo slots used made a difference.

mysql_query("UPDATE t1 SET id=$i+1024 WHERE id=$i",$db) or die(mysql_error());
mysql_query("DELETE FROM t1 WHERE id=$i",$db) or die(mysql_error());

(Note that for these 2 runs, I made t1.id PK/autoinc, and inserted 1024 rows, valued 1 through 1024.)

This didn't make a difference, except the cut-off point (when you reach max transactions) is lower.

For the UPDATE, it consumes the max undo slots at 512.  If the 513th transaction/connection is an UPDATE, it throws the "Too many active concurrent transactions" error.  If the 513th transaction/connection is the DROP TABLE, then it returns "Unknown table 'innodb_table_monitor'".

For the DELETE, it consumes the max undo slots at 525 (odd number?).  If the 526th transaction/connection is a DELETE, you get "Too many active concurrent transactions".  But if the 526th transaction is the DROP TABLE, then it returns "Unknown table 'innodb_table_monitor'".

Chris, I took a quick look at the issue. Seems InnoDB reported the correct error (HA_ERR_TOO_MANY_CONCURRENT_TRXS), but MySQL mysql_rm_table_part2() in sql/sql_table.cc reports "ER_BAD_TABLE_ERROR" if a drop table fails, even it is not due to missing table:

Stack:

1) row_drop_table_for_mysql   <=== Reports DB_TOO_MANY_CONCURRENT_TRXS
2) ha_innobase::delete_table  <=== Reports HA_ERR_TOO_MANY_CONCURRENT_TRXS
3) handler::ha_delete_table   <=== return HA_ERR_TOO_MANY_CONCURRENT_TRXS
4) ha_delete_table            <===  calls my_error (nr=1637, MyFlags=0)errorno: ER_TOO_MANY_CONCURRENT_TRXS
5) mysql_rm_table_part2       <=== Here, if finds ha_delete_table failed, it handls some error, but not this HA_ERR_TOO_MANY_CONCURRENT_TRXS error, then it reset error =0 and call my_printf_error(ER_BAD_TABLE_ERROR, ER(ER_BAD_TABLE_ERROR),...)
6) mysql_rm_table

So in short, in mysql_rm_table_part2(), it prints ER_BAD_TABLE_ERROR even because the failure is not due to bad table case. And HA_ERR_TOO_MANY_CONCURRENT_TRXS  is printed as a warning:

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning |  177 | Too many active concurrent transactions |
| Error   | 1051 | Unknown table 'aa'                      |
+---------+------+-----------------------------------------+
2 rows in set (0.00 sec)

So code in mysql_rm_table_part2 (sql/sql_table.cc) needs to be modified to fix this.

Reassign based on Jimmy's analysis.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Doxumented n the 5.6.5 changelog as follows:

        When DROP TABLE failed due to all undo slots being in use, the
        error returned was -Unknown table '...'- rather than the
        expected -Too many active concurrent transactions-.
      
Closed.