How to automatically update a MySQL database every day

[uranuskid]

Hey folks,

I'm curious about how to perform following task:

I need to update a MySQL database every day in terms of deleting expired entries.Therefore, I have to fields in the db named 'date' and 'expired' (which is the actual date ... How do I get that again?).

So, I want to run a script that compares both fields and deletes respective entries where both fields match.

I want to do this at the end of the day and the auto_increment field should be updated for the whole db.

If anyone could drop me some hints would be appreciated.

Thanks
uranuskid

[michaelb]

I think the SQL to update your table may look like this:

delete from my_table where current_date >= expiration_date
 

I am not sure about this:
>> the auto_increment field should be updated for the whole db.
You did not say anything about this field, and auto_increment is something that typically managed by the database (a new record ID, for example)

You can execute your script using the MySQL Event Scheduler.
It allows you create a new event, set the schedule, and provide the SQL for this event.

You may find these links helpful:
http://dev.mysql.com/tech-resources/...t-feature.html

http://dev.mysql.com/doc/refman/5.1/en/events.html

[ronverdonk]

I want to do this at the end of the day and the auto_increment field should be updated for the whole db.

When you want to reset your auto_increment field, you'll have to create a new table (like the one you have now) and insert your current table in that one.
That way the auto_increment field is re-started from the beginning.
After that delete the old table one and rename the new one.

Ronald :cool:

[uranuskid]

I think the SQL to update your table may look like this:

delete from my_table where current_date >= expiration_date
 

You may find these links helpful:
http://dev.mysql.com/tech-resources/...t-feature.html

http://dev.mysql.com/doc/refman/5.1/en/events.html

Thanks for the help.

Just to get it right, what I need to do is create a recurrent even scheduler that updates my current date and then compares it with expiring date and then deletes all entries that are expired. Do you reckon I'll get problems doing this with MyPHPadmin on the webserver?

Cheers,
uranuskid

[michaelb]

If you're asking whether you can use MyPHPadmin to create an event I don't see why not.
Provided that you have required privileges/permissions to the database, you should be able to run the "CREATE EVENT ..." sql just like you run "CREATE TABLE ..." or any other DML, using MyPHPadmin or any other interface you may have.

I don't quite understand why you need the current_date column.
Unless there's something you did not tell us, I would drop this nuisance and compare column expiration_date with result of CURRENT_TIMESTAMP() or CURRENT_DATE(), or whichever date/time function is appropriate for your situation.
In this case your SQL may look like this:

delete from my_table where expiration_date >= CURRENT_DATE()
 

[uranuskid]

Also ich hab das jetzt alles mal ausprobiert und das entsprechende im Manula gelesen. Kling alles sehr logisch. Nur leider geht es bei mir nicht. Liegt das an meiner MYSQL version (4.1.21) oder an meinem code:

CREATE event delete 
on schedule every 1 day starts at timestamp '2007-03-25 23:59:00' 
do delete from rides where current_date()v>=v RideDate();

[uranuskid]

Also ich hab das jetzt alles mal ausprobiert und das entsprechende im Manula gelesen. Kling alles sehr logisch. Nur leider geht es bei mir nicht. Liegt das an meiner MYSQL version (4.1.21) oder an meinem code:

CREATE event delete 
on schedule every 1 day starts at timestamp '2007-03-25 23:59:00' 
do delete from rides where current_date()v>=v RideDate();

Sorry, that was German. Forgott that I'm on an English forum. Well, I tried all the advices and they seem logic, also I read the respective Manual pages. However, it won't work. I wonder is it because of my version (4.1.21) or because of the wrong code (see above)

Cheers,
Frank

[michaelb]

I think you are correct with your first assumption; the Event Scheduler Syntax manual says this:
MySQL 5.1.6 and later provides several SQL statements for working with scheduled events:

Does upgrading to v. 5.1 look like a feasible option for you?

[Ahhu]

There is a problem in the syntax you used:

on schedule every 1 day starts at timestamp '2007-03-25 23:59:00'

Should be only:

on schedule every 1 day starts '2007-03-25 23:59:00'

If you're using phpMyAdmin, just go to:
http://YOURDOMAIN/phpmyadmin/db_events.php?db=YOUR_DB
And you can quickly create as many events as you want without ever knowing the proper syntax for events.
But if you insist in DIY, double check your syntax with:
http://dev.mysql.com/doc/refman/5.1/...ate-event.html

Btw, thanks for your code, it was the first time I saw SQL events like this and I used your code for a similar purpose. It worked after I removed that "at timestamp".