| Bug #9051 | Stored Functions: Database name in fully qualified name is case sensitive | ||
|---|---|---|---|
| Submitted: | 8 Mar 2005 15:20 | Modified: | 28 Feb 2007 23:24 |
| Reporter: | Disha | ||
| Status: | Verified | ||
| Category: | Server: SP | Severity: | S3 (Non-critical) |
| Version: | 5.0.12-beta-nt, 5.0.13-BK | OS: | Microsoft Windows (Windows Server 2003, XP, Linux) |
| Assigned to: | Alexander Nozdrin | Target Version: | |
| Triage: | Triaged: D3 (Medium) | ||
[8 Mar 2005 15:20]
Disha
[22 Apr 2005 15: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 9: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 16: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 21:18]
Miguel Solorzano
Bug: http://bugs.mysql.com/bug.php?id=15345 was marked as duplicate of this one.
[12 Oct 2006 16:12]
Andrey Hristov
bug##21848 is marked as duplicate of this one
[12 Oct 2006 16:12]
Andrey Hristov
bug#21848
[8 Jul 18: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 18: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.
