restore data fail when MySQL 5.5 with event
General
Escalation
General
Escalation
Description
Environment
xtrabackup version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)
Server version: 5.5.62-log Source distribution
Attachments
4
- 30 Mar 2021, 05:27 AM
- 30 Mar 2021, 05:27 AM
- 30 Mar 2021, 05:27 AM
- 30 Mar 2021, 05:26 AM
Smart Checklist
Activity
Show:
Lalit Choudhary April 29, 2021 at 11:38 AMEdited
Hi @code4happy
Thank you for the report and details.
MySQL 5.5 not supported anymore due to EOL and I don't see the described issue with MySQL 5.7 version and PXB 2.4.
So please upgrade to MySQL 5.7 and test again.
My Test with 5.7.32 version:
CREATE TABLE messages (
id INT PRIMARY KEY AUTO_INCREMENT,
message VARCHAR(255) NOT NULL,
created_at DATETIME NOT NULL
);
CREATE EVENT IF NOT EXISTS test_event_01
ON SCHEDULE AT CURRENT_TIMESTAMP
DO
INSERT INTO messages(message,created_at)
VALUES('Test MySQL Event 1',NOW());
CREATE EVENT test_event_02
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
DO
INSERT INTO messages(message,created_at)
VALUES('Test MySQL Event 2',NOW());
CREATE EVENT test_event_03
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
INSERT INTO messages(message,created_at)
VALUES('Test MySQL recurring Event',NOW());
mysql [localhost] {msandbox} (test) > SHOW EVENTS FROM test;
+------+---------------+--------------------+-----------+-----------+---------------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+------+---------------+--------------------+-----------+-----------+---------------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
| test | test_event_02 | msandbox@localhost | SYSTEM | ONE TIME | 2021-04-29 16:55:00 | NULL | NULL | NULL | NULL | ENABLED | 0 | utf8 | utf8_general_ci | latin1_swedish_ci |
| test | test_event_03 | msandbox@localhost | SYSTEM | RECURRING | NULL | 1 | MINUTE | 2021-04-29 16:54:00 | 2021-04-29 17:54:00 | ENABLED | 0 | utf8 | utf8_general_ci | latin1_swedish_ci |
+------+---------------+--------------------+-----------+-----------+---------------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
2 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT * FROM messages;
+----+----------------------------+---------------------+
| id | message | created_at |
+----+----------------------------+---------------------+
| 1 | Test MySQL Event 1 | 2021-04-29 16:54:00 |
| 2 | Test MySQL recurring Event | 2021-04-29 16:54:00 |
| 3 | Test MySQL Event 1 | 2021-04-29 16:54:44 |
| 4 | Test MySQL recurring Event | 2021-04-29 16:55:00 |
| 5 | Test MySQL Event 2 | 2021-04-29 16:55:00 |
+----+----------------------------+---------------------+
5 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > select db,name from mysql.event;
+------+---------------+
| db | name |
+------+---------------+
| test | test_event_02 |
| test | test_event_03 |
+------+---------------+
2 rows in set (0.00 sec)
$ ./xtrabackup --version
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql/
./xtrabackup version 2.4.21 based on MySQL server 5.7.32 Linux (x86_64) (revision id: 5988af5)
$ ./xtrabackup --defaults-file=/home/lalit/sandboxes/msb_5_7_32/my.sandbox.cnf --data-dir=/home/lalit/sandboxes/msb_5_7_32/data/ --user=msandbox --password=msandbox --socket=/tmp/mysql_sandbox5732.sock --backup --target-dir=~/backup
210429 16:56:52 [00] Writing /home/lalit/backup/backup-my.cnf
210429 16:56:52 [00] ...done
210429 16:56:52 [00] Writing /home/lalit/backup/xtrabackup_info
210429 16:56:52 [00] ...done
xtrabackup: Transaction log of lsn (115308936) to (115308945) was copied.
210429 16:56:53 completed OK!
percona-xtrabackup-2.4.21-Linux-x86_64.glibc2.12/bin$ ./xtrabackup --prepare --target-dir=~/backup
InnoDB: Shutdown completed; log sequence number 115309608
210429 16:58:35 completed OK!
#Restore backup###
/backup$ cp -r * ../sandboxes/msb_5_7_32/data/
mysql [localhost] {msandbox} (test) > select db,name from mysql.event;
+------+---------------+
| db | name |
+------+---------------+
| test | test_event_02 |
| test | test_event_03 |
+------+---------------+
2 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SHOW EVENTS FROM test;
+------+---------------+--------------------+-----------+-----------+---------------------+----------------+----------------+---------------------+---------------------+----------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+------+---------------+--------------------+-----------+-----------+---------------------+----------------+----------------+---------------------+---------------------+----------+------------+----------------------+----------------------+--------------------+
| test | test_event_02 | msandbox@localhost | SYSTEM | ONE TIME | 2021-04-29 16:55:00 | NULL | NULL | NULL | NULL | DISABLED | 0 | utf8 | utf8_general_ci | latin1_swedish_ci |
| test | test_event_03 | msandbox@localhost | SYSTEM | RECURRING | NULL | 1 | MINUTE | 2021-04-29 16:54:00 | 2021-04-29 17:54:00 | ENABLED | 0 | utf8 | utf8_general_ci | latin1_swedish_ci |
+------+---------------+--------------------+-----------+-----------+---------------------+----------------+----------------+---------------------+---------------------+----------+------------+----------------------+----------------------+--------------------+
2 rows in set (0.00 sec)
code4happy March 30, 2021 at 5:33 AM
In the Description section , which I have uploaded picture are not showen, so I have add again in the Comment section.
code4happy March 30, 2021 at 5:28 AM
Here are the pictures
Hello,
After backup my database(mysql 5.5.62-log) .I have found mysql.event table not correct.
This is my database(named A), and created with three event.
This database resotre from A. But only one event can been select.
Then I have see xtrabackup.log
Also I have see mysql.MYD
Now, I am confused, why mysql can not load event all?
Hope for your replay!