Developer | UX Advocate | T.J. Mahaffey - Blog

0

Cron: Legacy - Scheduling a MySQL Query

After building a subscription-based web application to serve clients at the small ad agency I worked for, I needed to run a maintenance query against one of the database tables on some regular basis. Records needed to be deleted from the table according to the value contained within an expiration date field. ( dte_exp ) Unfortunately, the low-traffic web application is running on a shared hosting server and the host does not allow for MySQL events under shared environments. (Introduced in MySQL 5.1) So, I needed to find another way to schedule a query. My solution was to create a cron job containing a raw SQL command like so:

mysql -h localhost -D mydbname -u myusername -pmypassword -e "DELETE from mytablename where dte_exp < NOW()"

Notice the password argument is the only one in this command which does not include a space. A cron job must also contain a schedule. Cron schedules aren't something I need to do frequently, so I never can seem to retain the scheme in my head. After a quick Google search and refresher, I came up with this, which says the job should run every night at 7pm. The screenshot below was taken of my web host's account control panel once the job was created. The net result of the query will be the removal of all rows in the table which contain an expiration date older than 7pm on the day the query runs.

 

Categories:MySQL, Server Admin

Comments: No comments yet

© 2017 T.J. Mahaffey