MySQL Bugs: #71408: Orphans in I

Bug #71408	Orphans in I_S.routines
Submitted:	17 Jan 2014 12:45	Modified:	24 Jan 2014 8:46
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.6.15	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have an orphaned entry in I_S.ROUTINES.  Restarting MySQL does not remove it.

How to repeat:
SHOW DATABASES;
/* returns

Database            
--------------------
information_schema 
ixtest              
mysql               
performance_schema  
test
*/

SELECT SPECIFIC_NAME, ROUTINE_SCHEMA FROM information_schema.ROUTINES;
* returns

SPECIFIC_NAME  ROUTINE_SCHEMA  
-------------  ----------------
PROC           TEST2           
*/

/* Note that there is no 'TEST2' database and there is also no 'TEST2' folder in datadir (C:\ProgramData\mysql\MySQL Server 5.6\data) .. so I am not able to understand where this is stored */

This occured while preparing the test case for http://bugs.mysql.com/bug.php?id=71407. I had restarted the server several times with different settings for lower_case_table_names and created databases and routines and dropped databases. I cannot reproduce the exact sequence of operations. But obviously the 'TEST2' database once existed, was dropped, but records in I_S of the routine inside it was not.

Very likely closely related to bug71047

Suggested fix:
No idea.

The data comes from the `mysql`.`proc` table.  Maybe that table was corrupt after a crash.

Or did anybody ever restore or modify that table directly? 

Appears the information_schema.routines table is not "live" in the sense that it can get out of sync easily.

Thanks for pointing out the position of storage - even though I definitely should have figured out myself. This enabled me to get rid of the orphan.

To my best knowledge there was no crash. I stopped and started the server multiple times using NET STOP|START command.  Sometimes I changed lower_case_table_names setting in my.ini between "1" and "2" in between stopping and starting server.

My assumption/suspection is that if
1) server is started with one setting for lower_case_table_names.
2) a database is created specifying its name in UPPERCASE and a routine is created inside it.
3) server is started with another setting for lower_case_table_names.
4) the database is dropped
.. there will be cases where the routine still exits - but orphaned as the database, where it belongs, is gone. And with reference to the other bug report, I linked to, this could be because the server does not identify the routine as belonging to the database when the database is dropped (as it checks for belongings to a database named using another lettercase). And thus the routine remains in the system tables.

Sorry .. I meant "very likely closely realted to bug71407"

Hello Peter,

Thank you for the bug report.
mysql.proc is of MyISAM engine, so can be easily corrupted; manual intervention is possible, but forbidden and hence may cause these kind of issues.
Imho - max that MySQL server can do is to print warning/message in the error log file about this inconsistency. I'm marking this as feature request.

Thanks,
Umesh

//// Manual steps to repeat
//// Start mysqld with default lower_case_table_names=0

# bin/mysqld_safe --defaults-file=./my.cnf --lower_case_table_names=0 --general-log --user=root &

SHOW STATUS LIKE 'lower_case_table_names';
CREATE DATABASE TEST2;
USE TEST2;

DELIMITER |
CREATE PROCEDURE proc1()
BEGIN
  CREATE TABLE PROC (c1 INT);
END|
DELIMITER ;

SHOW DATABASES;
SELECT SPECIFIC_NAME, ROUTINE_SCHEMA FROM information_schema.ROUTINES;

## Kill mysqld 
## Start mysqld with --lower_case_table_names=1
# bin/mysqld_safe --defaults-file=./my.cnf --lower_case_table_names=1 --general-log --user=root &

--error 1008 
## DROP DATABASE TEST2;

mysql> DROP DATABASE TEST2;
ERROR 1008 (HY000): Can't drop database 'test2'; database doesn't exist
mysql> DROP DATABASE test2;
ERROR 1008 (HY000): Can't drop database 'test2'; database doesn't exist

## SHOW DATABASES;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TEST2              |
| mysql              |
| performance_schema |
| test               |
+--------------------+

## SELECT SPECIFIC_NAME, ROUTINE_SCHEMA FROM information_schema.ROUTINES;

mysql> SELECT SPECIFIC_NAME, ROUTINE_SCHEMA FROM information_schema.ROUTINES;
+---------------+----------------+
| SPECIFIC_NAME | ROUTINE_SCHEMA |
+---------------+----------------+
| proc1         | TEST2          |
+---------------+----------------+

// Remove schema TEST2 manually
// # rm -rf /tmp/master/TEST2/

//// Orphan entry in mysql.proc

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> SELECT SPECIFIC_NAME, ROUTINE_SCHEMA FROM information_schema.ROUTINES;
+---------------+----------------+
| SPECIFIC_NAME | ROUTINE_SCHEMA |
+---------------+----------------+
| proc1         | TEST2          |
+---------------+----------------+
1 row in set (0.00 sec)