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

[6 Mar 2008 10:19] Davide Ferrari
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)
[6 Mar 2008 10:24] Davide Ferrari
Improving the synopsis
[6 Mar 2008 21:02] Sveta Smirnova
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.
[7 Mar 2008 9:28] Davide Ferrari
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.
[7 Mar 2008 16:14] Davide Ferrari
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
[12 Mar 2008 19:19] Sveta Smirnova
Thank you for the feedback.

Could you also please provide mysqld error log and Cluster log files.
[13 Mar 2008 9:48] Davide Ferrari
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)
[13 Mar 2008 22:08] Sveta Smirnova
Thank you for the feedback.

This looks like duplicate of bug #33158 for me.