Friday, July 11, 2008

Events and backup - two good features - an awful combination

The cool features coming with MySQL 5.1 and 6.0 are the event scheduler and so called online backup.
Both of them implement something that you can do outside the database server. The event scheduler frees the DBA from the operating system dependency and the database backup and restore makes mysqldump redundant.
So far, the good news. What's wrong with this picture?
A look at the manual tells you all. You can backup and restore a database with an explicit SQL statement, but you can't use it in a prepared statement or in an event.
What do I use the event scheduler for, then? Ask any DBA about the first thing that comes to mind associated to a time scheduled event, and 9 times out of 10 the answer is "backup".
So, why is the most logical action not allowed in events?
And don't try to cheat the system with a stored routine that may free you from the details of stating which backup file to use. The backup database and restore database commands are not supported in prepared statements. Ergo, even if you could use "backup database" in a stored routine, it won't do you any good, because the MySQL server can't overwrite an existing file.
Sure, you can circumvent this prohibition with some hacks (MySQL Proxy could be helpful, for instance), but why the user has to suffer for such a small issue?
MySQL developers, if you are listening, please think like users!