4

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.

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

2 Answers 2

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.

1
  • 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. Commented Dec 17, 2012 at 11:27
1

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.

5
  • You could have converted the slow log and general log into MyISAM tables. Would you like to know how ??? Commented Dec 17, 2012 at 18:43
  • @RolandoMySQLDBA with the tease ... do eeet!
    – jcolebrand
    Commented Dec 17, 2012 at 20:04
  • 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 Commented Dec 17, 2012 at 20:22
  • @@RolandoMySQLDBA not really, cause I used to logfiles in filesystem. Commented Dec 17, 2012 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. Commented Dec 17, 2012 at 20:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.