Bug #50653 drop procedure implicitely treats db name in a case sensitive way
Submitted: 27 Jan 2010 10:03 Modified: 31 Mar 2010 17:41
Reporter: Michael Skulsky Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0, 5.1, 5.5.99 OS:Any (MS Windows, Linux)
Assigned to: Luis Soares CPU Architecture:Any

[27 Jan 2010 10:03] Michael Skulsky 
Description:
During replication from Linux to Windows if database name is not all lower case then drop procedure works incorrectly.

How to repeat:
Configuration:
Mysql 5.0.67 on Linux, replication master
Mysql 5.0.89 on Windows XP, replication slave

Steps to reproduce:

1. Execute the following commands on master

create database TEST_db;
use TEST_db;
delimiter &
create definer=db59@localhost procedure test_proc()
begin
  select 1;
end
&
delimiter ;

2. Verify that both the database and the stored procedure have been created on master and slave. NOTE that database name have different capitalization: TEST_db on Linux and test_db on Windows.

3. On master now run the following commands:

use TEST_db;
drop procedure test_proc;

4. Result: 
- the procedure has been dropped on master, but still exists on slave.
- Issuing any other command on master causes replication to stop with the following error:

                 Last_Errno: 1305
                 Last_Error: Error 'PROCEDURE TEST_db.test_proc does not exist' on query. Default database: 'TEST_db'. Query: 'drop procedure test_proc'
- if we use "drop procedure if exists" then replication doesn't stop, but the procedure is not dropped nevertheless;
- the issue does not occur if database name is all lower case;
- the issue does not occur if database name is specified explicitely with correct capitalization, i.e. : drop procedure TEST_db.test_proc;
[27 Jan 2010 10:12] Michael Skulsky 
Please ignore definer specification, you don't need it to reproduce the problem.
[27 Jan 2010 10:58] Valeriy Kravchuk 
Looks like a duplicate of bug #37656. Please, check.
[27 Jan 2010 11:45] Michael Skulsky 
Judging by description yes, this looks very similar. As the fix is not yet in production, could you check if it really applies also to stored procedures?
[27 Jan 2010 20:26] MySQL Verification Team 
Could you please provide the my.cnf and my.ini files. Thanks in advance.
[28 Jan 2010 8:02] Michael Skulsky 
Here you are
[28 Jan 2010 9:31] Sveta Smirnova 
Thank you for the feedback.

Verified as described.

MTR test case:

--source include/master-slave.inc

create database TEST_db;
use TEST_db;
delimiter &;
create procedure test_proc()
begin
  select 1;
end
&
delimiter ;&

use TEST_db;
drop procedure test_proc;

sleep 1;

connection slave;
--vertical_results
show slave status;

select * from mysql.proc;

$cat src/tests/t/rpl_bug50653-slave.opt 
--lower_case_table_names=1
[31 Mar 2010 17:41] Luis Soares 
Marking this one as a duplicate of BUG#37656.

For what is worth, I have included in latest patch for BUG#37656 
a test case based on the one Sveta has posted (see: 
"[28 Jan 10:31] Sveta Smirnova").
[17 Dec 2010 12:57] Bugs System 
Pushed into mysql-trunk 5.6.1 (revid:georgi.kodinov@oracle.com-20101217125013-y8pb3az32rtbplc9) (version source revid:luis.soares@oracle.com-20101210150113-586io70ryd90b2p1) (merge vers: 5.6.1) (pib:24)