| Bug #38244 | PreparedStatement.setBytes() can't be used for data type BIT(>1) in where clause | ||
|---|---|---|---|
| Submitted: | 20 Jul 2008 1:57 | Modified: | 4 Aug 2008 9:47 |
| Reporter: | Yiping Wang | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 5.1.6 | OS: | Any (winXP+SP2) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | BIT(>1), setBytes(), where clause | ||
[30 Jul 2008 8:59]
Tonci Grgin
Sorry Yiping, will try to check out today. There are many older report waiting still.
[30 Jul 2008 9:04]
Tonci Grgin
Yiping, on a first glance I would have expected truncation to happen here, although I need to test this more. Can you try setting "jdbcCompliantTruncation" to false and retest?
[30 Jul 2008 9:19]
Yiping Wang
Hi Tonci, I just try what you suggested, ds.setJdbcCompliantTruncation(false); but the exception is still there. Thanks.
[30 Jul 2008 9:35]
Tonci Grgin
Test case
Attachment: TestBug38244.java (text/java), 3.48 KiB.
[30 Jul 2008 9:36]
Tonci Grgin
Test case output: .Loading JDBC driver 'com.mysql.jdbc.Driver' Done. Done. Connected to 5.0.68-pb10-log java.vm.version : 1.5.0_12-b04 java.vm.vendor : Sun Microsystems Inc. java.runtime.version : 1.5.0_12-b04 os.name : Windows XP os.version : null sun.management.compiler : HotSpot Client Compiler Test 1.1: jdbcCompliantTruncation=false, useInformationSchema=true [B 0 111 Update count (i) = 1 Update count = 1 Test 1.2: jdbcCompliantTruncation=true, useInformationSchema=true [B 0 -34 Update count (i) = 1 Update count = 1 Time: 44,328 OK (1 test)
[30 Jul 2008 9:37]
Tonci Grgin
Mysql cl. output: o) after initial 111 mysql> SELECT * FROM bug38244; +------+ | c | +------+ | o | +------+ 1 row in set (0.00 sec) o) After 222 mysql> SELECT * FROM bug38244; +------+ | c | +------+ | Ů | +------+ 1 row in set (0.00 sec) o) After 333 mysql> SELECT * FROM bug38244; +------+ | c | +------+ | ☺M | +------+ 1 row in set (0.00 sec) mysql>
[30 Jul 2008 9:43]
Tonci Grgin
Hi Yiping. I can not repeat your problems using latest c/J 5.1 sources and attached test case. You might want to check on snapshots page and retest with latest c/J build. Server log: Connection 46 is from test framework. Connection 47 is with jdbcCompliantTruncation=true Connection 48 is with jdbcCompliantTruncation=false 46 Query SHOW COLLATION 46 Query SET character_set_results = NULL 46 Query SET autocommit=1 46 Query SET sql_mode='STRICT_TRANS_TABLES' 46 Query SELECT VERSION() 46 Query DROP TABLE IF EXISTS bug38244 46 Query CREATE TABLE bug38244 (c BIT(10)) 47 Connect root@localhost on test 47 Query /* @MYSQL_CJ_FULL_PROD_NAME@ ( --<cut>-- 47 Query SHOW COLLATION 47 Query SET character_set_results = NULL 47 Query SET autocommit=1 47 Query SET sql_mode='STRICT_TRANS_TABLES' 47 Query INSERT INTO bug38244 VALUES (111) 47 Query SELECT * FROM bug38244 47 Query update bug38244 set c=222 where c = x'006F' 47 Quit 48 Connect root@localhost on test 48 Query /* @MYSQL_CJ_FULL_PROD_NAME@ --<cut>-- 48 Query SHOW COLLATION 48 Query SET character_set_results = NULL 48 Query SET autocommit=1 48 Query SELECT * FROM bug38244 48 Query update bug38244 set c=333 where c = x'00DE' 48 Quit 46 Quit
[30 Jul 2008 9:45]
Tonci Grgin
My SQL_MODE is empty: +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_mode | | +---------------+-------+
[4 Aug 2008 6:22]
Yiping Wang
Hi Tonci, Thank you for your effort, I am sorry for not replying in time. but I retested it with your attached test code, but I still can't make it pass. it is very strange. I just want to confirm with you that my mysql version is mysql-5.0.51b-win32 and my c/j verion is mysql-connector-java-5.1.6-bin.jar. do i need some other configuration? what do you mean for latest versio of Connector/J? Do you test it with some unpublished version? would you point me the version you tested. Thank you very much for your help!
[4 Aug 2008 6:35]
Yiping Wang
my test code. please review it.
Attachment: TestBug38244.java (application/octet-stream, text), 4.17 KiB.
[4 Aug 2008 6:37]
Yiping Wang
here is the test result: java.vm.version : 1.5.0_07-b03 java.vm.vendor : Sun Microsystems Inc. java.runtime.version : 1.5.0_07-b03 os.name : Windows XP os.version : null sun.management.compiler : HotSpot Client Compiler Test 1.1: jdbcCompliantTruncation=true, useInformationSchema=true URL:jdbc:mysql://localhost:3306/vtba Get connection: com.mysql.jdbc.ConnectionImpl@dd5b [B 0 111 Exception in thread "main" com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: '' at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922) at TestBug38244.testBug38244(TestBug38244.java:81) at TestBug38244.main(TestBug38244.java:124)
[4 Aug 2008 8:14]
Yiping Wang
Hi Tonci, looks like we don't use same version of database. from your log, you are using 5.0.68-pb10, but I am using 5.0.51b. I also tried mysql-5.1.26, I met the same issue. so would you please check if it is caused by different version. Thanks.
[4 Aug 2008 9:20]
Yiping Wang
Hi Tonci, finally, I have made it pass by setting the default char set to utf8(the original default charset is latin1). Would you please confirm this? should it be a bug for latin1 can't run pass?
[4 Aug 2008 9:35]
Tonci Grgin
Hi Yiping. You posted several questions and I'll try to answer them. o) Code review is not covered by free support in BugsDB, only if you have support contract. However, test case review is. o) Using new server c/J: I am obliged to use SW as fresh as possible. If you want to check/fix your problems on older MySQL SW versions, you should get support contract. o) By latest version of Connector/J I mean version compiled from latest development source trees. Those trees are publicly available for anyone to compile/use them. Please see manual for details as we were switching to bazaar recently and I am not yet sure where stuff is now. Now, it might be that I really used UTF8 in connection, can't remember now, but this look like it needs more testing... Was your table created with latin1 charset too?
[4 Aug 2008 9:47]
Yiping Wang
Hi Tonci, Looks it doesn't matter to create table under which charset. I have 2 scenarioes. 1. create table under latin1, it works on utf8. 2. create table under ut8 charset, it also works. btw, I set the charset on the server side.
Description: In mySQL jdbc driver, the data type BIT(>1) will be mapped to byte[]. so while we execute a "select" to the table, BIT(>1) will return a byte[] by using ResultSet.getObject(). why I can't use PreparedStatement.setBytes() for BIT(>1) type.I think it is a common scenario for users to use "select bit_col from table", and get bit_col value from ResultSet, then update the bit_col with the value in where clause. but it will introduce exception if using setBytes() in where clause for BIT(>1). Exception in thread "main" com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: '' at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922) at MySQLClient.testFloat(MySQLClient.java:45) at MySQLClient.main(MySQLClient.java:9) I have ever tried, the setBytes() can be used if the BIT(>1) is not in where clause. How to repeat: 1. create table test (c BIT(10)); 2 insert test values (111); 3. using the below code to do select and update. public static void testFloat() throws Exception { Connection conn = getConnection(); String select = "select * from test"; PreparedStatement stmt2 = conn.prepareStatement(select); ResultSet rs = stmt2.executeQuery(); byte[] b = null; while (rs.next()) { b = (byte[])rs.getObject(1); for (byte bb: b) System.out.println(bb); } String update = "update test set c=222 where c = ?"; PreparedStatement stmt = conn.prepareStatement(update); stmt.setBytes(1, b); stmt.executeUpdate(); System.out.println("Update count = " + stmt.getUpdateCount()); } then exception will be thrown out. Suggested fix: I think it makes sense to support setBytes() for the data type BIT(>1), since the type is mapped to byte[].