A customer wanted to migrate his company ERP software from a (never updated) three year old Amazon instance to a local physical server in his company office.
His software uses MySQL as its database engine.
The migration was really simple (I also put his ERP software code in a git repository in the new server, because apparently, nobody else has that code), I created the database, loaded the most recent backup, created the database user that the ERP will use, etc.
But when I started testing, almost nothing worked, and the log was clogged with this message:
Incorrect integer value: '' for column 'a_column_name' at row 1
In the old Amazon instance, the MySQL server version was 5.5 and the new server has MariaDB 10.2.16.
So, apparently, the MySQL server in the Amazon instance was configured in a kind of permissive way. I learned that MariaDB, by default, enables the “STRICT_TRANS_TABLES” mode. MySQL documentation states for this mode: “for transactional tables, an error occurs for invalid or missing values in a data-change statement…” (https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict).
The ERP software is trying to commit inserts or updates assigning a ‘’ value to an integer field. In the old Amazon instance, the MySQL server settings allowed this, transforming the ‘’ value into NULL or zero (if the field does not accept NULL).
Obviously I was not going to change the software, so I disabled the “STRICT_TRANS_TABLES” mode from the MariaDB server. But I did not want to mess around a lot with the settings so I logged into MariaDB with root (database root) and execute:
select @@sql_mode;
This query told me the enabled SQL modes. The result was this:
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
I only needed to disable STRICT_TRANS_TABLES mode. So I opened MariaDB configuration file (in this server was /etc/my.cnf).
Under the server settings section (mysqld) I override the default SQL modes like this:
[mysqld]
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
I only disabled the STRICT_TRANS_TABLES mode, leaving the other default modes enabled.
I restarted MariaDB and the ERP software now runs with no problem.
This took me some time to solve, because at the moment I did not know that MariaDB enables this mode by default. I hope someone finds post this useful.