com.mysql.jdbc.MysqlDataTruncation: Data truncatio

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

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 2

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 3

I faced same issue in my spring boot data jpa application when i going to insert image file in data base as byte its given me same error.

After many R & D i found solution like below.

I added below line in my application.properties file and resolve that

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

Hope it will helpful to someone.

Answer 4

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 5

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 6

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