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!


Sheeri K. Cabral said...

Well, if you read the manual for the online backup part you'll see that it does not actually back up any of the system tables. Most of us back up the "mysql" database when we back things up, so .... I do not think online backup will be as useful as we want it to be.

Lars Thalmann said...

Hi Fake Amelia,

Ah, you did not really like that?! :)

Neither do I. I would love to have everything right now and I agree with you that not having backup statement in stored procedures etc is a tough restriction. Having it work together would be really cool and something that many would find really useful.

However, we want to release a production-quality MySQL 6.0 server with backup as soon as possible and this means that we are need to limit ourselves and focus on stabilizing the code - fixing bugs and testing every feature thoroughly. In a few months, we will re-visit feature requests like this and see if we have time to fix them for the 6.0 release.

It is a question about "release often" or "release with many features at once". We are working to release MySQL more often, and this means that some features will have to wait for the next release.

If this feature does not make it for MySQL 6.0 (it does not look so right now), we will add it later. We recognize it is an important feature.

Best wishes,
Lars Thalmann
Manager and Technical Lead,
MySQL Backup and Replication

The bug that needs to be fixed for this feature to work: BUG#36333