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:
None 
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 12:37] Akash Kayal
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)
[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.