MySQL Bugs: #38954: Data truncation While using BIT(1) in stored procedure with INOUT type

Bug #38954	Data truncation While using BIT(1) in stored procedure with INOUT type
Submitted:	22 Aug 2008 5:36	Modified:	6 Apr 2022 22:30
Reporter:	Yiping Wang	Email Updates:
Status:	Closed	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

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)

material

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

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?

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.

Thanks for info, working on this.

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();

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?

I rechecked and, since there is no setBit in Java I do not think we can do anything less than implement it ourselves...

Posted by developer:
 
Added the following entry to the C/J 8.0.29 changelog: 

"Data truncation occurred for INOUT type parameters of data type BIT(1) for stored procedures."