MySQL Strict Mode Issues

Summary

Some hosts or servers may run MySQL with strict mode enabled which will cause query failures in Shopp.

Cause

MySQL strict mode enforces very explicit restrictions on the data and format of data that goes into the table columns. Some scenarios in Shopp will not format data because there is no data to format. In most cases, where strict mode is not in use, this is no issue because MySQL copes with empty data.

When strict mode is enabled, it can cause query failures most notable during checkout. Setting up Shopp to use PayPal Standard, for instance, which has no payment card details captured during checkout cause the order creation query to pass an empty card expiration date. With strict mode enabled, this query will fail causing an error during checkout preventing any order from being created (even though PayPal will accept the payment).

Errors

DB: Query failed: Incorrect date value: '' for column 'cardexpires' at row 1 - DB Query: INSERT wp_shopp_purchase SET data='a:0:{}',id=NULL,customer='0',shipping='0',billing='0',currency='0',ip='192.168.1.124',firstname='John',lastname='Doe',email='j.doe@address.com',phone='555-555-5555',company='Acme Corp',card='',cardtype='PayPal',cardexpires='',cardholder='',address='1 N Main Street',xaddress='',city='San Jose',state='CA',country='US',postcode='95131',shipname='John Doe',shipaddress='1 N Main Street',shipxaddress='',shipcity='San Jose',shipstate='CA',shipcountry='US',shippostcode='95131',geocode='',promos='a:0:{}',subtotal='1',freight='0',tax='0',total='1',discount='0',fees='0',taxing='exclusive',txnid='55H18934EK5765051',txnstatus='purchase',gateway='PayPalStandard',paymethod='paypal',shipmethod='',shipoption='',status='0',secured='',created='2012-06-13 20:33:02',modified='2012-06-13 20:33:02'

Solution

The best way to solve this issue is to disable MySQL strict mode. This may not be available for every hosting provider. Some hosts may not allow the setting to be turned off. In this case, Shopp will not operate properly in the environment.

To turn off strict mode in MySQL, edit the my.ini or my.cnf file and look for the sql-mode setting:

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

Replace with:

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

Windows users can also simply uncheck the Enable Strict Mode option while going through the pages of the MySQL Server Instance Configuration Wizard.

If you do not have access to either of these configuration settings, you will need to contact your hosting provider and request that the setting be disabled.

See Also

MySQL Strict Mode Issues
  • 0.00 / 5 5

You must be logged in to post a comment.

© Ingenesis Limited. Shopp™ is a registered trademark of Ingenesis Limited.

Skip to toolbar