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:
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

[30 May 2012 15:04] Jamie Jackson
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:



fputs(",\n", md_result_file);

again, when the option is set.
[30 May 2012 15:12] Jamie Jackson
A case could be made that this is a bug, as presumably, output from Windows an Linux clients should be similar.
[30 May 2012 15:35] Jamie Jackson
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.
[30 May 2012 15:44] Jamie Jackson
tag tweak
[30 May 2012 16:20] Valeriy Kravchuk
Thank you for the feature request.
[20 Dec 2012 17:58] thomas tulinsky
I second the feature request.
[22 Mar 2013 13:23] Tomáš Fejfar
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.
[22 Mar 2013 23:20] Jamie Jackson
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:


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.
[22 Mar 2013 23:41] Jamie Jackson
Similar: Bug #4328, Bug #31343, Bug #55007
[1 Aug 2013 15:59] John Huss
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.
[23 Jun 2014 19:09] Morgan Tocker
#73083 is a duplicate.
[30 Jun 2014 10:53] MySQL Verification Team
http://bugs.mysql.com/bug.php?id=73083 marked as duplicate of this one.
[25 Jun 2015 18:50] Daniel Kudwien
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?
[25 Sep 2015 5:50] MySQL Verification Team
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.
[3 Jun 2018 15:50] Sam Kuper
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.
[3 Jun 2018 15:56] Sam Kuper
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?