Large dump file taking over 9 hours

Good morning,

I've noticed very few posts like this, with even fewer responses.

I have a 400MB dump file (newest release of HeidiSQL) and ran it over night. It ran for 9 hours before I said enough. It only populated part of 1 table (700 or so records) and created a 2nd table. There are about 2 dozen tables in all.

I've also tried to straight import this with mysql source command. But its syntax is incorrect with mysql 5.

Can anyone shed a bit of light on this issue? I can't find documentation on command line to try this out.

I appreciate any help you guys may be able to offer.

That sounds odd. 400 MB is much, but should never take such a long time.

Ok, so the dumpfile was created using HeidiSQL 3.2 ?

Any chance you post some of the long lasting commands here or by mail ? If not I'm afraid I won't be able to help you.

And what syntax is exactly wrong for the commandline?

That sounds odd. 400 MB is much, but should never take such a long time.

Ok, so the dumpfile was created using HeidiSQL 3.2 ?

Any chance you post some of the long lasting commands here or by mail ? If not I'm afraid I won't be able to help you.

And what syntax is exactly wrong for the commandline?

Hi Anse,

Man, that weird thing happens to me too. My sql file have 30 MB, and after more than one hour (wjhile the program not responded), it only processed half of the querys and I think that are missing rows, because the numbers don't matches.

Here, take a look at some of my querys:

