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

Ron Phipps interchange-users@icdevgroup.org
Tue Sep 10 19:08:00 2002


> From: Charles Adams
> 
> 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!
> 

MySQL doesn't like the field name 'non-taxable'.  You have nontaxable
defined in your products.mysql, but for some reason it's trying to use
non-taxable.  Do you have a products/products.txt with a header that
says non-taxable?  If so change the header to nontaxable and try again.
Make sure you delete the products.sql.

Good luck,
-Ron