mysql: Delete all rows older than N days - Cloud - lists.wikimedia.org

List overview All Threads
Download

mysql: Delete all rows older than N days

Reminder: Share you feedback in...

Tomorrow: Technical Advice IRC...

MarcoAurelio

7 Apr 2018 7 Apr '18

3:41 p.m.

Hi, I'd like to issue a command to a database our team manages to clean all data older than 30 days. Browsing around I've found that: DELETE FROM $table WHERE $timestamp < NOW() - INTERVAL 30 DAY could work, but I'd like to check with you first just in case you have a better command or ideas. Thanks in advance for your help. Best regards, M.

Attachments:

attachment.htm (text/html — 530 bytes)

Reply

Show replies by thread

Maximilian Doerr

7 Apr 7 Apr

3:55 p.m.

That should technically work, but you want to be sure you have the timestamp column indexed. Based on what I’m seeing, it looks as if you mixed PHP in there. Here’s how my garbage collector works for my IABot interface. https://github.com/cyberpower678/Cyberbot_II/blob/master/IABot/www/Includes… <https://github.com/cyberpower678/Cyberbot_II/blob/master/IABot/www/Includes/session.php#L147> Cyberpower678 English Wikipedia Account Creation Team English Wikipedia Administrator Global User Renamer

On Apr 7, 2018, at 09:41, MarcoAurelio <strigiwm(a)gmail.com> wrote: Hi, I'd like to issue a command to a database our team manages to clean all data older than 30 days. Browsing around I've found that: DELETE FROM $table WHERE $timestamp < NOW() - INTERVAL 30 DAY could work, but I'd like to check with you first just in case you have a better command or ideas. Thanks in advance for your help. Best regards, M. _______________________________________________ Wikimedia Cloud Services mailing list Cloud(a)lists.wikimedia.org (formerly labs-l(a)lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/cloud

Reply

MarcoAurelio

4:05 p.m.

Hi, thanks for your reply. I do too run from time to time a query like yours: "DELETE FROM logging WHERE l_timestamp < 20180201000000;" for example; however I have to manually set the timestamp. Ideally we should perhaps move to have a coded garbage collector on our tool instead of doing this ourselves manually. Regards, M. 2018-04-07 15:55 GMT+02:00 Maximilian Doerr <maximilian.doerr(a)gmail.com>om>:

That should technically work, but you want to be sure you have the timestamp column indexed. Based on what I’m seeing, it looks as if you mixed PHP in there. Here’s how my garbage collector works for my IABot interface. https://github.com/cyberpower678/Cyberbot_II/blob/master/IABot/www/ Includes/session.php#L147 Cyberpower678 English Wikipedia Account Creation Team English Wikipedia Administrator Global User Renamer On Apr 7, 2018, at 09:41, MarcoAurelio <strigiwm(a)gmail.com> wrote: Hi, I'd like to issue a command to a database our team manages to clean all data older than 30 days. Browsing around I've found that: DELETE FROM $table WHERE $timestamp < NOW() - INTERVAL 30 DAY could work, but I'd like to check with you first just in case you have a better command or ideas. Thanks in advance for your help. Best regards, M. _______________________________________________ Wikimedia Cloud Services mailing list Cloud(a)lists.wikimedia.org (formerly labs-l(a)lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/cloud _______________________________________________ Wikimedia Cloud Services mailing list Cloud(a)lists.wikimedia.org (formerly labs-l(a)lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/cloud

Reply

Jaime Crespo

9 Apr 9 Apr

9:28 a.m.

Please share the data type of $timestamp (normaly mysql tables should use the timestamp data type, but mediawiki uses strings, requiring some conversion). Also provide how many rows you intend to delete and what are the needs (e.g. avoid large deletes- for which there are specific tools like pt-archiver). Also please say which language you want to use for automation. Based on those needs we can better advice on what is best to do. On Sat, Apr 7, 2018 at 4:05 PM, MarcoAurelio <strigiwm(a)gmail.com> wrote:

Hi, thanks for your reply. I do too run from time to time a query like yours: "DELETE FROM logging WHERE l_timestamp < 20180201000000;" for example; however I have to manually set the timestamp. Ideally we should perhaps move to have a coded garbage collector on our tool instead of doing this ourselves manually. Regards, M. 2018-04-07 15:55 GMT+02:00 Maximilian Doerr <maximilian.doerr(a)gmail.com>om>:

