Search code examples
mysqlsqllowercase

MySQL - Cannot query existing tables

I have a MySQL database (running on Ubunutu) with whole bunch of tables with names in camelcase. For example, when I call show tables; I get an output like

+-----------------------------------+
| Tables_in_[schema]                |
+-----------------------------------+
| tabHelloWorld                     |
| tabUserAccounts                   |
| tabHistory                        |
+-----------------------------------+

However, when I'm trying to query a table, e.g., SELECT COUNT(*) FROM tabHistory, I get the error

ERROR 1146 (42S02): Table 'schema.tabhistory' doesn't exist

Note the table name in all lowercase. The query SELECT COUNT(*) FROM tabhistory returns the same error.

I assume it has something to with the variable lower_case_table_names. It's currently set to 1, where I assumed that MySQL is not considering the case of table names. If I set lower_case_table_names = 0 the query with FROM tabHistory is working. The problem is that the MySQL server is running other databases in turn are no longer accessible if I make that change.

How can I access my tables now?

Solution

  • It sounds like you changed the setting of lower_case_table_names after creating these tables. This isn't a supported configuration. When lower_case_table_names=1, the system automatically converts the table names to lowercase when they're created, but it doesn't go back and fix up previously existing tables. The manual says:

    If you plan to set the lower_case_table_names system variable to 1 on Unix, you must first convert your old database and table names to lowercase before stopping mysqld and restarting it with the new variable setting. To do this for an individual table, use RENAME TABLE:

    RENAME TABLE T1 TO t1;

    What I think you need to do now is set it back to 0 temporarily, rename these tables, then restart mysqld.