com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'column_name'

Question

I am using Java JDBC with MySQL and I get this error:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: 
Data too long for column 'column_name'

How do I remedy this error?

Answer 1

I faced the same issue in my Spring Boot Data JPA application when I was going to insert an image file in the database as bytes - it gave me the same error.

After many R&D I found a solution like below.

---

I added the following line in my application.properties file and it resolved the issue:

spring.datasource.url=jdbc:mysql://localhost:3306/conweb?sessionVariables=sql_mode='NO_ENGINE_SUBSTITUTION'&jdbcCompliantTruncation=false

Hope it will be helpful to someone.

Answer 2

How to reproduce this error in MySQL terminal

mysql> create table penguins(val CHAR(2));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into penguins values("AB");
Query OK, 1 row affected (0.00 sec)

mysql> insert into penguins values("ABC");
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'val' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

This is the error you are getting, however in the MySQL terminal shows it as a warning and still inserts your row, just silently truncates it.

The MySQL JDBC is not as forgiving, and spits a hard error and will not insert the row.

---

2 Solutions

Either Increase the capacity of the data type you are inserting into, or decrease the size of the content you are placing in there.

Legend to increase the size of your column:

If you are using   then use this
-----------------  --------------
smallint           int
int                long
TEXT               LONGTEXT
LONGTEXT           CLOB
CLOB               Store the content to disk, and save the filename in the db.

Answer 3

This may seem pretty obvious, and it is. You apparently tried to insert or update a row with a value for the 'column_name' column that is larger than will fit into said column.

If it's CHAR or VARCHAR, for example, you probably have a string that's longer than the defined size of the column.

Your choices for fixing this are also somewhat obvious - either make sure that the data that you store is no larger than the column (perhaps by truncating longer strings before storing them), or increase the size of the database column.

Answer 4

I encountered same issue in my Spring Boot application while uploading an Image/Word/PDF file and fixed it with the below steps:

Solution

1. Please have a look at the size of the file that you are trying to upload.
2. Then compare that file size with the Max size allowed by your mapped Database FIELD type.
3. For example for LOB's:
   TINYBLOB ≈ 255 bytes, BLOB ≈ 64KB, MEDIUMBLOB ≈ 16MB and LONGBLOB ≈ 4GB Run

4. Use Alter table command as per your Space requirements:

   ALTER TABLE 'TABLE_NAME' MODIFY 'FIELD_NAME' MEDIUMBLOB;

   Note: It is always recommended to save a Link but not the actual file (having large sizes) in DB.

Answer 5

In my case The uploaded data contains quotation marks(' and "). That's why I get the same exception you mentioned above. After removing the quotation marks inside data successfully inserted to the tables.

Answer 6

This error appears because,the size of data you tried to insert into column is too large.

Solution - Change the column size to max.Assuming your column is of type VARCHAR,then in mySQL

ALTER TABLE table_name CHANGE COLUMN col_name col_name  VARCHAR(1000);  //assuming 1000 is enough

Answer 7

For those who stumble upon here, there could be another reason for similar error:

The type of the Stored Procedure parameter might be a limiting factor.

Eg: You have the corresponding column data type and the data sent to MySQL server as LONGTEXT while the parameter of the stored procedure to which you are sending LONGTEXT data may be having type TEXT. In this case you need to update the parameter data type

Answer 8

I suggest that you are using Spring Boot and Hibernate. I faced the same Problem and the solution was pretty obvious:

    @Column(length = 65555)
    @NotNull
    private String content;

I also tried the following line, but this somehow hasn't shown any effect:

@Size(min = 5, max = 65555)

I think that this might happen because there are default constrains for MySQL table sizes, and the @Size Annotation is just used for feedback to the user if the entered values are not in the defined ranges. I hope this fixed your problem!

Answer 9

In The JPA entity use annotation @Column with length. That should resolve the problem.

Sample Code:

@Entity
public class SampleEntity{

    @Column(length = 1200)
    private String column_name;

    // ...

}

Set the length as per your preference, it will become VARCHAR(length) in the table. If you give a bigger value it will get created as longtext.

Tried and tested - the create sql script shows like this:

CREATE TABLE `SampleEntity` (
  `id` bigint(20) NOT NULL,
  `dateCreated` datetime DEFAULT NULL,
  `dateModified` datetime DEFAULT NULL,
  `column_name` longtext,
  `userName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Reference - https://www.baeldung.com/jpa-size-length-column-differences

Answer 10

Had exact same issue. This solved my problem :

@Column(columnDefinition = "longtext")
private String document;

Answer 11

In our case just the reboot of the application resolves the issue , by reboot I mean restarting tomcat. Any ideas if this issue can be happening due to some other reason but the error is thrown like this.