| Bug #524 | 100% CPU Usage on Windows during a 'DROP TABLE' with InnoDB | ||
|---|---|---|---|
| Submitted: | 28 May 2003 8:01 | Modified: | 8 Jul 2003 0:35 |
| Reporter: | Mark Matthews | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 3.23.5x | OS: | Windows (Windows 2k/XP (maybe others?)) |
| Assigned to: | Venu Anuganti | CPU Architecture: | Any |
[28 May 2003 11:38]
Heikki Tuuri
Hi!
I am not able to repeat this in MySQL-3.23.57 or .52 in Linux with the C API:
/***** Test a bug *******/
if (mysql_query(&conn, "CREATE TABLE NEXT_ID (ID_ INTEGER) TYPE=INNODB")) {
printf("Failed to create table\n");
}
mysql_list_fields(&conn, "NEXT_ID", "%");
if (mysql_query(&conn, "DROP TABLE NEXT_ID")) {
printf("Failed to drop table\n");
}
exit(1);
/************************/
What does the JDBC API exactly call inside mysqld in the Java test program?
In the Java test program mysqld apparently fails to close the table it has opened in readFields("NEXT_ID"). InnoDB warns when mysqld tries to drop the table, and the main thread of InnoDB starts spinning in an infinite loop constantly checking if now have closed the table and it is safe to drop it.
I added now a 0.1 second sleep to 3.23.57 so that the spin does not monopolize the CPU, but the real bug is the failure to close the table, and that remains a mystery. Could this be connected to the case insensitivity of Windows?
Regards,
Heikki
[28 May 2003 11:43]
Mark Matthews
This bug is exposed not only through the JDBC API, but MyODBC (which uses libmysql) also has the same behavior. It has something to do with list_fields(), but I couldn't tell you what in particular is causing the table to not be closed.
[16 Jun 2003 2:37]
Heikki Tuuri
Hi! Venu was able to repeat this with an ODBC interface. Also in the case of a MyISAM table the call mysql_list_fields() returned some strange error message. Workaround: http://www.mysql.com/doc/en/mysql_list_fields.html " Note that it's recommended that you use SHOW COLUMNS FROM tbl_name instead of mysql_list_fields(). " Venu should study what happens in the server when ODBC calls mysql_list_fields(). I have put a delay sleep to InnoDB-4.0.14 so that tables in the drop queue no longer hog 100 % of the CPU, but that does not fix the real bug. Regards, Heikki
[8 Jul 2003 0:35]
Venu Anuganti
Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at
http://www.mysql.com/doc/en/Installing_source_tree.html
Description: The sequence of events given in 'How to repeat' causes the MySQL server (3.23.5x) to use all available CPU cycles. This behavior can be caused with MyODBC (which the user is using), and JDBC (which the test case is written in), or any other client that can do a mysql_list_fields() function call. This bug doesn't seem to be present in MySQL-4.0.x for Windows, and is also not triggered when the table type _isn't_ InnoDB. How to repeat: This test case is written in Java, but the same sequence of events in MyODBC cause the same behavior: stmt.executeUpdate("CREATE TABLE NEXT_ID (ID_ INTEGER) TYPE=INNODB"); // The next line does the same thing as mysql_list_fields() in libmysql... ((com.mysql.jdbc.Connection)c).readFields("NEXT_ID"); stmt.executeUpdate("DROP TABLE NEXT_ID"); After the 'DROP TABLE', the following message is issued to the error log, and 5-10 seconds later, CPU usage by 'mysqld' increases to 100%: 030528 9:58:11 InnoDB: Warning: MySQL is trying to drop table test/next_id InnoDB: though there are still open handles to it. InnoDB: Adding the table to the background drop queue.