MySQL sql-mode breaks PHP application

Guido van Loon

Tuesday, February 27. 2007

MySQL sql-mode breaks PHP application

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.

Posted by Guido van Loon in MySQL, PHP, SQL Comments: (10) Trackbacks: (0)
Defined tags for this entry: , ,
Related entries by tags:
PL/SQL Developer substitution variables

Trackbacks
Trackback specific URI for this entry

No Trackbacks

Comments
Display comments as (Linear | Threaded)

#1 - Ryan Behrman said:
2007-04-05 08:10 - (Reply)

Thanks for this tip. I followed the thread on the osCommerce forum which brought me to your blog. This indeed solves the 1264 errors (inserting an empty string into an integer field in MYSQL5), however the 1366 error on inserting an empty products_weight in the catalogue still remains. Just thought I'd let you know as your posting on the osCommerce forum says to look here for the fix.

#1.1 - Guido said:
2007-04-05 10:18 - (Reply)

Ryan, I've tried to reproduce the 1366 error you're describing but all my attempts are unsuccessful so far. All MySQL documentation about the 1366 error points in the direction of strict mode. Be sure you have the sql-mode configuration string in the my.ini file set to the following: sql-mode="NO_AUTO_USER,NO_ENGINE_SUBSTITUTION". If everything checks out, perhaps there is something else that's causing the 1366 error for you.

#2 - Ryan Behrman said:
2007-04-05 11:20 - (Reply)

Hi Guido, I've checked my my.ini and the line is now: sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" Yet the error I get is: 1366 - Incorrect decimal value: '' for column 'products_weight' at row 1 insert into products (products_quantity, products_model, products_price, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id, products_date_added) values ('1', '', '6', null, '', '1', '1', '', now()) [TEP STOP]

#3 - Ryan Behrman said:
2007-04-05 12:48 - (Reply)

It works now. MySQL just needed a restart! Thanks for this!

#3.1 - Guido van Loon 2007-04-05 13:03 - (Reply)

You're welcome!

#4 - rajeev 2008-06-09 05:56 - (Reply)

me using windows server with php5.i got an error when i m running the query. 1366 - Incorrect integer value: '' for column 'manufacturers_id' at row 1 Pls help me... Thnx in advance

#4.1 - Guido said:
2008-06-09 07:42 - (Reply)

It sounds like you have the same problem as I did. Did you try to edit the my.ini file and remove the STRICT_TRANS_TABLES option like I descibed? You should be able to find the my.ini file under the installation directory of MySql. Remove STRICT_TRANS_TABLES including the comma from the following line you find in the my.ini file: sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" Don't forget to restart mySql after saving the my.ini file.

#5 - G 2008-07-13 02:19 - (Reply)

Thank you , it solved my issue

#5.1 - Guido van Loon 2008-07-13 12:04 - (Reply)

It's always good to have helped someone. You're welcome!

#6 - kenji 2009-03-06 11:46 - (Reply)

thx alot sir.


Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA