MySQL Bugs: #15121: a faulty procedure is executed but no exception is thrown

Bug #15121	a faulty procedure is executed but no exception is thrown
Submitted:	22 Nov 2005 0:10	Modified:	27 Jan 2006 10:55
Reporter:	Roland Bouman	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0	OS:	NA
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
A faulty stored procedure can be called from the JDBC driver. The call does not throw an exception, whereas the procedure fails with an error when executed using the command line tool.
It was expected that the JDBC driver would throw an SQLException with the same error message and code as is observed at the command line:

How to repeat:
on the command line, do:

use test;

create procedure p_idonotexist()
begin
    select * from idonotexist;
end;

call p_idonotexist();

This returns:
ERROR 1146 (42S02): Table 'test.idonotexist' doesn't exist

Now, from the java environment, do:

Class.forName("com.mysql.jdbc.Driver").newInstance();
java.sql.Connection conn = java.sql.DriverManager.getConnection(
    "jdbc:mysql://localhost:3306/?user=root&password=mysqlroot"
);

try {
    System.out.println(1);
    java.sql.CallableStatement s = conn.prepareCall(
        "{call test.p_idonotexist()}"
    );
    System.out.println(2);
    s.execute()
    System.out.println(3);
} catch (SQLException sqlException){
    System.out.println(4);
    sqlException.printStackTrace();
    System.out.println(5);
}

gives me this output:

1
2
3

indicating that no exception is returned. Similar results are seen using this URL:

java.sql.Connection conn = java.sql.DriverManager.getConnection(
    "jdbc:mysql://localhost:3306/mysql?user=root&password=mysqlroot"
);

However, if the test is repeated with a modified connectionstring that has a 
database specified in which the procedure resides, the Exception is thrown:

java.sql.Connection conn = java.sql.DriverManager.getConnection(
    "jdbc:mysql://localhost:3306/test?user=root&password=mysqlroot"
);

1
2
4
5

Suggested fix:
Throw an exception similar to the one seen on the command line

I was not able to reproduce the bug using jdbc:mysql://localhost:3306/test?user=root

I've got the follow exception:

java.sql.SQLException: Table 'test.idonotexist' doesn't exist

But If I use jdbc:mysql://localhost:3306/mysql?user=root I don't have any exception. I would like to notice The table "idonotexist" is not anywhere.
My test case is attached.

Test case

Attachment: Bug15121.java (text/java), 771 bytes.

It's not entirely clear to me...should I do something? I mean, I don't see any attachment or something like that

Roland,

Vasily's note was intended for the developer who will be fixing this (me). 

One question, do you happen to have a stored procedure with the same name in a different database?

Thanks for clearing that up, sorry to bother you about that.

To answer your question: I only have one such procedure:

test.p_idonotexist

Seems to be a server bug. When you prefix the stored procedure name with the catalog (database) the procedure exists in (as the JDBC driver does), the procedure returns without error, but instead a warning:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.17-debug |
+--------------+
1 row in set (0.00 sec)

mysql> call `test`.p_testBug15121();
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Error | 1146 | Table 'test.idonotexist' doesn't exist |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)

However, if you change to the current database to the one the procedure is declared in, and execute it you get an error:

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> call p_testBug15121();
ERROR 1146 (42S02): Table 'test.idonotexist' doesn't exist

I can't repeat this from the mysql client in 5.0.19.