MySQL Bugs: #18559: log tables cannot change engine, and gets deadlocked when dropping w/ log on

Bug #18559	log tables cannot change engine, and gets deadlocked when dropping w/ log on
Submitted:	27 Mar 2006 22:02	Modified:	10 Aug 2006 16:30
Reporter:	Tobias Asplund	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S3 (Non-critical)
Version:	5.1.7/5.1.8BK	OS:	FreeBSD (FreeBSD/Linux Suse)
Assigned to:	Petr Chardin	CPU Architecture:	Any

Description:
eeyore> ALTER TABLE general_log ENGINE = MyISAM;
ERROR 1532 (HY000): You can't write-lock a log table. Only read access is possible.
eeyore> drop table general_log;

flupps@eeyore:~$ mysqladmin processlist
+-----+-------------+------------------------+-------+-------------+------------+-----------------------------------------------------------------------+------------------------+
| Id  | User        | Host                   | db    | Command     | Time       | State                                                                 | Info                   |
+-----+-------------+------------------------+-------+-------------+------------+-----------------------------------------------------------------------+------------------------+
| 179 | system user |                        |       | Connect     | 331000     | Waiting for master to send event                                      |                        |
| 180 | system user |                        |       | Connect     | 4294966569 | Has read all relay log; waiting for the slave I/O thread to update it |                        |
| 275 | flupps      | localhost              | mysql | Query       | 49         | Waiting on cond                                                       | drop table general_log |
| 279 | sr          | tigger.flupps.org:4507 |       | Binlog Dump | 945        | Has sent all binlog to slave; waiting for binlog to be updated        |                        |
| 280 | flupps      | localhost              |       | Query       | 0          |                                                                       | show processlist       |
+-----+-------------+------------------------+-------+-------------+------------+-----------------------------------------------------------------------+------------------------+

Entered by request of Brian.

How to repeat:
turn on the general log, make sure it logs to a table.
Execute the command(s) above.

Thank you for a problem report. Please, specify the exact version (or ChangeSet) used. Why do you think there is a deadlock?

Sorry, could have been more clear.

The mysql thread hangs until you kill it, displaying the output in the mysqladmin processlist command until killed.

Thank you for the bug report.

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/9708

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/9777

ChangeSet@1.2254, 2006-07-30 01:09:23+04:00, petr@mysql.com +12 -0
  Fix Bug #18559 "log tables cannot change engine, and
                  gets deadlocked when dropping w/ log on"
  
  Log tables rely on concurrent insert machinery to add data.
  This means that log tables are always opened and locked by
  special (artificial) logger threads. Because of this, the thread
  which tries to drop a log table starts to wait for the table
  to be unlocked. Which will happen only if the log table is disabled.
  Alike situation happens if one tries to alter a log table.
  However in addition to the problem above, alter table calls
  check_if_locking_is_allowed() routine for the engine. The
  routine does not allow alter for the log tables. So, alter
  doesn't start waiting forever for logs to be disabled, but 
  returns with an error.
  Another problem is that not all engines could be used for
  the log tables. That's because they need concurrent insert.
  
  In this patch we:
  (1) Explicitly disallow to drop/alter a log table if it
      is currently used by the logger.
  (2) Update MyISAM to support log tables
  (3) Allow to drop log tables/alter log tables if log is
      disabled
  At the same time we (4) Disallow to alter log tables to
  unsupported engine (after this patch CSV and MyISAM are 
  alowed)

Just want to make sure that if MyISAM is used, so that a crashed MyISAM general log table will not prevent the server from working (just like out of disk for the general log will not stop the server).

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/10021

ChangeSet@1.2254, 2006-08-03 21:28:15+04:00, petr@mysql.com +14 -0
  Fix Bug #18559 "log tables cannot change engine, and
                  gets deadlocked when dropping w/ log on"
  
  Log tables rely on concurrent insert machinery to add data.
  This means that log tables are always opened and locked by
  special (artificial) logger threads. Because of this, the thread
  which tries to drop a log table starts to wait for the table
  to be unlocked. Which will happen only if the log table is disabled.
  Alike situation happens if one tries to alter a log table.
  However in addition to the problem above, alter table calls
  check_if_locking_is_allowed() routine for the engine. The
  routine does not allow alter for the log tables. So, alter
  doesn't start waiting forever for logs to be disabled, but 
  returns with an error.
  Another problem is that not all engines could be used for
  the log tables. That's because they need concurrent insert.
  
  In this patch we:
  (1) Explicitly disallow to drop/alter a log table if it
      is currently used by the logger.
  (2) Update MyISAM to support log tables
  (3) Allow to drop log tables/alter log tables if log is
      disabled
  At the same time we (4) Disallow to alter log tables to
  unsupported engine (after this patch CSV and MyISAM are 
  alowed)
  Recommit with review fixes.

Pushed to 5.1.12.

The section on the log tables has been updated
to say this:

By default, the log tables use the CSV storage engine that writes
data in comma-separated values format. For those users who have
access to the .CSV files that contain log table data, the files are
easy to import into other programs such as spreadsheets that can 
process CSV input.

Beginning with MySQL 5.1.2, the log tables can be altered to use the
MyISAM storage engine. You cannot use ALTER TABLE to alter a log
table that is in use. The log must be disabled first. No engines
other than CSV or MyISAM are legal for the log tables.

The use of DROP TABLE for log tables is similarly restricted: It
cannot be used to drop a log table that is in use. The log must be
disabled first.

Also noted the bugfix and behavior changes in the 5.1.12 changelog.

Previous comment should say:

Beginning with MySQL 5.1.12, ...

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/11999

ChangeSet@1.2307, 2006-09-15 13:07:23+04:00, petr@mysql.com +8 -0
  Post-review fixes for
  Bug #18559 "log tables cannot change engine, and
              gets deadlocked when dropping w/ log on":
  1) Add more generic error messages
  2) Add new handlerton flag for engines, which support
     log tables
  3) Remove (log-tables related) mutex lock in myisam to
     improve performance