CREATE TABLE `phpbb2_banlist` (
`ban_id` mediumint(8) unsigned NOT NULL auto_increment,
`ban_userid` mediumint(8) NOT NULL default '0',
`ban_ip` varchar(8) collate latin1_general_ci NOT NULL default '',
`ban_email` varchar(255) collate latin1_general_ci default NULL,
PRIMARY KEY  (`ban_id`),
KEY `ban_ip_user_id` (`ban_ip`,`ban_userid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=344 ;
--
-- Dumping data for table `phpbb2_banlist`

--
INSERT INTO `phpbb2_banlist` VALUES(1, 347, '', NULL);
INSERT INTO `phpbb2_banlist` VALUES(70, 0, '', '*@freemailacc.com');
INSERT INTO `phpbb2_banlist` VALUES(13, 0, '', '*@ofdjgpwrigp.info');
INSERT INTO `phpbb2_banlist` VALUES(12, 0, '', '*@yandex.ru');
INSERT INTO `phpbb2_banlist` VALUES(11, 0, '', '*@cashette.com');
INSERT INTO `phpbb2_banlist` VALUES(14, 0, '', '*@34jg-rjgrep.info');
INSERT INTO `phpbb2_banlist` VALUES(15, 0, '', '*@mail333.com');
INSERT INTO `phpbb2_banlist` VALUES(16, 0, '', '*@wpoejgpwjeg3wg.org');
INSERT INTO `phpbb2_banlist` VALUES(17, 0, '', '*@tut.by');
INSERT INTO `phpbb2_banlist` VALUES(18, 0, '', '*@imap.cc');
INSERT INTO `phpbb2_banlist` VALUES(19, 0, '', '*@rogji4j40jgire.info');
INSERT INTO `phpbb2_banlist` VALUES(20, 0, '', '*@globalsitegroup.ru');
INSERT INTO `phpbb2_banlist` VALUES(21, 0, '', '*@hotpop.com');
INSERT INTO `phpbb2_banlist` VALUES(22, 0, '', '*@fansiteworld.com');
INSERT INTO `phpbb2_banlist` VALUES(23, 0, '', '*@nm.ru');
INSERT INTO `phpbb2_banlist` VALUES(24, 0, '', '*@emailmanonline.org');
INSERT INTO `phpbb2_banlist` VALUES(25, 0, '', '*@mail333.com');
INSERT INTO `phpbb2_banlist` VALUES(26, 0, '', '*@youremailsoftware.org');
INSERT INTO `phpbb2_banlist` VALUES(27, 0, '', '*@freetechemail.org');
INSERT INTO `phpbb2_banlist` VALUES(28, 0, '', '*@fldojhwprhwp.info');
INSERT INTO `phpbb2_banlist` VALUES(29, 0, '', '*@mxvost.info');
INSERT INTO `phpbb2_banlist` VALUES(30, 0, '', '*@pfjhpw2ihpjhr.info');
INSERT INTO `phpbb2_banlist` VALUES(31, 0, '', '*@dfpijfpwiejf.org');
INSERT INTO `phpbb2_banlist` VALUES(32, 0, '', '*@rpfhwrihwiruhw432.org');
INSERT INTO `phpbb2_banlist` VALUES(33, 0, '', '*@lofhpwirhgpwirhg.info');
INSERT INTO `phpbb2_banlist` VALUES(34, 0, '', '*@*.info');
INSERT INTO `phpbb2_banlist` VALUES(35, 0, '', '*@*.org');
INSERT INTO `phpbb2_banlist` VALUES(36, 0, '', '*@gawab.com');
INSERT INTO `phpbb2_banlist` VALUES(37, 0, '', '*@alsado.com');
INSERT INTO `phpbb2_banlist` VALUES(38, 0, '', '*@*.ru');
INSERT INTO `phpbb2_banlist` VALUES(39, 0, '', '*@*.lv');
INSERT INTO `phpbb2_banlist` VALUES(40, 0, '', '*@*.ua');
INSERT INTO `phpbb2_banlist` VALUES(41, 0, '', '*@inbox.lv');
INSERT INTO `phpbb2_banlist` VALUES(42, 0, '', '*@best-finance.biz');
INSERT INTO `phpbb2_banlist` VALUES(43, 0, '', '*@narod.ru');
INSERT INTO `phpbb2_banlist` VALUES(44, 0, '', '*@mail.ru');
INSERT INTO `phpbb2_banlist` VALUES(45, 0, '', '*@emails.ru');
INSERT INTO `phpbb2_banlist` VALUES(46, 0, '', '*@mail.zp.ua');
INSERT INTO `phpbb2_banlist` VALUES(47, 0, '', '*@cracklord.com');
INSERT INTO `phpbb2_banlist` VALUES(48, 0, '', '*@thefreemail.com');
INSERT INTO `phpbb2_banlist` VALUES(49, 0, '', '*@mail.com');
INSERT INTO `phpbb2_banlist` VALUES(50, 0, '', '*@kent.com');
INSERT INTO `phpbb2_banlist` VALUES(51, 0, '', '*@me.by');
INSERT INTO `phpbb2_banlist` VALUES(52, 0, '', '*@seresa.com');
INSERT INTO `phpbb2_banlist` VALUES(53, 0, '', 'danielaisabelita0789@yahoo.com');
INSERT INTO `phpbb2_banlist` VALUES(54, 0, '', '*@marihuana.ro');
INSERT INTO `phpbb2_banlist` VALUES(55, 0, '', '*@mymail-in.net');

Ok, I think that dump was not created using HeidiSQL - I can tell that by the INSERT and the comment syntax.

The cause of the slowness is that the dump contains tons of single INSERTs . For few queries this is absolutely ok, but if you have such a large amount of INSERTs the memory and CPU-overhead is growing and growing. Instead you should create the dump with the "extended syntax", where multiple rows go into one INSERT statement, just like that:

INSERT INTO `mytable`
(field1, field2)
VALUES
('foo','bar'),
('what','ever'),
('bla','blub'),
... etc.

You can create this syntax with either HeidiSQL or with mysqldump commandline. For the latter one you have to ensure that the parameter "--extended-insert" is set (in newer MySQL versions enabled by default). This will extremely speed up the import of such large SQL dumps in HeidiSQL.

Man, i did the back up with heid sql now, and... it copied the entire database to my local server in five minutes!

thanks a lot for alert me about the querys. with heid sql they are a lot better!

thaks a lot!!!!!!

Hi,
after dealing with same sort of problem for some time, I luckily found this thread. And found roots of my problem.

Ok, I think that dump was not created using HeidiSQL - I can tell that by the INSERT and the comment syntax.

...

Heidi actually can produce this kind of syntax if we choose "Standard ANSI SQL" as "Target compatibility:" in Export dialog. This, as I understand, is a desired behavior, but maybe there should be some kind of warning that it's not clever to use this type of export for big databases, since later it's impossible to load them. I believe it could save some time for mysql and/or heidi newbies (and also prevent them from asking same questions here).

Generally really nice program, thanks!

I know old thread, but
1) It's a sticky so it's still relevant
2) I've got a problem :D

I've got a SQL Dump for a server I'm rebuilding, 140Meg that has a ton of

insert commands, so I get the slowdown right away.
I understand that the --extended-insert command should take care of this issue.

But I can't get a new dump, so is their a way to convert a few 100,000s insert commands to --extended-insert commands. other than trying to manually edit the raw text file ?

Could be doable by opening the file in Ultraedit (or whatever) and search and replace the

;
INSERT INTO bla (col1, col2, ...) VALUES

with

,

(of course leave out the first one which you will need). Then, configure the server to make it accept such large packets (max-allowed-packet=150M) and run the script.

Probably more simple is to use mysql.exe on your commandline which is significantly faster that Heidi for importing large SQL dumps:

mysql.exe -hlocalhost -uroot -p mydatabase <mydump.sql

sory for bing immature but am i the only person who fell of thier chair when they read the tital of this thread? :P