How can I get rid of these comments in a MySQL dump?

Question

I'm trying to create a simple structure only dump of my database. Using mysqldump gives me a result like:

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

DROP TABLE IF EXISTS `foo`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;

No matter what I try, I just can't seem to get rid of those comments.

I'm currently using: mysqldump -p -d --add-drop-table --skip-tz-utc --skip-set-charset -h 127.0.0.1 -u foo bar --result-file=dumpfile.sql

Edit: I do however wish to retain other comments, such as -- MySQL dump 10.13 Distrib 5.1.41, for Win32 (ia32)

Answer 1

WHOA! These aren't really comments even though they look that way. They are conditional-execution tokens.

Take this line:

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

If the version of mySQL is 4.00.14 or higher, then the MySQL server will run this statement.

This magic comment syntax is documented in the Comment Syntax section of the manual.

You probably don't want to get rid of this stuff.

Answer 2

I know this is an ancient question, but here is an answer at least. I also couldn't find a flag in mysqldump to remove the conditional comments, or indeed a better option to set a minimum mysql version for these comments to appear. If you just want to nuke them all, you can do so using grep or sed (sed leaves blank lines, grep does not):

mysqldump ... | grep -v '^\/\*![0-9]\{5\}.*\/;$'
mysqldump ... | sed -e 's/^\/\*![0-9]\{5\}.*\/;$//g'

To answer my own wish of conditionally removing comments dependent on mysql version, use one of these (removes any comments for anything < mysql5):

mysqldump ... | grep -v '^\/\*![0-4][0-9]\{4\}.*\/;$'
mysqldump ... | sed -e 's/^\/\*![0-4][0-9]\{4\}.*\/;$//g'

Answer 3

Try --skip-comments ?

Thanks

Edit:

I see .. Try this

--skip-add-drop-table --skip-add-locks --skip-disable-keys --skip-set-charset

Play around to remove some of the options till you get the desired result, basically this is same as --compact without --skip-comments

--skip-comments removes the comments relating to version and stuff ..

Answer 4

Have you tried the shortcut option --compact?

Information here.

Answer 5

Technically the lines you are trying to get rid of are not comments. They temporarily modify some variables at the beginning, and then reset them to the previous value at the end.

They're not very useful (but they're also harmless) in your case, since you're using --no-data, but I thought it worth mentioning that the lines do serve a purpose, and are not just comments.

Answer 6

Those are not comments, the execution of that part of the scripts depends on the version of your mysql.

You can delete "the comment part", like

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */

to

SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0

making the script more "comfortable" for reading.

If you try to run a "comfortable" script in a version newer than the specified in the "comment", you will get an error.

Answer 7

Use --dump-date=FALSE

Does exactly what OP asks for. (not exactly, I see)

Source: mysqldump option summary

Edit: Just after a minute I realized, this is what me was looking for not the OP, but leaving here... in hope someone can use it: This date line which ruins source control, because it always a change...

Answer 8

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 9

If you've stumbled up on this answer trying to include your structure.sql file in git/github, you can strip out auto-increment with the following code right after you rake db:structure:dump

# Remove beginning auto increments to prevent merge conflicts
filename = 'db/structure.sql'
File.atomic_write(filename) do |output|
  File.open(filename, 'rb').each do |input|
    output.write(input.gsub(/\s+AUTO_INCREMENT=\d+\s+/, ' '))
  end
end

Answer 10

Probably running a regex on it to remove lines that contain 40014 or 40111 etc.

Answer 11

Since you are on Windows, if no-one finds a better solution then you could use a Python script instead:

import re, sys
sql = sys.stdin.read()
regex = re.compile(r'/\*![^\n]* \*/;\n', re.M)
print regex.sub('', sql)

Usage from command line:

python program.py < your.sql > output.sql

It removes all lines like this:

/*!....... */;

Answer 12

I made this script to normalize the dump, including removing conditional comments: https://github.com/luissquall/dbdump.

You just have to:

npm install -g @luissquall/dbdump

# Redirect output to a file
dbdump -u user -p -d database > struct.sql

Answer 13

I used the following multiline Perl regexp to remove these lines from my dump file:

perl -0pi -e 's/\/\*\!\d+(.*?)\*\//\1/gms' dump.sql

I found other sed solutions not sufficient in cases where my trigger procedures spanned multiple lines.

Answer 14

I dont know if it is what are you looking for, i simply wanted to get rid of all the mysql comments stuff to be able to use a syntax highlighter, i used a simple regex and replace all with the following "/\*![0-9]{5}|\*/" and voila! nice colors in the code ;)

Answer 15

As @Ollie and a few others pointed out, these are are conditional-execution tokens written in comment style but served a purpose. Without them, you may run into issues of recreating tables with heavily enforced foreign key constraint. For instance, table A has FK for table B and thus table A cannot be created until table B do and so on so forth. Without disabling the key checks, you may never be able to recreate them depending how your table order is fined.