MySQL Bugs: #55007: Data Dump should add line break between VALUES lists for INSERT statements

Bug #55007	Data Dump should add line break between VALUES lists for INSERT statements
Submitted:	5 Jul 2010 17:40	Modified:	9 Jul 2010 6:59
Reporter:	d m	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S4 (Feature request)
Version:		OS:	Windows (XP Pro SP3)
Assigned to:		CPU Architecture:	Any
Tags:	Data Dump, INSERT sql, mysqldump

Description:
When making a backup using the Data Dump tool in the Administration interface of Workbench the 'Dumping data for table ...' section the whole INSERT statement runs on a single line.

It would be useful if a linebreak could be inserted between each list of column values.

Sometimes it's necessary to read the dump output!!

Thank you for your attention,

David Marten

How to repeat:
1. In server admin tab select Data Dump
2. Select schema(s) to export
3. Select tables to export
4. Choose 'Export to Self-Contained File'
5. Go to 'Advanced Export Options'
6. Ensure 'extended-insert - Use multiple-row INSERT syntax that include several VALUES lists.' is checked
7. Perform export.
8. After completion, inspect SQL file
9. See that INSERT statements for table data dumps are single line, and go on for a very long time for large tables!

Suggested fix:
Adapt dump code to insert a line break after each comma between the VALUES lists.

Sounds like a feature request.

It may be a nice option to have, but as mysqldump is used the only easy way to implement this request is to NOT use -e option of it (http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_extended-insert).

Thanks for your comments, but since you are trying to replace MySQL Administrator this seems like a regression. When performing the same operation from MySQL Administrator it places one 'row' of Insert VALUES on a single line in the dump.

Perhaps it's a windows only thing, requiring CR & LF to return. I'll fire up a Linux vm later to check there.

As it seems that -e is passed by default to mysqldump, we'll add an option to turn that off (--skip-extended-insert) in the options UI.

But there should be an option to dump a row per line in mysqldump, or to simply always do that when -e is enabled.

mysqldump feature request based on last comment makes sense.

Workbench has been fixed so that unchecking the "extended-insert" option will output an INSERT statement per row/line

[quote]or to simply always do that when -e is enabled.[/quote]

That would be the ideal!

Thanks Alfredo.

Regards,

David Marten

[quote]mysqldump feature request based on last comment makes sense.[/quote]

I have added to already existing feature request http://bugs.mysql.com/bug.php?id=31343

It would be good to push this request because having to turn off extended-insert functionality seems like a poor workaround (performance etc), and especially since this bug/request originated in 2004!

Similar: Bug #4328, Bug #65465