Bug #68650 Dump restoration does not create events
Submitted: 12 Mar 2013 13:19 Modified: 12 Apr 2013 19:14
Reporter: Lev Bystritskiy Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.5.30, 5.1.70, 5.5.32, 5.6.12, 5.7.2 OS:Linux
Assigned to: CPU Architecture:Any
Tags: --all-databases, events, mysqldump
Triage: Needs Triage: D2 (Serious)

[12 Mar 2013 13:19] Lev Bystritskiy
Description:
I made all databases dump with  mysqldump utility (with --all-database and with option to dump events (--events) . Following after that dump restoration does not created any events.

However, the same procedure with only one base was successsfull - DB restored from one-base-dump with all needed events.

And both dumps have events data in it's body.

How to repeat:
mysqldump --all-databases --single-transaction --events -d > somedump.sql

mysql < somedump.sql

use <database with events>
Database changed
mysql> show events;
Empty set (0.00 sec)

Suggested fix:
I walk-around it with dumping and restoring one by one all databases with events, after restoring all-databases dump.
[12 Mar 2013 19:06] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior. Please specify which exact MySQL package do you use (file name you downloaded), provide example of one of events which was not saved and full server error log file from both backup and restore servers. Would be good if you also check dump file for existence of events.
[13 Mar 2013 7:14] Lev Bystritskiy
Hi!

>> Please specify which exact MySQL package do you use (file name you downloaded)

I used MySql from remi repository. OS: CentOS 5.9 x64

mysql.x86_64 5.5.30-1.el5.remi
mysql-server.x86_64 5.5.30-1.el5.remi
Percona-Server-shared-compat.x86_64 5.5.30-rel30.1.465.rhel5

>>provide example of one of events which was not saved

All events creating (or droping) partitions:

mysql> show create event m_partitions \G
*************************** 1. row ***************************
               Event: m_partitions
            sql_mode: NO_DIR_IN_CREATE
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`idp`@`localhost` EVENT `m_partitions` ON SCHEDULE EVERY 1 DAY STARTS '2011-01-01 23:59:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
    DECLARE new_partition CHAR(32) DEFAULT
      CONCAT ('p_', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 5 DAY), '%Y%m%d'));
    DECLARE max_day INTEGER DEFAULT TO_DAYS(NOW()) +5;

    SET @s =
      CONCAT('ALTER TABLE messages ADD PARTITION (PARTITION ', new_partition,
      ' VALUES LESS THAN (', max_day, '))');
    PREPARE stmt FROM @s;
    EXECUTE stmt;
  END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci

>> and full server error log file from both backup and restore servers. Would be good if you also check dump file for existence of events.

There is no errors in logs of the backup server. It have only some master-slave network errors. Some warning in restored server log:

130312 15:45:55 InnoDB: 5.5.30 started; log sequence number 0                                                                                                                                                                                
130312 15:45:55 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306                                                                                                                                                                 
130312 15:45:55 [Note]   - '0.0.0.0' resolves to '0.0.0.0';                                                                                                                                                                                  
130312 15:45:55 [Note] Server socket created on IP: '0.0.0.0'.                                                                                                                                                                               
130312 15:45:55 [Warning] 'user' entry 'root@db02-dc1' ignored in --skip-name-resolve mode.                                                                                                                                                  
130312 15:45:55 [Warning] 'user' entry '@db02-dc1' ignored in --skip-name-resolve mode.                                                                                                                                                      
130312 15:45:55 [Warning] 'proxies_priv' entry '@ root@db02-dc1' ignored in --skip-name-resolve mode.                                                                                                                                        
130312 15:45:55 [Note] Event Scheduler: Loaded 0 events                                                                                                                                                                                      
130312 15:45:55 [Note] /usr/libexec/mysqld: ready for connections.                                                                                                                                                                           
Version: '5.5.30-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Remi                                                                                                                                 
130312 15:45:55 [Note] Event Scheduler: scheduler thread started with id 1                                                                                                                                                                   
130312 16:15:24 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='192.168.87.4', master_port='3306', master_log_file='mysql-bin.000003', 
master_log_pos='107'.                                                                                                                                                                                                                        
130312 16:15:25 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000003' at position 107, relay log './mysqld-relay-bin.000001' position: 4                                                                       
130312 16:15:25 [Note] Slave I/O thread: connected to master 'repl@192.168.87.4:3306',replication started in log 'mysql-bin.000003' at position 107                                                                                          
130312 17:32:27 [Warning] 'user' entry 'root@<domain name that i removed>' ignored in --skip-name-resolve mode.       

