[ic] Converting from gdbm to MySQL - unable to create test table: create table products

Jonathan Clark interchange-users@icdevgroup.org
Tue Sep 10 19:11:01 2002


> I followed the steps below to convert from gdbm to MySQL...
> 1) Create a MySQL user.
> 2) Create an empty MySQL database with and set the permissions so that new
> user can read/write/create/etc. any tables that are created.
> 3) Edit site's products/variable.txt file and set the following
> (keeping the
> tab-separated format of the file intact):
> MYSQL 1 Database
> SQLDSN dbi:mysql:dbname Database
> SQLUSER dbusername Database
> SQLPASS dbuserpass Database
> 4) Remove any .sql files from the products directory
> 5) Restart Interchange
> ++++++++
> I first tried the steps specified above on a newly created catalog and it
> worked like a charm... then I tried it on a 10000 item production catalog
> and got a "Reconfiguration may have timed out." message when applying
> changes.
>
> The failed mysql conversion seems to have worked on all the
> production data,
> createing every table EXCEPT the products table...
>
> The error logs report (ERROR MESSAGE from Interchange Logs)
>
> - - - [10/September/2002:12:21:45 -0700] - - unable to create test table:
> create table products (
> sku char(64) NOT NULL PRIMARY KEY,
> description VARCHAR(128) NOT NULL,
> title VARCHAR(128) DEFAULT '' NOT NULL,
> template_page VARCHAR(64),
> comment TEXT,
> thumb VARCHAR(128),
> image VARCHAR(64),
> price DECIMAL(12,2) NOT NULL,
> wholesale char(128),
> prod_group VARCHAR(64) NOT NULL DEFAULT '',
> category VARCHAR(64) NOT NULL DEFAULT '',
> tax_category char(128),
> non-taxable char(128),
> weight VARCHAR(12) DEFAULT '0' NOT NULL,
> size VARCHAR(96),
> color VARCHAR(96),
> gift_cert VARCHAR(3),
> related text,
> featured VARCHAR(32),
> download char(128),
> dl_type char(128),
> dl_location char(128),
> inactive char(128),
> url char(128),
> sale_price char(128),
> image_large char(128)
> )
>
>
> Error: You have an error in your SQL syntax near '-taxable char(128),
> weight VARCHAR(12) DEFAULT '0' NOT NULL,
> size VARCHAR(96),
> c' at line 14
> - - - [10/September/2002:12:21:45 -0700] - - table 'products' failed: No
> database object for table: products
>
> Probable mismatch of Database directive to database type,
> for example calling DBI without proper modules or database
> access.
>
> <End Error Text>
>
> SQL STATEMENTS (from /catalogs/motherbo/dbconf/mysql/products.mysql in the
> Interchange Distribution 4.8.6 in use) FOLLOW:
>
> Database products products.txt __SQLDSN__
> ifdef SQLUSER
> Database products USER __SQLUSER__
> endif
> ifdef SQLPASS
> Database products PASS __SQLPASS__
> endif
> Database products KEY sku
> Database products COLUMN_DEF "sku=char(64) NOT NULL PRIMARY KEY"
> Database products COLUMN_DEF "description=VARCHAR(128) NOT NULL"
> Database products COLUMN_DEF "title=VARCHAR(128) DEFAULT '' NOT NULL"
> Database products INDEX title
> Database products COLUMN_DEF "template_page=VARCHAR(64)"
> Database products COLUMN_DEF "comment=TEXT"
> Database products COLUMN_DEF "thumb=VARCHAR(128)"
> Database products COLUMN_DEF "image=VARCHAR(64)"
> Database products COLUMN_DEF "price=DECIMAL(12,2) NOT NULL"
> Database products INDEX price
> Database products COLUMN_DEF "category=VARCHAR(64) NOT NULL DEFAULT ''"
> Database products INDEX category
> Database products COLUMN_DEF "prod_group=VARCHAR(64) NOT NULL DEFAULT ''"
> Database products INDEX prod_group
> Database products COLUMN_DEF "nontaxable=CHAR(3)"
> Database products COLUMN_DEF "weight=VARCHAR(12) DEFAULT '0' NOT NULL"
> Database products COLUMN_DEF "size=VARCHAR(96)"
> Database products COLUMN_DEF "color=VARCHAR(96)"
> Database products COLUMN_DEF "gift_cert=VARCHAR(3)"
> Database products COLUMN_DEF "related=text"
> Database products COLUMN_DEF "featured=VARCHAR(32)"
> Database products NUMERIC price
>
> Any help on this is greatly appreciated!

check the field names at the top of your products text file - your
'nontaxable' field is called 'non-taxable'.. and its choking on the illegal
char '-'.

The field names for the CREATE TABLE code come from this line, using the
products.mysql file to define the data types etc.

Jonathan
Webmaint.

ps. good thorough email :-)