Bug #38954 Data truncation While using BIT(1) in stored procedure with INOUT type
Submitted: 22 Aug 2008 5:36 Modified: 20 Mar 2012 16:10
Reporter: Yiping Wang Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.6 OS:Any (WinXP SP2)
Assigned to: Alexander Soklakov CPU Architecture:Any
Tags: BIT(1), INOUT, stored procedure, truncation
Triage: D4 (Minor)

[22 Aug 2008 5:36] Yiping Wang
Description:
My DB version is 5.0.51b-community-nt 
My jdbc driver is 5.1.6 
I met a data truncation issue While using BIT(1) in stored procedure with INOUT type. 

How to repeat:
the steps to reproduce: 
1. create table and procedure. 
create table bit_test(c bit); 

create procedure sp_bit (inout p1 bit) 
begin 
insert into bit_test values (p1); 
end 

2. java client to call it. 

CallableStatement stmt = conn.prepareCall("{call sp_bit(?)}"); 
stmt.setBoolean(1, true); 
stmt.registerOutParameter(1, java.sql.Types.BIT); 
stmt.execute(); 

then some exception thrown out. 

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'p1' at row 1 

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3510) 

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3444) 

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1948) 

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2081) 

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554) 

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1751) 

at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1011) 

at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:859) 

at MySQLBITTest.test(MySQLBITTest.java:40) 

at MySQLBITTest.main(MySQLBITTest.java:14)
[22 Aug 2008 5:42] Yiping Wang
material

Attachment: MySQL_BIT_1.zip (application/x-zip-compressed, text), 867 bytes.

[3 Sep 2008 9:48] Tonci Grgin
Hi Yiping and thanks for your report.

What I would like you to check is general query log from server. I want to see exactly what's happening as this might be regression from BUG#25715.

Also, please try setting JDBCCompliantTruncation to false and retest. Does this help?

What is your SQL_MODE?

Why is parameter defined as INOUT?
[4 Sep 2008 7:20] Yiping Wang
Hi Tonci, 
Below is the log from server side:
-------------------------------------------------
mysqld-nt.exe, Version: 5.0.51b-community-nt-log (MySQL Community Edition (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
080904 14:07:11	      1 Connect     root@localhost on vtba
		      1 Query       /* mysql-connector-java-5.1.6 ( Revision: ${svn.Revision} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
		      1 Query       SHOW COLLATION
		      1 Query       SET character_set_results = NULL
		      1 Query       SET autocommit=1
		      1 Query       USE `vtba`
		      1 Query       SELECT DATABASE()
		      1 Query       USE `vtba`
		      1 Query       SHOW CREATE PROCEDURE `vtba`.`sp_bit`
		      1 Query       SET @com_mysql_jdbc_outparam_p1=x'31'
		      1 Query       CALL sp_bit(@com_mysql_jdbc_outparam_p1)
080904 14:09:44	      2 Connect     root@localhost on vtba
		      2 Query       SELECT @@sql_mode
080904 14:09:49	      2 Query       SELECT @@sql_mode

-------------------------------------------------
I have tried the jdbcCompliantTruncation, the exception is always there no matter I set it to false or true.

Here is the SQL_MODE, I use by default:
-------------------------------------------------
mysql> SELECT @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
-------------------------------------------------

For the reason I use it with INOUT, because I also want to do a "select into p1" operation, so it must be a INOUT type, right?

I put the simple SP (only use a insert) there as an example just to simplify the logic.

Thank you, hope these could be useful.
[13 Sep 2008 19:11] Tonci Grgin
Thanks for info, working on this.
[13 Sep 2008 21:55] Tonci Grgin
Yiping, truncation warning is thrown so cJ should pass it to you but I see data in table:

mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'p1' at row 1 |
+---------+------+------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from bug38954;
+---+
| c |
+---+
| ☺ |
+---+
1 row in set (0.00 sec)

Now, I'd say this is not a bug because:
  o) In MySQL BOOLEAN is defined as TINYINT
  o) In MySQL if you omit [(M)] in BIT[(M)], as you did, the default is 1 bit
  o) You have squeezed TINYINT (stmt.setBoolean(1, true);) into BIT param and I don't see how cJ can reliably convert this to BIT...

Maybe you forgot that *before* 5.0.3, BIT is a synonym for TINYINT(1). Now it is not.

If you define column as BOOLEAN not BIT, and thus have matching types, everything works:
	createTable("bug38954", "(c BOOLEAN PRIMARY KEY)");
	this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS sp_bug38954");
	this.stmt.executeUpdate("create procedure sp_bug38954(INOUT p1 BOOLEAN)\n"
		+ "begin\n"
		+ "INSERT INTO bug38954 VALUES(p1);\n"
		+ "end\n");
			
        CallableStatement stmt = conn.prepareCall("{call sp_bug38954(?)}");
        stmt.setBoolean(1, true);
        stmt.registerOutParameter(1, java.sql.Types.BOOLEAN);
        stmt.execute();
[13 Sep 2008 22:13] Tonci Grgin
Hmmm, I reconsidered my ruling after making IN test case:

	createTable("bug38954", "(c BIT PRIMARY KEY)");
	this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS sp_bug38954");
	this.stmt.executeUpdate("create procedure sp_bug38954(IN p1 BIT)\n"
			+ "begin\n"
			+ "INSERT INTO bug38954 VALUES(p1);\n"
			+ "end\n");
			
        CallableStatement stmt = conn.prepareCall("{call sp_bug38954(?)}");
        stmt.setBoolean(1, true);
        //stmt.registerOutParameter(1, java.sql.Types.BOOLEAN);
        stmt.execute();
        System.out.println(stmt.getUpdateCount());
        //System.out.println(stmt.getObject(1));

Now, this works properly (meaning pushing TINYINT into BIT) so there is no reason why INOUT test should fail.

Verified as described using latest c/J 5.1 sources. Mark?
[8 Feb 2011 14:46] Tonci Grgin
I rechecked and, since there is no setBit in Java I do not think we can do anything less than implement it ourselves...