Change MySql Case sensitivity with phpMyAdmin?

Question

I am running Blogengine.Net and have noticed that the tables are all created lower case (table name be_settings) but a lot of the queries are written mixedcase (Select * from be_Settings). This works fine if your MySql instance is running on Windows or set for capatability with Windows. I am getting an error as my hosting provider MySql instance is case sensitive. Is there a setting I can change to fix this error through phpMyAdmin? I don't want to have to fish through all of the code and fix BlogEngine.Net if I don't have to.

Answer 1

How is the database being created? You might find that the queries which create the tables are using the correct case:

CREATE TABLE be_Settings ( ... )

It's just that myisam tables are stored as files in the filesystem, and since it's on Windows, they're just converted to lower case. If you create the database from these queries on a linux system, you'll find that all the tables are in the correct case.

The lesson from this is to always make tables names lower case...

Answer 2

The setting you are after is lower_case_table_names (set to 1 or 2). Unfortunately, this would need to be set on the MySQL daemon start, not through phpmyadmin. Could you ask your hosting provider to do this for you?

Answer 3

Case sensitivity in MySQL table names is specific to the OS. MySQL data is stored in files, which are subject to whatever rules the OS enforces. Linux IS case-sensitive, for example.

There is a variable 'lower_case_table_names' that can be manipulated, but it seems like you'd have to recreate all your tables.

http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

Answer 4

If you are trying to solve this for BlogEngine.Net you can easily rename all of the tables to use CamelCase as all of the queries in BlogEngine.Net are written using CamelCase.