Archives

The List

« MySQL Cluster Certification Guide now available at Amazon | Main | Controlling the MySQL Slow Log (Another Item for the Wish List) »

ERROR 1553 (HY000): You can’t use locks with log tables. — Now what?

By Carsten | February 4, 2008

One of the cooler inventions in recent versions of MySQL is having the slow log and general log available as plain-text files as well as CSV-engine type tables directly within the mysql database.

So now you want to analyze the slow query log using SQL, but (a) you don’t want to lock up the table while you do your work, and (b) you suspect that your work may involve adding indexes, dropping columns and such during the analysis.

So you decide to make a copy of the table.

Except that you can’t.

mysql> CREATE TABLE slow SELECT * FROM mysql.slow_log;
ERROR 1553 (HY000): You can't use locks with log tables.

As all the data is stored as a plain CSV file, the solution is to read the data directly from disk rather than using the storage engines and SQL layer:

USE test;

DROP TABLE IF EXISTS slow;

CREATE TABLE slow LIKE mysql.slow_log;

LOAD DATA INFILE "mysql/slow_log.csv"
 INTO TABLE slow
 FIELDS TERMINATED BY ','
 OPTIONALLY ENCLOSED BY '"' ;

And presto .. a copy of your slow query log in your default storage format.

Personally, I’d have preferred using the SQL interface, but this seems to do the trick.

Update: (Aug 27, 2008) According to the bug status page, a fix is now “In progress”, with target of MySQL 5.1.29

Topics: MySQL |

8 Responses to “ERROR 1553 (HY000): You can’t use locks with log tables. — Now what?”

  1. Brian Aker Says:
    February 4th, 2008 at 6:48 pm

    Hi!

    The developer who added this code was told how to fix this. He used the MyISAM concurrent locks, instead of just pushing the locks down into the structure of the engine (aka what Archive does).

    Pretty lame, since there is nothing in the engine that prevents what you are doing.

    Cheers,
    -Brian

  2. Hakan Kuecuekyilmaz Says:
    February 4th, 2008 at 7:08 pm

    This again proves how brain dead and half baked features MySQL delivers.

  3. Jon Stephens Says:
    February 5th, 2008 at 1:14 pm

    Hi Carsten,

    This is Just Stupid Behaviour. Please file a bug if you haven’t done so already.

  4. Carsten Says:
    February 5th, 2008 at 1:41 pm

    Hi Jon,

    Okiedokie… http://bugs.mysql.com/bug.php?id=34306 it is.

  5. Carsten’s Random Ramblings Says:
    February 5th, 2008 at 3:44 pm

    [...] ERROR 1553 (HY000): You can’t use locks with log tables. — Now what? [...]

  6. van Says:
    November 24th, 2008 at 9:19 pm

    this problem is not fix in 5.1.29 the community version. I’m currently encountering this problem and Mysql have been less than helpful in helping me resolve this issue.

  7. Carsten Says:
    December 3rd, 2008 at 12:13 pm

    According to the bug report, it’s fixed in 5.1.30.

  8. Pete Says:
    January 17th, 2012 at 4:00 pm

    Bug still there in 5.1.53

Comments