MySQL Bugs: #29628: mysqldump should put newlines between VALUES when using extended inserts

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:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S4 (Feature request)
Version:	5.0.38	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysqldump

Description:
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:
INSERT INTO `__test1` VALUES
(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.

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"

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?

+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.