Recently I was working on setting up a new local development environment at work and stumbled across a MySQL error when importing legacy data in to the remake of an old app. This error lead me to a scary revelation about the defaults of MySQL version 5.6 and earlier.

I've been working recently with Ansible to set up a local dev environment for our developers on OSX using Homebrew. I had everything set up and working when I decided to do an import of legacy data for the specific portion of the app I was working on. Strangely, I ended up getting the following fatal error...

[Doctrine\DBAL\Driver\PDOException]
SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'myfield' at row 1

Well that's weird, I've never had that issue before with the exact same data. At this point my only thought is that this has to do with the version of MySQL that Homebrew installs (v5.7) versus the version I had been using (v5.5). I knew there was a possibility of incompatibility but I wasn't expecting it to be with basic INSERTS.

Luckily the Doctrine error also spit out the attempted query so I could see what it was trying to do. There was a pretty lengthy string being put in to the VARCHAR(255) mentioned in the error. So that made me wonder, What the heck was it doing with this data in other environments? I checked out the table in the old MySQL and saw that it had truncated the data. Well that's not good.

After finding this article and comparing the documentation for version 5.5 and version 5.7, I learned that in versions previous to 5.7, the default sql-mode config did not include STRICT_TRANS_TABLES or STRICT_ALL_TABLES which causes MySQL to error out on value length issues.

For the record, MySQL isn't totally silent if you don't have a strict sql-mode set. It does output a warning, but that would be written to the MySQL log file and not outputted as a message to the developer. So it might as well be silent.

The whole journey brought to light that we did not have the right field types for certain model properties. Some VARCHAR(255) fields should have been TEXT and certain INT fields should have been BIGINT. It's a good thing we caught this before this app was launched. I imagine this might be happening to other developers without them knowing about it.

The moral of the story is that you should make sure you have a strict sql-mode set if you are on a version of MySQL prior to 5.7. This can be set in the my.cnf file or as a command argument. See the documentation for details.

To set the SQL mode at server startup, use the --sql-mode="modes" option on the command line, or sql-mode="modes" in an option file such as my.cnf (Unix operating systems) or my.ini (Windows). modes is a list of different modes separated by commas. To clear the SQL mode explicitly, set it to an empty string using --sql-mode="" on the command line, or sql-mode="" in an option file.