Bug #72029 Case sensitive problem with routines (PROCEDURE/FUNCTION)
Submitted: 13 Mar 2014 6:55 Modified: 17 Mar 2014 19:29
Reporter: Guillermo Bonvehi Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:5.5.32, 5.6.16 OS:Linux
Assigned to: CPU Architecture:Any
Tags: procedure case sensitive
Triage: Needs Triage: D3 (Medium)

[13 Mar 2014 6:55] Guillermo Bonvehi
Description:
When working with a case sensitive file-system, MySQL allows you to have a database with the same name in different cases (example TEST/test).
This works correctly.
However, procedures that are related to that database are stored inside information_schema.routines associated with ROUTINE_SCHEMA which is a case insensitive column.
This brings a problem, for example, when using mysqldump since when listing routines to dump in the line "SHOW %s STATUS WHERE Db = '%s'" it would bring procedures from other databases that have the same name with different case.

Thanks,
Guillermo Bonvehi

How to repeat:
CREATE DATABASE A;
USE A;

delimiter //

CREATE PROCEDURE simpleproc()
    BEGIN
       SELECT 1;
    END//

delimiter ;
CREATE DATABASE a;
USE a;

SHOW PROCEDURE STATUS WHERE Db = 'a';

DROP DATABASE A;
DROP DATABASE a;

-- This should show none procedures for 'a'.

Suggested fix:
Make columns in information_schema which should be case aware use utf8_bin collation.
[13 Mar 2014 7:35] Guillermo Bonvehi
When working with a case sensitive file-system, MySQL allows you to have a database with the same name in different cases (example TEST/test).
This works correctly.
However, procedures that are related to that database are stored inside information_schema.routines associated with ROUTINE_SCHEMA which is a case insensitive column.
This brings a problem, for example, when using mysqldump since when listing routines to dump in the line "SHOW %s STATUS WHERE Db = '%s'" it would bring procedures from other databases that have the same name with different case.
mysqldump could be modified to use mysql.proc instead which correctly has utf8_bin in the Db column, but I'm not sure that would be the best to go since this affects anyone using SHOW <P/F> STATUS.

CREATE DATABASE A;
USE A;

delimiter //

CREATE PROCEDURE simpleproc()
    BEGIN
       SELECT 1;
    END//

delimiter ;
CREATE DATABASE a;
USE a;

SHOW PROCEDURE STATUS WHERE Db = 'a';
-- Incorrectly shows procedure from A
SELECT count(*) FROM mysql.proc WHERE Db='a';
-- Correctly show 0 results

DROP DATABASE A;
DROP DATABASE a;

Thanks again,
Guillermo Bonvehi
[13 Mar 2014 7:57] Guillermo Bonvehi
Worse case, SHOW CREATE PROCEDURE shows incorrect procedure

Attachment: bug.72029.sql (application/octet-stream, text), 459 bytes.

[13 Mar 2014 8:25] Guillermo Bonvehi
Calling procedures is also affected even specifying database

Attachment: bug.72029.callproc.sql (application/octet-stream, text), 538 bytes.

[13 Mar 2014 9:04] Peter Laursen
See also the bug I reported at http://bugs.mysql.com/bug.php?id=71407
[14 Mar 2014 13:25] Hartmut Holzgraefe
"mysqldump --routines A" will dump the procedure correctly,
"mysqldump --routines a" will fail with:

--
-- Dumping routines for database 'a'
--
mysqldump: Couldn't execute 'SHOW CREATE PROCEDURE `simpleproc`': PROCEDURE simpleproc does not exist (1305)
[17 Mar 2014 19:29] Sveta Smirnova
Thank you for the report.

Verified as described: `mysqldump --routines a` fails