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.

score 1 · Answer 1 · edited Dec 17 '12 at 11:52

I did these steps :

set global slow_query_log=0;
remove the slow log file in system
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 · Accepted Answer · 2012-12-17T20:53:00.973

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.

Enable slow_query_log tries to write to slow_log.CSV

2 Answers2