Bug #15345 Using lower_case_table_names=2 and stored procedure is created error
Submitted: 30 Nov 2005 10:42 Modified: 10 Aug 2006 19:01
Reporter: Carl Pretorius Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.16/5.0.17 BK OS:Wndows XP
Assigned to: Assigned Account CPU Architecture:Any

[30 Nov 2005 10:42] Carl Pretorius
Description:
When lower_case_table_names=2 is used and a stored procedure is created using CREATE PROCEDURE Blah.. , then none of the tools (query browser, administrator) "sees" the stored proc. The stored procedure's record in the mysql.proc table always contains the lowercase for the db column. 

How to repeat:
1) Set lower_case_table_names=2 in the instance configuration.
2)  In mysql console:
create database Sakila;
use Sakila;
DROP TABLE IF EXISTS `Yadda`;
CREATE TABLE `Yadda` (
  `Yadda_Id`    int(10) unsigned not null auto_increment,
  `Name`  varchar(45) default null,
  PRIMARY KEY  (`Yadda_Id`)
) ENGINE=InnoDB;

DELIMITER //
DROP PROCEDURE IF EXISTS `Blah`//
CREATE PROCEDURE `Blah`(iYaddId int)
BEGIN
END
//
DELIMITER ;

3) Run MySQL Query Browser
4) Tables are listed, not the stored procs.

5) Edit the mysql.proc table directly, change the db column value from 'sakila' to 'Sakila'.
6) Procs are seen now.
[30 Nov 2005 13:26] Miguel Solorzano
C:\mysql5017>bin\mysql --defaults-file=c:\mysql5017\s5017.ini -uroot -p --prompt="win5017>"
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.17-nt-max

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

win5017>show variables like "%lower%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 2     |
+------------------------+-------+
2 rows in set (0.02 sec)

win5017>create database Sakila;
Query OK, 1 row affected (0.00 sec)

win5017>use Sakila;
Database changed
win5017>DROP TABLE IF EXISTS `Yadda`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

win5017>CREATE TABLE `Yadda` (
    ->   `Yadda_Id`    int(10) unsigned not null auto_increment,
    ->   `Name`  varchar(45) default null,
    ->   PRIMARY KEY  (`Yadda_Id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

win5017>
win5017>DELIMITER //
win5017>DROP PROCEDURE IF EXISTS `Blah`//
Query OK, 0 rows affected, 1 warning (0.00 sec)

win5017>CREATE PROCEDURE `Blah`(iYaddId int)
    -> BEGIN
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

win5017>DELIMITER ;
win5017>select db, name from mysql.proc where db = "sakila";
+--------+------+
| db     | name |
+--------+------+
| sakila | Blah |
+--------+------+
1 row in set (0.02 sec)

win5017>show create procedure sakila.blah\G
*************************** 1. row ***************************
       Procedure: blah
        sql_mode:
Create Procedure: CREATE PROCEDURE `blah`(iYaddId int)
BEGIN
END
1 row in set (0.00 sec)

win5017>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Sakila             |
| db7                |
| db8                |
| db9                |
| mintrab            |
| mysql              |
| p4                 |
| test               |
+--------------------+
9 rows in set (0.00 sec)

win5017>
[10 Aug 2006 19:01] Jim Winstead
This is a duplicate of Bug #9051.