cleaning up general_log table?

Hello,

Is there a way to purge parts of general log table
without interrupting the server? Ideally, we would
like to transfer older logs (say copy all entries
from 2010 into a separate location, and then run:

DELETE FROM mysql.general_log WHERE event_time<'2011-01-01'

However this yields an error:

ERROR 1556 (HY000): You can't use locks with log tables.

What is a recommended way to clean up mysql.general_log?

Thanks,
Jacek

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: cleaning up general_log table?

Since that table is a MyISAM table, you must do the following:

01. In mysql, CREATE DATABASE IF NOT EXISTS mystuff;
02. service mysql stop
03. cd /var/lib/mysql/mysql
04. mv general_log.* ../mystuff
05. comment out general log from my.cnf
06. service mysql start
07. In mysql, DELETE FROM mystuff.general_log WHERE event_time<'2011-01-01'=
;
08. service mysql stop
09. cd /var/lib/mysql/mstuff
10. mv general_log.* ../mysql
11. enable general log in my.cnf
12. service mysql start
13. In mysql, DROP DATABASE mystuff;

Does anyone know a shorter way ???

Rolando A. Edwards
MySQL DBA (SCMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwards@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-----Original Message-----
From: Jacek Becla [mailto:becla@slac.stanford.edu]=20
Sent: Thursday, January 06, 2011 5:57 PM
To: mysql@lists.mysql.com
Subject: cleaning up general_log table?

Hello,

Is there a way to purge parts of general log table
without interrupting the server? Ideally, we would
like to transfer older logs (say copy all entries
from 2010 into a separate location, and then run:

DELETE FROM mysql.general_log WHERE event_time<'2011-01-01'

However this yields an error:

ERROR 1556 (HY000): You can't use locks with log tables.

What is a recommended way to clean up mysql.general_log?

Thanks,
Jacek

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dredwards@logicworks=
..net


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: cleaning up general_log table?

Hi Ronaldo,

After some digging I did find a way!

USE mysql;
CREATE TABLE gn2 LIKE general_log;
RENAME TABLE general_log TO oldLogs, gn2 TO general_log;

the rename is atomic, so it is quick, there is no
need to stop the server. After that, oldLogs is
very easy to mess up with

thanks,
Jacek





On 01/06/2011 07:53 PM, Rolando Edwards wrote:
> Since that table is a MyISAM table, you must do the following:
>
> 01. In mysql, CREATE DATABASE IF NOT EXISTS mystuff;
> 02. service mysql stop
> 03. cd /var/lib/mysql/mysql
> 04. mv general_log.* ../mystuff
> 05. comment out general log from my.cnf
> 06. service mysql start
> 07. In mysql, DELETE FROM mystuff.general_log WHERE event_time<'2011-01-01';
> 08. service mysql stop
> 09. cd /var/lib/mysql/mstuff
> 10. mv general_log.* ../mysql
> 11. enable general log in my.cnf
> 12. service mysql start
> 13. In mysql, DROP DATABASE mystuff;
>
> Does anyone know a shorter way ???
>
> Rolando A. Edwards
> MySQL DBA (SCMDBA)
>
> 155 Avenue of the Americas, Fifth Floor
> New York, NY 10013
> 212-625-5307 (Work)
> 201-660-3221 (Cell)
> AIM& Skype : RolandoLogicWorx
> redwards@logicworks.net
> http://www.linkedin.com/in/rolandoedwards
>
>
> -----Original Message-----
> From: Jacek Becla [mailto:becla@slac.stanford.edu]
> Sent: Thursday, January 06, 2011 5:57 PM
> To: mysql@lists.mysql.com
> Subject: cleaning up general_log table?
>
> Hello,
>
> Is there a way to purge parts of general log table
> without interrupting the server? Ideally, we would
> like to transfer older logs (say copy all entries
> from 2010 into a separate location, and then run:
>
> DELETE FROM mysql.general_log WHERE event_time<'2011-01-01'
>
> However this yields an error:
>
> ERROR 1556 (HY000): You can't use locks with log tables.
>
> What is a recommended way to clean up mysql.general_log?
>
> Thanks,
> Jacek
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org