| Bug #32265 | Server returns different metadata if prepared statement is used | ||
|---|---|---|---|
| Submitted: | 11 Nov 2007 19:23 | Modified: | 15 Mar 2008 11:01 |
| Reporter: | Franklin Schmidt | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Prepared statements | Severity: | S3 (Non-critical) |
| Version: | 5.0.50pb | OS: | Any |
| Assigned to: | Davi Arnaut | CPU Architecture: | Any |
[13 Nov 2007 12:47]
Tonci Grgin
Java test case
Attachment: TestBug32265.java (text/x-java), 1.76 KiB.
[13 Nov 2007 12:50]
Tonci Grgin
Hi Franklin and thanks for your report. I will try to explain what I think is happening here. Server is missing metadata, since setting "useCursorFetch=true", and then calling setFetchSize(>0) will actually create a server-side prepared statement behind the scenes, and use cursor-based fetching with it which probably ends up creating a temporary table on the server. Let me clarify this with general query log: 071113 13:43:52 12 Connect root@localhost on test 12 Query SHOW SESSION VARIABLES 12 Query SHOW COLLATION 12 Query SET NAMES utf8 12 Query SET character_set_results = NULL 12 Query SET autocommit=1 12 Query SET sql_mode='STRICT_TRANS_TABLES' 12 Query SELECT VERSION() 12 Query DROP TABLE IF EXISTS bug32265 12 Query CREATE TABLE bug32265 (Id INTEGER NOT NULL PRIMARY KEY) 12 Query INSERT INTO bug32265 (Id) values (1) 12 Query SELECT * FROM bug32265 12 Prepare [1] SELECT * FROM bug32265 12 Execute [1] SELECT * FROM bug32265 12 Query DROP TABLE IF EXISTS bug32265 12 Quit
[11 Feb 2008 16:17]
Davi Arnaut
FWIW, attached test case succeeds with connector/j 5.0.4 and fails with later versions.
[11 Feb 2008 18:17]
Tonci Grgin
Davi, I fail to see the relevance... Every connector team leader is free to work or not work around server bug we described here. He is even free to drop a workaround appearing to be functional and wait for proper fix. My guess would be this came in packet of changes in 5.0.5...
[11 Feb 2008 19:30]
Konstantin Osipov
Connector team workaround: use result set metadata from PREPARE phase in such case, it contains the original table name.
[14 Feb 2008 19:42]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/42303 ChangeSet@1.2578, 2008-02-14 17:42:46-02:00, davi@mysql.com +2 -0 Bug#32265 Server returns different metadata if prepared statement is used Executing a prepared statement associated with a materialized cursor yields to the client a metadata packet with wrong table and database names. The problem was occurring because the server was sending the the name of the temporary table used by the cursor instead of the table name of the original table. The same problem occurs when selecting from views, in which case the table name was being sent and not the name of the view. The solution is to backup the table and database names of the fields of the original tables or views and restore then later into the fields of the temporary table.
[20 Feb 2008 13:57]
Konstantin Osipov
Approved a slightly different solution. Please put back to In Progress if the suggested solution is not correct.
[20 Feb 2008 19:45]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/42691 ChangeSet@1.2580, 2008-02-20 16:45:24-03:00, davi@mysql.com +2 -0 Bug#32265 Server returns different metadata if prepared statement is used Executing a prepared statement associated with a materialized cursor yields to the client a metadata packet with wrong table and database names. The problem was occurring because the server was sending the the name of the temporary table used by the cursor instead of the table name of the original table. The same problem occurs when selecting from views, in which case the table name was being sent and not the name of the view. The solution is to fill the list item from the temporary table but preserving the table and database names of the original fields. This is achieved by tweaking the Select_materialize to accept a pointer to the Materialized_cursor class which contains the item list to be filled.
[20 Feb 2008 20:40]
Davi Arnaut
Queued in 5.0-runtime
[26 Feb 2008 10:56]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/42979 ChangeSet@1.2578, 2008-02-26 13:55:46+03:00, kostja@dipika.(none) +1 -0 Use an API instead of looking into stmt internals to fetch fields (Test for Bug#32265)
[3 Mar 2008 18:14]
Bugs System
Pushed into 5.0.58
[3 Mar 2008 18:18]
Bugs System
Pushed into 6.0.5-alpha
[3 Mar 2008 18:18]
Bugs System
Pushed into 5.1.24-rc
[15 Mar 2008 11:01]
Jon Stephens
Documented bugfix in the 5.0.58, 5.1.24, and 6.0.5 changelogs as follows:
Executing a prepared statement associated with a materialized
cursor sent to the client a metadata packet with incorrect table
and database names. The problem occurred because the server
sent the the name of the temporary table used by the cursor
instead of the table name of the original table.
The same problem occured when selecting from a view, in which case the
name of the table name was sent, rather than the name of the view.
[31 Mar 2008 14:36]
Jon Stephens
Also noted fix in the 5.1.23-ndb-6.3.11 changelog.
Description: If I have a ResultSet on table 't' with field 's', then I can call ResultSet.getString("t.s"). But if I first call Statement.setFetchSize() then this field isn't found. How to repeat: import java.sql.*; import java.util.Properties; class FetchBug { static final String url = "jdbc:mysql://localhost/test"; static final String user = ""; static final String password = ""; public static void main(String[] args) throws Exception { Class.forName("org.gjt.mm.mysql.Driver"); Properties props = new Properties(); props.setProperty("useCursorFetch","true"); props.setProperty("user",user); props.setProperty("password",password); Connection con = DriverManager.getConnection(url,props); Statement stmt = con.createStatement(); stmt.executeUpdate( "drop table if exists t" ); stmt.executeUpdate( "create table t (i int)" ); stmt.executeUpdate( "insert into t set i=1" ); ResultSet rs = stmt.executeQuery( "select * from t" ); rs.next(); rs.getInt("t.i"); // ok rs.close(); stmt.setFetchSize(10); rs = stmt.executeQuery( "select * from t" ); rs.next(); rs.getInt("t.i"); // fails rs.close(); stmt.close(); con.close(); } }