MySQL Bugs: #68376: -- Warning: Skipping the data of table mysql.event. Specify the --events option

Bug #68376	-- Warning: Skipping the data of table mysql.event. Specify the --events option
Submitted:	14 Feb 2013 10:47	Modified:	18 Feb 2013 21:02
Reporter:	Sergey Fomin	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	Ver 14.14 Distrib 5.5.30	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
MySQL 5.5.30. When I run a mysqldump of the 'mysql' schema, I get this warning:

$. mysqldump -uroot mysql > /tmp/mysql.sql
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

How to repeat:
[/]# mysql -V
mysql  Ver 14.14 Distrib 5.5.30, for Linux (x86_64) using readline 5.1

[/]# mysqldump -uroot -p'abcd' mysql > /tmp/mysql.sql
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

If i downgrade mysql version to 5.5.28, then everithing is work:

[/]# mysql -V
mysql  Ver 14.14 Distrib 5.5.28, for Linux (x86_64) using readline 5.1

[/]# mysqldump -uroot -p'FI0TLBvN' mysql > /tmp/mysql.sql
[/]#

Suggested fix:
Downgrade to mysql 5.5.28

Try this workaround:

mysqldump -uroot --events --ignore-table=mysql.events mysql > /tmp/mysql.sql

You want to say that this is not a bug?

Hello Sergey,

Thank you for the report.

Verified as described on reported version.

Thanks,
Umesh

This is not a bug. Without the specified switch the event table is not being dumped. As this was confusing to users, a warning about it was added to alert users to fact and offer them the solution of performing the dump with the switch.

I found the "workaround" kinda confusing:

 > mysqldump -uroot --events --ignore-table=mysql.events mysql > /tmp/mysql.sql

This will back up the "mysql" database, including "mysql.event". But then "--ignore" tells to ignore the "mysql.events" table, which does not exist, it's "mysql.event" (w/o the "s"). But since we specified "--events", why would we want to ignore this table?

To suppress the warning message, just do as the warning message suggests:

 > mysqldump -uroot --events mysql > /tmp/mysql.sql

However, even without "--events", the "mysql.event" table is dumped:
 
 $ mysqldump --events mysql > mysql_with-events.sql
 $ mysqldump mysql > mysql.sql
 -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

 $ diff mysql.sql mysql_with-events.sql
 162a163,171
 > -- Dumping data for table `event`
 > --
 >
 > LOCK TABLES `event` WRITE;
 > /*!40000 ALTER TABLE `event` DISABLE KEYS */;
 > /*!40000 ALTER TABLE `event` ENABLE KEYS */;
 > UNLOCK TABLES;
 >
 > --
 725a735,738
 >
 > --
 > -- Dumping events for database 'mysql'
 > --
 736c749
 < -- Dump completed on 2013-04-13 20:47:51
 ---
 > -- Dump completed on 2013-04-13 20:47:41
 
 $ mysqldump --version
 mysqldump  Ver 10.13 Distrib 5.5.30, for debian-linux-gnu (x86_64)

You are using the incorrect table name when trying to skip writing the table.  Its "event" not events"

This will suppress the warning and skip writing the table.

mysqldump -uroot -p --events --ignore-table=mysql.event mysql > /tmp/mysql.sql

This will suppress the warning and skip writing the table.
mysqldump -uroot -p --events --ignore-table=mysql.event mysql > /tmp/mysql.sql"

Although this suppresses the message your command is conflicting.

The --events flag will explicitly include event Scheduler events.
The --ignore-table=mysql.event will ignore the same table

So either flag will do, but it makes no sense to include both. One says to include it while the other says to ignore.

Sorry if it was clear in my post above but the first two lines are quoting the previous comment for reference only, and they are not my words.

It appears there is no "correct" way to stop this warning.

Please add an option such as "--no-events" so we can suppress this warning in a proper way without having to specify --events.

At the moment the only way to suppress this warning is to specify --events as earlier posters have mentioned. Of course, if you do not actually want the events you then need to ALSO specify --ignore-table=mysql.event.

--events - Dump events (causes suppression of message)
--ignore-table=mysql.event - On second though, don't dump it

--ignore-table on its own will NOT suppress the message, only --events does the suppression so you MUST use both.

If anybody who doesn't know this looks at code that does this they will see the conflict and it will cause undue confusion. Likewise, without this, the output will no longer be clear, concise and readable and cause undue confusion, "Warning? Crap has it worked? Need to waste 5 mins checking"

So let's add a --no-events please, and mention it in the warning:
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly to dump this table, or --no-events to suppress this warning.

Thanks,

Jason

I retract my previous comments regarding --ignore-table - this is actually ineffective.

So the workaround to specify --events AND --ignore-table, does not appear to work, at least with the following version:
mysqldump  Ver 10.13 Distrib 5.5.34, for Linux (x86_64)
(Amazon AWS package mysql55)

So the only way to suppress the warning is to actually dump the events with --events. So we could really do with a --no-events option. If the argument is it doesn't matter if we're dumping events or not, then let's be consistent and start dumping events by default.

Thanks.

Why does the --events option exist ? Why would the mysql.event table not be needed if one wants to make a full database export ?

According to http://datacharmer.blogspot.ca/2013/02/mysql-and-warnings-yet-another.html this would be solved in MySQL 5.6.