Categories


Archives


Recent Posts


Categories


Beware of 0 Values in Primary Keys

astorm

Frustrated by Magento? Then you’ll love Commerce Bug, the must have debugging extension for anyone using Magento. Whether you’re just starting out or you’re a seasoned pro, Commerce Bug will save you and your team hours everyday. Grab a copy and start working with Magento instead of against it.

No Frills Magento Layout is the only Magento front end book you'll ever need. Get your copy today!

In a stock Magento system, there’s several tables where a row will have an ID field of 0.

mysql> select * from core_storeG:
*************************** 1. row ***************************
  store_id: 0
      code: admin
website_id: 0
  group_id: 0
      name: Admin
sort_order: 0
 is_active: 1
*************************** 2. row ***************************
  store_id: 1
      code: default
website_id: 1
  group_id: 1
      name: Default Store View
sort_order: 0
 is_active: 1

In turn, there are times where the Magento application will assume these 0 ID rows exist. For example, a store ID of 0 is always interpreted as the admin store object.

In day to day operations this is no big deal — however MySQL has a bit of default behavior that might lead to some weird problems. Specifically, if you use 0 in an INSERT for a primary key, MySQL interprets that as a request to generate a new key.

Again, day to day, there won’t be any problems running Magento with these rows, since the 0 ID already exists. However, if you need to move or upgrade the data you might run into a problem. Consider an OUTFILE export of the above table.

mysql> select * from core_store INTO OUTFILE '/tmp/test.sql';

$ cat /tmp/test.sql 
0   admin   0   0   Admin   0   1
1   default 1   1   Default Store View  0   1

The OUTFILE command correctly exports the ID of 0. However, if you perform an INFILE import, or create INSERTs manually from this data, the 0 will INSERT as a PRIMARY KEY request, meaning the admin store will no longer have an ID of 0, meaning Magento will break.

You can fix this during import by running the following

SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO';

The sql_mode MySQL variable tells MySQL if it should interpret 0 INSERTs as a true 0, not a PRIMARY KEY generation request.

One sharp stick in the eye for anyone who says MySQL’s lax data integrity doesn’t cause real world tradeoffs.

Copyright © Alan Storm 1975 – 2018 All Rights Reserved

Originally Posted: 13th July 2014