Bug #22946 In certain cases prepared statement problems
Submitted: 3 Oct 2006 16:12 Modified: 16 Oct 2006 11:12
Reporter: [ name withheld ] Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:mySQL 4.1.21 / connector 5.0.3 OS:Linux (Linux AMD64)
Assigned to: CPU Architecture:Any
Tags: java, jdbc, prepared statement

[3 Oct 2006 16:12] [ name withheld ] 
Description:
When using prepared statements with Connector/J it results in no values bind to variables or only garbage bind to variables. When garbage bind to variables it turns into an Data truncation: Data truncated for column exception. Have started mySQL with log option to log all SQL statements. Below is from log file when running simple test using prepared statements and not. 

061003 18:01:51       3 Connect     root@localhost on test
                      3 Query       SET NAMES latin1
                      3 Query       SET character_set_results = NULL
                      3 Query       SHOW VARIABLES
                      3 Query       SHOW COLLATION
                      3 Query       SET autocommit=1
                      3 Query       insert into user(username) values('test')
                      3 Prepare     [1] insert into user(username) values(?)
                      3 Execute     [1] insert into user(username) values('')

The JDBC url used is jdbc:mysql://localhost/test?profileSQL=true. Output from program is pasted below. 

Oct 3, 2006 4:01:51 PM N/A N/A
INFO: Profiler Event: [QUERY] 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0, message: SET NAMES latin1
Oct 3, 2006 4:01:51 PM N/A N/A
INFO: Profiler Event: [FETCH] 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 16 ms, connection-id: 0, statement-id: 999, resultset-id: 0
Oct 3, 2006 4:01:51 PM N/A N/A
INFO: Profiler Event: [QUERY] 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0, message: SET character_set_results = NULL
Oct 3, 2006 4:01:51 PM N/A N/A
INFO: Profiler Event: [FETCH] 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0
Oct 3, 2006 4:01:51 PM N/A N/A
INFO: Profiler Event: [QUERY] 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 1 ms, connection-id: 0, statement-id: 1, resultset-id: 1, message: SHOW VARIABLES
Oct 3, 2006 4:01:51 PM N/A N/A
INFO: Profiler Event: [FETCH] 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 4 ms, connection-id: 0, statement-id: 1, resultset-id: 1
Oct 3, 2006 4:01:51 PM N/A N/A
INFO: Profiler Event: [QUERY] 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 0 ms, connection-id: 0, statement-id: 2, resultset-id: 2, message: SHOW COLLATION
Oct 3, 2006 4:01:51 PM N/A N/A
INFO: Profiler Event: [FETCH] 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 1 ms, connection-id: 0, statement-id: 2, resultset-id: 2
Oct 3, 2006 4:01:51 PM N/A N/A
INFO: Profiler Event: [QUERY] 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0, message: SET autocommit=1
Oct 3, 2006 4:01:51 PM N/A N/A
INFO: Profiler Event: [FETCH] 	at java.sql.DriverManager.getConnection(DriverManager.java:525) duration: 0 ms, connection-id: 0, statement-id: 999, resultset-id: 0
Oct 3, 2006 4:01:51 PM N/A N/A
INFO: Profiler Event: [QUERY] 	at Test.main(Test.java:18) duration: 0 ms, connection-id: 3, statement-id: 3, resultset-id: 0, message: insert into user(username) values('test')
Oct 3, 2006 4:01:51 PM N/A N/A
INFO: Profiler Event: [FETCH] 	at Test.main(Test.java:18) duration: 0 ms, connection-id: 3, statement-id: 3, resultset-id: 0
===========================
Using Statement
===========================
===========================
Using PreparedStatement
===========================
Oct 3, 2006 4:01:51 PM N/A N/A
INFO: Profiler Event: [PREPARE] 	at Test.main(Test.java:23) duration: 1 ms, connection-id: 3, statement-id: 4, resultset-id: -1, message: insert into user(username) values(?)
Oct 3, 2006 4:01:51 PM N/A N/A
INFO: Profiler Event: [EXECUTE] 	at Test.main(Test.java:27) duration: 0 ms, connection-id: 3, statement-id: 4, resultset-id: -1, message: insert into user(username) values('hfjsdk')

Table definition used: 
CREATE TABLE user ( username VARCHAR(100) NOT NULL ) TYPE = MyISAM COMMENT ='';

Table is defined to use UTF8. Have tried use both UTF8 and LATIN1 as default charset in mySQL. If I set useServerPrepStmts to false everything works great. Whole system is running UTF8.

Test program used: 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;

public class Test {
	public static void main(String argv[]) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");

		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test?profileSQL=true", "root", "xxx");

		System.out.println("===========================");
		System.out.println("Using Statement");
		System.out.println("===========================");
		Statement s = conn.createStatement();

		s.execute("insert into user(username) values('test')");

		System.out.println("===========================");
		System.out.println("Using PreparedStatement");
		System.out.println("===========================");
		PreparedStatement ps = conn.prepareStatement("insert into user(username) values(?)");
		String x = "hfjsdk";
		ps.setString(1, x);

		ps.execute();
	}
}  

Please advise if I can provide more relevant information.

How to repeat:
Run above test. Have searched all over the net and it seems that some people can produce bug and some can't. Can not find bug reported before on bugs.mysql.com
[5 Oct 2006 19:45] [ name withheld ] 
Tried to upgrade mySQL to 5.0.24a. This time it works like supposed to. Downgraded to 4.1.21 again and again it does not work. Could it be an error in mySQL itself?

/usr/sbin/mysqld, Version: 5.0.24a-log. started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
061005 21:31:57       1 Connect     root@localhost on
061005 21:32:01       1 Query       SELECT DATABASE()
                      1 Init DB     test
                      1 Query       show databases
                      1 Query       show tables
061005 21:32:03       1 Query       CREATE TABLE user ( username VARCHAR(100) NOT NULL ) TYPE = MyISAM COMMENT =''
061005 21:32:11       2 Connect     root@localhost on test
                      2 Query       SET NAMES utf8
                      2 Query       SET character_set_results = NULL
                      2 Query       SHOW VARIABLES
                      2 Query       SHOW COLLATION
                      2 Query       SET autocommit=1
                      2 Query       SET sql_mode='STRICT_TRANS_TABLES'
                      2 Query       insert into user(username) values('test')
061005 21:32:12       2 Prepare     [1]
                      2 Execute     [1] insert into user(username) values('hfjsdk')
[16 Oct 2006 11:12] Tonci Grgin 
Hi Jonas.
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Explanation: This is the duplicate of BUG#22425. If you feel I'm wrong, please reopen this report and give me your explanation.