Bug #72029 Case sensitive problem with routines (PROCEDURE/FUNCTION)

• Submitted: 13 Mar 2014 6:55
• Modified: 17 Mar 2014 19:29
• Reporter: Guillermo Bonvehi
• Status: Verified
• Category: MySQL Server: Information schema
• Severity: S2 (Serious)
• Version: 5.5.32, 5.6.16
• OS: Linux
• CPU Architecture: Any
• Tags: procedure case sensitive

[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