MariaDB 10.2 on cPanel and missing default value

So latest cPanel 66.x offers upgrade for MariaDB to version 10.2, which sounds great if you want to offer latest to your customers. But one thing has been quickly discovered….

After the upgrade several clients started to complain, that their systems partly stopped working. Partly – that is because data is there, but some insert and update queries were throwing errors – and as we discovered – for a reason: some fields in affected tables did not have default value set, NULL values were not allowed and queries were missing those fields.

Exact SQL error message was:

Field 'xxx' doesn't have a default value

So technically everything sounds quite reasonable – to be strict – it should not work. But how often can you explain that to customers, that their systems are… You know, what I mean. 🙂

Anyway. The solution.

Solution 1: set default values in each table structure.

Solution 2: edit /etc/my.cnf and add one line

sql-mode="NO_ENGINE_SUBSTITUTION"

Remember to restart MySQL server after. 🙂