MySQL Bugs: #31343: mysqldump extended insert: put newlines between records

Bug #31343	mysqldump extended insert: put newlines between records
Submitted:	2 Oct 2007 13:56	Modified:	20 Jul 2009 20:51
Reporter:	Alex W	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S4 (Feature request)
Version:	mysqldump Ver 10.11 Distrib 5.0.45	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	mysqldump, newline, source control

Description:
This is a reactivation of bug #4328. 

I'd like to have extended inserts in mysqldump for performance reasons; however, we also need to track .sql files that mysqldump creates in the source control software, as we need to see the diffs. Plus, the files need to be human readable (i.e. which record did we add in this changelist?)

These two requirements are impossible to combine with the current mysqldump output. --skip-extended-inserts is not a real workaround.

How to repeat:
Create a table with multiple records.
From the command line, run "mysqldump", specifying the database and table name.  Capture
stdout to a file.
Note that the "INSERT INTO" statement in that file has all of its records (in the "VALUES"
clause) on a single very long line.

Suggested fix:
Add a newline at the beginning of each record to be inserted.  (The SQL parser for the
"INSERT INTO" statement has no problem parsing a SQL statement containing newlines.)

Thank you for the reasonable feature request.

If I understood correctly you want output looks like:

...
--
-- Dumping data for table `t`
--

LOCK TABLES `t` WRITE;
/*!40000 ALTER TABLE `t` DISABLE KEYS */;
INSERT INTO `t` VALUES (23)
,(42)
,(42)
,(33)
;
/*!40000 ALTER TABLE `t` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
...

Sveta - thanks, that's exactly what we need. 

Spasibo!

I would like to add to this feature request. Mysqldump is being used by mysql workbench and this output format for extended insert valueless is much more useful/sensible especially when dumping large tables. Also see here http://bugs.mysql.com/bug.php?id=55007.

Thanks for your attention.

David Marten

Sorry, above it is meant to say 'Values List' instead of 'valueless' - I posted from my phone and the auto-correct obviously did something clever.

See duplicate request in Bug #4328 and comments there.

Similar: Bug #55007, Bug #65465