Bug #20139 Infinite loop after "FLUSH" and "LOCK tabX, general_log"
Submitted: 30 May 2006 7:44 Modified: 10 Aug 2006 16:32
Reporter: Magnus Blåudd Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S1 (Critical)
Version:5.1.12-BK, 5.1.11 OS:Linux (FC5)
Assigned to: Petr Chardin CPU Architecture:Any

[30 May 2006 7:44] Magnus Blåudd
Description:
LOCK TABLES on any table plus the "general_log" table after a FLUSH TABLES will never be answered by MySQL Server, it will enter an infinite loop and use almost 100% cpu.

This is caused when "refresh_version" is incremented and all tables except the log tables are closed. When the LOCK TABLE command later find this situation it starts to wait for "other threads" to close the log table, this will never happen as the refresh version was incremented by the current thread.

How to repeat:
FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;
USE mysql;
LOCK TABLES general_log READ LOCAL,  help_category READ LOCAL;
[30 May 2006 21:21] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.1.12-BK (ChangeSet@1.2175, 2006-05-30 10:22:31+02:00). It hangs in "Opening tables" state.
[5 Jul 2006 16:57] Konstantin Osipov
Magnus, I can't repeat it, but I will ask Petr to add a test case and close this bug.
Thank you for the report.
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.04 sec)

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> LOCK TABLES general_log READ LOCAL,  help_category READ LOCAL;
Query OK, 0 rows affected (0.00 sec)

mysql> select version();
+--------------------------------+
| version()                      |
+--------------------------------+
| 5.1.12-beta-valgrind-max-debug | 
+--------------------------------+
1 row in set (0.00 sec)

mysql>
[5 Jul 2006 17:00] Konstantin Osipov
Actually I was able to repeat it.
An important note is that the server has to be started with --log
[19 Jul 2006 7:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9324
[27 Jul 2006 11:27] Konstantin Osipov
Approved by email with minor comments.
[3 Aug 2006 13:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10007

ChangeSet@1.2245, 2006-08-03 17:23:37+04:00, petr@mysql.com +3 -0
  Fix Bug #20139 Infinite loop after "FLUSH" and "LOCK tabX, general_log"
  Due to incorrect handling of FLUSH TABLES, log tables were marked for flush,
  but not reopened. Later we started to wait for the log table to be closed
  (disabled) after the flush. And as nobody disabled logs in concurrent treads,
  the command lasted forever.
  After internal consultations it was decided to skip logs during FLUSH TABLES.
  The reasoning is that logging is done in the "log device", whatever it is
  which is always active and controlled by FLUSH LOGS. So, to flush logs
  one should use FLUSH LOGS, and not FLUSH TABLES.
[10 Aug 2006 10:17] Tomash Brechko
Pushed to 5.1.12.
[10 Aug 2006 16:31] Paul Dubois
Noted in 5.1.12 changelog.

FLUSH TABLES followed by a LOCK TABLES statement to lock a log table
and a non-log table caused an infinite loop and high CPU use. Now
FLUSH TABLES ignores log tables. To flush the log tables, use FLUSH
LOGS instead.