MySQL Bugs: #33158: NDB table name problem(sensitive/insensitive)

Bug #33158	NDB table name problem(sensitive/insensitive)
Submitted:	12 Dec 2007 7:38	Modified:	3 May 2010 15:33
Reporter:	li zhou	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-6.3	OS:	Any
Assigned to:	Martin Skold	CPU Architecture:	Any
Tags:	5.1.22-ndb-6.3.7

Description:
I don't konw if the ndb table'name is sensitive in linux.

   * We can't create table "T1" after we have a table "t1" in the  same database. That seems ndb look "t1" and "T1" as same table  name(insensitive). The error message is "Table 'T1' already exists".

   * We can't drop table "T1" when we have table "t1" in the same  database. Tha seems ndb look "t1" and "T1" as different table  name(sensitive).

   Although "Table 'T1' already exists", why we can't drop it?

In MYISAM, we can create "T1" after we have "t1". MYISAM is case sensitive in the same mysqld in linux. 

How to repeat:
All the operations is execute in the same mysqld in linux

mysql> create table t1(a int) engine ndb;
Query OK, 0 rows affected (0.50 sec)

mysql> create table T1(a int) engine ndb;
ERROR 1050 (42S01): Table 'T1' already exists

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

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

mysql> drop table t1;
Query OK, 0 rows affected (0.50 sec)

mysql> create table t1(a int) engine myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> create table T1(a int) engine myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| T1             |
| t1             |
+----------------+
2 rows in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table T1;
Query OK, 0 rows affected (0.01 sec)

Suggested fix:
According document "http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html"

We should keep case consistent in one kind of platform.

Thank you for the report.

Verified as described.

Workaround: use lower_case_table_names=1

Bug #35101 was marked as duplicate of this one.

Will be this bug fixed? It's a regression/behaviour change from 5.0 (or other tablespaces), Thanks for the workaround but we rely on case sensitiveness in our DB so we cannot migrate to ndb.

I found that RENAME TABLE is case sensitive on Linux, but CREATE TABLE is case insensitive.

This leads to the possibility to create the same table name with different cases anyway... (and allows a workaround for dropping a table when there are more than one table with the same name but different cases).

Here is the output on linux:
SHOW VARIABLES LIKE '%case%';
Variable_name	Value
lower_case_file_system	OFF
lower_case_table_names	0
create table TABLEA (a INT) engine = ndb;
create table tablea (a INT) engine = ndb;
ERROR 42S01: Table 'tablea' already exists
create table TABLEB (a INT) engine = ndb;
SHOW TABLES;
Tables_in_test
TABLEA
TABLEB
rename table TABLEA to tablea;
rename table TABLEB to TableA;
SHOW TABLES;
Tables_in_test
TableA
tablea

Warning in master.err:
[Warning] NDB_SHARE: ./test/TABLEA already exists  use_count=1. Moving away for safety, but possible memleak.

(on Mac OS X, seems more consistent.)
SHOW VARIABLES LIKE '%case%';
Variable_name	Value
lower_case_file_system	ON
lower_case_table_names	2
create table TABLEA (a INT) engine = ndb;
create table tablea (a INT) engine = ndb;
ERROR 42S01: Table 'tablea' already exists
create table TABLEB (a INT) engine = ndb;
SHOW TABLES;
Tables_in_test
TABLEA
TABLEB
rename table TABLEA to tablea;
ERROR 42S01: Table 'tablea' already exists
rename table TABLEB to TableA;
ERROR 42S01: Table 'TableA' already exists
SHOW TABLES;
Tables_in_test
TABLEA
TABLEB

I think this is the same problem as bug#37719, where the problem is wrong charset in the table hash (using a case insensitive charset for a case sensitive setting of lowercase_table_names).

