MySQL Bugs: #935: "PURGE LOGS BEFORE '2003-04-02 22:46:26' ;" from manual causes error

Bug #935	"PURGE LOGS BEFORE '2003-04-02 22:46:26' ;" from manual causes error
Submitted:	28 Jul 2003 5:16	Modified:	10 Dec 2004 22:19
Reporter:	Victor Vagin	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	4.1	OS:	Any (all)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
mysql> PURGE LOGS BEFORE now();
ERROR 1064 (42000): You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'LOGS BEFORE now()' at line 1
mysql> PURGE master LOGS BEFORE now();
Query OK, 0 rows affected (0.01 sec)

How to repeat:
PURGE LOGS BEFORE now();
PURGE binary LOGS BEFORE '2003-04-02 22:46:26' ;

Suggested fix:
change manual.texi or or sql_yacc.yy

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

I'm sorry

I don't see why this is not a bug. I have problems with using the "BEFORE" syntax of the "purge 
master 
logs" command, but not with the "TO" form.

In my use, the "BEFORE" form did not remove the log file entries that it should 
have removed. Here is a sample run:

mysql> show master logs;
+--------------------+
| Log_name           |
+--------------------+
| e450-01-bin.000399 |
| e450-01-bin.000400 |
| e450-01-bin.000401 |
| e450-01-bin.000402 |
| e450-01-bin.000403 |
| e450-01-bin.000404 |
+--------------------+
6 rows in set (0.00 sec)

mysql> purge master logs before now();
Query OK, 0 rows affected (0.00 sec)

mysql> show master logs;
+--------------------+
| Log_name           |
+--------------------+
| e450-01-bin.000399 |
| e450-01-bin.000400 |
| e450-01-bin.000401 |
| e450-01-bin.000402 |
| e450-01-bin.000403 |
| e450-01-bin.000404 |
+--------------------+
6 rows in set (0.12 sec)

This should have removed the 5 entries at the top. But it didn't. If I use the "TO" form of the 
command it works:

mysql> show master logs;
+--------------------+
| Log_name           |
+--------------------+
| e450-01-bin.000399 |
| e450-01-bin.000400 |
| e450-01-bin.000401 |
| e450-01-bin.000402 |
| e450-01-bin.000403 |
| e450-01-bin.000404 |
+--------------------+
6 rows in set (0.12 sec)

mysql> purge master logs to 'e450-01-bin.000404';
Query OK, 0 rows affected (0.01 sec)

mysql> show master logs;
+--------------------+
| Log_name           |
+--------------------+
| e450-01-bin.000404 |
+--------------------+
1 row in set (0.01 sec)

Also, I don't know why the result shows "0 rows affected" when it clearly affected 5 rows.

Hi George,
I can't repeat with latest 4.1 version on Linux.
[guilhem 23:07 ~] mysql1
sWelcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.8-valgrind-max-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

hMASTER> show master logs;
+---------------------+
| Log_name            |
+---------------------+
| gbichot2-bin.000001 |
| gbichot2-bin.000002 |
| gbichot2-bin.000003 |
+---------------------+
3 rows in set (0.17 sec)

MASTER> purge master logs before now();
Query OK, 0 rows affected (0.16 sec)

MASTER> show master logs;
+---------------------+
| Log_name            |
+---------------------+
| gbichot2-bin.000003 |
+---------------------+
1 row in set (0.00 sec)

MASTER> flush logs;
Query OK, 0 rows affected (0.01 sec)

MASTER> show master logs;
+---------------------+
| Log_name            |
+---------------------+
| gbichot2-bin.000003 |
| gbichot2-bin.000004 |
+---------------------+
2 rows in set (0.00 sec)

MASTER> purge master logs before now();
Query OK, 0 rows affected (0.00 sec)

MASTER> show master logs;
+---------------------+
| Log_name            |
+---------------------+
| gbichot2-bin.000004 |
+---------------------+
1 row in set (0.00 sec)

MASTER> flush logs;
Query OK, 0 rows affected (0.01 sec)

MASTER> flush logs;
Query OK, 0 rows affected (0.01 sec)

MASTER> flush logs;
Query OK, 0 rows affected (0.01 sec)

MASTER> flush logs;
Query OK, 0 rows affected (0.01 sec)

MASTER> flush logs;
Query OK, 0 rows affected (0.03 sec)

MASTER> show master logs;
+---------------------+
| Log_name            |
+---------------------+
| gbichot2-bin.000004 |
| gbichot2-bin.000005 |
| gbichot2-bin.000006 |
| gbichot2-bin.000007 |
| gbichot2-bin.000008 |
| gbichot2-bin.000009 |
+---------------------+
6 rows in set (0.00 sec)

MASTER> purge master logs before now();
Query OK, 0 rows affected (0.00 sec)

MASTER> show master logs;
+---------------------+
| Log_name            |
+---------------------+
| gbichot2-bin.000009 |
+---------------------+
1 row in set (0.00 sec)

Looks like it does always remove logs as expected.
Do all your logs have dates prior to now() (i.e. do they have modification times in the past - yes that sounds like a stupid question?)? An output of ls -l would help, and also of SELECT NOW().
Does the user which the MySQL server is running as has enough permission to ls -l the binary logs?
Are you using recent 4.1, and on which OS ?