Bug #33813 Schema names are case-sensitive in DROP FUNCTION

• Submitted: 11 Jan 2008 0:40
• Modified: 13 Mar 2009 20:29
• Reporter: Todd Farmer (OCA)
• Status: Closed
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 5.0.52
• OS: Any
• Assigned to: Georgi Kodinov
• CPU Architecture: Any
• Tags: regression

[11 Jan 2008 0:40] Todd Farmer

Description:
Since MySQL 5.0.52, DROP FUNCTION commands taking schema-qualified function names compare the schema name on a case-sensitive basis, even when lower_case_table_names is set to 1.  This means that functions are not correctly identified.

How to repeat:
DROP DATABASE IF EXISTS `TESTDB`;
CREATE DATABASE `TESTDB`;

USE `TESTDB`;
DELIMITER //

CREATE FUNCTION test_fn() RETURNS INTEGER
BEGIN
DECLARE rId bigint;
RETURN rId;
END
//

DELIMITER ;

DROP FUNCTION IF EXISTS `TESTDB`.`test_fn`;
SHOW WARNINGS;
DROP FUNCTION IF EXISTS `testdb`.`test_fn`;

DELIMITER //

Suggested fix:
Make schema name comparison case-insensitive, at least when lower_case_table_names is set to 1 or 2.

[11 Jan 2008 0:42] Todd Farmer

mysql> DROP DATABASE IF EXISTS `TESTDB`;
Query OK, 0 rows affected (0.00 sec)

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

mysql>
mysql> USE `TESTDB`;
Database changed
mysql> DELIMITER //
mysql>
mysql> CREATE FUNCTION test_fn() RETURNS INTEGER
    -> BEGIN
    -> DECLARE rId bigint;
    -> RETURN rId;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql> DROP FUNCTION IF EXISTS `TESTDB`.`test_fn`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+---------------------------------+
| Level | Code | Message                         |
+-------+------+---------------------------------+
| Note  | 1305 | FUNCTION test_fn does not exist |
+-------+------+---------------------------------+
1 row in set (0.00 sec)

At this point, executing CREATE FUNCTION `TESTDB`.test_fn() ... generates an error, since the DROP FUNCTION command didn't match the appropriate function to be dropped, and the function still exists.

[11 Jan 2008 0:52] Todd Farmer

Possible workarounds include:

1) DROP/CREATE function without qualifying the schema name (issue USE db statement to set default schema).
2) Ensure that the qualified schema name matches the stored schema letter-case.

[5 Aug 2008 1:01] Marc ALFF

Executed the following test script on a platform where lower_case_table_name=0.
against 5.0.68, 5.1.28 and 6.0.7

test script:

select version();
show variables like "lower_case_file_system";
show variables like "lower_case_table_names";

--disable_warnings
DROP DATABASE IF EXISTS `TESTDB`;
--enable_warnings

CREATE DATABASE `TESTDB`;
USE `TESTDB`;
DELIMITER //;

CREATE FUNCTION test_fn() RETURNS INTEGER
BEGIN
  DECLARE rId bigint;
  RETURN rId;
END
//

DELIMITER ;//

select db, name from mysql.proc;

DROP FUNCTION IF EXISTS `TESTDB`.`test_fn`;
SHOW WARNINGS;

DELIMITER //;

CREATE FUNCTION test_fn() RETURNS INTEGER
BEGIN
  DECLARE rId bigint;
  RETURN rId;
END
//

DELIMITER ;//

select db, name from mysql.proc;
DROP FUNCTION IF EXISTS `testdb`.`test_fn`;
SHOW WARNINGS;

result file:
select version();
version()
5.0.68-debug-log
show variables like "lower_case_file_system";
Variable_name   Value
lower_case_file_system  OFF
show variables like "lower_case_table_names";
Variable_name   Value
lower_case_table_names  0
DROP DATABASE IF EXISTS `TESTDB`;
CREATE DATABASE `TESTDB`;
USE `TESTDB`;
CREATE FUNCTION test_fn() RETURNS INTEGER
BEGIN
DECLARE rId bigint;
RETURN rId;
END
//
select db, name from mysql.proc;
db      name
TESTDB  test_fn
DROP FUNCTION IF EXISTS `TESTDB`.`test_fn`;
SHOW WARNINGS;
Level   Code    Message
CREATE FUNCTION test_fn() RETURNS INTEGER
BEGIN
DECLARE rId bigint;
RETURN rId;
END
//
select db, name from mysql.proc;
db      name
TESTDB  test_fn
DROP FUNCTION IF EXISTS `testdb`.`test_fn`;
Warnings:
Note    1305    FUNCTION test_fn does not exist
SHOW WARNINGS;
Level   Code    Message
Note    1305    FUNCTION test_fn does not exist

In all cases, the reported behavior could not be reproduced.

Please clarify:
- if the bug reported can still be observed,
- the value of
  - select version();
  - show variables like "lower_case_file_system";
  - show variables like "lower_case_table_names";
for the failing platform,
and please also provide the result of:
- select db, name from mysql.proc.

Changing to need feedback

Thanks

[5 Aug 2008 3:40] Todd Farmer

