MySQL Bugs: #17839: Event Scheduler: TRUNCATE TABLE mysql.slow

Bug #17839	Event Scheduler: TRUNCATE TABLE mysql.slow_log doesn't work from within event
Submitted:	1 Mar 2006 20:08	Modified:	1 May 2006 22:14
Reporter:	Markus Popp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.7	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
The command "TRUNCATE TABLE mysql.slow_log" is not executed from within an event. Possible that this applies to other tables (e.g. mysql.general_log), too.

How to repeat:
I created following event in schema mysql:

DELIMITER //

DROP EVENT archiveSlowLog //

CREATE EVENT archiveSlowLog
ON SCHEDULE EVERY 30 MINUTE
STARTS '2006-03-01 21:00:00'
ENABLE
DO BEGIN

  INSERT INTO slow_log.slow_log_data
    SELECT * FROM mysql.slow_log;

  TRUNCATE TABLE mysql.slow_log;

END //

DELIMITER ;

Verifying the table count before and after 21:00:00 shows, that the INSERT INTO ... command worked, but not the TRUNCATE TABLE command:

mysql> select count(*) from mysql.slow_log; select count(*) from slow_log.slow_log_data; select now();
+----------+
| count(*) |
+----------+
|   151737 |
+----------+
1 row in set (1.23 sec)

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.13 sec)

+---------------------+
| now()               |
+---------------------+
| 2006-03-01 20:59:45 |
+---------------------+
1 row in set (0.11 sec)

mysql> select count(*) from mysql.slow_log; select count(*) from slow_log.slow_log_data; select now();
+----------+
| count(*) |
+----------+
|   151741 |
+----------+
1 row in set (1.24 sec)

+----------+
| count(*) |
+----------+
|   151738 |
+----------+
1 row in set (0.34 sec)

+---------------------+
| now()               |
+---------------------+
| 2006-03-01 21:00:48 |
+---------------------+
1 row in set (0.13 sec)

Issuing the TRUNCATE TABLE command manually works, though:

mysql> truncate table mysql.slow_log;
Query OK, 0 rows affected (0.14 sec)

mysql> select count(*) from mysql.slow_log;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.08 sec)

I cannot reproduce (5.1.8-bk). Especially INSERT INTO SELECT does not work. Tried the same with a stored procedure with the same body and effect is the same. INSERT INTO fails, and the code does not reach TRUNCATE.
On the console :

060302 21:21:48 [Note] SCHEDULER: Started thread 7
060302 21:21:48 [Note] SCHEDULER: Executing event test.archiveSlowLog of root@localhost [EXPR:1]
060302 21:21:48 [ERROR] SCHEDULER: [`root`@`localhost`][`test`.`archiveSlowLog`] You can't use usual read lock with log tables. Try READ LOCAL instead.]
060302 21:21:48 [Note] SCHEDULER: Executed event test.archiveSlowLog of root@localhost  [EXPR:1]. RetCode=1

You can try the followin SP (make it an event). However, don't put TRUNCATE in it because it will lock :( (just reported it as bug#17876). WHERE clause of the CURSOR seemed not to work for me, so you need two events. One with the following body and 1 that executes few seconds later and truncates the table (but you could get few statements loss on a busy server with a lot of slow statements).

DELIMITER //

DROP procedure archiveSlowLog //

CREATE event archiveSlowLog on schedule every 1 minute
DO
BEGIN
  DECLARE start_time, query_time, lock_time CHAR(20);
  DECLARE user_host MEDIUMTEXT;
  DECLARE rows_set, rows_examined, last_insert_id, insert_id, server_id INT;
  DECLARE dbname MEDIUMTEXT;
  DECLARE sql_text BLOB;
  DECLARE done INT DEFAULT 0;
  DECLARE cur1 CURSOR FOR SELECT * FROM mysql.slow_log;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur1;
  
  REPEAT
    FETCH cur1 INTO
       start_time,
       user_host,
       query_time,
       lock_time,
       rows_set,
       rows_examined,
       dbname,
       last_insert_id,
       insert_id,
       server_id,
       sql_text;
    IF NOT done THEN
    BEGIN
      INSERT INTO
        slow_log.slow_log_data
      VALUES(start_time, user_host, query_time, lock_time, rows_set, rows_examined,
             dbname, last_insert_id,  insert_id, server_id, sql_text);
     END;
     END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
  TRUNCATE mysql.slow_log;
END //

DELIMITER ;

This happens on windows because the ha_tina is not included in the built binary. See bug#17368

Sorry for a long delay with this bug report. Please, try to repeat with a newer version, 5.1.9 beta, and inform about the results.

This works now in 5.1.9.