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.

Did you check the option/configuration files? (depending on your OS, you may have more than one) — ypercubeᵀᴹ, Dec 17 '12 at 15:21
@ypercube: SHOW VARIABLES displays information from the configuration file plus any runtime changes — Max Kolesnikov, Dec 17 '12 at 16:40
You can find a useful post here: Problems turning on the MySQL slow log — Cristian Porta, Dec 17 '12 at 13:49
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. — Max Kolesnikov, Dec 17 '12 at 14:39
Which version of mysql you run? You have upgraded recently your db server? — Cristian Porta, Dec 17 '12 at 15:29

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

1

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.

edited Dec 17 '12 at 11:52

András Váczi

31,278
13
101
147

answered Dec 17 '12 at 11:12

devilkin

56
2

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. – Max Kolesnikov Dec 17 '12 at 11:27

Max Kolesnikov · 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.

You could have converted the slow log and general log into MyISAM tables. Would you like to know how ??? — RolandoMySQLDBA, Dec 17 '12 at 18:43
So as not to reinvent the wheel, I already wrote an old post on this for the slow log (Mar 30, 2012) : http://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) : http://dba.stackexchange.com/a/12672/877 — RolandoMySQLDBA, Dec 17 '12 at 20:22
@@RolandoMySQLDBA not really, cause I used to logfiles in filesystem. — Max Kolesnikov, Dec 17 '12 at 20:23
You could still use both MyISAM and text files at the same time. Just set log_output=FILE,TABLE in my.cnf. — RolandoMySQLDBA, Dec 17 '12 at 20:41

Enable slow_query_log tries to write to slow_log.CSV

2 Answers2