Having Trouble Backing Up / Restoring DB

Jus · 13th April 2005, 02:23 PM

Currentley trying to take a backup of a database and restore it to a new database to allow me to use it for a dev version of one of my sites, but am having a few problems.

The backup of the current database has no problems executing via SSH, and I don't get any errors at all and it seems fine, but when I seem to try to restore it to a new DB, I get this.

ERROR 1064 at line 11: 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 'from varchar(255) NOT NULL default '', fromemail varchar(255)

But the database itself that I took the backup of is running fine on a live site so it's not anything serious, but I simply can't restore the DB no matter how hard I try.

I've tried the repair function in phpmyadmin as well to see if it makes any difference, but unfortunatley it does not seem to make the slightest bit at all.

Is there any other way I can copy this database that will avoid this error? Or anybody got an idea on how to fix it?

DigiAl · 13th April 2005, 03:44 PM

Don't know if it matters, but have you tried exporting the DB from phpmyadmin?

gossi · 13th April 2005, 03:47 PM

Could you paste lines 10-13 from the exported file, Jus (minus any confidential info)?

Jus · 13th April 2005, 04:07 PM

Is there a way I can do that without download the whole database, as it's rather big...

gossi · 13th April 2005, 04:16 PM

If you SSH in and run 'more DBNAME.db' it'll bring it up on screen.

Jus · 13th April 2005, 04:22 PM

Quote:

-- MySQL dump 9.10
--
-- Host: localhost Database: xxxxx
-- ------------------------------------------------------
-- Server version 4.0.18-Max

--
-- Table structure for table `ListMess_email_messages`
--

CREATE TABLE ListMess_email_messages (
message_id int(11) NOT NULL auto_increment,
template_id int(11) NOT NULL default '0',
date bigint(64) NOT NULL default '0',
title varchar(255) NOT NULL default '',
from varchar(255) NOT NULL default '',
fromemail varchar(255) NOT NULL default '',
replyemail varchar(255) NOT NULL default '',
erroremail varchar(255) NOT NULL default '',
priority int(1) NOT NULL default '0',
subject varchar(255) NOT NULL default '',
text longtext NOT NULL

See anything? I think I know which tables are causing the problems, is there any way I can drop selected ones from the exported .sql file using SSH then reimport it?

simbart · 13th April 2005, 06:07 PM

i was having the same problem backing up and restoring a mambo database. adding a -Q option to mysqldump fixed the problem. i.e:

mysqldump --add-drop-table -Q -uUSERNAME -pPASSWORD DBNAME > backup.sql

this adds back quotes around around field names. The restore then went without a hitch.

Jus · 13th April 2005, 06:17 PM

Tried that, still same problem

void · 13th April 2005, 09:49 PM

Quote:

Originally Posted by Jus

Tried that, still same problem

did you check new sql file?
can you see if 'drop table' is added ? and if names within quotes now?

`date`
`from`
`text`

(all 3 belong to reserved mysql words)

Jus · 14th April 2005, 11:16 AM

But won't that drop tables from the live database when doing a backup, I don't want to do that, i just want to make an exact replica of the current DB into another DB without losing anything.

gossi · 14th April 2005, 11:27 AM

I have to admit, I'm not quite sure on this one myself. It does appear 'date' and such are reserved words and need quotes around them.

Jus · 14th April 2005, 11:30 AM

I've done the command, just without the drop bit and it makes no difference, I'm very reluctant to take any risks as the backup I want to take from a database is from a very important live database, I can't risk screwing it up.

simbart · 14th April 2005, 12:06 PM

the drop bit, just adds a drop table command for each table to the script. So if you restored to a database that already had the tables it would drop them before, recreating them. It won't affect the live database as long you don't restore to that database. (but you don't need to use it if your restoring to a new/clean database)

are you getting the error in exactly the same place with the quotes version of the file? the problem should be that you have mysql reserved words as column names, but the quotes should have fixed that

the db that your restroing to is this on a uh server or else where.

Andy · 14th April 2005, 01:33 PM

When this sort of problem occurs it usually relates to the data not being quoted correctly in the backup file.

If your using mysqldump to do the backups use the "--quote-names" option (See http://dev.mysql.com/doc/mysql/en/mysqldump.html ) . If your using phpmyadmin i think it offers a similar option. I vaguely remember having come across this problem myself when restoring a database to the test mysql server on my pc.

Andy

Jus · 15th April 2005, 03:36 PM

"are you getting the error in exactly the same place with the quotes version of the file?"

Yep, Afraid so, any more ideas, I tried what Andy said, still same problem, not looking good, I may just download the almost 100MB database then trim the tables out that are causing the problem from the dump from phpmyadmin then upload it then SSH.

It's long and tedious but it'll work.

TygerTyger · 18th January 2010, 04:53 PM

I'm having similar trouble.

Code:

-bash: /var/www/html/mydb.sql: line 28: syntax error near unexpected token `('
-bash: /var/www/html/mydb.sql: line 28: `CREATE TABLE `mytable` ('

The database was exported from phpMyAdmin with backquotes enabled and I'm using SSH to restore it. The file is 350Mb, I can't read it much less edit it myself. Does anyone know what has caused this and whether I can do anything to fix the restoration?

mark.willis · 18th January 2010, 09:25 PM

the ` will be getting executed in the bash terminal ... how are you running the command?

TygerTyger · 18th January 2010, 09:42 PM

Quite possibly, I have no idea how to use SSH. Uploaded the db to root, then something like:

Code:

mysql -u username -p password example_com_db
\. /var/www/html/mydb.sql

I'm not getting anywhere on Google finding a way to escape backticks within a file without editing the file itself.

Lost_Password · 19th January 2010, 02:14 PM

If your testing website is on the same server as the original website, you can run both sites on the same database without moving anything anywhere.

If that's not what you want but the set up is the same, you could duplicate the tables you're working on with slightly different names e.g. events might become events_test etc.

If that's still no good, you can put together your own php script which outputs the contents of the database to screen. You'd do this in such a way that it creates its own SQL INSERT statements. I have had to resort to this method in the past, it's a last resort but works a treat.

D.

mark.willis · 19th January 2010, 03:50 PM

try this...

Code:

mysql -u username -pPassword example_com_db < /var/www/html/mydb.sql

What you are doing is telling bash to execute the sql file instead of sending it to the mysql client