| Bug #17659 | prepareStatement parameter substitution fails after upgrade 4.0 to 4.1 | ||
|---|---|---|---|
| Submitted: | 23 Feb 2006 7:01 | Modified: | 23 Feb 2006 16:28 |
| Reporter: | Pieter Claassen | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / J | Severity: | S1 (Critical) |
| Version: | 4.1 | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[23 Feb 2006 8:57]
Pieter Claassen
The following test code also fails with variables not being substituted in prepareStatement. You can see that I forced all communication to utf8 in case there was a problem with that. Also, the driver automatically selected latin1 as the character encoding even after the server was started with the following settings in /etc/mysql/my.cnf
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
default-character-set = utf8
The database was also created with utf8 as a character set.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
public class dbtest {
public static void main(String[] args){
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (Exception ex) {
System.out.println("My Exception: " + ex.getMessage());
}
try {
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/content?user=root&password=Hmwdih02&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8");
// Statement stmt = conn.createStatement();
// ResultSet rs= stmt.executeQuery("SELECT data FROM content where lang='nl'");
PreparedStatement stmt = conn.prepareStatement("SELECT data FROM content WHERE lang=?");
stmt.setString(1,"nl");
ResultSet rs = stmt.executeQuery();
while (rs.next()){
System.out.println(rs.getString(1));
}
rs = null;
stmt = null;
conn.close();
} catch (Exception ex){
System.out.println("My Exception: " + ex.getMessage());
}
}
[23 Feb 2006 16:28]
Mark Matthews
This is a known issue with the debian-provided builds of MySQL-4.1 (see the JDBC forums at http://forums.mysql.com/ for more reports). The issue is listed in the bug tracker for Ubuntu https://launchpad.net/distros/ubuntu/+source/mysql-dfsg-4.1/+bug/3448 but it appears upstream as well in Debian. The only workaround (until the Debian folks figure out what's wrong with their build) is to add "useServerPrepStmts=false" to your JDBC URL to tell the JDBC driver not to use server-side prepared statements.
[5 Mar 2006 15:33]
Christian Hammers
The example given in the Ubunto bug report works here.
I tested with unstable's 5.0.18 and stable's 4.1.11.
mysql> CREATE TABLE mytable ( id INT PRIMARY KEY, class VARCHAR(32) );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO mytable ( id, class ) VALUES ( 1, 'hallo' );
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO mytable ( id, class ) VALUES ( 2, 'hugo' );
Query OK, 1 row affected (0.00 sec)
mysql> PREPARE debug_stmt_3 FROM "select * from mytable where id=?"; /* conn id 0 */ SET @debug_stmt_param3_0='1';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
Query OK, 0 rows affected (0.00 sec)
mysql> EXECUTE debug_stmt_3 USING @debug_stmt_param3_0;
+----+-------+
| id | class |
+----+-------+
| 1 | hallo |
+----+-------+
1 row in set (0.00 sec)
Java works fine, too. Using the above class and the Code from the MySQL bug
report:
$ jikes -cp /usr/share/kaffe-common/lib/glibj.zip:. dbtest.java
$ kaffe -cp /usr/share/java/mysql.jar:. dbtest
hallo
bye,
-christian- <ch@debian.org>
Description: When I prepare a query with the following code and I enable logging to /var/log/mysql/myql.log, tomcat authentication and all my queries now fail. This is using Tomcat 5.5 and is happening after I upgraded from MySQL 4.0 to 4.1 on a Ubunutu (Debian) Breeze Linux system. The sql log indicates that the parameter substitution is not working now. [1] SELECT data FROM content WHERE id=? and lang=? All normal queries in the mysql client works fine. I have tried this with mysql-connector-java-3.1.12 and 5.0.0-beta and both have this problem. How to repeat: By creating a java application and running it under tomcat with this code PreparedStatement stmt = conn.prepareStatement("UPDATE content set data=? where id=? and lang=?"); stmt.setString(1,decode(data)); stmt.setString(2,id); stmt.setString(3,lang); Suggested fix: A workaround is to not use prepareStatement but then Tomcat uses prepareStatement for its internal authentication.