short diff of the fix for bug#37719:
-  if (hash_init(&archive_open_tables, system_charset_info, 32, 0, 0,
+  if (hash_init(&archive_open_tables, table_alias_charset, 32, 0, 0,

(Assigned to Martin after discussion)

=== modified file 'sql/ha_ndbcluster.cc'
--- sql/ha_ndbcluster.cc        2010-02-09 13:15:32 +0000
+++ sql/ha_ndbcluster.cc        2010-02-15 13:31:47 +0000
@@ -381,7 +381,7 @@ Thd_ndb::Thd_ndb()
   m_error_code= 0;
   query_state&= NDB_QUERY_NORMAL;
   options= 0;
-  (void) hash_init(&open_tables, &my_charset_bin, 5, 0, 0,
+  (void) hash_init(&open_tables, table_alias_charset, 5, 0, 0,
                    (hash_get_key)thd_ndb_share_get_key, 0, 0);
   m_unsent_bytes= 0;
   global_schema_lock_trans= NULL;
@@ -7237,9 +7237,9 @@ int ndbcluster_table_exists_in_engine(ha
   for (uint i= 0 ; i < list.count ; i++)
   {
     NdbDictionary::Dictionary::List::Element& elmt= list.elements[i];
-    if (my_strcasecmp(system_charset_info, elmt.database, db))
+    if (my_strcasecmp(table_alias_charset, elmt.database, db))
       continue;
-    if (my_strcasecmp(system_charset_info, elmt.name, name))
+    if (my_strcasecmp(table_alias_charset, elmt.name, name))
       continue;
     DBUG_PRINT("info", ("Found table"));
     DBUG_RETURN(HA_ERR_TABLE_EXIST);
@@ -7529,7 +7529,7 @@ int ndbcluster_find_files(handlerton *ht
                         NdbDictionary::Object::UserTable) != 0)
     ERR_RETURN(dict->getNdbError());
 
-  if (hash_init(&ndb_tables, system_charset_info,list.count,0,0,
+  if (hash_init(&ndb_tables, table_alias_charset,list.count,0,0,
                 (hash_get_key)tables_get_key,0,0))
   {
     DBUG_PRINT("error", ("Failed to init HASH ndb_tables"));
@@ -7876,7 +7876,7 @@ static int ndbcluster_init(void *p)
   if (ndbcluster_connect(connect_callback))
     goto ndbcluster_init_error;
 
-  (void) hash_init(&ndbcluster_open_tables,system_charset_info,32,0,0,
+  (void) hash_init(&ndbcluster_open_tables,table_alias_charset,32,0,0,
                    (hash_get_key) ndbcluster_get_key,0,0);
 #ifdef HAVE_NDB_BINLOG
   /* start the ndb injector thread */

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/101221

3079 Martin Skold	2010-02-23
      Bug#33158 NDB table name problem(sensitive/insensitive): Using table_alias_charset for comparing table names, added tests
      modified:
        mysql-test/suite/ndb/r/ndb_cache.result
        mysql-test/suite/ndb/r/ndb_charset.result
        mysql-test/suite/ndb/t/ndb_cache.test
        mysql-test/suite/ndb/t/ndb_charset.test
        sql/ha_ndbcluster.cc

Patch looks ok, you might just consider adding some comments to the .test file describing what it's testing.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105812

3177 Martin Skold	2010-04-16
      Bug#33158 NDB table name problem(sensitive/insensitive): Using table_alias_charset
      for comparing table names, added tests
      modified:
        mysql-test/suite/ndb/r/ndb_cache.result
        mysql-test/suite/ndb/r/ndb_charset.result
        mysql-test/suite/ndb/t/ndb_cache.test
        mysql-test/suite/ndb/t/ndb_charset.test
        sql/ha_ndbcluster.cc

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105815

3502 Martin Skold	2010-04-16 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_cache.result
        mysql-test/suite/ndb/r/ndb_charset.result
        mysql-test/suite/ndb/t/ndb_cache.test
        mysql-test/suite/ndb/t/ndb_charset.test
        sql/ha_ndbcluster.cc

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105824

3564 Martin Skold	2010-04-16 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_cache.result
        mysql-test/suite/ndb/r/ndb_charset.result
        mysql-test/suite/ndb/t/ndb_cache.test
        mysql-test/suite/ndb/t/ndb_charset.test
        sql/ha_ndbcluster.cc

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105858

3179 Martin Skold	2010-04-16
      Bug#33158 NDB table name problem(sensitive/insensitive): Updated result due to correct charset for table names giving different sort order for show tables
      modified:
        mysql-test/suite/ndb/r/ndb_restore_options.result

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105860

3505 Martin Skold	2010-04-16 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_restore_options.result

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105862

3567 Martin Skold	2010-04-16 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_restore_options.result

Pushed into 5.1.44-ndb-7.0.15 (revid:martin.skold@mysql.com-20100416124212-rifnrnaoc9jrdz2r) (version source revid:martin.skold@mysql.com-20100416124212-rifnrnaoc9jrdz2r) (merge vers: 5.1.44-ndb-7.0.15) (pib:16)

Documented in the NDB-6.3.34, 7.0.15, and 7.1.4 changelogs, as follows:

      NDB did not distinguish correctly between table names differing 
      only by lettercase when lower_case_table_names was set to 0.

Closed.