MySQL Bugs: #50628: Object names not accessible via case sensitive reference on lctn=1 systems

Bug #50628	Object names not accessible via case sensitive reference on lctn=1 systems
Submitted:	26 Jan 2010 15:46	Modified:	26 Jan 2010 16:51
Reporter:	Chuck Bell	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.5, 6.0,5.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If a system has lctn=1 and the user creates a function (or any object) in uppercase, the system fails to locate the object using the name. However, if one issues another operation, e.g. GRANT, on that object, the object is magically made accessible.

The object should be accessible by its original name.

TEST CASE
---------
CREATE DATABASE DB1;
CREATE FUNCTION DB1.F1(a int) RETURNS INT RETURN a;

SELECT DB1.F1(0);  ---> fails

GRANT EXECUTE ON FUNCTION DB1.F1 TO '';

SELECT DB1.F1(0);  ---> succeeds

DROP DATABASE DB1;

How to repeat:
TEST RESULTS
------------
The above test case was run against mysql-next-mr and mysql-6.0-codebase. The results show the code is causing the first call of DB1.F1 to fail.

Server version: 5.5.99-m3-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql> CREATE DATABASE DB1;
Query OK, 1 row affected (0.00 sec)

mysql> create function DB1.F1(a int) returns int return a;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DB1.F1(0);
ERROR 1305 (42000): FUNCTION DB1.F1 does not exist

mysql> grant execute on function DB1.F1 TO '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DB1.F1(0);
+-----------+
| DB1.F1(0) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

mysql> 

Server version: 6.0.14-alpha-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql> CREATE DATABASE DB1;
Query OK, 1 row affected (0.00 sec)

mysql> create function DB1.F1(a int) returns int return a;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DB1.F1(0);
ERROR 1305 (42000): FUNCTION DB1.F1 does not exist

mysql> grant execute on function DB1.F1 TO '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DB1.F1(0);
+-----------+
| DB1.F1(0) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

mysql> 

Suggested fix:
Unknown

Thank you for the bug report.

C:\DBS>c:\dbs\6.0\bin\mysql -uroot --port=3600 --prompt="mysql 6.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.12-alpha-Win X64 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 6.0 > CREATE DATABASE DB1;
Query OK, 1 row affected (0.00 sec)

mysql 6.0 > CREATE FUNCTION DB1.F1(a int) RETURNS INT RETURN a;
Query OK, 0 rows affected (0.04 sec)

mysql 6.0 > SELECT DB1.F1(0);
ERROR 1305 (42000): FUNCTION DB1.F1 does not exist
mysql 6.0 > GRANT EXECUTE ON FUNCTION DB1.F1 TO '';
Query OK, 0 rows affected (0.02 sec)

mysql 6.0 > SELECT DB1.F1(0);
+-----------+
| DB1.F1(0) |
+-----------+
|         0 |
+-----------+
1 row in set (0.03 sec)

mysql 6.0 >

REFINED PROBLEM DESCRIPTION
---------------------------
SELECT DB1.F1() fails because function can not be located in mysql.proc table using upper case name. The problem lies in database name, because it is stored in the db colum of the table as 'db1' and that column uses case sensitive collation utf8_bin. 

Both SELECT db1.F1() and SELECT db1.f1() work OK, because name column of mysql.proc uses case insensitive collation.

The lookup for function in mysql.proc table is done inside db_find_routine_aux() function (sp.cc:492) which is called during execution of SELECT DB1.F1() like this:

> #0  db_find_routine_aux (thd=0xa30a3b8, type=1, name=0xb7354518, table=0xa314b10) at sp.cc:492
> #1  0x08492a8a in db_find_routine (thd=0xa30a3b8, type=1, name=0xb7354518, sphp=0xb7354514) at sp.cc:550
> #2  0x08492fcf in sp_cache_routine (thd=0xa30a3b8, type=1, name=0xb7354518, sp=0xb7354514) at sp.cc:1820
> #3  0x08307b48 in open_and_process_routine (thd=0xa30a3b8, prelocking_ctx=0xa30b314, rt=0xa305eb8, prelocking_strategy=0xb7354670, need_prelocking=0xb73545ab) at sql_base.cc:3842
> #4  0x0830e2ad in open_tables (thd=0xa30a3b8, start=0xb7354644, counter=0xb735462c, flags=0, prelocking_strategy=0xb7354670) at sql_base.cc:4265
> #5  0x0830e624 in open_and_lock_tables_derived (thd=0xa30a3b8, tables=0x0, derived=true, flags=0, prelocking_strategy=0xb7354670) at sql_base.cc:4789
> #6  0x082c18db in open_and_lock_tables_derived (thd=0xa30a3b8, tables=0x0, derived=true, flags=0) at ../../sql/mysql_priv.h:1571
> #7  0x082c1918 in open_and_lock_tables (thd=0xa30a3b8, tables=0x0) at ../../sql/mysql_priv.h:1581
> #8  0x082b4747 in execute_sqlcom_select (thd=0xa30a3b8, all_tables=0x0) at sql_parse.cc:4925
> #9  0x082b6272 in mysql_execute_command (thd=0xa30a3b8) at sql_parse.cc:2152
> #10 0x082bec84 in mysql_parse (thd=0xa30a3b8, inBuf=0xa305d90 "SELECT DB1.F1(0)", length=16, found_semicolon=0xb7355e78) at sql_parse.cc:5974

The test backup_xpfm_compat_restore_lctn1 was changed to match the current behavior of the server. When this bug is fixed, this change must be removed. This was changed in the mysql-backup-backport tree.

The test backup_xpfm_compat_restore_lctn1 was changed to match the current behavior of the server. When this bug is fixed, this change must be removed. This was changed in the mysql-backup-backport tree.

The test backup_xpfm_compat_restore_lctn1 was changed to match the current behavior of the server. When this bug is fixed, this change must be removed. This was changed in the mysql-backup-backport tree.

The test backup_xpfm_compat_restore_lctn2 has the same issues and has been changed to accept the current behavior. This test must also be changed once this patch is fixed.