I really can't get rid of these conditional statements in mysqldump?

Question

My mysqldump DDL file is filled with tons of this:

/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `my_trigger`...

I've searched to no avail for an option to silence this.

My understanding is that this kind of output is necessary so that the script can successfully recreate the schema in what may be another version of mysql server. For my scenario with a closed system of installations, I know that the version will be the same as the source of the dump.

Again, I need to be able to make edits (manual or scripted) in the dump, and these outputs make the task quite a bit more daunting.

Elsewhere at SO, I saw a couple of questions on this, but folks have concentrated on preaching about why the tokens should be left alone. Kinder people recommended parsing the output on the command line to filter them out. Note that when I dump with phpMyAdmin, these tokens are not there.

Yak!

So, there really isn't a neater option to skip this kind of output? I work on Windows and with PowerShell.

Answer 1

It's really important to keep the conditional-execution comments. But if you absolutely know that the MySQL version that will load the dump is greater or equal to the one that creates it, you can remove the "comment" part with this:

sed -r  s'#/\*![0-9]{5} ?([^*]*)\*/#\1#'g

It will convert lines such as

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

to

SET SQL_MODE=@OLD_SQL_MODE ;

Because this line must run on any MySQL >= 4.1.1

Note that this will not remove multi-line conditional-execution comments, such as when dumping a trigger.

Since it's impossible to predict the future, it's better to store the dump with the comments on, and only remove them when you want to visualize it.

mysqldump ... > dump.sql
cat dump.sql | sed -E  s'#/\*![0-9]{5} ?([^*]*)\*/#\1#'g > dump.no-comments.sql

Answer 2

You do not want to silence this. Those options are for your protection (or at least the protection of less experienced MySQL Users).

The only option you could attempt to play with is --compatible

Produce output that is more compatible with other database systems or with older MySQL servers. The value of name can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options. To use several values, separate them by commas. These values have the same meaning as the corresponding options for setting the server SQL mode. See Section 5.1.8, “Server SQL Modes”.

This option does not guarantee compatibility with other servers. It only enables those SQL mode values that are currently available for making dump output more compatible. For example, --compatible=oracle does not map data types to Oracle types or use Oracle comment syntax.

Try doing

mysqldump --compatible=mysql40,no_field_options,no_key_options --no-data --skip-routines

just to experiment and see if the MySQL-centric commented meta-commands go away.