MySQL Bugs: #35101: Table already exists error when the table does not exist

Bug #35101	Table already exists error when the table does not exist
Submitted:	6 Mar 2008 10:19	Modified:	13 Mar 2008 22:08
Reporter:	Davide Ferrari	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.23a-maria-alpha	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	CREATE TABLE, ndb

Description:
It seems that in latest Mysql 5.1 version there's a bug with the table creation routine. I'm getting duplicate table errors when it's not true at all, because the table DOESN'T exist, it is a just created database.
I'm using Mysql with NDB cluster activated, and for most table namespaces, it works well (creating ndb, innodb or myisam tables)

How to repeat:
mysql> drop table `recovery`;
ERROR 1051 (42S02): Unknown table 'recovery'

mysql> CREATE TABLE `recovery` (   `log_id` int(11) NOT NULL,   `vlogin` varchar(255) NOT NULL ) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1;
ERROR 1050 (42S01): Table 'recovery' already exists

mysql> CREATE TABLE `recovery` (   `log_id` int(11) NOT NULL,   `vlogin` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ERROR 1050 (42S01): Table 'recovery' already exists

mysql> CREATE TABLE `recovery` (   `log_id` int(11) NOT NULL,   `vlogin` varchar(255) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
ERROR 1050 (42S01): Table 'recovery' already exists

mysql> show tables like 'rec%';
Empty set (0.00 sec)

Improving the synopsis

Thank you for the report.

I can not repeat described behavior with current development sources. Please indicate package you use (file name) or if you compile from source please provide compile string. Also please provide your error log file.

We are using 

MySQL-server-community-maria-5.1.23a-0.glibc23.x86_64.rpm

(as I put in the version field) because the day I downloaded it, it was the DEFAULT downlaod for Mysql *5.1*. Now I see there's a dedicated "Maria alpha" download section and the 5.1 now again points to the standard 5.1.23 version. I'll try this version before following with this bug.

Ok, now I'm using

MySQL-server-5.1.23-0.glibc23.x86_64.rpm

and still the same exact problem. In the error log there's nothing interesting. Is there a way to improve error log or to see some log of this error?
Maybe it's because I'm using NDB and I already have > 600 tables in this database?
These are my config.ini options:

[ndbd default]
NoOfReplicas=2    # Number of replicas
DataMemory=6G    # How much memory to allocate for data storage
IndexMemory=6G   # How much memory to allocate for index storage
MaxNoOfOrderedIndexes = 4096
MaxNoOfTables=800
MaxNoOfUniqueHashIndexes=512
MaxNoOfAttributes=9000

Thank you for the feedback.

Could you also please provide mysqld error log and Cluster log files.

Ok, i found the problem! It seems that this version of Mysql is case INSENSITIVE on CREATE TABLE tablename and case SENSITIVE on DROP, SELECT etc etc.
The error I'm experiencing is because I had in the same DB another "recover" table spelt in capitals, `RECOVERY`.

Take a look, now I'm trying to create `RECOVERY` with `recovery` already present

mysql> show tables like 'REC%';
Empty set (0.01 sec)

mysql> show tables like 'rec%';
+---------------------------+
| Tables_in_database (rec%) |
+---------------------------+
| recovery                  |
+---------------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `RECOVERY`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `RECOVERY` (
    ->   `log_id` int(11) NOT NULL auto_increment,
    ->   `vlogin` varchar(255) NOT NULL default '0',
    ->   `sql` varchar(255) NOT NULL default '0',
    ->   `sql_param` varchar(255) default NULL,
    ->   `auto_conn_tran` char(1) NOT NULL default '0',
    ->   `transaction_id` bigint(20) NOT NULL default '0',
    ->   `request_id` bigint(20) default NULL,
    ->   `exec_status` char(1) NOT NULL default '0',
    ->   `exec_time` bigint(20) default NULL,
    ->   `update_count` int(11) default NULL,
    ->   `completion_log_id` bigint(20) NOT NULL default '0',
    ->   PRIMARY KEY  (`log_id`)
    -> ) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1;
ERROR 1050 (42S01): Table 'RECOVERY' already exists

mysql> drop table RECOVERY;
ERROR 1051 (42S02): Unknown table 'RECOVERY'

mysql> drop table recovery;
Query OK, 0 rows affected (0.60 sec)

mysql> CREATE TABLE `RECOVERY` (   `log_id` int(11) NOT NULL auto_increment,   `vlogin` varchar(255) NOT NULL default '0',   `sql` varchar(255) NOT NULL default '0',   `sql_param` varchar(255) default NULL,   `auto_conn_tran` char(1) NOT NULL default '0',   `transaction_id` bigint(20) NOT NULL default '0',   `request_id` bigint(20) default NULL,   `exec_status` char(1) NOT NULL default '0',   `exec_time` bigint(20) default NULL,   `update_count` int(11) default NULL,   `completion_log_id` bigint(20) NOT NULL default '0',   PRIMARY KEY  (`log_id`) ) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.98 sec)

Thank you for the feedback.

This looks like duplicate of bug #33158 for me.