Beware of 0 Values in Primary Keys

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.