MySQL Bugs: #29938: wrong behavior of mysqldump --skip-events

Bug #29938	wrong behavior of mysqldump --skip-events
Submitted:	20 Jul 2007 16:07	Modified:	14 Sep 2007 12:09
Reporter:	Omer Barnir (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.1.20	OS:	Any
Assigned to:	Gleb Shchepa	CPU Architecture:	Any

Description:
In 5.1 myqldump has a new option --skip-events that prevents the export of events.
The option is implemented by preventing the re-creation of the events in the dump file (something that happens if the --events option is specified).

However even in the case where --skip-events is specified, the mysql.events table is exported -- and imported when the dump file is loaded -- the import does include the events.

So currently, the only affect of --skip-events is that the events will not start until the server is restarted (as opposed to the --events option that recreates them and therefor starts them immediately. 

How to repeat:
Start a 5.1 system and create some events in it such as
USE TEST;
SET GLOBAL event_scheduler='ON';
CREATE TABLE ev_count (i INT);
INSERT INTO ev_count VALUES (0);
CREATE EVENT test_event ON SCHEDULE EVERY 5 SECOND
DO UPDATE ev_count SET i= i+1;

wait for a few seconds and check that the valuse of i increases.

export the database using mysqldump *without* specifying '--events'.

Create a new system and import the above export into it.

Restart the system
SET GLOBAL event_scheduler='ON';
Chech:
1) mysql.event will have the event defined
2) a SELECT * from test.test_event every 5 seconds will show increased valuses

Suggested fix:
Using the '--skip-events' option should not include an export of the mysql.events table (in addition to not including the DROP...CREATE statements)

dump file showing event table data

Attachment: mysql_dump.tar.gz (application/x-gunzip, text), 105.22 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/33658

ChangeSet@1.2572, 2007-09-04 23:20:06+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #29938.
  mysqldump --skip-events --all-databases dumped data of the mysqld.event table,
  and during the restoration from this dump events was created instead
  of the --skip-events option.
  
  The mysqldump client has been modified to ignore mysql.event table data
  in case of --skip-events options.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/33690

ChangeSet@1.2572, 2007-09-05 11:35:29+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #29938.
  mysqldump --skip-events --all-databases dumped data of the mysqld.event table,
  and during the restoration from this dump events were created in spite
  of the --skip-events option.
  
  The mysqldump client has been modified to ignore mysql.event table data
  in case of --skip-events options.

Pushed into 5.1.23-beta

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented fix in 5.1.23 changelog.