Help with DB Restore

I have a 750 MB vb 3.8 .SQL file. Its 91 MB gzipped. I don't know what method was used for backup.

On restoration (via SSH), its throwing a Duplicate Entry error. Only 30-40 tables of 180 MB import well.

Code:

ERROR 1062 (23000) at line 1187158: Duplicate entry '717215' for key 1

Is there a way of rectifying this problem? I cannot take backup again so I guess I will have to manually rectify this problem in the .SQL file. What should I do?

Thanks

Please post what you have on line 1187158

But how? What program should I use to open a file of that size? My PC is hanging on opening this file via Wordpad, Notepad++ etc.

Try using the ms-dos FIND command to find the line which contains the string "717215".
As, I don't know the command to find a particular line in a text file. :|

Code:

C:\test>find /N /I "717215" db.sql

Sample:

Code:

C:\test>find /N /I "CREATE TABLE " db.sql

---------- DB.SQL
<snip>
[5145]CREATE TABLE `wp_comments` (
[5184]CREATE TABLE `wp_links` (
[5222]CREATE TABLE `wp_options` (
[5250]CREATE TABLE `wp_postmeta` (
[5277]CREATE TABLE `wp_posts` (
<snip>

C:\test>

You can load with Komodo Edit . You have to wait a while as the file size is huge.

Thats was cool shadab.

Here it is:-

Code:

F:\>find /N /I "717215" dbbackup.sql

---------- DBBACKUP.SQL
[10815]INSERT INTO `adminlog` (`adminlogid`,`userid`,`dateline`,`script`,`action
`,`extrainfo`,`ipaddress`) VALUES ('6461','2','1197172151','misc.php','updateuse
r','','91.38.52.211');
[10816]INSERT INTO `adminlog` (`adminlogid`,`userid`,`dateline`,`script`,`action
`,`extrainfo`,`ipaddress`) VALUES ('6462','2','1197172153','misc.php','updateuse
r','','91.38.52.211');
[10817]INSERT INTO `adminlog` (`adminlogid`,`userid`,`dateline`,`script`,`action
`,`extrainfo`,`ipaddress`) VALUES ('6463','2','1197172156','misc.php','updateuse
r','','91.38.52.211');
[10818]INSERT INTO `adminlog` (`adminlogid`,`userid`,`dateline`,`script`,`action
`,`extrainfo`,`ipaddress`) VALUES ('6464','2','1197172158','misc.php','updateuse
r','','91.38.52.211');
[382500]INSERT INTO `pm` (`pmid`,`pmtextid`,`userid`,`folderid`,`messageread`,`i
mportpmid`,`parentpmid`) VALUES ('717215','75441','22768','0','0','0','716733');

[1044668]INSERT INTO `post_thanks` (`id`,`userid`,`username`,`date`,`postid`) VA
LUES ('558216','6112','dweir1','1217717215','73170');
[1187157]INSERT INTO `post_thanks` (`id`,`userid`,`username`,`date`,`postid`) VA
LUES ('717215','8829','Wingy56','1222493859','95306');
[1187158]INSERT INTO `post_thanks` (`id`,`userid`,`username`,`date`,`postid`) VA
LUES ('717215','8829','Wingy56','1222493859','95306');
[1689830]INSERT INTO `post_thanks` (`id`,`userid`,`username`,`date`,`postid`) VA
LUES ('1320747','9930','blimfark','1237172150','138121');
[1689831]INSERT INTO `post_thanks` (`id`,`userid`,`username`,`date`,`postid`) VA
LUES ('1320748','2608','gnrfan','1237172157','152392');
[2267684]INSERT INTO `reputation` (`reputationid`,`postid`,`userid`,`reputation`
,`whoadded`,`reason`,`dateline`) VALUES ('1314033','152392','5008','3','2608','T
hanked Post','1237172157');
[2268534]INSERT INTO `reputation` (`reputationid`,`postid`,`userid`,`reputation`
,`whoadded`,`reason`,`dateline`) VALUES ('1314032','138121','284','3','9930','Th
anked Post','1237172150');
[2488420]INSERT INTO `reputation` (`reputationid`,`postid`,`userid`,`reputation`
,`whoadded`,`reason`,`dateline`) VALUES ('550930','73170','2','5','6112','Thanke
d Post','1217717215');
[2638006]INSERT INTO `reputation` (`reputationid`,`postid`,`userid`,`reputation`
,`whoadded`,`reason`,`dateline`) VALUES ('717215','109218','2','5','13015','Than
ked Post','1222697990');

F:\>

Also, let me tell you that 82 tables worth 177 MB import fine and THEN it throws this error. The last table that imports is post_thanks (as I see in phpmyadmin on my localhost), but am not sure if it has imported fully. Then I suppose there is no table with 'q' , then it has to be 'reputation' table I think.

Thanks

Is the database table you are importing is empty or does it have data in it ?

Originally Posted by Mert Gökçeimam

Is the database table you are importing is empty or does it have data in it ?

Ofcourse empty, fresh new database, importing from shell.

Originally Posted by webwizzy

Ofcourse empty, fresh new database, importing from shell.

I have a feeling that its got to be post_thanks table that is creating this problem. Its not imported fully as I see in phpmyadmin. Do you think its a good idea to remove this table from .sql ?? If yes, how can I do so via command-prompt?

Thanks

Yes, looks like you have entries with duplicate keys in post_thanks table in your backup. You can run mysqlimport with --replace or --ignore options to keep only one of those records: MySQL :: MySQL 5.0 Reference Manual :: 4.5.5 mysqlimport ? A Data Import Program

ah yes, I see there is this same insert query twice with same ID of 71725, which is probably causing this error.

Code:

[1187157]INSERT INTO `post_thanks` (`id`,`userid`,`username`,`date`,`postid`) VA
LUES ('717215','8829','Wingy56','1222493859','95306');
[1187158]INSERT INTO `post_thanks` (`id`,`userid`,`username`,`date`,`postid`) VA
LUES ('717215','8829','Wingy56','1222493859','95306');

I cannot understand that mysqlimport thing, can you please tell me what command should I run to remove one of those insert queries.

Thanks

How do you restore your db backup? I supposed that you do that with mysqlimport command line utility, and you should just append "--ignore" option in this case.

But I restore database through 'mysql' command, like this.

mysql -u dbusername -p databasename < backupname.sql

You can use this command instead:
mysqlimport --ignore -u dbusername -p databasename backupname.sql

thank you oleg, but I got it working through this.

mysql -f -u dbusername -p databasename < backupname.sql

-f forces DB to restore even if it encounters SQL errors.

Thanks