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 |