MySQL Bugs: #12417: Connection.prepareCall() is database name case sensitive

Bug #12417	Connection.prepareCall() is database name case sensitive
Submitted:	6 Aug 2005 20:01	Modified:	28 Feb 2007 22:24
Reporter:	Paul van Rossem	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
com.mysql.jdbc.Connection.prepareCall(String sql) is sensitive for the case used in the database name passed during connection creation.
This is strange since:
1) If the database name is case sensitive, it should already give an error during Connection creation, not later, only when calling a stored procedure, while all other kinds of database accesses succeed.
2) The SQLException generated during prepareCall() has as message text:
"FUNCTION MYPROCEDURE does not exist." This is wrong, it exists, but it cannot be found because of the wrong case in the database name.
3) MYPROCEDURE is not a function, but a procedure.

This bug did not exist in ConnectorJ 3.1.7. 
Using server 5.0.9 beta via TCP/IP.

How to repeat:
package nl.timeware.test;                 // note the case of Test instead test  
import java.sql.*;                             // in the second prepareCall()
public class TestApp
{ static public void main(String[] args)
   { try
     { Class.forName("com.mysql.jdbc.Driver");
        Connection connection = 
          DriverManager.getConnection("jdbc:mysql://localhost/test", "me","me");
        connection.prepareCall("{CALL MYPROCEDURE()}").execute();
        System.out.println("Success!"); 
        connection = 
        DriverManager.getConnection("jdbc:mysql://localhost/Test", "me", "me");
        connection.prepareCall("{CALL MYPROCEDURE()}").execute();  // throws
	System.out.println("Never gets here!"); 
      }
      catch(Exception exc)
      {  System.out.println("Exception: " + exc.getMessage()); 
      }
   }
}

Suggested fix:
If the case in the database name is important, an exception should be thrown during connect.
If it is not important, then Connection.prepareCall(String sql) should not dependent on this.

Technically a server bug, but I will work on a work-around for the driver (and fix the dubious error message). The reason that this worked prior to 3.1.8 is that the driver didn't respect the current catalog setting when asking for stored procedure metadata, which was a bug.

Now, it turns out that the server doesn't respect lower_case_tablenames for "SHOW CREATE PROCEDURE", but does for CALL, which is what causes this error:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.10a-beta

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show create procedure `Test`.`foo`;
ERROR 1305 (42000): PROCEDURE foo does not exist
mysql> show create procedure `test`.`foo`;
+-----------+----------+-----------------------------------------------------+
| Procedure | sql_mode | Create Procedure                                    |
+-----------+----------+-----------------------------------------------------+
| foo       |          | CREATE PROCEDURE `test`.`foo`()
begin
select 1;
end |
+-----------+----------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> call test.foo();
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call Test.foo();
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> \s
--------------
c:\mysql-servers\mysql-5.0.10a-beta-win32\bin\mysql  Ver 14.12 Distrib 5.0.10a-b
eta, for Win32 (ia32)
Connection id:          6
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.0.10a-beta
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 12 min 20 sec

Threads: 2  Questions: 37  Slow queries: 0  Opens: 0  Flush tables: 1  Open tabl
es: 2  Queries per second avg: 0.050
--------------

mysql>

Patch is at http://lists.mysql.com/internals/29043 and http://lists.mysql.com/internals/29048 

The code can be tested in tonight's nightly snapshot build which will be available after 00:00 GMT on the 31-Aug at http://downloads.mysql.com/snapshots.php#connector-j

Thanks for your bug report.

See also Bug#9051.
A possible fix is to make stored procedures cache in sync with --lowercase-table-names and
filesystem character set.

Still in 8.0.3.

mysql> SHOW CREATE PROCEDURE test.p \G
*************************** 1. row ***************************
           Procedure: p
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `p`()
begin end
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> call test.p;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE PROCEDURE Test.p \G
ERROR 1305 (42000): PROCEDURE p does not exist
mysql> call Test.p;
Query OK, 0 rows affected (0.00 sec)