Database Restore Error

[mkinsel]

Hi all,

 

I'm trying to move a store from one server to another (old server: GoDaddy; new server: Hostgator). I backed up the database (through the Database Backup interface in the osCommerce admin portal), and copied the backup file to the new server. After installing osCommerce on the new server (with Fantastico), setting it up, etc., I tried to "restore" the old database backup file to the new database (again, via the admin's Database Backup Manager interface). I located the backup file, clicked "Restore", and after a few seconds it gave me this error:

 

1064 - 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 'repeat tinyint(4) default '0' not null , PRIMARY KEY (products_attributes_id),' at line 8

create table products_attributes ( products_attributes_id int(11) not null auto_increment, products_id int(11) default '0' not null , options_id int(11) default '0' not null , options_values_id int(11) default '0' not null , options_values_price decimal(15,4) default '0.0000' not null , price_prefix char(1) not null , repeat tinyint(4) default '0' not null , PRIMARY KEY (products_attributes_id), KEY idx_products_attributes_products_id (products_id) )

[TEP STOP]

1146 - Table 'mkinsel_osc1.sessions' doesn't exist

select count(*) as total from sessions where sesskey = '609abf09ac6ca5fbce6fb872eb137d22'

[TEP STOP]

 

This error came on a page with this address:

http://babybellasdesigns.com/shop/admin/backup.php?file=db_shop2008-20090828144710.sql&action=restorenow

 

Just FYI, in the admin's Database Backup Manager interface, it gave me the option to click "Restore", or use the following code in a mysql client:

mysql -hlocalhost -umkinsel_osc1 -p mkinsel_osc1 < /home/mkinsel/public_html/babybella sdesigns/shop/admin/backups/db_shop 2008-20090828144710.sql

 

 

My new database is now broken, thanks to this problem with the restoration. If I try to visit any page in the store, I get this:

 

1146 - Table 'mkinsel_osc1.sessions' doesn't exist

select value from sessions where sesskey = 'b851ed9a40e6d45516e5ed121421477b' and expiry > '1254531104'

[TEP STOP]

 

 

Could anyone help me with this??? I really don't want to have lost all my product information!!! Thank you so much!!!!!!

[spooks]

try using the restore option within cpanel

[mkinsel]

try using the restore option within cpanel

 

Is that an option specific to osCommerce? Unfortunately, I don't have access to the old server, the old database, or anything else from GoDaddy. All I have are the files I copied over via FTP, and this database backup file.

[spooks]

Is that an option specific to osCommerce? Unfortunately, I don't have access to the old server, the old database, or anything else from GoDaddy. All I have are the files I copied over via FTP, and this database backup file.

 

 

no nothing to do with osc, your cpanel is your hosting control panel, emials, ftp, domain utils, dbase utils etc etc.

 

your host will have given u login details with your hosting package

 

 

ps its where u went to install via Fantastico

[mkinsel]

no nothing to do with osc, your cpanel is your hosting control panel, emials, ftp, domain utils, dbase utils etc etc.

 

your host will have given u login details with your hosting package

 

 

ps its where u went to install via Fantastico

 

Oh yes, I know what cpanel is. But since I don't have access to the old server / database, I don't think I could use cpanel on the new server to restore anything....

[FIMBLE]

why cant you use the database that back up that you are trying to restore via admin?

Download it to your desktop, then do as Sam suggests add it via phpmyadmin

[mkinsel]

why cant you use the database that back up that you are trying to restore via admin?

Download it to your desktop, then do as Sam suggests add it via phpmyadmin

 

Ah, okay, so will that database backup (which was created through the osc admin panel) be in correct format for restoration via phpmyadmin? Here's a screenshot of the Import panel on phpmyadmin, and I have a few questions regarding it.

 

 

1. Am I in the right place? Should I be using "Import" for this database?

2. What character set do I use for the osCommerce backup file?

3. What "SQL compatibility mode" do I use? (NONE, ANSI, DB2, MAXDB, MYSQL323, MYSQL40, MSSQL, ORACLE, or TRADITIONAL)

 

Thanks in advance!!!

[mkinsel]

Any ideas?

 

TIA :)

[spooks]

The backup you made in admin should be fine fore use with cpanel restore or phpmyadmin restore.

 

Why havent u tried already, the worst u would get it file wrong format?

[mkinsel]

I got the same error through phpmyadmin, too.

 

SQL query:

CREATE TABLE products_attributes(

products_attributes_id INT( 11 ) NOT NULL AUTO_INCREMENT ,
products_id INT( 11 ) DEFAULT  '0' NOT NULL ,
options_id INT( 11 ) DEFAULT  '0' NOT NULL ,
options_values_id INT( 11 ) DEFAULT  '0' NOT NULL ,
options_values_price DECIMAL( 15, 4 ) DEFAULT  '0.0000' NOT NULL ,
price_prefixCHAR( 1 ) NOT NULL ,
repeat TINYINT( 4 ) DEFAULT  '0' NOT NULL ,
PRIMARY KEY ( products_attributes_id ) ,
KEY idx_products_attributes_products_id( products_id )
);

MySQL said: 

#1064 - 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 'repeat tinyint(4) default '0' not null ,
 PRIMARY KEY (products_attributes_id),' at line 8

 

Apparently it's having trouble with this line in my database backup file:

price_prefix char(1) not null ,

 

Suggestions???

[mkinsel]

Oh, wait. Actually it may be a problem with the line right after that:

repeat tinyint(4) default '0' not null ,

 

There's one other instance of "tinyint" in the file; it comes on the products table as:

products_status tinyint(1) default '0' not null ,

 

However, there are no other instances of "repeat", used as it is in that line. Is that valid SQL syntax? Could that be the problem?

[Jan Zonjee]

However, there are no other instances of "repeat", used as it is in that line. Is that valid SQL syntax? Could that be the problem?

repeat is a reserved word in MySQL version 5. Apparently not in version 4 and 3.

[mkinsel]

Ah, okay, so what should I do about this?

[spooks]

Ah, okay, so what should I do about this?

 

 

Delete that entire line, or change the name, you must have added that field sometime, perhaps with a contrib? ask in support thread for it for fix.

 

You will need to look at the data insert entries for that too.

[mkinsel]

I can't just delete that line, because, as you said, there are data insert entries using "repeat". Obviously, it is of some importance to osCommerce. I can't rename it, or remove all of these entries, because I'll loose data, and in the future, osCommerce is still going to be saving data using "repeat", apparently. So doesn't this appear to be a fault in osCommerce coding? It hasn't kept up with 5.x MySQL updates?

[spooks]

I can't just delete that line, because, as you said, there are data insert entries using "repeat". Obviously, it is of some importance to osCommerce. I can't rename it, or remove all of these entries, because I'll loose data, and in the future, osCommerce is still going to be saving data using "repeat", apparently. So doesn't this appear to be a fault in osCommerce coding? It hasn't kept up with 5.x MySQL updates?

 

 

As I said, u have added that field, it does not exist in the rc2a distrib!!

 

Remane & alter date insert to suit, then alter your modified code to reflect same.