As i said, events was in the body of full and some-db-only dump. I checked this again right now.
[13 Mar 2013 13:15] Sveta Smirnova
Thank you for the feedback.

I can not repeat described behavior with provided event as well.

Please try with Oracle's version of MySQL available at dev.mysql.com/downloads to ensure this is problem of MySQL and not its ports and forks.
[14 Mar 2013 9:07] Lev Bystritskiy
Atm i have only an opportunity to test on my desktop PC. Results are the same:

MySql resore server: 5.5.29

Backup (only structure and events) made with:
mysqldump -d --all-databases --events --single-transaction -uroot > idp4m9.sql

Backup only one DB (only structure and events) made with:
mysqldump -d --events --single-transaction -uroot idp > idp4m9idp.sql

Both backups have event data:

/*!50003 SET sql_mode              = 'NO_DIR_IN_CREATE' */ ;;                                                                                                                                                                                
/*!50003 SET @saved_time_zone      = @@time_zone */ ;;                                                                                                                                                                                       
/*!50003 SET time_zone             = 'SYSTEM' */ ;;                                                                                                                                                                                          
/*!50106 CREATE*/ /*!50117 DEFINER=`idp`@`localhost`*/ /*!50106 EVENT `m_partitions` ON SCHEDULE EVERY 1 DAY STARTS '2011-01-01 23:59:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN                                                         
    DECLARE new_partition CHAR(32) DEFAULT                                                                                                                                                                                                   
      CONCAT ('p_', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 5 DAY), '%Y%m%d'));                                                                                                                                                             
    DECLARE max_day INTEGER DEFAULT TO_DAYS(NOW()) +5;                                                                                                                                                                                       
                                                                                                                                                                                                                                             
    SET @s =                                                                                                                                                                                                                                 
      CONCAT('ALTER TABLE messages ADD PARTITION (PARTITION ', new_partition,                                                                                                                                                                
      ' VALUES LESS THAN (', max_day, '))');                                                                                                                                                                                                 
    PREPARE stmt FROM @s;                                                                                                                                                                                                                    
    EXECUTE stmt;                                                                

etc.

Restoring backup:

mysql < idp4m9.sql

mysql> use idp;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show events;
Empty set (0.00 sec)

Restoring backup of one DB:

mysql idp < idp4m9_idp.sql

mysql> use idp;
Database changed
mysql> show events;
+-----+----------------------+---------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db  | Name                 | Definer       | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+-----+----------------------+---------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| idp | delpart_messages     | idp@localhost | SYSTEM    | RECURRING | NULL       | 1              | DAY            | 2011-01-01 23:50:00 | NULL | ENABLED |          0 | utf8                 | utf8_general_ci      | utf8_general_ci    |
| idp | delpart_transactions | idp@localhost | SYSTEM    | RECURRING | NULL       | 1              | DAY            | 2011-01-01 23:50:00 | NULL | ENABLED |          0 | utf8                 | utf8_general_ci      | utf8_general_ci    |
| idp | m_partitions         | idp@localhost | SYSTEM    | RECURRING | NULL       | 1              | DAY            | 2011-01-01 23:59:00 | NULL | ENABLED |          0 | utf8                 | utf8_general_ci      | utf8_general_ci    |
| idp | t_partitions         | idp@localhost | SYSTEM    | RECURRING | NULL       | 1              | DAY            | 2011-01-01 23:59:00 | NULL | ENABLED |          0 | utf8                 | utf8_general_ci      | utf8_general_ci    |
+-----+----------------------+---------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
4 rows in set (0.01 sec)

I made the same actions on restored PC - backup, drop, restore again. All bases and only one, with events. Everything as i described before.
[21 Mar 2013 18:45] Sveta Smirnova
Thank you for the feedback.

Could you please send us mysqldump file of all databases and full MySQL server error log file?

If issue is repeatable on test instance with minimum database objects it would be enough: I want to compare your dump file with one which I create in my environment and which re-creates the event successfully.
[22 Mar 2013 9:55] Lev Bystritskiy
# mysqldump -d --all-databases --events --single-transaction -uroot | gzip > idp4m9.sql.gz

Attachment: idp4m9.sql.gz (application/x-gzip, text), 13.83 KiB.

[12 Apr 2013 19:14] Sveta Smirnova
Thank you for the script.

Problem is: mysqldump has query DROP TABLE IF EXISTS `events` in mysql database after event creation. I could not repeat issue before, because tested with database `test`.

Yet another workaround: don't backup mysql database if use option --events

Verified as described.
[12 Apr 2013 19:14] Sveta Smirnova
test case for MTR

Attachment: bug68650.test (application/octet-stream, text), 970 bytes.