MySQL case sensitivity table name on MacOS with case insensitive file system

Question

I have researched a lot and what I understand to make database tables name sensitive, you have to set the variable lower_case_table_names=0. Im on osX. I did this change in my.cnf. After that, if I run

select * from users

I get results. While if I run:

select * from Users

I get error saying table doesn't exist.

However, for a particular database, the case sensitivity doesnt affect. I can use any case I will never receive errors. Why? I could have a look at the big sql-file used to import the database and try to find out if there are specific directives to ignore case sensitivity (?). Anyway, why you think the case sensitivity applies for all database but not the one Im interested in? One of those that does case sensitivity is InnoDB. While the one that doesnt care about this is MyIsam. Could it be the reason? Any work around in that case?

Answer 1

Tables and Columns are Case Sensitive in Linux! To make them case insensitive, follow this:

Open terminal and edit /etc/mysql/my.cnf

sudo nano /etc/mysql/my.cnf

Underneath the [mysqld] section, add:

lower_case_table_names = 1

Restart mysql

sudo /etc/init.d/mysql restart

Then check it here:

mysqladmin -u root -p variables

Just altering the lower_case_table_names setting isn't enough. It needs to be done before you import your database(s).

The MySQL 5.7 documentation lists a procedure for moving between Windows and Linux/UNIX. A note about Mac OSX from that reference:

One notable exception is OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive. However, OS X also supports UFS volumes, which are case sensitive just as on any Unix.

Review the manual page to ensure that your desired rules for enforcing case sensitivity are followed. Take a look and verify that you did these steps in the correct order:

To convert one or more entire databases, dump them before setting lower_case_table_names, then drop the databases, and reload them after setting lower_case_table_names:

1 - Use mysqldump to dump each database:

mysqldump --databases db1 > db1.sql

mysqldump --databases db2 > db2.sql

... Do this for each database that must be recreated.

2 - Use DROP DATABASE to drop each database.

3 - Stop the server, set lower_case_table_names in the [mysqld] section of your \etc\mysql\my.cnf file, and restart the server.

4 - Reload the dump file for each database. Because lower_case_table_names is set, each database and table name will be converted to lowercase as it is recreated:

mysql < db1.sql

mysql < db2.sql

Concerning the MySQL System Variable lower_case_table_names Server Variable (or setting):

Additional References:

• MySQL case sensitive table names on Linux
• How to make MySQL table name case insensitive in Ubuntu?

Answer 2

The case sensitivity of database and table names depends on the underlying OS and file system.

On Windows they are not case sensitive. On Linux they are case sensitive.

OSX is somewhere in the middle; the HFS file system supports both case-sensitive and case-insensitive file names (not on the same time though). It depends on how it was formatted.

Answer 3

However, for a particular database, the case sensitivity doesnt affect. I can use any case I will never receive errors. Why?

This is because this database is simply created with option of case-insensitivity (by default). You need first to put case-sensitive option in the top of sql create script before the database creation, so the DBMS takes care.

Answer 4

Only solution on MacOS goes neither over lower_case_table_names nor else settings of MySQL DBMS.

You need a case sensitive file system (volume). But fortunately it's easy to create it under MacOS:

• start the 'Disk Utility'

• Unter a main APFS-Container add a new APFS volume with case sensitivity (assume, we name it 'MysqlData', you can also define the size quota for this volume)

• move all the database binary data to this volume (in a directory in this volume i.e. /Volumes/MysqlData/data)

• make a symbolic link (assume our mysql data directory is /usr/local/mysql/data) to this volume: i.e.

  /usr/local/mysql/data -> /Volumes/MysqlData/data

DO A BACKUP OF /usr/local/mysql/data BEFORE!!!

• start MySQL DBMS and load/import a DB (maybe you have to remove these DBs before) with uppercase tables
• voila: you will finally see these tables with uppercase

Answer 5

1. Locate file at /etc/mysql/my.cnf

2. Edit the file by adding the following lines:

   [mysqld]

   lower_case_table_names=1

3. sudo /etc/init.d/mysql restart

You might need to re-create these tables to make it work

Answer 6

MacOsx 10.13, docker 3.0.1.
Changing lower_case_table_names to 0 did not help me. Mysql gave error

[ERROR] The server option ‘lower_case_table_names’ is configured to use case sensitive table names but the data directory is on a case-insensitive file system which is an unsupported combination. Please consider either using a case sensitive file system for your data directory or switching to a case-insensitive table name mode.

Turning this setting Use gRPC FUSE for file sharing off in docker settings helped:

Answer 7

mysql manual states:

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.

Answer 8

In any new APFS based Mac you can do this which appears to be working. The following will create a new volume that which is case-sensitive (APFSX) that you can use for your volume data. Note: You should do this in a fresh directory.

mkdir <docker volume directory>
sudo diskutil apfs addVolume disk1 APFSX docker -mountpoint <docker volume directory>
sudo chown -R $(id -u):$(id -g) <docker volume directory>

Ref: Feature to opt-in into a case sensitive file-system (osxfs) on a volume mount

Answer 9

By default, table aliases are case-sensitive on Unix, but not so on Windows or macOS. And you can't force this on OSX.

The MySql documentation gives the following recommendation:

To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use.

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). Triggers also correspond to files. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. This means such names are not case-sensitive in Windows but are case-sensitive in most varieties of Unix. One notable exception is macOS, which is Unix-based but uses a default file system type (HFS+) that is not case-sensitive. However, macOS also supports UFS volumes, which are case-sensitive just as on any Unix. See Section 1.7.1, “MySQL Extensions to Standard SQL”. The lower_case_table_names system variable also affects how the server handles identifier case sensitivity, as described later in this section.

The recommended is use lower_case_table_names=1 on all systems, but the disadvantage with this is that when you use SHOW TABLES or SHOW DATABASES, you don't see the names in the original case.

the variable lower_case_table_names is 0 by default, which means table names are stored as specified and comparisons are case-sensitive.

that is the possible values:

• 0: table names are stored as specified and comparisons are case-sensitive.
• 1: table names are stored in lowercase on disk and comparisons are not case-sensitive.
• 2: table names are stored as given but compared in lowercase. This option also applies to database names and table aliases.

On Windows the default value is 1. On macOS, the default value is 2. On Linux, a value of 2 is not supported; the server forces the value to 0 instead.

and here are your answer

You should not set lower_case_table_names to 0 if you are running MySQL on a system where the data directory resides on a case-insensitive file system (such as on Windows or macOS). It is an unsupported combination that could result in a hang condition when running an INSERT INTO ... SELECT ... FROM tbl_name operation with the wrong tbl_name lettercase. With MyISAM, accessing table names using different lettercases could cause index corruption.

An error message is printed and the server exits if you attempt to start the server with --lower_case_table_names=0 on a case-insensitive file system.

you can change this in my.cnf file, and you can find it using following command (if you are using unix-based system)

mysql --help | grep cnf

References:

MySQL 5.7 Reference Manual Identifier Case Sensitivity

MySQL 5.7 Reference Manual 5.1.7 Server System Variables