MySQL Bugs: #30917: jdbcCompliantTruncation initialization has no effect

Bug #30917	jdbcCompliantTruncation initialization has no effect
Submitted:	8 Sep 2007 16:05	Modified:	11 Sep 2007 16:35
Reporter:	Rico H.	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.0.7	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Setting jdbcCompliantTruncation=false on the connection URI has no effect.
According to Bug #21544, this was fixed. However, it seems that the same bug reappeared, as com.mysql.jdbc.MysqlDataTruncation is still thrown when trying to store a string that its length exceeds the length of the corresponding varchar column. This is the exception thrown:

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

Please refer to Bug #21544 on http://bugs.mysql.com/bug.php?id=21544 for more info and test cases. I've also been able to repeat this bug with version 5.0.6.

Thank you!

How to repeat:
To test it, just set jdbcCompliantTruncation=false in your connection URI and then try to store a string that its length exceeds the length of the corresponding varchar column of the table. A com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column... will be thrown.

Which version of the server and on what platform are you running it? MySQL-5.0 on Windows is installed with a sql_mode of "strict" by default, which means that the JDBC driver has nothing to do with enforcing truncation or not, the server is doing it.

Mark,

Thanks for looking into this so fast. I'm using MySQL 5.0.45 on Linux, but as you pointed, sql-mode was set to "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION". I removed the STRICT_TRANS_TABLES from it and repeated the tests again.
This time, the server issues a warning instead of an error, and the JDBC driver throws an exception or not depending on the value of jdbcCompliantTruncation. Therefore, there is not bug, but I would strongly recommend including a comment on the JDBC's documentation (as part of the jdbcCompliantTruncation parameter explanation) explaining this relation between sql-mode and the jdbcCompliantTruncation parameter. According to the current explanation, it seems like the driver is the one performing the validation/truncation and not the server. I also found a lot of posts on the forum from users not being able to auto-truncate columns by setting jdbcCompliantTruncation=false. That's the reason why I opened this bug.

Thanks one more time!

Hi Rico and thanks for your report. I will post a request to Doc team.

I've updated the description of jdbcCompliantTruncation to make note of the effect when the sql_mode is in effect.