[MDEV-5844] Case sensitive problem with routines SHOW <P/F> STATUS; Created: 2014-03-13  Updated: 2015-04-02

Status: Open
Project: MariaDB Server
Component/s: Admin statements
Affects Version/s: 5.5, 10.0, 10.1
Fix Version/s: 10.1

Type: Bug Priority: Minor
Reporter: Guillermo Bonvehi Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: procedure, show, status, upstream, verified
Environment:

Linux (and probably all case sensitive file systems)


Attachments: File bug.72029.callproc.sql     File bug.72029.sql    

 Description   

When working with a case sensitive file-system, MariaDB (it also fails in 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;



 Comments   
Comment by Guillermo Bonvehi [ 2014-03-13 ]

This shows a worse scenario where SHOW CREATE PROCEDURE shows the incorrect procedure.
Note that I also reported this on MySQL: http://bugs.mysql.com/?id=72029

Comment by Guillermo Bonvehi [ 2014-03-13 ]

Calling procedures is also affected even specifying database

PS: Thanks for tiding up the ticket

Comment by Elena Stepanova [ 2015-04-02 ]

It's been a year, the upstream bug is still in 'verified'.

Generated at Thu Feb 08 07:07:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.