Bug #29628 mysqldump should put newlines between VALUES when using extended inserts
Submitted: 8 Jul 2007 18:01 Modified: 9 Jul 2007 9:38
Reporter: dAniel hAhler Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: mysqldump Command-line Client Severity:S4 (Feature request)
Version:5.0.38 OS:Any
Assigned to: CPU Architecture:Any
Tags: mysqldump

[8 Jul 2007 18:01] dAniel hAhler
mysqldump (with --extended-insert) should separate the VALUES by a newline instead of putting the whole INSERT statement on a single line.

This would make it a lot easier to look at the file using a text viewer/editor and to see differences when comparing dump files.

There has been a report about this already (bug 4328), but it seems there was a misunderstanding.
Please note, that I do not want --complete-insert/--skip-extended-insert, but improve the output of --extended-insert.

Currently however, --skip-extended-insert seems to be required as a workaround, if you want readable dump files.

How to repeat:
$ mysqldump "test"

INSERT INTO `__test1` VALUES (1,'some data'),(2,'data for row 2'),(3,'data\nfor row 3');

Suggested fix:
(1,'some data'),
(2,'data for row 2'),
(3,'data\nfor row 3');

Because this is likely to be not backward compatible, a new option to mysqldump might be added to configure the output format.
[9 Jul 2007 9:38] Sveta Smirnova
Thank you for the report.

As definition of option extended-insert is "Use multiple-row INSERT syntax that include several VALUES lists. This results in a *smaller* dump file and speeds up inserts when the file is reloaded." I reclassified this report as feature request "add new option to mysqldump for generating dump in readable format"
[2 May 2011 23:29] Tim Riker
See my comments on Bug #4328. I'm willing to create a patch for this if there is interest in adding the feature. Perhaps --extended-insert-multiline or similar?
[8 Jul 2011 15:08] Lon B
+1 this needs a fix; the extended format cannot be 100% properly parsed based on the comma or parenthesis, you would to count fields. The best solution, fix mysqldump to linebreak on output.

Until then, it a very minor change:
On line 3506, you can see where the row-ending comma is output:
fputc(',',md_result_file);            /* Always row break */

Simply insert this line immediately after line 3506:
fputc('\n',md_result_file);     /* Lon Binder says wrap that line! */

re-compile and done.

p.s. I don't think another CLI switch is needed, this should always be used, we're talking one byte per row, pretty negligible for far more useful output.