Mysql data truncation exception

Fix Data Is Truncated for a Column Error in MySQL

This article demonstrates the possible reasons and solutions for an error, saying that Data is truncated for a column in MySQL.

Fix Data is Truncated for a Column Error in MySQL

Here, we will explore the possible reasons and solutions to get rid of an error saying MySQL data is truncated for a column .

Data Is Too Large

There are some scenarios when we have to load the data from a file instead of inserting manually into the MySQL table. While loading, the Data truncated for a column ‘columnName’ at row # error may occur. Why?

This is because the given data seems too big for a data type of the column in the MySQL table. For instance, you have a table where a value attribute is of type text which can only accommodate 65K characters of the provided data.

You will get this error when you insert data for the value column that exceeds 65K.

To solve this problem, you need to change the type that can accept more data, such as MEDIUMTEXT or LONGTEXT , which can accommodate 16MB and 4GB. You can also find some optimization tips here.

We can also truncate the data using SET ANSI_WARNINGS OFF and insert the records again, considering the maximum length of the column’s data type.

Invalid Data Is Passed

The Error Code: 1265. Data truncated for column ‘a’ at row 1 also occurs in MySQL if we try to insert invalid data.

For example, we have a table where the price field is type float and accepts the NULL values. See the following:

Читайте также:  Weakauras error decompressing unknown compression method 64

Here, we insert an empty string in the price column, a float type that takes NULL values. Remember, NULL and » are not the same thing.

Still, if we try to insert an empty string in a column that takes NULL values, it would generate the Error Code: 1265. Data truncated for column ‘a’ at row 1 . The simplest solution to this problem is passing the correct value of the right data type as required by the respective column of the price table.

We can solve it by specifying the NULL values explicitly instead of an empty string ( » ) or do not even write the values for that particular column in the INSERT statement. See the following snippet considering our example.

Incorrect Terminating Character

If we insert every line manually in the MySQL table, everything goes well. The error occurs when we try to load more than one line at once and don’t get the terminating character properly.

To solve this, we need to check the terminating character of the file and specify in the LOAD command as follows.

Mehvish Ashiq is a former Java Programmer and a Data Science enthusiast who leverages her expertise to help others to learn and grow by creating interesting, useful, and reader-friendly content in Computer Programming, Data Science, and Technology.


mysql error 1265 data truncated for column

I created a table in mysql like this :

and when I want to insert value into this table, it fails and show the exception as DataError: (1265, «Data truncated for column ‘open’ at row 1»)

Читайте также:  Expected constant expression error

and the values I want to insert into open is 3535929.559. I code the insert data in python like this:

The most strange thing is when I insert the same value in MySQL workbench ,it went smoothly, the query is like this :

But the data shown in the table is:

I think the problem maybe insertion from python is more restricted? Then how to set it to be less restricted? Any help would be appreciated!

3 Answers 3

Not addressing the question, but addressing 2 bugs in your schema.

  • FLOAT(m,n) takes the decimal input, rounds to n decimal places in decimal, then converts to binary with another rounding error.
  • FLOAT (with or without (m,n)) can hold only about 7 digits of precision. Hence, both numbers in your example lose precision at the low end. As you found out, storing 3535929.559 into a FLOAT(20,3) , then retrieving it will yield only 3535929.500 . This is because a FLOAT has only 24 «significant bits of precision».

There are two solutions for both bugs:

  • DECIMAL(m,n) — (20,3) will still round to 3 decimal places, but that might be OK for your application. But it does not have the extra rounding to binary nor the loss of precision.
  • DOUBLE (without (m,n)) — This has about 16 digits of precision, more than enough for most applications. And, even though the decimal input will be rounded to binary, it will be so far out that it is unlikely to cause errors.

FLOAT (with or without (m,n)) takes 4 bytes.
DOUBLE (with or without (m,n)) takes 8 bytes.
DECIMAL(20,3) takes 10 bytes. Shrinking either number may save space.

Читайте также:  Compiler error message the compiler failed with error code 532462766


Facing Issues On IT

BatchUpdateException is sub class of SQLException. BatchUpdateException occurred when any queries fail during batch update. BatchUpdateException also return the values of counts for each queries update before queries fail.

A SQL queries can fail because of data constraints violations or syntax issues etc.

Below is some common situation where ConstraintViolationException occurs:

  • Insert a record with primary key as null.
  • Insert/ Update primary key value is not unique.
  • Insert data value size is more than predefine column size.
  • The inserted record data type is not compatible with predefined column type.

To see a complete list of Database integrity constraints follow this link :

BatchUpdateException Example

Here this exception is occurring because inserting value of title with size more than predefined as 100 characters.

Exception Stacktrace


As title column configured is unique with size of 100 characters. Because here inserting value for title as more than 100 characters that’s what causing issues and throwing DataIntegrityConstraintViolationException and BatchUpdateException but main cause of this exception is data truncation.

To resolve this issue pass title column value as less than 100 characters or increase size of column.

You would like to see

Follow below link to see more JDBC, Hibernate and JPA issues solutions.


Оцените статью