sql errors restoring a site

T

TheBear

Verified User
Joined
Apr 16, 2004
Messages
78
Hello,

I am trying to restore a site that was hosted on another DA box. When trying to restore just the databases I get the following:

----
Unable to restore database wilds_affiliate.sql to wilds_affiliate : ERROR 1064 at line 20 in file: '/home/wilds/backups/backup/wilds_affiliate.sql': You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 6
Unable to restore database wilds_billing.sql to wilds_billing : ERROR 1064 at line 20 in file: '/home/wilds/backups/backup/wilds_billing.sql': You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 29
Unable to restore database wilds_bot.sql to wilds_bot : ERROR 1064 at line 20 in file: '/home/wilds/backups/backup/wilds_bot.sql': You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 8
Unable to restore database wilds_directory.sql to wilds_directory : ERROR 1064 at line 20 in file: '/home/wilds/backups/backup/wilds_directory.sql': You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 8
Unable to restore database wilds_forum.sql to wilds_forum : ERROR 1064 at line 20 in file: '/home/wilds/backups/backup/wilds_forum.sql': You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 11
Unable to restore database wilds_help.sql to wilds_help : ERROR 1064 at line 20 in file: '/home/wilds/backups/backup/wilds_help.sql': You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 5
Unable to restore database wilds_im.sql to wilds_im : ERROR 1064 at line 20 in file: '/home/wilds/backups/backup/wilds_im.sql': You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 6
Unable to restore database wilds_jobs.sql to wilds_jobs : ERROR 1064 at line 20 in file: '/home/wilds/backups/backup/wilds_jobs.sql': You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 5
Unable to restore database wilds_mail.sql to wilds_mail : ERROR 1064 at line 20 in file: '/home/wilds/backups/backup/wilds_mail.sql': You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 12
Unable to restore database wilds_members.sql to wilds_members : ERROR 1064 at line 20 in file: '/home/wilds/backups/backup/wilds_members.sql': You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 5
Unable to restore database wilds_pages.sql to wilds_pages : ERROR 1064 at line 20 in file: '/home/wilds/backups/backup/wilds_pages.sql': You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 12
Unable to restore database wilds_panel.sql to wilds_panel : ERROR 1064 at line 20 in file: '/home/wilds/backups/backup/wilds_panel.sql': You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 34
Unable to restore database wilds_personals.sql to wilds_personals : ERROR 1064 at line 20 in file: '/home/wilds/backups/backup/wilds_personals.sql': You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 6
Unable to restore database wilds_phpBB1.sql to wilds_phpBB1 : ERROR 1064 at line 20 in file: '/home/wilds/backups/backup/wilds_phpBB1.sql': You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 18
Unable to restore database wilds_processing.sql to wilds_processing : ERROR 1064 at line 20 in file: '/home/wilds/backups/backup/wilds_processing.sql': You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 34
Unable to restore database wilds_webmail.sql to wilds_webmail : ERROR 1064 at line 20 in file: '/home/wilds/backups/backup/wilds_webmail.sql': You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 12
----

My box is a standard install and I have been told the other box is also a standard install.

Any suggestions? Really trying to get this site moved tonight :(

Thanks!

Dale
 
Dale, it's most likely a problem of the mysqldump on the source machine using commands not supported by the version of mysql on the destination machine.

Jeff
 
Thanks Jeff,

I will check with the (now former) site owner tonight when he gets back to see if he does own or lease the box so he can hut down mysql for a bit.
 
I can guarentee that is what it is. :(

I run into this constantly. Really frustrating because it really is so cryptic as to what the problem is.

I bet it's the "DEFAULT CHARSET='latin1'" that's causing it...seen that before.
 
Hello,

sullise said:
I can guarentee that is what it is. :(

I run into this constantly. Really frustrating because it really is so cryptic as to what the problem is.

I bet it's the "DEFAULT CHARSET='latin1'" that's causing it...seen that before.
Click to expand...

Any suggestions on how to get around this?

I haven't gotten word yet on if the former site owner as root/ssh access to be able to grab the raw databases.
 
Hello,

sullise is correct. Older versions of MySQL (less than 4.1?) don't like the DEFAULT CHARSET=latin1 bit. You can just PCRE it out:
Code: 
perl -pi -e 's/DEFAULT\s*CHARSET.[\w\d]+(\s*PACK_KEYS.\d+)*//' backup.sql
If you are using a DA backup archive and want to use the backup-restore tool in DA, you probably will need to extract the DA backup, run the above on any .sql files, and then create the backup archive again.

Cheers, Phil.
 
Last edited:
Hello,

Thanks for the info.

l0rdphi1 said:
Hello,

sullise is correct. Older versions of MySQL (less than 4.1?) don't like the DEFAULT CHARSET=latin1 bit. You can just PCRE it out:
Code: 
perl -pi -e 's/DEFAULT\s*CHARSET.[\w\d]+(\s*PACK_KEYS.\d+)*//' backup.sql
If you are using a DA backup archive and want to use the backup-restore tool in DA, you probably will need to extract the DA backup, run the above on any .sql files, and then create the backup archive again.

Cheers, Phil.
Click to expand...

If I do the above, how would I re-create the archive?
 
Code: 
mkdir tempo
tar xzfC theuserbackup.tar.gz tempo
perl -pi -e 's/DEFAULT\s*CHARSET.[\w\d]+(\s*PACK_KEYS.\d+)*//' tempo/backup/*.sql
tar czf newuserbackup.tar.gz tempo/*
Would go something like that (untested). :)

Phil.
 
Hello,

Thanks for the info.

l0rdphi1 said:
Code: 
mkdir tempo
tar xzfC theuserbackup.tar.gz tempo
perl -pi -e 's/DEFAULT\s*CHARSET.[\w\d]+(\s*PACK_KEYS.\d+)*//' tempo/backup/*.sql
tar czf newuserbackup.tar.gz tempo/*
Would go something like that (untested). :)

Phil.
Click to expand...

I tried the above, and everything seemed to go fine. Unfortunately, when I then try to restore the backup, the only options to restore are the DNS entries.

Apparently something is getting changed or not reset properly so that DA knows this new resulting tar file is a 'backup'.

Any other ideas?

Thanks!

Dale
 
You could restore the rest of the site from the original, assuming your restoring from within the clients interface and not the reseller one.

Then extract the SQL files and patch them and then do a manual restore via the CLI. It's a PITA, but I've done it.
 
I wonder if it would work to back everything up from the old site, except for the dbs, then do the dbs seperately.

Thanks for the suggestions!

Dale
 
Update.

I restored the rest of the site (unfortunately it has its own problems - the index.htm file won't load, only getting the 404 error :( )

I used the suggestions here to remove the 'latin1' bit, then tar'ed them back up and 'restored' them. The databases get created, but they are all empty.

I guess now the question is, should I just go and update mysql to the version he is using instead of messing around with this anymore?

The only concern I have about upgrading mysql, is that I might break something. Luckily there are only 2 sites currently using mysql and they are both mine. But one is a popular joomla based site that I can't afford to be messed up - thereby giving me more work to do.

Thanks for any suggestions.
 
You must log in or register to reply here.
Back
Top