CRON: Legacy - Scheduling a SQL Query

3.12.2011

After building a subscription-based web application to serve clients at the small ad agency I work 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 that that the password argument is the only one in this command which does not include a space.

A cron job must also contain a schedule when created. 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 that the job should run every night 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 which is older than 7pm on the day the query runs.