Same result, using Windows (case-insensitive platform by default) and 5.0.66a:

C:\WINDOWS\system32>mysql -uroot -P3310
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.66a-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> DROP DATABASE IF EXISTS `TESTDB`;
Query OK, 0 rows affected (0.03 sec)

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

mysql>
mysql> USE `TESTDB`;
Database changed
mysql> DELIMITER //
mysql>
mysql> CREATE FUNCTION test_fn() RETURNS INTEGER
    -> BEGIN
    -> DECLARE rId bigint;
    -> RETURN rId;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql> DROP FUNCTION IF EXISTS `TESTDB`.`test_fn`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+---------------------------------+
| Level | Code | Message                         |
+-------+------+---------------------------------+
| Note  | 1305 | FUNCTION test_fn does not exist |
+-------+------+---------------------------------+
1 row in set (0.00 sec)

mysql> DROP FUNCTION IF EXISTS `testdb`.`test_fn`;
Query OK, 0 rows affected (0.00 sec)

[5 Aug 2008 3:44] Todd Farmer

C:\WINDOWS\system32>mysql -uroot -P3310
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.66a-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> DROP DATABASE IF EXISTS `TESTDB`;
Query OK, 0 rows affected (0.00 sec)

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

mysql>
mysql> USE `TESTDB`;
Database changed
mysql> DELIMITER //
mysql>
mysql> CREATE FUNCTION test_fn() RETURNS INTEGER
    -> BEGIN
    -> DECLARE rId bigint;
    -> RETURN rId;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql> SELECT db, name FROM mysql.proc;
+--------+---------+
| db     | name    |
+--------+---------+
| testdb | test_fn |
+--------+---------+
1 row in set (0.00 sec)

mysql>

[10 Feb 2009 9:59] 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/65712

2731 Georgi Kodinov	2009-02-10
      Bug #33813: Schema names are case-sensitive in DROP FUNCTION
      
      The parser was not using the correct fully-qualified-name
      production for DROP FUNCTION.
      Fixed by copying the production from DROP PROCEDURE.
      Tested in the windows specific suite to make sure it's 
      tested on a case-insensitive file system.
      modified:
        mysql-test/r/windows.result
        mysql-test/t/windows.test
        sql/sql_yacc.yy

[12 Feb 2009 13:35] 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/66034

2742 Georgi Kodinov	2009-02-12
      Bug #33813: Schema names are case-sensitive in DROP FUNCTION
      
      Additional fix:
       1. Revert the unification of DROP FUNCTION
      and DROP PROCEDURE, because DROP FUNCTION can be used to
      drop UDFs (that have a non-qualified name and don't require
      database name to be present and valid).
       2. Fixed the case sensitivity problem by adding a call to 
      check_db_name() (similar to the sp_name production).
      modified:
        sql/sql_yacc.yy

[14 Feb 2009 13:00] Bugs System

Pushed into 6.0.10-alpha (revid:[e-mail]-20090212211028-y72faag15q3z3szy) (version source revid:[e-mail]-20090212211028-y72faag15q3z3szy) (merge vers: 6.0.10-alpha) (pib:6)

[19 Feb 2009 13:03] Bugs System

Pushed into 6.0.10-alpha (revid:[e-mail]-20090218125737-5y5b2xo3duo1wlvo) (version source revid:[e-mail]-20090213182753-tsqikd41gkwowrqq) (merge vers: 6.0.10-alpha) (pib:6)

[27 Feb 2009 20:55] Paul DuBois

Noted in 6.0.10 changelog.

For DROP FUNCTION with names that were qualified with a database
name, the database name was handled in case-sensitive fashion even
with lower_case_table_names set to 1.

Setting report to NDI pending push into 5.0.x/5.1.x.

[9 Mar 2009 14:12] Bugs System

Pushed into 5.0.79 (revid:[e-mail]-20090309135922-a0di9ebkxoj4d4wv) (version source revid:[e-mail]-20090213125529-oznlhyq5c50ej39f) (merge vers: 5.0.79) (pib:6)

[9 Mar 2009 14:50] Paul DuBois

Noted in 5.0.79 changelog.

Setting report to NDI pending push into 5.1.x.

[13 Mar 2009 19:04] Bugs System

Pushed into 5.1.33 (revid:[e-mail]-20090313111355-7bsi1hgkvrg8pdds) (version source revid:[e-mail]-20090218093153-sjzxf01i4ezte0xp) (merge vers: 5.1.33) (pib:6)

[13 Mar 2009 20:29] Paul DuBois

Noted in 5.1.33 changelog.

[9 May 2009 16:41] Bugs System

Pushed into 5.1.34-ndb-6.2.18 (revid:[e-mail]-20090508185236-p9b3as7qyauybefl) (version source revid:[e-mail]-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)

[9 May 2009 17:39] Bugs System

Pushed into 5.1.34-ndb-6.3.25 (revid:[e-mail]-20090509063138-1u3q3v09wnn2txyt) (version source revid:[e-mail]-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)

[9 May 2009 18:36] Bugs System

Pushed into 5.1.34-ndb-7.0.6 (revid:[e-mail]-20090509154927-im9a7g846c6u1hzc) (version source revid:[e-mail]-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)