distal-attribute
distal-attribute
distal-attribute
distal-attribute

Large dump file taking over 9 hours

User, date Message
Written by falcnor
7 years ago
Category: General
1 posts since Tue, 18 Dec 07
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.
Written by ansgar
7 years ago
4936 posts since Fri, 07 Apr 06
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?
Written by laury
7 years ago
4 posts since Thu, 20 Dec 07

anse wrote: 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');

Written by ansgar
7 years ago
4936 posts since Fri, 07 Apr 06
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.
Written by laury
7 years ago
4 posts since Thu, 20 Dec 07
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!!!!!!
Written by jack555
7 years ago
1 posts since Tue, 25 Dec 07
Hi,
after dealing with same sort of problem for some time, I luckily found this thread. And found roots of my problem.

anse wrote: 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!
Written by pgp_protector
6 years ago
2 posts since Tue, 06 Jan 09
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 ?
Written by ansgar
6 years ago
4936 posts since Fri, 07 Apr 06
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

Written by thelethalmoo
5 years ago
2 posts since Sat, 26 Sep 09
sory for bing immature but am i the only person who fell of thier chair when they read the tital of this thread? :P
 

Please login to leave a reply, or register at first.