AWS RDS: “SQLSTATE[22001] – Data too long for column” using MariaDB 10.2
Tags: aws, rds, mariadb, myssql
Published: 2019-08-08 14:46:29

---
title: AWS RDS: “SQLSTATE[22001] – Data too long for column” using MariaDB 10.2
published: true
tags: AWS,RDS,MariaDB,MySSQL
canonical_url: https://rtfm.co.ua/en/aws-rds-sqlstate22001-data-too-long-for-column-using-mariadb-10-2/
---

![](https://rtfm.co.ua/wp-content/uploads/2014/11/aws-logo-square-02-e1417012834176-1.png)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:

[![](https://rtfm.co.ua/wp-content/uploads/2019/08/Screenshot_20190808_154619.png "AWS RDS: Data too long for column")](https://rtfm.co.ua/wp-content/uploads/2019/08/Screenshot_20190808_154619.png)

Here is what we need here – the [`STRICT_TRANS_TABLES`](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html):

> 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`:

[![](https://rtfm.co.ua/wp-content/uploads/2019/08/Screenshot_20190808_155313.png "AWS RDS: SQLSTATE[22001] - Data too long for column")](https://rtfm.co.ua/wp-content/uploads/2019/08/Screenshot_20190808_155313.png)

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

- <small>08/08/2019</small> [AWS RDS: SQLSTATE[22001] – Data too long for column в MariaDB 10.2](https://rtfm.co.ua/data-too-long-for-column/)
- <small>05/14/2019</small> [AWS: MariaDB RDS – kill: You are not owner of thread](https://rtfm.co.ua/en/aws-mariadb-rds-kill-you-are-not-owner-of-thread-2/)
- <small>12/13/2016</small> [AWS: RDS Aurora db.t2.medium vs t2.nano и MariaDB](https://rtfm.co.ua/aws-rds-aurora-db-t2-medium-vs-t2-nano-i-mariadb/)
- <small>09/03/2016</small> [AWS: миграция RTFM, часть #2: ручное создание инфраструктуры – AIM, S3, RDS и EBS](https://rtfm.co.ua/aws-migraciya-rtfm-chast-2-ruchnoe-sozdanie-infrastruktury-aim-s3-rds-i-ebs/)