DEV Community

Arseny Zinchenko
Arseny Zinchenko

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

3

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

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)

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more