MySQL Bugs: #65465: mysqldump: Add Newlines Between Rows of Extended Inserts

Bug #65465	mysqldump: Add Newlines Between Rows of Extended Inserts
Submitted:	30 May 2012 15:04	Modified:	22 Mar 2013 23:41
Reporter:	Jamie Jackson	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S4 (Feature request)
Version:	4.1.2a+	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	mysqldump extended insert

Description:
Many folks put mysqldumps into version control. 

In order to make sense of the changes between revisions, rows/values need to be on separate lines.

More details can be found here: http://bugs.mysql.com/bug.php?id=4328

In recent dumps I've seen from Windows colleagues, there are newlines between rows in the inserts.

I *think* there are differences in output between Windows and Linux versions of mysqldump, and the Windows version's output has the preferred newlines.

How to repeat:
Run mysqldump with extended inserts, on a table with multiple rows.

Suggested fix:
Stolen from Tim Riker:

Would a patch for this be accepted? Some command line option like --extended-inserts-multiline or similar?

should just add to this change this:

row_length= 2 + extended_row.length;

adding 1 more for *nix platforms. May need to add 2 more for crlf on windows. And then here:

fputc(',',md_result_file);

becomes:

fputs(",\n", md_result_file);

again, when the option is set.

A case could be made that this is a bug, as presumably, output from Windows an Linux clients should be similar.

I take back my last comment. Windows behaves the same.

I found out that my colleague is using old client tools (pre workbench GUI) to generate the extended inserts with newlines.

My enhancement/feature request stands, though.

tag tweak

Thank you for the feature request.

I second the feature request.

I also vote for this. We use mysqldump for version control and we hate having one long line - because you can't see what has changed.

Until we get this, I'm using:

mysqldump -u root -p mydb --complete_insert | \ 
 perl -p -e 's/VALUES \(/VALUES \n\ \(/g' | \
 perl -p -e 's/\),\(/\),\n \(/g' | \
 > ~/mydump.sql

The second line breaks:

"VALUES ("
into
"VALUES 
("

Then, the third line splits each set of values onto its own line (each preceded with a space).

As far as I remember, this massages the output into the old-style mysqldumps, which were split out on new lines.

I'm sure there are cases (an unexpected pattern in the database) which break this parsing, but I haven't run into such a case yet.

Similar: Bug #4328, Bug #31343, Bug #55007

In order to find and solve problems with a database restore you have to be able to go to the problem record in the file and look at it.  Without line breaks between records this is extremely difficult.  This is a must-have.

#73083 is a duplicate.

http://bugs.mysql.com/bug.php?id=73083 marked as duplicate of this one.

A backwards-compatible solution would be to dual-purpose the existing --extended-insert flag.

It could additionally support a string value denoting the row/line delimiter:

- TRUE (default): No delimiter. (same as now)

- '<string>': Same as TRUE, but <string> will be used as line delimiter.

- FALSE: Separate INSERT statements on separate lines. (same as now)

The default value remains to be TRUE.  To get newlines in dumps, you can change your system-wide configuration (my.ini), or manually pass an option value on the command line:

$ mysqldump --extended-insert="\n"

Older versions will output a warning and ignore the unknown value.  But since --extended-insert defaults to TRUE, the resulting dump should match the user's expectations in most cases nevertheless.  In any case, the user will notice the warning.

Thoughts?  Would that be possible to implement?

if this won't be fixed, I don't mind using --extended-insert=0 but the problem is when innodb-flush-log-at-trx-commit=1 and you dont have a write caching enabled, import takes ages.  in this case, we'd like to specific that every X rows, a start transaction/commit get printed.  X can be something like 10000 by default.

I think these are also duplicates: Bug #9741, Bug #29628.

Closely related (might not need to be addressed if this bug is fixed): Bug #61468.

Oracle, this bug is now ~14 years old (it is a dupe of Bug #4328, which was filed in 2004 and closed erroneously). It is clearly an ongoing inconvenience to users.

It seems to require only a trivial 1-line modification to the source code, in order to be fixed. This has already been supplied by the community. (See e.g. https://stackoverflow.com/a/20046484 .)

Please can we either see this fixed this year, or at least get some feedback on why not?