--extended-insert not working properly

[Griffinator]

Hi,

I am having a problem with using the --extended-insert option when using mysqldump. As I understand it, this option creates multiple insert statements within the .sql file for tables that contain more than 1 row of data. According to documentation I have found, this substantially speeds up the restoration process. The main database I am concerned with uses the InnoDB engine, however I also want to backup the mysql database as well (which, to my knowledge, uses the MyISAM engine).

However, when I execute the database dump using this option, there is only 1 insert statement, and all values are seperated by commas. When trying to restore my databases, this causes my system to eventually freeze up due to the inefficiency of inserting multiple rows this way. I am using Windows XP, 1.8 Ghz, 512 MB of RAM.

The command I am issuing is as follows:

mysqldump --opt --single-transaction --u root --all-databases > "C:\testbackup.sql"

I have also issued the following command, with the same result:

mysqldump --extended-insert --single-transaction --u root --all-databases > "C:\testbackup.sql"

If someone could shed some insight into what I am doing wrong and how to execute the command to include multiple insert statements, it would be greatly appreciated.

Thanks in advance!

 

[Griffinator]

Sorry, I also should have mentioned that I am using MySQL 5.0.18-nt. Hope this helps...

 

[KarveR]

I normally use :
mysqldump --complete-insert --quick db_name > file.whatever

For transactional stuff, you'll ideally need --single-transaction

I never do all at once, don't think I have 80gig free

Once dumped, gzip/bzip/winzip will do a nice job of shrinking the size, and never had any problems reloading from them.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[Griffinator]

Thank you for your reply, KarveR

I tried the command line syntax as you suggested, and unfortunately the problem is the same. Do you have any other suggestions?

 

[KarveR]

Not some problem with the way windows likes to terminate lines or encloseure problems?

set these parameters by --fields-terminated-by=…, --fields-enclosed-by=…, --fields-optionally-enclosed-by=…, --fields-escaped-by=…, --lines-terminated-by=…

without knowing how much data its trying to dump, where to, or whats in the data ('`|,"') etc its a tad hard to tell really.

If you specify a --where='blah blah' and limit the dump to just one table and say 10 lines, and working through each table to see how well each one goes, you may be able to narrow down your error.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.