| 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: | |
| 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 | ||
[12 Mar 2013 13:19]
Lev Bystritskiy
[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.
