Guido van Loon

I'm looking for an open source implementation of a stable, easy customizable, well supported ecommerce application like OsCommerce or CubeCart. To be able to try one or two of these apps for myself I installed a complete LAMP stack on a virtual windows XP image a while back.

I used MySQL 5 for that was the latest version at the time. Not aware of some of the changes in the default configuration, every ecommerce package that I tried to run gave me an error message at the point I tried to add something to the database. Be it a new category or item. OsCommerce as well as CubeCart could not insert anything into the database and gave me a message similar to this one:

1366: Incorrect decimal value: '' for column 'per_ship' at row 1

QUERY = INSERT INTO store_CubeCart_category (`cat_name`, `cat_father_id`, `cat_image`, `per_ship`, `item_ship`, `item_int_ship`, `per_int_ship`) VALUES ('test', '1', '', '', '', '', '')

The cause of this error can be traced back to the configuration of MySQL 5 and a new 'feature' called STRICT_TRANS_TABLES that is turned on by default. At least for the windows version of MySQL. The following line in the configuration file my.ini was causing these PHP applications to fail:

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

The MySQL documentation gives the following information about STRICT_TRANS_TABLES: If a value could not be inserted as given into a transactional table, abort the statement. For a non-transactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement. More detail is given later in this section. (Implemented in MySQL 5.0.2)

The solution to the errors is simple. Remove the STRICT_TRANS_TABLES from the sql-mode setting and everything runs smoothly.