Bug #55587 mysqldump will silently skip the `event` table data if dumps mysql database
Submitted: 27 Jul 2010 17:15 Modified: 12 Nov 2012 23:51
Reporter: Sveta Smirnova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version:5.1+ OS:Any
Assigned to: CPU Architecture:Any

[27 Jul 2010 17:15] Sveta Smirnova
Description:
If mysqldump dumps mysql database and option --events is not used data in table event is not dumped.

At the same time if skip option --routines data in proc and func tables are dumped.

How to repeat:
create event ev1 on schedule every 1 hour do select 1 into @v;
select * from mysql.event;

--exec $MYSQL_DUMP mysql

--exec $MYSQL_DUMP --tab=$MYSQL_TEST_DIR/var/tmp mysql

--exec ls $MYSQL_TEST_DIR/var/tmp

Suggested fix:
Please dump data in event table if one dumps mysql database only.
[13 Aug 2010 18:52] Omer Barnir
triage: Steva, to clarify, I agree that the behavior is different what I am questioning is why do they have to be the same? 

--routine controls the inclusion (or not) of the code to re-create the procedures/functions. It does not affect the the execution of the functions/procedures that still need to be called.

--events controls if to include the event table itself as the existance of content in the table will make the server start events. So if the behavior was consistant, the user dumping the the system without specifying events (assuming the dump was inclusive of the mysql database), after loading the dump to a different server would have gotten one with events running and that would have been a bug.

See bug#29938 for more information.

Setting to documentation to explain better the isue with '--event' not dumping the events table
[13 Aug 2010 20:06] Harrison Fisk
Hi Omer,

The problem is that it silently does not dump the table.  Can you at least add a warning or something that is output if you try to dump a table that isn't allowed?

Silenting skipping a table is not good behavior.  I don't think any sort of argument can be made about that.  When I say "backup a database", no one means "backup a database except for some random table due to some arcane reasoning".

If you don't want to make it not skip the table, can you at least make it output a warning on stderr?  This will hit 99.99% of users that try to dump the mysql database and silently break their backup.
[16 Aug 2010 13:45] Paul DuBois
This is a server bug.

The --events and --routines (and --triggers) options are for dumping event, routine, and trigger *definitions*, not tables in the mysql database.

mysqldump mysql should dump all tables in the mysql database, no exceptions.

--events and --routines have nothing to do with whether the mysql.event and mysql.proc tables are dumped.

It is a server bug that --events affects which tables mysqldump mysql dumps. It should affect only whether the definitions for events associated with the dumped database are dumped.

Bug#29938 is a non-bug that should never have been "fixed".
[16 Aug 2010 14:35] Paul DuBois
Also: Suppression of the mysql.event table when dumping the mysql database can be easily effected using the --ignore-table=mysql.event option.
[14 Oct 2010 15:19] Sveta Smirnova
Bug #57451 was marked as duplicate of this one.
[12 Nov 2012 23:51] Paul DuBois
Noted in 5.5.30, 5.6.9, 5.7.0 changelogs.

For dumps of the mysql database, mysqldump skipped the event table
unless the --events option was given. To skip this table if that is
desired, use the --ignore-table option instead
[11 Mar 2013 17:46] Paul DuBois
Noted in 5.1.68 changelog.
[10 Apr 2013 19:21] Paul DuBois
There were two different patches for this bug.

For 5.1.68, 5.5.30:

For dumps of the mysql database, mysqldump skips the event table
unless the --events option is given. mysqldump now prints a warning
if invoked without --events that the mysql.event table is not dumped
without that option.

For 5.6.9, 5.7.0:

For dumps of the mysql database, mysqldump skipped the event table
unless the --events option was given. This no longer occurs. To skip
the event table if that is desired, use the --ignore-table option
instead