1. Overview
The Java Database Connectivity (JDBC) Application Programming Interface (API) provides a set of classes and interfaces. We can use these to connect to data sources such as relational databases and run SQL statements. We use MySQL database-specific JDBC Driver com.mysql.cj.jdbc.Driver, which implements this API when we want to connect to MySQL.
When running SQL statements, we could encounter the exception message: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column.
In this tutorial, we’ll learn how to solve this exception.
2. Schema Setup
We don’t need any special setup besides the development environment we may already use. We’ll discuss solving this exception using JUnit 5 integration tests with Apache Maven as the build tool. To demonstrate, we’ll use the Department table from the University database. Further, we’ll use the table column code when running SQL statements. Let’s find the table definition for the table so that we know the size of the data we can add:
DESC department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(50) | YES | | NULL | |
| code | varchar(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
Notably, the code column definition allows only a varchar of size 4 or less.
3. Cause of the Exception
We get the MysqlDataTruncation exception when we try to insert or update column data that exceeds the maximum column size set by the column definition. This exception can only occur at runtime, i.e., when we run an application. Further, it can occur only with specific Data Manipulation Language (DML) statements.
3.1. Statements That Could Cause the Exception
We can get this exception when we use the INSERT SQL statement to add data to a column. To demonstrate with a corresponding SQL example, when we try to add a new Department table row in which the code column size exceeds 4, we get the following SQL error:
INSERT INTO
DEPARTMENT(id, name, code)
VALUES(6, "Data Science", "DATSCI");
ERROR 1406 (22001): Data too long for column 'code' at row 1
We get an error/exception regardless of whether the INSERT statement is run in a MySQL Client shell or within a JDBC application.
Additionally, we can get this exception when using the UPDATE SQL statement. To demonstrate with a corresponding SQL example, let’s try to update a row of data in the Department table in which the updated code column size exceeds 4:
UPDATE department
SET code = 'COMPSCI'
WHERE id = 1;
ERROR 1406 (22001): Data too long for column 'code' at row 1
Indeed, we get an SQL error.
3.2. Running a JUnit Test
Let’s demonstrate getting the MysqlDataTruncation exception in a Java application:
@Test
void givenTableInsert_whenDataTruncationExceptionThrown_thenAssertionSucceeds()
throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/university?" +"user=root");
Statement stmt = conn.createStatement ();
Exception exception = assertThrows(SQLException.class, () -> {
stmt.execute ("INSERT INTO DEPARTMENT(id, name, code) VALUES(6, 'Data Science', 'DATSCI')");
});
String expectedMessage = "Data truncation: Data too long for column";
String actualMessage = exception.getMessage();
assertTrue(actualMessage.contains(expectedMessage));
}
Furthermore, the exact exception message includes the column name.
4. How to Fix the Exception
We fix the exception by using one of the two options.
4.1. Reduce the Data Size to Match Column Definition
We should reduce the column data size added to match the column definition. In the cited example for the INSERT SQL statement, we should add a new data row in which the code column data doesn’t exceed 4:
INSERT INTO
DEPARTMENT(id, name, code)
VALUES(6, "Data Science", "DS");
Similarly, in the cited example for the UPDATE SQL statement, we should supply an updated column value for the code column data that doesn’t exceed 4:
UPDATE department
SET code = 'CSCI'
WHERE id = 1;
Furthermore, we can verify SQL statements in a MySQL client before including them in a JDBC application.
4.2. Alter the Column Definition
When we don’t want to reduce the size of the column data added, or updated, we should alter the table’s structure. More precisely, we should increase the column size generating the exception. We can do this with an ALTER TABLE SQL statement:
ALTER TABLE DEPARTMENT
CHANGE COLUMN code
code VARCHAR(10);
Afterward, when we run the same INSERT and UPDATE SQL statements, whether in a MySQL Client or a JDBC application, we won’t get an error/exception. However, we should remember that although we can increase the column size to add a larger amount of data, we can’t decrease the column size once we’ve added larger objects. To demonstrate, let’s try to reduce the code column size back to 4 after increasing it to 10 and adding a larger size column data:
ALTER TABLE DEPARTMENT
CHANGE COLUMN code
code VARCHAR(4);
ERROR 1265 (01000): Data truncated for column 'code' at row 6
Therefore, we should alter the column definition only if this is what we need for subsequent data modifications.
4.3. Verifying the Fix With a JUnit Test
Let’s verify that using one of the two options discussed fixes the MysqlDataTruncation exception. We add a second test method to the same test class, MySQLDataTruncationUnitTest, to run another JUnit integration test. Accordingly, the second test method is to give a table insert (run with INSERT). When this statement is run, ensure that no exception is thrown. We use the assertion assertDoesNotThrow() this time:
@Test
void givenTableInsert_whenStatementRun_thenEnsureNoExceptionThrown() throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/university?" + "user=root");
Statement stmt = conn.createStatement();
assertDoesNotThrow(() -> {
stmt.execute ("INSERT INTO DEPARTMENT(id, name, code) VALUES(6, 'Data Science', 'DS')");
});
}
When we run the JUnit tests with the example table, both tests should pass.
5. Conclusion
In this article, we learned how to fix the com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column exception problem.
We have two options to fix this exception. When we don’t want to reduce the column data size added, we can alter the table structure. We do this to increase the column size in the column definition. However, when altering the table structure isn’t an option, we should decrease the data size added to match the column size.
The code backing this article is available on GitHub. Once you're
logged in as a Baeldung Pro Member, start learning and coding on the project.