That should technically work, but you want to be sure you have the timestamp column indexed. Based on what I’m seeing, it looks as if you mixed PHP in there. Here’s how my garbage collector works for my IABot interface. https://github.com/cyberpower678/Cyberbot_II/blob/master/ IABot/www/Includes/session.php#L147 Cyberpower678 English Wikipedia Account Creation Team English Wikipedia Administrator Global User Renamer On Apr 7, 2018, at 09:41, MarcoAurelio <strigiwm(a)gmail.com> wrote: Hi, I'd like to issue a command to a database our team manages to clean all data older than 30 days. Browsing around I've found that: DELETE FROM $table WHERE $timestamp < NOW() - INTERVAL 30 DAY could work, but I'd like to check with you first just in case you have a better command or ideas. Thanks in advance for your help. Best regards, M. _______________________________________________ Wikimedia Cloud Services mailing list Cloud(a)lists.wikimedia.org (formerly labs-l(a)lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/cloud _______________________________________________ Wikimedia Cloud Services mailing list Cloud(a)lists.wikimedia.org (formerly labs-l(a)lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/cloud

_______________________________________________ Wikimedia Cloud Services mailing list Cloud(a)lists.wikimedia.org (formerly labs-l(a)lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/cloud

-- Jaime Crespo <http://wikimedia.org>

Reply

MarcoAurelio

12 Apr 12 Apr

12:03 a.m.

Hi Jaime, - $timestamp is as follows: YYYYMMDDhhmmss (ie: 19700101000000) - average rows to delete each month: a couple of hundred at most - needs: garbage collection, avoid storing old data no longer useful - automation: none, as my knowledge here is rather scarce so I'd do this by hand for now. Please let me know if you need further information. Thanks! -M. 2018-04-09 9:28 GMT+02:00 Jaime Crespo <jcrespo(a)wikimedia.org>rg>:

Please share the data type of $timestamp (normaly mysql tables should use the timestamp data type, but mediawiki uses strings, requiring some conversion). Also provide how many rows you intend to delete and what are the needs (e.g. avoid large deletes- for which there are specific tools like pt-archiver). Also please say which language you want to use for automation. Based on those needs we can better advice on what is best to do. On Sat, Apr 7, 2018 at 4:05 PM, MarcoAurelio <strigiwm(a)gmail.com> wrote:

Hi, thanks for your reply. I do too run from time to time a query like yours: "DELETE FROM logging WHERE l_timestamp < 20180201000000;" for example; however I have to manually set the timestamp. Ideally we should perhaps move to have a coded garbage collector on our tool instead of doing this ourselves manually. Regards, M. 2018-04-07 15:55 GMT+02:00 Maximilian Doerr <maximilian.doerr(a)gmail.com>om>:

That should technically work, but you want to be sure you have the timestamp column indexed. Based on what I’m seeing, it looks as if you mixed PHP in there. Here’s how my garbage collector works for my IABot interface. https://github.com/cyberpower678/Cyberbot_II/blob/master/IAB ot/www/Includes/session.php#L147 Cyberpower678 English Wikipedia Account Creation Team English Wikipedia Administrator Global User Renamer On Apr 7, 2018, at 09:41, MarcoAurelio <strigiwm(a)gmail.com> wrote: Hi, I'd like to issue a command to a database our team manages to clean all data older than 30 days. Browsing around I've found that: DELETE FROM $table WHERE $timestamp < NOW() - INTERVAL 30 DAY could work, but I'd like to check with you first just in case you have a better command or ideas. Thanks in advance for your help. Best regards, M. _______________________________________________ Wikimedia Cloud Services mailing list Cloud(a)lists.wikimedia.org (formerly labs-l(a)lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/cloud _______________________________________________ Wikimedia Cloud Services mailing list Cloud(a)lists.wikimedia.org (formerly labs-l(a)lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/cloud

_______________________________________________ Wikimedia Cloud Services mailing list Cloud(a)lists.wikimedia.org (formerly labs-l(a)lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/cloud

-- Jaime Crespo <http://wikimedia.org> _______________________________________________ Wikimedia Cloud Services mailing list Cloud(a)lists.wikimedia.org (formerly labs-l(a)lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/cloud

Reply

2205

days inactive

2209

days old

cloud@lists.wikimedia.org

Manage subscription

4 comments

3 participants

tags (0)

participants (3)

Jaime Crespo
MarcoAurelio
Maximilian Doerr