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.

11 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

Anonymous said...

hang on...

--skip-events is not a known option, I think
--events includes events in the dumps (and the data from the table as well)
--ignore-table=mysql.event excludes the data from the table (and the events as well)

mysqldump warns you that you should either include both or exclude both, while the default is to include the data but exclude the events. passing one of two options is enough

Anonymous said...

Thanks everyone....

Is there any reason that I should NOT backup the mysql.event table?

Anonymous said...

I had to resort to using the following to make the backup script not trigger a cron email anymore. The backup user that I'm using doesn't have access to the events table, and the solution of specifying --events with --ignore-table=mysql.event failed as it requires the user to have access to see the definition of the events table.

/usr/bin/mysqldump -u backup -A > /dev/shm/mysql-backup 2> /dev/stdout | grep -v "Warning: Skipping the data of table mysql.event"

Marcel said...

Actually, looking at what's in the event table, I think it makes sense to backup in my server-wide backup script. So I *don't* want to skip it.

And, believe it or not, in spite of the warning, it *did* get backed up!!

$ sudo grep -i ^CREATE /var/backups/mysqldump.sql
...
CREATE TABLE `event` (
...

Drew said...

I think you might want to be careful with this assumption Marcel.
If you look deeper, the table 'event' was created, BUT no data inserted.

We need to include the '--events' option to not only get rid of the warning message, but to also include the data into the table.

It appears, leaving the flag off, still allows for the full creation of the database schema, just doesn't populate this table data.

Vote on Planet MySQL