Blog

I write on blua.blue, dev.to and groups.hyvor.com

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

image

MySQL: ERROR1364 fix

The painful realization of why people use containers.

image

Cyber wars: Defending your server

Maintaining your own server can be a thrill. High security standards can protect you from data leakage, injection attacks and DDoS attempts. But what about adaptive brute force?

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.

image

MySQL in PHP - how to deal with databases

How I handle MYSQL database transactions in PHP

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.

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.

image

Static content pages - still the fastest web-experience

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

image

blua.blue PHP SDK

Create your own blog.

image

dev.to plugin for headless CMS blua.blue (part 2)

A solution to supplying plugins to blua.blue

image

Cross publishing to dev.to

How to publish your content to dev.to from blua.blue - hopefully

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.

The Uselessness of Pre-Assessment

After almost two decades in the industry, new jobs will still ask you for "assessment tests". A little rant...

image

SEO strategies for blua.blue

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

image

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

image

Help us document neoan3

Over 4000 brave developers are exploring the framework on their own.

image

When politics kill innovation

How misunderstood diversity killed the PHP Central Europe Conference for good.