Inconsistent table case sensitivity results

We have a situation where the table_name_case_sensitivity seems to be applied inconsistently. On some tables it appears to work just fine, but on others it does not:

memsql> show global variables like '%case_sens%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| table_name_case_sensitivity | OFF   |
+-----------------------------+-------+
1 row in set (0.00 sec)

memsql> show tables;
+-------------------------+
| Tables_in_crca_core     |
+-------------------------+
| DATABASECHANGELOG       |
| DATABASECHANGELOGLOCK   |
| hospitals               |
| hospitals_users         |
| user_hospital_filters   |
| user_password_histories |
| users                   |
+-------------------------+
7 rows in set (0.00 sec)

memsql> select count(*) from crca_core.hospitals_USERS;
+----------+
| count(*) |
+----------+
|       15 |
+----------+
1 row in set (0.06 sec)

memsql> select count(*) from crca_core.hospitals_users;
+----------+
| count(*) |
+----------+
|       15 |
+----------+
1 row in set (0.06 sec)

memsql> select count(*) from crca_core.Users;
ERROR 1146 (42S02): Table 'crca_core.Users' doesn't exist
memsql> select count(*) from crca_core.users;
+----------+
| count(*) |
+----------+
|       13 |
+----------+
1 row in set (0.00 sec)

memsql> select count(*) from crca_core.DATABASECHANGELOG ;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.09 sec)

memsql> select count(*) from crca_core.DatabaseChangeLog;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.06 sec)

Thanks for reporting this issue. I can reproduce it with a table named users. I’ve opened a bug to track fixing this.

1 Like

Thank you Adam.

Is there any way for customers to track bug fixes so we know when they will be available?

Thanks,
Marty

If you have an enterprise license you can file a support ticket and support will let you know when the fix has made.

This is likely an easy fix - probably its failing because the table name matches a table in information_schema which results in an incorrect comparison is made somewhere.

Just a quick update. This issue was patched in memsql 7.0.20 and 7.1.4:

  • Fixed an issue where user-defined tables having the same name as system tables, such as USERS , were treated as case-sensitive when table_name_case_sensitivity was set to OFF .

Thank you Adam.

Are patches in MemSQL considered new upgrades? i.e:

memsql-deploy upgrade --version 7.0.20

Yes, that should work to upgrade.

-Adam