Bug #24546 When a Data Truncate warning arises, no rows are inserted

• Submitted: 23 Nov 2006 14:58
• Modified: 23 Nov 2006 20:01
• Reporter: Stefano Crimi'
• Status: Not a Bug
• Category: Connector / J
• Severity: S3 (Non-critical)
• CPU Architecture: Any

[23 Nov 2006 14:58] Stefano Crimi'

Description:
When you perform an INSERT statement and one (or more) columns are larger that the column size, the row is not inserted.

How to repeat:
Look at the MYSQL code:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17 to server version: 5.1.11-beta-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> connect test
Connection id:    18
Current database: test

mysql> create TABLE TEST3 (A VARCHAR(4));
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO TEST3 VALUES('prova');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'A' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test3;
+------+
| A    |
+------+
| prov |
+------+
1 row in set (0.03 sec)

Although the string lenght is larger than the maximum, the row is inserted (and truncated). Instead, using this java code:

import java.sql.*;

public class l1  {

    public static void main (String[] args) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://10.39.64.94/test","root","root");
		Statement st = conn.createStatement();
		try {
			st.executeUpdate("INSERT INTO TEST3 VALUES('prova');");
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println(e.getSQLState());
			System.out.println(e.getErrorCode());
			System.out.println(e.getCause());
			System.out.println(e.getMessage());
		}
		st.close();
		conn.close();
	}
}

I got an error:

------------------------------------------------------------------------------------------------
C:\>java -cp javalib\mysql-connector-java-5.0.3-bin.jar;. l1
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'A' at row 1
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2973)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2998)
        at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1071)
        at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1005)
        at l1.main(l1.java:10)
01004
0
null
Data truncation: Data too long for column 'A' at row 1

C:\>
------------------------------------------------------------------------------------------------
C:\>java -cp javalib\mysql-connector-java-5.0-nightly-20061123-bin.jar;. l1
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'A' at row 1
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2869)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1574)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3208)
        at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1316)
        at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1235)
        at l1.main(l1.java:10)
01004
0
null
Data truncation: Data too long for column 'A' at row 1
------------------------------------------------------------------------------------------------

And no data is inserted in the table. Altering the column size (from varchar(4) to varchar(5) the problem disappears because there is no more data truncation).

[23 Nov 2006 15:54] Tonci Grgin

Hi Stefano and thanks for your report. I can't modify your post...

I don't think this is a bug. The driver sets STRICT_TRANS_TABLES mode to enforce JDBC compliance on truncation checks. If you can't use STRICT_TRANS_TABLES as part of your sql_mode, then you'll have to disable truncation checks by adding "jdbcCompliantTruncation=false" as a URL configuration parameter.
Let me know if this helps.

[23 Nov 2006 16:29] Stefano Crimi'

Yes, setting the sql_mode to a propter value (for example 'ANSI') the row is INSERTED. But in any case an Exception is thrown, which is resource consuming. There is any way to avoid (maybe setting a parameter in the Statement) this?

[23 Nov 2006 20:01] Tonci Grgin

Stefano, as I said:
If you can't use STRICT_TRANS_TABLES as part of your sql_mode, then you'll have to disable truncation checks by adding 

"jdbcCompliantTruncation=false" as a URL configuration parameter.

[4 Feb 2011 10:06] Sachin Sharma

Hi Tonci Grgin

Although its an old post when you replied, but you just solved my problem!
Thanks

Sachin