Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 Tsepkov Dec 17 '12 at 14:39
    
Did you check the option/configuration files? (depending on your OS, you may have more than one) –  ypercube Dec 17 '12 at 15:21
    
Which version of mysql you run? You have upgraded recently your db server? –  Cristian Porta Dec 17 '12 at 15:29
    
@ypercube: SHOW VARIABLES displays information from the configuration file plus any runtime changes –  Max Tsepkov Dec 17 '12 at 16:40
show 1 more comment

2 Answers

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.

share|improve this answer
    
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 Tsepkov Dec 17 '12 at 11:27
add comment
up vote 1 down vote accepted

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.

share|improve this answer
    
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
    
@RolandoMySQLDBA with the tease ... do eeet! –  jcolebrand Dec 17 '12 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 –  RolandoMySQLDBA Dec 17 '12 at 20:22
    
@@RolandoMySQLDBA not really, cause I used to logfiles in filesystem. –  Max Tsepkov 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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