Bug #32476 unknown table when trying to drop a table and open files limit had been reached

• Submitted: 18 Nov 2007 7:45
• Modified: 6 Oct 2009 9:56
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Verified
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.1.22, 5.1 bzr
• OS: Any
• CPU Architecture: Any

[18 Nov 2007 7:45] Shane Bester

Description:
When attempting to drop an existing table, and the number of open files limit has been hit, an unknown table note occurs but no error.  See:

mysql> show create table table_8592\G
ERROR 1016 (HY000): Can't open file: '.\test\table_8592.frm' (errno: 24)
mysql> drop table if exists table_8592;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+----------------------------+
| Level | Code | Message                    |
+-------+------+----------------------------+
| Note  | 1051 | Unknown table 'table_8592' |
+-------+------+----------------------------+
1 row in set (0.06 sec)

This bug caused a script to fail that would succeed when it does this:

mysqli_query($link,"drop table if exists $table") or die(mysqli_error($link));
mysqli_query($link,"create table $table (id int)") or die(mysqli_error($link));

How to repeat:
uploading testcase later.

Suggested fix:
Workaround: configure mysqld correctly to avoid this.  i.e. lower table_open_cache and increase open_file_limit as needed.

It might be nice to have an Error instead of Note for the drop table, if possible.

I assume this can break replication a bit, in case master/slave have different limits on open files..  Shall test it later.

[24 Aug 2008 14:33] Sveta Smirnova

Bug #38938 was marked as duplicate of this one.

[6 Oct 2009 9:56] Sveta Smirnova

Thank you for the report.

Verified as described.

Test used:

<?php
	mysql_connect('127.0.0.1:3351', 'root', '');
	
	mysql_select_db('test');
	
	for ($i = 0; $i < 10000; $i ++) {
		$result = mysql_query("drop table if exists t_$i");
		if (0 != mysql_errno())
			break;
		$result = mysql_query("create table t_$i (id int)");
		if (0 != mysql_errno())
			break;
		$result = mysql_query("select * from t_$i");
		if (0 != mysql_errno())
			break;
	}
	
	var_dump(mysql_error());
	
	mysql_close();
?>