Bug #9051 Stored Functions: Database name in fully qualified name is case sensitive
Submitted: 8 Mar 2005 14:20 Modified: 28 Feb 2007 22:24
Reporter: Disha Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.12-beta-nt, 5.0.13-BK OS:Windows (Windows Server 2003, XP, Linux)
Assigned to: CPU Architecture:Any

[8 Mar 2005 14:20] Disha
Description:
If the case of the datbase name is changed in a fully qualified function call, the function call fails stating an error that the function does not exist.

How to repeat:
Repro Steps:
Run the following SQL queries:

1. DROP FUNCTION IF EXISTS FN1//
2. Create Function FN1() returns int
	Return 1//
3. use test//
4. SELECT DB1.FN1()//
5. SELECT DB1.FN1()//
6. SELECT DB1.FN1()//
7. SELECT db1.fn1()//
8. SELECT DB1.FN1()//

Expected Result: The FUNCTION call to FN1 at step# 4,5,6 should execute successfully without any errors.

Actual Results: The function call with 'DB1' as database name fails until we reference it once with 'db1'

	mysql> DROP DATABASE IF EXISTS db1//
	Query OK, 0 rows affected (0.02 sec)

	mysql> DROP DATABASE IF EXISTS test//
	Query OK, 0 rows affected (0.00 sec)

	mysql> Create database db1//
	Query OK, 1 row affected (0.00 sec)

	mysql> Create database test//
	Query OK, 1 row affected (0.00 sec)

	mysql> use db1//
	Database changed
	
	mysql> DROP FUNCTION IF EXISTS FN1//
	Query OK, 0 rows affected, 1 warning (0.00 sec)

	mysql> Create Function FN1() returns int
	    ->  Return 1//
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> use test//
	Database changed
	
	mysql> SELECT DB1.FN1()//
	ERROR 1305 (42000): FUNCTION DB1.FN1 does not exist
	
	mysql> SELECT DB1.FN1()//
	ERROR 1305 (42000): FUNCTION DB1.FN1 does not exist
	
	mysql> SELECT DB1.FN1()//
	ERROR 1305 (42000): FUNCTION DB1.FN1 does not exist

	mysql> SELECT db1.fn1()//
	+-----------+
	| db1.fn1()  | 
	+-----------+
	|         1     |
	+-----------+
	1 row in set (0.00 sec)

	mysql> SELECT DB1.FN1()//
	+-----------+
	| DB1.FN1() |
	+-----------+
	|         1     |
	+-----------+
	1 row in set (0.00 sec)
[22 Apr 2005 13:01] Per-Erik Martin
This is an odd side-effect of SP caching and the fact that the database
    names are case sensitive on some (Unix-like) systems when reading from disk,
    which is not standard compliant. Once cached, name lookups are done in
    a case insensitive way. A work-around would be awkward, since we can't
    make routine names case sensitive.
    (The same thing happens for procedures of course, not just functions.)
    I don't think this should be worked around, the case sensitivity of
    database names should be fixed instead.
[14 Sep 2005 7:11] Valeriy Kravchuk
The problem still exists, verified on XP and Linux (Fedora Core 1):

mysql> create database db1//
Query OK, 1 row affected (0,00 sec)

mysql> use db1//
Database changed
mysql> Create Function FN1() returns int Return 1//
Query OK, 0 rows affected (0,00 sec)

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> select DB1.FN1()//
ERROR 1305 (42000): FUNCTION DB1.FN1 does not exist
mysql> select DB1.FN1()//
ERROR 1305 (42000): FUNCTION DB1.FN1 does not exist
mysql> select DB1.FN1()//
ERROR 1305 (42000): FUNCTION DB1.FN1 does not exist
mysql> select db1.fn1()//
+-----------+
| db1.fn1() |
+-----------+
|         1 |
+-----------+
1 row in set (0,00 sec)

mysql> select DB1.FN1()//
+-----------+
| DB1.FN1() |
+-----------+
|         1 |
+-----------+
1 row in set (0,00 sec)

mysql> select version()//
+-------------------+
| version()         |
+-------------------+
| 5.0.13-beta-debug |
+-------------------+
1 row in set (0,00 sec)

