| Bug #61150 | First call to stored procedure fails with "No Database Selected" | ||
|---|---|---|---|
| Submitted: | 12 May 2011 12:37 | Modified: | 30 Jun 2011 17:44 |
| Reporter: | Akash Kayal | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 5.1.13 | OS: | Windows (XP SP3) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | catalogue, stored procedure | ||
[12 May 2011 16:00]
Valeriy Kravchuk
What exact version of server, 5.x.y, do you use?
[12 May 2011 16:52]
Akash Kayal
I'm using 5.0.41-community-nt output for: SHOW VARIABLES LIKE "%version%"; "version" "5.0.41-community-nt" "version_comment" "MySQL Community Edition (GPL)" "version_compile_machine" "ia32" "version_compile_os" "Win32"
[13 May 2011 12:44]
Tonci Grgin
Akash, I do not get how this is S2? Have you checked the definitions? "Severe loss of service"?
[13 May 2011 13:41]
Akash Kayal
My bad, S3 seems a better fit.
[23 May 2011 7:57]
Tonci Grgin
Akash, what happens if you use I__S (useInformationSchema=true)?
[23 May 2011 8:43]
Akash Kayal
I'm still getting the same error with "useInformationSchema=true". [Modified URL = jdbc:mysql://127.0.0.1?useInformationSchema=true]
[23 May 2011 10:22]
Tonci Grgin
Akash, I am sorry but my tests against latest trunk show no problem...
JVM settings and URL:
-Xmx1024M -XX:+UseParallelGC -Dcom.mysql.jdbc.java6.javac=C:\jvms\jdk1.6.0\bin\javac.exe -Dcom.mysql.jdbc.java6.rtjar=C:\jvms\jdk1.6.0\jre\lib\rt.jar -Dcom.mysql.jdbc.testsuite.url.default=jdbc:mysql://solaris:3306/?user=root&password=***
//NO database
createDatabase("TST1");
createProcedure("TST1.PROC", "(x int, out y int)\n"
+ "begin\n"
+ "declare z int;\n"
+ "set z = x+1, y = z;\n" + "end\n");
CallableStatement cStmt = null;
cStmt = this.conn.prepareCall("{call `TST1`.`PROC`(?, ?)}");
cStmt.setInt(1, 5);
cStmt.registerOutParameter(2, Types.INTEGER);
cStmt.execute();
assertEquals(6, cStmt.getInt(2));
cStmt.clearParameters();
cStmt.close();
//SetCatalog
this.conn.setCatalog("TST1");
cStmt = null;
cStmt = this.conn.prepareCall("{call TST1.PROC(?, ?)}");
cStmt.setInt(1, 5);
cStmt.registerOutParameter(2, Types.INTEGER);
cStmt.execute();
assertEquals(6, cStmt.getInt(2));
cStmt.clearParameters();
cStmt.close();
//Change catalog
this.conn.setCatalog("mysql");
cStmt = null;
cStmt = this.conn.prepareCall("{call `TST1`.`PROC`(?, ?)}");
cStmt.setInt(1, 5);
cStmt.registerOutParameter(2, Types.INTEGER);
cStmt.execute();
assertEquals(6, cStmt.getInt(2));
cStmt.clearParameters();
cStmt.close();
Connected to 5.1.31-log
java.vm.version : 1.5.0_17-b04
java.vm.vendor : Sun Microsystems Inc.
java.runtime.version : 1.5.0_17-b04
os.name : Windows Server 2008
os.version : null
sun.management.compiler : HotSpot Client Compiler
General query log:
1 Query /* mysql-connector-java-5.1.17-SNAPSHOT ( Revision: mark.matthews@oracle.com-20110421143026-z5yedbvt5m64j8m8 ) */SELECT @@session.auto_increment_increment
1 Query SHOW COLLATION
1 Query SET character_set_results = NULL
1 Query SET autocommit=1
1 Query SET sql_mode='STRICT_TRANS_TABLES'
1 Query SELECT VERSION()
1 Query DROP DATABASE IF EXISTS TST1
1 Query CREATE DATABASE TST1
1 Query DROP PROCEDURE IF EXISTS TST1.PROC
1 Query CREATE PROCEDURE TST1.PROC (x int, out y int)
begin
declare z int;
set z = x+1, y = z;
end
1 Query SELECT name, type, comment FROM mysql.proc WHERE name like 'PROC' and db <=> 'TST1' ORDER BY name
1 Query SHOW CREATE PROCEDURE `TST1`.`PROC`
1 Query CALL `TST1`.`PROC`(5, @com_mysql_jdbc_outparam_y)
1 Query SELECT @com_mysql_jdbc_outparam_y
1 Query USE `TST1`
1 Query SELECT name, type, comment FROM mysql.proc WHERE name like 'PROC' and db <=> 'TST1' ORDER BY name
1 Query SHOW CREATE PROCEDURE `TST1`.`PROC`
1 Query CALL TST1.PROC(5, @com_mysql_jdbc_outparam_y)
1 Query SELECT @com_mysql_jdbc_outparam_y
1 Query USE `mysql`
1 Query SELECT name, type, comment FROM mysql.proc WHERE name like 'PROC' and db <=> 'TST1' ORDER BY name
1 Query SHOW CREATE PROCEDURE `TST1`.`PROC`
1 Query CALL `TST1`.`PROC`(5, @com_mysql_jdbc_outparam_y)
1 Query SELECT @com_mysql_jdbc_outparam_y
1 Query USE ``
1 Query USE `mysql`
1 Query USE ``
1 Query USE `mysql`
1 Quit
[23 May 2011 11:22]
Akash Kayal
Here is the query log, I am still seeing the problem after altering my code to something similar to yours. As you can in lines 32-35, there is a USE `tst1` query which is immediately followed by the USE ``, which might be the problem.
I see that you are using C/J 5.1.17 for your tests. Would it be possible to try with 5.1.16? I'll be really glad to know if it is already fixed in 5.1.17.
E:\Programs\MySQL5.1\bin\mysqld, Version: 5.1.48-community-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time Id Command Argument
110523 16:39:19 1 Connect root@localhost on
1 Query SELECT @@sql_mode
1 Query SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
1 Query SET NAMES utf8
110523 16:39:21 2 Connect root@localhost on
2 Query SELECT @@sql_mode
2 Query SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
2 Query SET NAMES utf8
2 Quit
110523 16:39:33 3 Connect root@localhost on
3 Query /* mysql-connector-java-5.1.16 ( Revision: ${bzr.revision-id} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
3 Query /* mysql-connector-java-5.1.16 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment
3 Query SHOW COLLATION
3 Query SET character_set_results = NULL
3 Query SET autocommit=1
3 Query drop database if exists tst1
3 Query create database tst1
3 Query DROP PROCEDURE IF EXISTS tst1.proc
3 Query CREATE PROCEDURE tst1.proc (x int, out y int)
begin
declare z int;
set z = x+1, y=z;
end
3 Query SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS'
3 Query SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS'
3 Query SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS'
3 Query SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS'
3 Query SELECT name, type, comment FROM mysql.proc WHERE name like 'proc' and db <=> 'tst1' ORDER BY name
3 Query USE `tst1`
3 Query USE ``
3 Query USE `tst1`
3 Query USE ``
3 Query SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS'
3 Query SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS'
3 Query SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS'
3 Query SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS'
3 Query SELECT name, type, comment FROM mysql.proc WHERE name like 'proc' and db <=> 'tst1' ORDER BY name
3 Query USE `tst1`
3 Query SELECT DATABASE()
3 Query USE `tst1`
3 Query SHOW CREATE PROCEDURE `tst1`.`proc`
3 Query CALL `tst1`.`proc` (5, @com_mysql_jdbc_outparam_y)
3 Query SELECT @com_mysql_jdbc_outparam_y
3 Query SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS'
3 Query SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS'
3 Query SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS'
3 Query SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS'
3 Query SELECT name, type, comment FROM mysql.proc WHERE name like 'proc' and db <=> 'tst1' ORDER BY name
3 Query USE `tst1`
3 Query SELECT DATABASE()
3 Query USE `tst1`
3 Query SHOW CREATE PROCEDURE `tst1`.`proc`
3 Query CALL `tst1`.`proc` (5, @com_mysql_jdbc_outparam_y)
3 Query SELECT @com_mysql_jdbc_outparam_y
3 Quit
[23 May 2011 11:46]
Tonci Grgin
Akash, lines 32-35 are not the problem, you can see them in my log too. The problem I see is that your test case doesn't match your actual code. AFAIS, you are using I__S so I will test that now. As for 5.1.17, it's just bumped version, there are no actual changes pushed yet. 5.1.16 should do just fine.
[23 May 2011 12:23]
Tonci Grgin
Works for me with I__S too... There must be something else.
[24 May 2011 14:27]
Bogdan Degtyariov
Tonci, YOUR test case failed for me. To make it fail don't use MySQL Server 5.5, use 5.1.
[24 May 2011 14:29]
Tonci Grgin
Tested against 5.1.31x64 on remote OpenSolaris host.
[24 May 2011 14:47]
Bogdan Degtyariov
For me it doesn't even come to CALL, stops on USE ``: 110525 0:42:42 3 Connect dbs2@w7x64 on 3 Query /* mysql-connector-java-5.1-nightly-20110524 ( Revision: mark.matthews@oracle.com-20110421143026-z5yedbvt5m64j8m8 ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect' 3 Query /* mysql-connector-java-5.1-nightly-20110524 ( Revision: mark.matthews@oracle.com-20110421143026-z5yedbvt5m64j8m8 ) */SELECT @@session.auto_increment_increment 3 Query SHOW COLLATION 3 Query SET NAMES latin1 3 Query SET character_set_results = NULL 3 Query SET autocommit=1 3 Query SET sql_mode='STRICT_TRANS_TABLES' 3 Query drop database if exists TST1 3 Query create database TST1 3 Query DROP PROCEDURE IF EXISTS TST1.PROC 3 Query CREATE PROCEDURE TST1.PROC(x int, out y int) begin declare z int; set z = x+1, y = z; end 110525 0:42:43 3 Query SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS' 3 Query SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS' 3 Query SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS' 3 Query SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS' 3 Query SELECT name, type, comment FROM mysql.proc WHERE name like 'PROC' and db <=> 'tst1' ORDER BY name 3 Query USE `tst1` 3 Query USE `` 3 Query USE `tst1` 3 Query USE `` java.sql.SQLException: No database selected at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619) at com.mysql.jdbc.ConnectionImpl.setCatalog(ConnectionImpl.java:5083) at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1535) at com.mysql.jdbc.DatabaseMetaData.getProcedureOrFunctionColumns(DatabaseMetaData.java:4253) at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:4094) at com.mysql.jdbc.DatabaseMetaDataUsingInfoSchema.getProcedureColumns(DatabaseMetaDataUsingInfoSchema.java:1305) at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:845) at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:626) at com.mysql.jdbc.CallableStatement.getInstance(CallableStatement.java:519) at com.mysql.jdbc.ConnectionImpl.parseCallableStatement(ConnectionImpl.java:3986) at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:4070) at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:4044) at TestStoredProcedureFailure.main(TestStoredProcedureFailure.java:29)
[24 May 2011 15:02]
Bogdan Degtyariov
Strange why it fails in MySQL 5.1 and works ok in 5.5. Will check tomorrow
[24 May 2011 15:14]
Tonci Grgin
5.5 has full support for PARAMETERS (in form of I__S table) so it takes a different execution path. However, test case works for me on 5.1.31 server. I suggest you to look closer into what's happening on server side of the story. There might be a bug here but I'd really need to see it before I can do anything else.
[31 May 2011 8:48]
Bogdan Degtyariov
Tonci,
Your Solaris host has case sensitive table names and "SELECT DATABASE()", which fails for me is never executed for you:
if (this.conn.lowerCaseTableNames() && catalog != null
&& catalog.length() != 0) {
.....
// NOT executed for Solaris/Linux
paramRetrievalStmt.executeQuery("SELECT DATABASE()");
rs.next();
....
}
Please try MySQL 5.1 Windows version.
[31 May 2011 12:59]
Tonci Grgin
Seemingly, the workaround introduced in DatabaseMetaData.getCallStmtParameterTypes to fix the bug in server where SHOW CREATE PROCEDURE was not respecting lower-case table names is misbehaving when connection is not attached to database and on non-casesensitive OS. Scope of the problem is thus very very limited and does not represent a problem due to fact that: o It's easy to work around it writing proper code. o There are many OS on the market. However, it should be fixed.
[30 Jun 2011 17:44]
Tonci Grgin
Pushed up to revision 1065.
Description: The first call to a stored procedure fails with a "No Database Selected" exception. However, subsequent calls run perfectly fine. This happens when database name is NOT part of the URL but is appended to the stored procedure name itself. Also, before making the call if database name is set explicitly using connection.setCatalogue(), the call works fine. The last C/J version I tested with in which it worked fine is 5.1.7. I have tested with 5.1.13 and 5.1.16, this doesn't work with either. All tests were run on Win XP SP3. How to repeat: Sample procedure: DELIMITER // DROP PROCEDURE IF EXISTS `test`.`sp_test` // CREATE PROCEDURE `test`.`sp_test`(IN something varchar(32)) BEGIN select something; END// DELIMITER ; Sample code: public static void main(String[] args) { try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e1) { e1.printStackTrace(); } String url = "jdbc:mysql://127.0.0.1/"; Connection conn = null; try { conn = DriverManager.getConnection(url, "root", "sa"); } catch (SQLException e1) { e1.printStackTrace(); } CallableStatement cstmt = null; for(int i = 0; i < 3; i++) { try { cstmt = conn.prepareCall("{ CALL TEST.SP_TEST (?)}"); cstmt.setString(1, "test"); cstmt.execute(); System.out.println("Execution : " + i); } catch (SQLException sqle) { sqle.printStackTrace(); } finally { if(cstmt != null) try { cstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } } try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } Exception trace: java.sql.SQLException: No database selected at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620) at com.mysql.jdbc.ConnectionImpl.setCatalog(ConnectionImpl.java:5116) at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1528) at com.mysql.jdbc.DatabaseMetaData.getProcedureOrFunctionColumns(DatabaseMetaData.java:4246) at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:4087) at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:845) at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:626) at com.mysql.jdbc.JDBC4CallableStatement.<init>(JDBC4CallableStatement.java:47) at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:513) at com.mysql.jdbc.Util.handleNewInstance(Util.java:407) at com.mysql.jdbc.CallableStatement.getInstance(CallableStatement.java:522) at com.mysql.jdbc.ConnectionImpl.parseCallableStatement(ConnectionImpl.java:4008) at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:4092) at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:4066) at TestAddScreen.main(TestStoredProc.java:41)