| 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: | |
| 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
[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.
