Arseny Zinchenko
Posted on August 8, 2019
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:
MariaDB [dbname]> ALTER TABLE table_name MODIFY column_name LONGTEXT;
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:
MariaDB [(none)]> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
And on the new one, with the 10.2:
MariaDB [dbname]> select @@sql_mode;
+-------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
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:
MariaDB [(none)]> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode |
+--------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
Done.
Similar posts
Posted on August 8, 2019
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.