Backup restore 1064 & 1067 SQL syntax errors - need help

[steffanih]

Hello, I am restoring a site after an SSL certificate was added to our site and there are many database syntax errors I am fishing through to get my database back up. This is the one I am having a hard time finding a resolution for:

 

This is the portion of my code:

 

drop table if exists ps_customized_data;

create table ps_customized_data (

id_customization int(10) unsigned not null ,

type tinyint(1) not null ,

index int(3) not null ,

value varchar(255) not null ,

PRIMARY KEY (id_customization, type, index)

);

 

 

and this is the error message I receive when running the backup:

 

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 'int(3) not null ,

value varchar(255) not null ,

PRIMARY KEY (id_customizat' at line 4

Edited July 19, 2010 by steffanih

[steffanih]

It turns out this did the trick:

 

drop table if exists ps_customization_data;

create table ps_customization_data (

id_customization_data int(10) unsigned not null ,

type tinyint(1) not null ,

id_index int(3) not null ,

id_value varchar(255) not null ,

PRIMARY KEY (id_customization_data, type, id_index)

);

 

so if you're stuck on the same thing I am, here ya go. I ended up having to query in some of the original osCommerce tables to get everything back up and running correctly. My database was only partially backing up. I am almost good to go and may needs some more help on SSL implementation issues.

[Jan Zonjee]

#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 'int(3) not null ,

value varchar(255) not null ,

PRIMARY KEY (id_customizat' at line 4

The basic problem is that index is a reserved word in MySQL, basically meaning it shouldn't be used as a column name. You can use it but then all the queries need to put backticks around the "index" name. Simplest way is to avoid it, like you do now to change the name from index to id_index.