MySQL: ERROR1364 fix

The painful realization of why people use containers.

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 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:


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';

neoan @neoan

504 readers
linux mysql
On topic

Install PHP 7.4 on Ubuntu

Finally PHP 7.4 is out! You have read about the new features, you have followed externals, you have gathered ideas on how new capabilities will save time. Let's get it running.

Static content pages - still the fastest web-experience

Tutorial: How to utilize to generate static content for your website.

Scaffolding REST APIs with JWT authentication

Ever had the need for your own backend while developing your web-app?

Git: globally change GitHub-remotes from git@ to https

Does your IDE or composer set remote repositories to ssh rather than https? Or are repositories you are using set up that way? You are not alone. Let's fix it once and for all!

VueJS & neoan3: a love story.

Setting up neoan3 to play nice with Vue isn't hard. Here is how the two frameworks are combined to support fast, dynamic and rapid development.

Install PHP 8 on Ubuntu

PHP8 has finally been released and it is FAAAAST! Time to update.

SEO strategies for

How to get your content listed where you want it to. PHP SDK

Create your own blog.

How to Build an Express App in Node That Reads From a Headless CMS

A headless CMS lets you design your own front-end, sometimes your own back-end. Let's set up a small application to get us started. Beginner friendly. Approx. 20 min. to reproduce / follow along

MySQL in PHP - how to deal with databases

How I handle MYSQL database transactions in PHP

Cross publishing to

How to publish your content to from - hopefully

How to install global npm packages without sudo on Ubuntu

Running npm on a server can be painful. Privileges are there for a reason, and so is sudo. Running npm with sudo is not the solution.

Transformer - falling in love with PHP's magic methods all over again

PHP's magic functions have been around for a long time. But there were always good reasons to avoid them. Transformer is a practical and reliable way to make use of the most common operations we program: CRUD operations.

What is composer?

Composer has become PHP's package & dependency manager. Why you should use it.