MySQL Bugs: #34306: Can't make copy of log tables when server binary log is enabled

Bug #34306	Can't make copy of log tables when server binary log is enabled
Submitted:	5 Feb 2008 11:40	Modified:	29 Jun 2009 16:00
Reporter:	Carsten Pedersen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.1.26-rc	OS:	Any
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	csv engine, slow log

Description:
The following statement:

  CREATE TABLE slow SELECT * FROM mysql.slow_log;

Throws an error:

  ERROR 1553 (HY000): You can't use locks with log tables.

I can't for the life of me think why this is necessary. There's nothing that prevents me from doing SELECT * FROM mysql.slow_log, so why should the fact that I do an insert at the same time do so?

More information + workaround: http://www.bitbybit.dk/carsten/blog/?p=115

How to repeat:
CREATE TABLE slow SELECT * FROM mysql.slow_log;

Suggested fix:
Don't lock the table.

Thank you for the bug report. Verified as described.

Bug #37998 about general log was marked as duplicate of this one.

Related to Bug#25422 (which contains explanations on the prohibition of locking log tables). Ssee also Bug#29129 and Bug#27858

Test case:

--source include/have_log_bin.inc
CREATE TABLE slow SELECT * FROM mysql.slow_log;

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

2683 Davi Arnaut	2008-09-12
      Bug#34306: Can't make copy of log tables when server binary log is enabled
      
      The problem is that when statement-based replication was enabled,
      statements such as INSERT INTO .. SELECT FROM .. and CREATE TABLE
      .. SELECT FROM need to grab a read lock on the source table that
      does not permit concurrent inserts, which would in turn be denied
      if the source table is a log table because log tables can't be
      locked exclusively.
      
      The solution is to not take such a lock when the source table is
      a log table as it is unsafe to replicate log tables under statement
      based replication. Furthermore, the read lock that does not permits
      concurrent inserts is now only taken if statement-based replication
      is enabled and if the source table is not a log table.

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

2683 Davi Arnaut	2008-09-13
      Bug#34306: Can't make copy of log tables when server binary log is enabled
      
      The problem is that when statement-based replication was enabled,
      statements such as INSERT INTO .. SELECT FROM .. and CREATE TABLE
      .. SELECT FROM need to grab a read lock on the source table that
      does not permit concurrent inserts, which would in turn be denied
      if the source table is a log table because log tables can't be
      locked exclusively.
      
      The solution is to not take such a lock when the source table is
      a log table as it is unsafe to replicate log tables under statement
      based replication. Furthermore, the read lock that does not permits
      concurrent inserts is now only taken if statement-based replication
      is enabled and if the source table is not a log table.

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

2690 Davi Arnaut	2008-09-29
      Bug#34306: Can't make copy of log tables when server binary log is enabled
      
      The problem is that when statement-based replication was enabled,
      statements such as INSERT INTO .. SELECT FROM .. and CREATE TABLE
      .. SELECT FROM need to grab a read lock on the source table that
      does not permit concurrent inserts, which would in turn be denied
      if the source table is a log table because log tables can't be
      locked exclusively.
      
      The solution is to not take such a lock when the source table is
      a log table as it is unsafe to replicate log tables under statement
      based replication. Furthermore, the read lock that does not permits
      concurrent inserts is now only taken if statement-based replication
      is enabled and if the source table is not a log table.

Queued to 5.1.29-rc

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

2692 Davi Arnaut	2008-09-30
      Bug#34306: Can't make copy of log tables when server binary log is enabled
      
      Post-merge bug fix: lock_type is a enumeration type and not a bit mask.

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

2692 Davi Arnaut	2008-09-30
      Bug#34306: Can't make copy of log tables when server binary log is enabled
      
      Post-merge bug fix: lock_type is a enumeration type and not a bit mask.

Noted in 5.1.29 changelog.

With binary logging enabled, CREATE TABLE ... SELECT failed if the
source table was a log table. 

Leaving report status unchanged; this is early documentation of an upcoming push into 5.1.29.

Pushed into 5.1.30  (revid:davi.arnaut@sun.com-20080930134701-mmn4c1r41qx48l2k) (version source revid:kgeorge@mysql.com-20081001094725-vf4mqjkmajlm22qy) (pib:4)

Setting report to NDI pending push into 6.0.x.

Pushed into 6.0.8-alpha  (revid:davi.arnaut@sun.com-20080930134701-mmn4c1r41qx48l2k) (version source revid:kgeorge@mysql.com-20081001100520-exs1tupnfanm1mij) (pib:5)

Noted in 6.0.8 changelog.

Pushed into 5.1.29-ndb-6.2.17  (revid:davi.arnaut@sun.com-20080930134701-mmn4c1r41qx48l2k) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)

Pushed into 5.1.29-ndb-6.3.19  (revid:davi.arnaut@sun.com-20080930134701-mmn4c1r41qx48l2k) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)

Pushed into 5.1.29-ndb-6.4.0  (revid:davi.arnaut@sun.com-20080930134701-mmn4c1r41qx48l2k) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)

Please update documentation at http://dev.mysql.com/doc/refman/5.1/en/binary-log-mixed.html
The patch added a new condition under which rbl is used when binlog_format=MIXED; namely, if one of the involved tables has table_category == TABLE_CATEGORY_PERFORMANCE (I don't know what such tables are called - log tables?)

Noted as requested.