How to change MySQL table names in Linux server to

Question

I'm working on an old website that used to be hosted on an Apple server. When it was migrated into a new Linux server it stopped working. I'm pretty sure it's because all the MySQL queries used in the php scripts have different case combinations for the table names (I don't know why the original developers didn't follow any conventions when they created the table names or the php scripts) and it didn't matter because both Mac and Windows MySQL servers are case insensitive by default when it comes to this. However, Linux is not.

Is there a way to change the Linux default on MySQL so it becomes case insensitive and it works like Mac or Windows? I've been looking but haven't found any answers that don't involve changing either the scripts or the table names or both. The website must have been generated using some CMS so there are dozens upon dozens of pages and include files with multiple queries in each and hundreds of tables. I began trying to implement this type of solution in the smartest way I could think of but if I touch the table names then other currently working pages stop working (I'm trying to avoid breaking the site further).

There was a system variable (lower_case_table_names) in the MySQL Server console in Webmin in the Linux server that I read could be changed from 0 to 1 to tackle this issue, but Webmin won't let me change it because it's a "read-only" variable.

You'd think this would be an easily problem to solve, but so far I'm losing hope. I'm hoping someone's got an answer that maybe eludes me at the moment.

Answer 1

You can't change the value of lower_case_table_names while mysql is running - it needs to be set on startup. You will need to edit my.cnf (maybe in /etc, maybe somewhere else, not sure). Then restart mysql and you should be good.

Answer 2

MySQL's case sensitivity is by default handled by the file system, which is why you found this difference:

9.2.2. Identifier Case Sensitivity

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names. This means database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix. One notable exception is Mac OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive. However, Mac OS X also supports UFS volumes, which are case sensitive just as on any Unix. See Section 1.8.4, “MySQL Extensions to Standard SQL”.

Fortunately, the next sentence could help you:

The lower_case_table_names system variable also affects how the server handles identifier case sensitivity, as described later in this section.

The lower_case_table_names blurb:

If set to 0, table names are stored as specified and comparisons are case sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive. If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases. For additional information, see Section 9.2.2, “Identifier Case Sensitivity”.

You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or Mac OS X). If you set this variable to 0 on such a system and access MyISAM tablenames using different lettercases, index corruption may result. On Windows the default value is 1. On Mac OS X, the default value is 2.

So it appears you should set lower_case_table_names to 1 in the MySQL config file.

Answer 3

Edit the mysql configuration file

nano /etc/mysql/my.cnf

or any other my.cnf file which is used to configure your mysql.

Under

[mysqld]

add the line

lower_case_table_names=1

Run

sudo service mysqld restart

You might want to reimport your windows database into your linux database. Preferably from scratch, with add table and insert statements.

It's good!

Answer 4

The lower_case_table_names parameter should be set as part of a custom DB parameter group before creating a DB instance. You should avoid changing the lower_case_table_names parameter for existing database instances because doing so could cause inconsistencies with point-in-time recovery backups and Read Replica DB instances.

Answer 5

There is a MySQL server variable with the same name. You probably need to set that specific variable on system start-up, as described in this help page. You will have to find the location of the MySQL options file (mine is at /etc/my.cnf) for your DB server instance and edit/add this option to the [mysqld] section.

Don't forget to restart the MySQL daemon afterwards...