Which, if any, MySQL / InnoDB (strict) modes to use?

Question

I am using Percona Server 5.6 with Magento CE 1.8. I originally used the percona tool to generate the my.cnf file that I am currently using. It's been in play for 3-4 weeks now, and I haven't noticed any issues because of these settings as far as I can tell. However, today, I was trying to consolidate a barely used database server which just held the data for a single wordpress install. Suddenly, things that were working fine, were not working at all, and I was racking my brain until I finally realized that the set "sql_mode" values in the my.cnf were to blame. Frankly, I don't know a ton about this setting, though I am quickly learning. For a quick workaround, I have disabled all sql modes for the wordpress sessions, but it got me thinking - which of these modes, if any, do I need for Magento? I don't want to disable it all and cause issues if I need some of these, nor do I want to leave any in play if they in fact are causing problems.

Can someone more informed than I tell me which I need to keep and which I should get rid of? I am more looking for which ones are detrimental or necessary to the daily operation of magento. If one happens to be, say, user preference, please note as such. Unfortunately I have not been able to find much information about these options and Magento specifically with my Google-Fu. I can take the time to research these if you don't feel like writing out an explanation, so don't feel obligated to explain every little detail.

[mysqld]
...
sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
...
innodb-strict-mode = 1

Answer 1

I can't say for Magento 1.8, but when I on a development box enable the general query log of mysql and do some database operation via the Magento API like

// Init Magento
require '/path/to/magento/app/Mage.php';
Mage::app('default');
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
...

and then accessing models for example, the following I can see from the log for the given operation:

141015 13:00:53    58 Connect   root@localhost on dbimport
                   58 Query     SET SQL_MODE=''
                   58 Query     SET NAMES utf8
                   58 Query     select website_id, code, name from mage_core_website
...

As this shows, Magento itself is setting the SQL Mode for the connection with

SET SQL_MODE=''

this overrides any setting that is in the mysql (or percona) configuration file.

I assume this has been done for portability. From personal preference I can say not having strict mode can cause errors later (INSERT ... ON DUPLICATE KEY UPDATE) scenarios where creating (INSERT) a row with wrong entries gets passed through but the modification (UPDATE) will fail with a Integrity constraint violation. Depending on if/how you're using the API those errors can be tedious to debug especially if you don't know the code that creates it. But this is more with Magento PHP API using code on our end and having a development box where you can enable the general query log occasionally helps a lot.

So this is probably not the answer you were looking for and it does only answer a specific part of your problem.

But Magento setting the SQL mode on it's own also explains why you haven't got much traction on your question yet: As nobody needs to care about the SQL mode (or everyone's my.cnf just works™) not much information is available.

If you're interested for the meaning of the many values for the SQL_MODE setting, you should find this in the product documentation by the vendor of your database server.