We have a PHP-application with the AWS RDS MariaDB as a backend.
On the previously used 10.0 version all was good, but right after we upgraded to the MariaDB 10.2 – got errors during tests:
PDOException: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column ‘name’ at row 1 in /data/projects/projectname/vendor/yiisoft/yii2/db/Command.php:1290
The first solution here could be just by changing the column’s type from the VARCHAR to the LONGTEXT, like that:
[simterm]
MariaDB [dbname]> ALTER TABLE table_name MODIFY column_name LONGTEXT;
[/simterm]
But in this current case, this will be not the right way.
The solution
Check sql_mode on the old MariaDB RDS with the 10.0:
[simterm]
MariaDB [(none)]> select @@sql_mode; +------------+ | @@sql_mode | +------------+ | | +------------+
[/simterm]
And on the new one, with the 10.2:
[simterm]
MariaDB [dbname]> select @@sql_mode; +-------------------------------------------------------------------------------------------+ | @@sql_mode | +-------------------------------------------------------------------------------------------+ | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-------------------------------------------------------------------------------------------+
[/simterm]
And take a look at the Parameters group which is attached to the new RDS:
Here is what we need here – the STRICT_TRANS_TABLES:
If a value could not be inserted as given into a transactional table, abort the statement.
Now, need to overwrite the default “”STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION“:” value to the NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION:
Re-connect to the MySQL console, and check again:
[simterm]
MariaDB [(none)]> select @@sql_mode; +--------------------------------------------+ | @@sql_mode | +--------------------------------------------+ | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +--------------------------------------------+
[/simterm]
Done.
