Enable slow_query_log tries to write to slow_log.CSV

Question

I'm running MySQL 5.5

I wanted to truncate the slow log file, so I did:

SET GLOBAL slow_query_log = 0;

then truncated the file in filesystem.

But, when I enable it back it fails:

SET GLOBAL slow_query_log = 1;
ERROR 13 (HY000): Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)

Although the slow_query_log_file set explicitly to another path.

SHOW GLOBAL VARIABLES LIKE 'slow_query%';
+------------------------------------+---------------------------------+
| Variable_name | Value |
+------------------------------------+---------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /home/mysql_data/jobs1-slow.log |
| slow_query_log_timestamp_always | OFF |
| slow_query_log_timestamp_precision | second |
| slow_query_log_use_global_control | |
+------------------------------------+---------------------------------+

What is wrong? I want MySQL log to the specified file. Plain text, not CSV.

SHOW GLOBAL VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+

Before I stopped it logging was fine.

• Cristian Porta: You can find a useful post here: Problems turning on the MySQL slow log
• Maxim Mercuriev: That was the blog posted I found the first. But the problem is that I had tried to touch the csv file both in datadir and basedir, but it results to the same error.
• ypercubeᵀᴹ: Did you check the option/configuration files ? (depending on your OS, you may have more than one)
• Cristian Porta: Which version of mysql you run? You have upgraded recently your db server?
• Maxim Mercuriev: @ypercube: SHOW VARIABLES displays information from the configuration file plus any runtime changes

Answer 1

I did these steps :

1. set global slow_query_log=0;
2. remove the slow log file in system
3. set global slow_query_log=1;

it works fine, so I tried these steps:

mysql> set global slow_query_log=0;
Query OK, 0 rows affected (0.12 sec)
mysql> set global slow_query_log_file='/tmp/slow.log';
Query OK, 0 rows affected (0.10 sec)
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)

still works, and new slow log file is generated as /tmp/slow.log.

At last, the value of global variable log_output decides where to generate the general log and slow log's output, not the file format.

• Maxim Mercuriev: I tried both, removed the file (instead of truncate) and pointed to another file. The same error. I guess this behaviour is caused by another variable.

Answer 2

Solved the problem.

It turned out that tables mysql.general_log and mysql.slow_log were corrupted.
In fact it only had frm file but had no associated csv data file.

To resolve, I've re-created those tables.

mysql> DROP TABLE mysql.general_log;
ERROR 1051 (42S02): Unknown table 'general_log'
mysql> DROP TABLE mysql.slow_log;
ERROR 1051 (42S02): Unknown table 'slow_log'

Despite the error, the frm file has been removed anyway. So it's OK.

Then execute SQL from 5.5 distribution.

-- Create general_log if CSV is enabled.
SET @str = IF (@@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS general_log (event_time TIMESTAMP NOT NULL, user_host MEDIUMTEXT NOT NULL, thread_id INTEGER NOT NULL, server_id INTEGER UNSIGNED NOT NULL, command_type VARCHAR(64) NOT NULL, argument MEDIUMTEXT NOT NULL) engine=CSV CHARACTER SET utf8 comment="General log"', 'SET @dummy = 0');
PREPARE stmt FROM @str;
EXECUTE stmt;
DROP PREPARE stmt;
-- Create slow_log if CSV is enabled.
SET @str = IF (@@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS slow_log (start_time TIMESTAMP NOT NULL, user_host MEDIUMTEXT NOT NULL, query_time TIME NOT NULL, lock_time TIME NOT NULL, rows_sent INTEGER NOT NULL, rows_examined INTEGER NOT NULL, db VARCHAR(512) NOT NULL, last_insert_id INTEGER NOT NULL, insert_id INTEGER NOT NULL, server_id INTEGER UNSIGNED NOT NULL, sql_text MEDIUMTEXT NOT NULL) engine=CSV CHARACTER SET utf8 comment="Slow log"', 'SET @dummy = 0');
PREPARE stmt FROM @str;
EXECUTE stmt;
DROP PREPARE stmt;

At last, enable log just fine:

SET GLOBAL slow_query_log = 1;
Query OK, 0 rows affected (0.20 sec)

I think it was caused by an outside program or a newbie, because I turned off and on logging just fine afterwards.

• RolandoMySQLDBA: You could have converted the slow log and general log into MyISAM tables. Would you like to know how ???
• jcolebrand: @RolandoMySQLDBA with the tease ... do eeet!
• RolandoMySQLDBA: So as not to reinvent the wheel, I already wrote an old post on this for the slow log (Mar 30, 2012) : dba.stackexchange.com/a/15890/877 . This includes log rotation of the MyISAM table. Here is the same stuff for the general log (Feb 11, 2012) : dba.stackexchange.com/a/12672/877
• Maxim Mercuriev: @@RolandoMySQLDBA not really, cause I used to logfiles in filesystem.
• RolandoMySQLDBA: You could still use both MyISAM and text files at the same time. Just set log_output=FILE,TABLE in my.cnf.