Menu
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.
484
In order to prevent this problem you need to set the mysql variable lower_case_table_names=1 in /etc/mysql/my. cnf file. In this way the mysql server will store the table in the file system using lower case.
Table names in MySQL are file system entries, so they are case insensitive if the underlying file system is.
Table names are stored in lowercase on disk and name comparisons are not case-sensitive. MySQL converts all table names to lowercase on storage and lookup.
Case insensitive SQL SELECT: Use upper or lower functions select * from users where lower(first_name) = 'fred'; As you can see, the pattern is to make the field you're searching into uppercase or lowercase, and then make your search string also be uppercase or lowercase to match the SQL function you've used.
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.
111
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...
13
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
