Wednesday, February 06, 2013

MySQL and warnings - Yet another compatibility break

The MySQL team seems to have a very peculiar idea about defaults and compatibility. Recently, I wrote about an annoying warning that cannot be removed. And previously, I noticed that MySQL 5.6 mixes warnings, info lines and errors in the same bunch of excessive chattiness.

With MySQL 5.5.30 came another headache. When I run a mysqldump of the 'mysql' schema, I get this warning:

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

OK. No big deal. What if I tell the little troublemaker that I DON'T WANT the events table?

$ mysqldump --skip-events mysql > m.sql
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

It seems that we have different ideas about when the warning should be given. If I skipped the events, I should not get the warning. Anyway, maybe it was the wrong option. Looking at the Release notes for MySQL 5.5.30, I see this:

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 (Bug #55587, Bug #11762933)

Aha! Let's try with --ignore-table

mysqldump --ignore-table=mysql.events mysql > m.sql
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

WTF? The only way of removing the warning is by running mysqldump with --events. But in that case I will get what I wanted to avoid. Otherwise I pollute the output with a warning that should not be there. Perhaps this warning could be removed when an explicit option tells mysqldump that this behavior is intentional?

Strangely enough, in the latest MySQL 5.6 GA this warning does not show up.

6 comments:

João Nelas said...

Maybe it was a typo.

In your examples you are ignoring the mysql.eventS (plural) table, but the message is about the mysql.event (singular).

Giuseppe Maxia said...

João
Thanks.
Indeed, it was a typo.
Only with the --events (which the warning mentions) we continue to have a warning.

Jason Stames said...

Per the following page, you must specify the --events flag to get rid of the warning then use the --ignore-table flag.

http://bugs.mysql.com/bug.php?id=68376

Ex: mysqldump -u [user] -p [database] --events --ignore-table=mysql.events > [file]

You can also include the event options in your my.cnf file so you do not have to specify them on the command line.

[mysqldump]
events
ignore-table=mysql.events

Anonymous said...

Thanks for this post. http://bugs.mysql.com/bug.php?id=68376 also suggests to use --events, but then the mysql.event table is dumped anyway, only the warning disappears.

Brian Stolz said...

Here is the actual solution:

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

Notice "--ignore-table=mysql.event" .. not plural.

--events makes the warning go away, and then --ignore-table=mysql.event makes the table go away.

Anonymous said...

Thank you!
At least I found this.
I had the problem that in my backup-script: automysqlbackup this error was giving me every day a waring through cron on my Ubuntu 12.04 Server, which autoupdated mysql on 25.04.2013
I put the parameter into the my.cnf and it works again (I hope).
Thanks.

frank

Vote on Planet MySQL