| Bug #1592 | ResultSet.CONCUR_UPDATABLE and cross-database queries | ||
|---|---|---|---|
| Submitted: | 18 Oct 2003 6:15 | Modified: | 19 Nov 2003 11:31 |
| Reporter: | Teemu Kuulasmaa | ||
| Status: | Closed | ||
| Category: | Connector/J | Severity: | S2 (Serious) |
| Version: | Connector/J 3.0.9-stable | OS: | Microsoft Windows (WIN32, LINUX) |
| Assigned to: | Mark Matthews | Target Version: | |
[18 Oct 2003 8:17]
Mark Matthews
The error message is misleading, but it comes down to the fact that the JDBC driver can not create updatable result sets for queries that reference more than one table. The error message will be changed for the case where you use more than one database (this is currently not checked correctly). The JDBC spec states that a JDBC driver can allow updatable result sets to be restricted to queries that reference only one table, that table must have a primary key, and all parts of the primary key must be present in the table. Until MySQL has cursors, this is the only way to create 'safe' updatable result sets (ones that don't destroy data). Therefore, you can't use updatable result sets to update multiple tables at once.
[18 Oct 2003 8:20]
Mark Matthews
actually, now that I look at it, the error message can't be improved for MySQL servers older than 4.1.0, because the information about which database the table comes from is not returned to the client. I'll fix the case for 4.1.0 and newer, but unfortunately, this is the error message you will get for servers < 4.1.0 (I'll add something in the documentation about this restriction).
[18 Oct 2003 10:23]
Teemu Kuulasmaa
I agree your arguments, but those were not 100% relevant for my bug report. I use SELECT queries that select ALL the columns (also all primary keys) from the ONE table (example: "SELECT * FROM mysql.user"). JDBC driver doesn't allow to execute the query, if current database is different from the database to which the query is referred (CURRENT DATABASE="test", query database="mysql"). The error message is really annoying (java.sql.SQLException: General error, message from server: "Table 'test.user' doesn't exist"). Even if I am quering table "user" from "mysql" databse the error message claims that server can not find table "user" from database "test"!! Why sever doesn't try to use the database that was mentioned in the query?! The example query works if I create only READ_ONLY result sets. Why server/driver handles database notations differently depending on the result set type? Teemu
[18 Oct 2003 10:29]
Mark Matthews
Actually, it is pertinent to your bug, because for the same reasons I explained earlier, the JDBC driver can't figure out what database you are using when the query refers to a database that is not the 'current' one (mysql < 4.1.0 doesn't return that information to the client). So, you can either do a Connection.setCatalog() before you do your query to fix this, _or_ upgrade to MySQL 4.1.0 or newer.
[18 Oct 2003 13:23]
Teemu Kuulasmaa
It would be wonderful if I could overcome this issue by upgrading the server/driver. Unfortunately it didn't help. I tried MySQL 4.1.0-alpha with Connector/J 3.0.9 and 3.1.0. I got the same error message and query did not work! Should I use newer CVS snapshot?
[18 Oct 2003 20:38]
Mark Matthews
As a clarification of my first response; this is a bug that needs to be fixed in the driver, and the fix can only work with servers that are >= 4.1.0. The fix has not been made yet...It will be at least Wednesday before I can look into fixing this, so don't start looking for CVS snapshots until then.
[19 Nov 2003 11:31]
Mark Matthews
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Thank you for your bug report. This issue should now be fixed. You can test with a
snapshot build after 00:00 GMT Nov 20 at http://downloads.mysql.com/snapshots.php
Description: Hi JDBC driver doesn't allow cross-database SELECT-queries if statement's concurrency is ResultSet.CONCUR_UPDATABLE? Why? Is this real bug? I try to execute queries that select columns from tables from different databases. This works nicely if I don't try to execute queries trough a statement that has been created using ResultSet.CONCUR_UPDATABLE parameter! Only SELECT queries are affected. I can still execute INSERT, CREATE TABLE and UPDATE queries without problem. I am using MySQL 4.0.15/4.0.13 and Connector/J 3.0.9-stable. OS doesn't make difference. I use windows 2K sp4 and debian testing both as a client and server. Teemu How to repeat: Working example ( read-only result set ): Class.forName("com.mysql.jdbc.Driver").newInstance(); // Database "test" specified in connection URL Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test/?user=dummy&password=dummypa ss"); Statement stmt = conn.createStatement(); // ResultSet.CONCUR_READ_ONLY stmt.execute("SELECT * FROM mysql.user"); // Table: mysql.user, CURRENT_DB: test ResultSet rs = stmt.getResultSet(); -> returns all the columns from mysql.user table even if database "test" is specified in connection url. Non-working example ( updatable result set ): Class.forName("com.mysql.jdbc.Driver").newInstance(); // Database "test" specified in connection URL Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test/?user=dummy&password=dummypa ss"); Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); stmt.execute("SELECT * FROM mysql.user"); // Table: mysql.user, CURRENT_DB: test ResultSet rs = stmt.getResultSet(); -> java.sql.SQLException: General error, message from server: "Table 'test.user' doesn't exist" at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1825) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1020) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1109) at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1070) at com.mysql.jdbc.Connection.execSQL(Connection.java:2027) at com.mysql.jdbc.Connection.execSQL(Connection.java:1984) at com.mysql.jdbc.Statement.executeQuery(Statement.java:1152) at com.mysql.jdbc.DatabaseMetaData.getPrimaryKeys(DatabaseMetaData.java:2561) at com.mysql.jdbc.UpdatableResultSet.checkUpdatability(UpdatableResultSet.java:2057) at com.mysql.jdbc.UpdatableResultSet.<init>(UpdatableResultSet.java:107) at com.mysql.jdbc.MysqlIO.buildResultSetWithRows(MysqlIO.java:1306) at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:287) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1156) at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1070) at com.mysql.jdbc.Connection.execSQL(Connection.java:2027) at com.mysql.jdbc.Connection.execSQL(Connection.java:1984) at com.mysql.jdbc.Statement.execute(Statement.java:896) at JDBCTest.main(JDBCTest.java:25)