MySQL: ERROR1364 fix

It's all about the money

Saving $s has become a hard fact for many individuals and companies these days. In this process I started questioning whether I really need an AWS RDS database instance of whether I run my mysql installation on my EC2. After a rather simple switch, I set up a cron-job for dumps and planned on storing them in a separate S3 bucket (you never know, right?).

The complete process took me less than two hours and I started to rethink some DevOps concepts after being very happy with the benchmarks & performance of my brave EC2 micro instance.

Failing inserts

However, something was off. Did people stop using the POC installation of blua.blue? Since I am a big proponent of privacy, monitoring is limited to a minimum. But API traffic seemed normal.

I tried logging in, changing settings, signing up, commenting, viewing - all seemed working. Until I failed to create a new article.

Unusual debugging

I was unable to reproduce the error locally and none of the error logs on my server showed anything. Hm, how was my error reporting set up again? Sometimes the bold methods are the right choice: I logged every SQL-transaction result and found the following error-number on some transactions: 1364

Field doesn't have a default value

A quick google search revealed that apparently some of my tables contain columns requiring a value. But didn't I use exactly the same schemata as previously used via RDS? Thankfully, I am not the first one running into this issue and it seemed I should check somewhere else then I expected: in my MySQL configuration. The fist thing I did was verifying that what I read online is indeed my problem:

mysql> SHOW VARIABLES LIKE 'sql_mode';

And sure enough, there it was: STRICT_TRANS_TABLES

What this variable does is setting the your MySQL to reject any empty (or most likely undefined) field or your query unless there is a defined default value.
I don't know about you, but I always lived quite happily with the fact that nullable fields will default to null. And minimizing constraints is definitely a plus on top of the laziness.
So how can I get rid of that behavior?

Changing the configuration

The easiest way is to override your mysql configuration. Depending on your setup, your will find various .cnf files that will offer possibilities. If you are not sure, simply create a new one in the conf-directory (likely /etc/mysql/conf.d/).

Here are the steps to take:

1. copy your existing mode-variable (see above SQL-query)
2. define your new sql_mode variable (under mysqld block)
3. restart the mysql service

If you created your own .cnf-file (e.g. custom.cnf ), it could now look like this:

[mysqld]
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

After restarting your service, you might want to check your setup again

$ sudo service mysql restart

$ mysql -u root -p
mysql> SHOW VARIABLES LIKE 'sql_mode';