| Bug #57451 | mysqldump omits events | ||
|---|---|---|---|
| Submitted: | 14 Oct 2010 11:02 | Modified: | 14 Oct 2010 15:18 |
| Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: mysqldump Command-line Client | Severity: | S1 (Critical) |
| Version: | 5.5.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | qc | ||
[14 Oct 2010 11:09]
MySQL Verification Team
probably his stored routines are lost too. he must give --routines --events to have those backed up.
[14 Oct 2010 11:11]
Peter Laursen
Really .. ? If you just specify a database or --all-databases everybody would expect that all type of database objects are backed up.
[14 Oct 2010 12:54]
Peter Laursen
If this is the way it is supposed to work than at least it should be more obvious from documentation (emphasized and on top) that only tables (and views) are dumped if no --options are specified. I am uncertain about TRIGGERS as I also find now that there is both a --triggers and a --skip-triggers option (refer: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html). What is default as regards TRIGGERS if neither is specified? Unclear, isn't it? But updating documentation is not a real fix. RTFM unfortunately sometimes only applies when things went wrong. I am very surprised about this, but it is my fault (and worse the fault of our customer, who lost important code - we did fortunately not advise to use mysqldump) of course. To simply 'backup a database' should in my understanding mean *backup everything stored in the database so that it can be restored to full functionality* I think.
[14 Oct 2010 15:18]
Sveta Smirnova
Thank you for the report. There is --event option for dumping events. Regarding why table mysql.event is not dumped there is verified bug #55587. So this one is duplicate of that one.
Description: (recategorize if you want). We have a customer that has upgraded MySQL from 5.5.5 to 5.5.6. He backed up using mysqldump and restored to 5.5.6 (he should have used SQLyog instead of course! :-) ). He lost his EVENTS in the process. It is reproducible for me with 5.5.6 that EVENTS are not backup up. How to repeat: C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqldump newdb -uroot -p Enter password: ******** -- MySQL dump 10.13 Distrib 5.5.6-rc, for Win64 (x86) -- -- Host: localhost Database: newdb -- ------------------------------------------------------ -- Server version 5.5.6-rc /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2010-10-14 12:52:19 C:\Program Files\MySQL\MySQL Server 5.5\bin> Event is there: SHOW CREATE EVENT newdb.testevent Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation --------- -------- --------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- ------------------ testevent SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `testevent` ON SCHEDULE EVERY 1 HOUR STARTS '2010-10-14 12:50:37' ENDS '2010-11-14 12:50:37' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN utf8 utf8_general_ci utf8_general_ci -- do nothing END Suggested fix: Obvious, I think!