MySQL Bugs: #14048: jdbcCompliantTruncation not working

Bug #14048	jdbcCompliantTruncation not working
Submitted:	15 Oct 2005 15:59	Modified:	2 Dec 2005 0:03
Reporter:	Doug Satchwell	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.1.11	OS:	Windows (Windows XP)
Assigned to:	Timothy Smith	CPU Architecture:	Any

Description:
I am using Connector/J via hibernate, with MySQL 5.0.13-rc-win32.

I have set the url to jdbc:mysql://localhost/cjs?jdbcCompliantTruncation=false. However, I continue to get data truncation exceptions, for instance:

Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'orderRef' at row 1
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2924)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
	at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1120)
	at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:675)

How to repeat:
In the example above, my orderRef column is a varchar(20), and I get this exception whenever I attempt to update the column with more than 20 characters.

Sorry...I was not able to reproduce the bug. I tested using 3.1.11 Connector/J and MySQL 5.0.14 rc. Could you please re-test the bug on MySQL 5.0.14 rc ?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Doug,

Can you please provide some more details about your setup?

What is the exact version of Java you're using?  What is the exact version of Windows?

Here is a complete test case - can you see if this throws an exception for you, or if it works correctly?

import com.mysql.jdbc.util.BaseBugReport;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class T1 extends BaseBugReport
{
    private static Connection conn;
    private static Statement stmt;
    private static ResultSet rs;

    public static void main(String[] args) throws Exception
    {
        new T1().run();
    }

    public void setUp() throws Exception
    {
        this.conn = getConnection("jdbc:mysql://localhost/test?user=root&password=&useUnicode=true&characterEncoding=UTF-8&jdbcCompliantTruncation=false");
        this.stmt = this.conn.createStatement();

        try
        {
            this.stmt.executeUpdate("drop table t1");
        }
        catch (SQLException sqlEx)
        {
            ;
        }

        this.stmt.executeUpdate("create table t1 (f1 char(20) character set utf8)");
        this.stmt.executeUpdate("insert into t1 (f1) values ('串丱丰sdfasdfasdfasdfasdfasdf')");
    }

    public void runTest() throws Exception
    {
        this.rs = this.stmt.executeQuery("select * from t1");

        while (this.rs.next())
        {
            String v = this.rs.getString(1);
            System.out.println(v);
            assertTrue("String not truncated to 20", v.length() == 20);
        }
    }

    public void tearDown() throws Exception
    {
        this.stmt.executeUpdate("drop table t1");
    }
}

Can you add this to the URL and test it:

useServerPrepStmts=false

Can you please send your my.cnf file?

Regards,

Timothy

Hi!

I just was able to repeat this, if I do:

set global sql_mode = 'strict_all_tables';

With that setting, the server sends an error (not just a warning) for data truncation.  This is something that Connector/J can not ignore, and so the error is propogated as an SQLException, just like all other server errors.

The jdbcCompliantTruncation connection property only controls how Connector/J handles data truncation warnings from the server, when strict mode is not in effect.  By default, Connector/J throws an exception in this case in order to comply with JDBC specs.

In short, Connector/J can be more strict than mysqld, but it can not be less strict.  If the server is set to report an error on data truncation, then Connector/J will propogate that error as well.

A possible workaround is to turn off strict mode, either for the whole server, for a particular session, or for just a few statements.  For example:

set @old_sql_mode = @@sql_mode;
set sql_mode = '';
-- Run some statements which may result in data truncation
set sql_mode = @old_sql_mode;

Regards,

Timothy