I changes severity to S3 also bacause of possible workarounds.
[1 Aug 2006 14:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9894

ChangeSet@1.2238, 2006-08-01 16:45:22+02:00, andrey@lmy004. +26 -0
  Fix for bug#9051 Functions: Database name in fully qualified Function call is case sensitive
  
  The SP code did not respect the value of the startup option --lower-case-table-names.
  Namely, on platforms with filesystems (even on *nix with mounted FAT or others) which are by their
  nature case insensitive, the lookup was case sensitive.
  Example:
  create database MY_DATABASE;
  create procedure MY_DATABASE.MY_PROC1();
  --echo "This will yield an error"
  call my_database.my_proc1();
  --echo "This will work"
  call MY_DATABASE.MY_PROC1();
  
  In addition, this patch fixes a problem of the SP cache which used character set with
  case insensitive collation. This problem is some kind of shadowing, which has no
  security implications only because the SP cache is per connection.
  Example:
  create database miXed_case_DATA_base;
  create procedure miXed_case_DATA_base.proc1() select "miXed_case_DATA_base.proc1";
  --echo "Let's create a database with the same name but different case"
  create database mixed_case_data_BASE;
  --echo "Now a procedure in the newly created database"
  create procedure mixed_case_data_BASE.proc1() select "mixed_case_data_BASE.proc1";
  --echo "This will yield correct result. Will call the first procedure we created"
  call miXed_case_DATA_base.proc1();
  --echo "This will lead to an error. It will compile and run the first procedure and not the one we want"
  call mixed_case_data_BASE.proc1();
  
  
  #9051 is solved by adding additional column to mysql.proc (this means that the users will have to
  fix their privilege tables with scripts/mysql_fix_privilege_tables.sh)
  which is a copy of `db` but has case insensitive collation which is needed to handle --lower-case-table-names=2
  According to our documentation in this mode the tables are created with the originally supplied name but
  the lookup is with lowercased version. This can't work for routines without having a column with case insensitive
  collation. In the case of tables this is handled by the file system
  
  The second bug is fixed by changing the charset of the per thread SP cache from having case insensitive collation
  to have case sensitive collation.
[10 Aug 2006 19:18] MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=15345 was marked as duplicate of
this one.
[12 Oct 2006 14:12] Andrey Hristov
bug##21848 is marked as duplicate of this one
[12 Oct 2006 14:12] Andrey Hristov
bug#21848
[8 Jul 2009 16:17] Ingo Strüwing
This problem affects the cross-platform compatibility of backup image files (see also WL#4771).

The problem seems to be related to lower_case_table_names.

I will attach a test case. It works fine (on Linux) if run with --lower_case_table_names=0. It fails if run (on Linux) with
--lower_case_table_names=1.

The test case does also proves Pem's suspect wrong that stored procedures are also affected. Fully qualified stored procedures are found with any letter case combination, while fully qualified functions are not always found.

Please consider to re-triage with higher priority.
[8 Jul 2009 16:19] Ingo Strüwing
Test case showing letter case problems for functions but not for procedures

Attachment: bug9051-1.test (application/octet-stream, text), 4.06 KiB.

[19 Mar 2010 20:36] Adam Cain
With Lower case table names set to a 0 or 1 I get the error shown below on latest 5.1 and 5.5 versions of mysql.

CREATE SCHEMA Bug1;
CREATE SCHEMA Bug2;
USE Bug1;
DROP FUNCTION IF EXISTS Bug1.TestFunction; CREATE FUNCTION Bug1.TestFunction(iAnyNumber INT) RETURNS INT
    DETERMINISTIC
RETURN 1;
USE Bug2;
DROP VIEW IF EXISTS Bug2.vwServiceMatrix; 
CREATE VIEW CarrierConnect.vwServiceMatrix AS SELECT Bug1.TestFunction(1);

The error I get is on the CREATE VIEW
FUNCTION Bug1.TestFunction does not exist
[22 Mar 2010 12:11] Adam Cain
Sorry I meant 1 not 0 or 1.
[22 Mar 2010 12:15] Adam Cain
it also fails on a 2. That only leaves the option of 0 which is heavily warned against. I believe the bug should be elevated to critical.
[27 Sep 2016 12:27] Peter Laursen
What happened here? This report is stil 'verified', though some note above tells that a patch is ready. Was the path never pushed?

We just had a report from a customer hiting a very similar (if not the same issue) with MySQL 5.6.33.
[27 Sep 2016 14:08] Ståle Deraas
Posted by developer:
 
Hi Peter,

Seems that the patch was never approved and pushed. This was in 5.0 times, it seems.
[13 Nov 2017 0:14] Federico Razzoli
This bug is still in 8.0.3.