MySQL on Windows: A lesson learned about STRICT_TRANS_TABLES & InnoDB

[jarednichols]

So, really fresh personal experience here. If you're running a setup on Windows, your MySQL my.ini file likely has a statement like

default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

If you're doing various database dumps from Windows and into other environments (like Linux in my case for my test area) that STRICT_TRANS_TABLES will absolutely fuck you up. Windows also sets InnoDB as the default engine. I'm not sure if other platforms use that by default or not.

If you move your database from Windows to Linux (Ubuntu 12.04 LTS in particular) make sure you've got the following lines in your /etc/mysql/my.cnf file and specifically do NOT include the STRICT_TRANS_TABLES setting:

[mysqld]
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
default-storage-engine=innodb

Hopefully this'll help someone.

[jarednichols]

Consequently, if you're running Windows-based JSSes (as I suspect many folks are) you may want to see if you're currently running STRICT_TRANS_TABLES because it can cause All Sorts Of Badness™. Essentially, if something is 121 characters meant for a 120 character field, it won't truncate the last character but rather not enter anything into the field. So yeah, bad stuff.