You are not logged in.

#1 2008-07-06 01:01:28

RAH
Member
Registered: 2008-06-20
Posts: 205

[SOLVED] Restore Database

Hello,

I am currently attempting to restore a very large MySQL database (1.4GB).

[root@server]# mysql --password=xxx --user=root vb < backup.sql
ERROR 1060 (42S21) at line 5116: Duplicate column name 'quoteid'

Is there anyway of forcing the database to restore? Basically it takes like an hour to get to this error where it halts and doesn't restore the rest of the database.

Thanks.

Last edited by RAH (2008-07-06 19:24:15)

Offline

#2 2008-07-06 01:06:40

ralvez
Member
From: Canada
Registered: 2005-12-06
Posts: 1,694
Website

Re: [SOLVED] Restore Database

Well, it could be a corruption in your backup; which I hope is not.
But a good way to go would be to create a database and restore the contents on that DB into the new (empty) DB. If it fails, then there is a problem with that backup, if it passes then and everything is OK then you can damp the bad DB and restore from scratch.
Is that a live site? If it is you should time the restore in the test DB to have an idea of how long your site will be down.

Hope this helps.

R.

Offline

#3 2008-07-06 01:14:54

RAH
Member
Registered: 2008-06-20
Posts: 205

Re: [SOLVED] Restore Database

That's what I did - restored into a new empty database.  After researching the error apparently the QuoteIt! mod for vBulletin has a bug where it inserts the duplicate collumn which I presume is related to this issue.  If there was some way of forcing the restore so it would ignore this.

Offline

#4 2008-07-06 01:21:50

ralvez
Member
From: Canada
Registered: 2005-12-06
Posts: 1,694
Website

Re: [SOLVED] Restore Database

I'll have to research this a bit I never had that problem wink
I'll post if I find a way around it.

R.

edit: On second thought, if you open the back up file in vim (or whatever text editor) you could remove the added column and re-try the upload.

Last edited by ralvez (2008-07-06 01:23:30)

Offline

#5 2008-07-06 01:27:28

RAH
Member
Registered: 2008-06-20
Posts: 205

Re: [SOLVED] Restore Database

ralvez wrote:

edit: On second thought, if you open the back up file in vim (or whatever text editor) you could remove the added column and re-try the upload.

I was thinking of doing just that - however it would take forerver to load a 1.4GB file and search through it or am I mistaken?

Offline

#6 2008-07-06 01:32:49

ralvez
Member
From: Canada
Registered: 2005-12-06
Posts: 1,694
Website

Re: [SOLVED] Restore Database

No, no... do not upload the file. Just open it with a text editor (that should not take too long). If you are using say vim you the type /quoteid and it will take you to the line where the first instance of the string appears. Then take note of the line number continue the search to see any other instances. Then you just delete them and let's hope for the best.

Juts in case, make a buck up of the original file too ... the devil never sleeps big_smile

R.

Offline

#7 2008-07-06 01:38:40

RAH
Member
Registered: 2008-06-20
Posts: 205

Re: [SOLVED] Restore Database

The file is already on the server.  I guess I will try nano but that usually takes forever from past experience.

Offline

#8 2008-07-06 01:40:16

ralvez
Member
From: Canada
Registered: 2005-12-06
Posts: 1,694
Website

Re: [SOLVED] Restore Database

Then, do not touch the file in the serer.
Make a dump of the file and download it to your machine. It is less error prone and will go faster when editing.

R.

Offline

#9 2008-07-06 01:56:34

RAH
Member
Registered: 2008-06-20
Posts: 205

Re: [SOLVED] Restore Database

I can't do it locally as my upstream bandwidth is very low so would take a very long time to upload the edited file.  I'm trying nano, it might not be as bad as I had anticipated.  Otherwise phpMyAdmin may be another option.

Offline

#10 2008-07-06 23:40:52

Ruckus
Member
Registered: 2007-02-17
Posts: 204

Re: [SOLVED] Restore Database

RAH wrote:

I can't do it locally as my upstream bandwidth is very low so would take a very long time to upload the edited file.  I'm trying nano, it might not be as bad as I had anticipated.  Otherwise phpMyAdmin may be another option.

Write up a quick script to do the search and replace for you if you can. That's what I'd do.

Offline

#11 2008-07-07 02:34:02

ralvez
Member
From: Canada
Registered: 2005-12-06
Posts: 1,694
Website

Re: [SOLVED] Restore Database

Glad to know things worked out well for you.

R.

Offline

Board footer

Powered by FluxBB