DEV Community

Arseny Zinchenko
Arseny Zinchenko

Posted on • Originally published at rtfm.co.ua on

AWS RDS: “SQLSTATE[22001] – Data too long for column” using MariaDB 10.2

#aws #rds #mariadb #myssql

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;
Enter fullscreen mode Exit fullscreen mode

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 |
+------------+
|            |
+------------+
Enter fullscreen mode Exit fullscreen 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 |
+-------------------------------------------------------------------------------------------+
Enter fullscreen mode Exit fullscreen mode

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 |
+--------------------------------------------+
Enter fullscreen mode Exit fullscreen mode

Done.

Similar posts

Top comments (0)