MariaDB 10.2 , jdbcCompliantTruncation=false not working

Question

We have migrated from MariaDB 10.0 to 10.2 and now facing an issue related to data too long for column

We were using jdbcCompliantTruncation=false in the DB connection string for MariaDB 10.0, and it was helping in truncating the long values for columns, and no exception as mentioned below were observed

Exception encountered in MariadDb 10.2

           org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; 
           SQL [insert into table(column1,column2) values(?,?,?)(conn=21823) Data too long for column 'column2' at row 1; nested exception is java.sql.BatchUpdateException: (conn=21823) Data too long for column 'column2' at row 1
           at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:104)
           at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
           at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
           at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
           at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1402)
           at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:620)
           at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:634)
           at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:924)

Has something changed in MariaDB 10.2 or I am missing something. Any pointers in this direction will be highly appreciated.

Check [SQL_MODE](https://mariadb.com/kb/en/library/sql-mode/) configured on MariaDB 10.0 and MariaDB 10.2. **"This property (jdbcCompliantTruncation) has no effect if the server sql-mode includes STRICT_TRANS_TABLES."**, see [jdbcCompliantTruncation](https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html) MySQL Connector/J. — wchiquito, Nov 30 '19 at 14:45

score 1 · Answer 1 · answered Dec 01 '19 at 17:27

Finally, the issue was resolved by making changes in the SQL MODE for MariaDB 10.2

The default value for SQL MODE can be ascertained by the below command

SELECT @@SQL_MODE;

It returns the following

STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO , NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

Now the issues is that jdbcCompliantTruncation will have no effect if the server sql-mode includes STRICT_TRANS_TABLES.

The solution is to update the SQL_MODE

  SET SQL_MODE='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

MariaDB 10.2 , jdbcCompliantTruncation=false not working

1 Answers1