Changing lower_case_table_names variable in MariaDB

Question

I have a test Linux server running the database for test, but locally I'm working on a windows. This means that my lower_case_table_names variable is set to 1, so basically my table names case sensitive, whereas it's set to 0 by default on Linux.

I tried altering the variable in the my.ini located in my installation directory (C:\Program Files\MariaDB 10.0\data\my.ini). Before these changes apply, I do need to restart the service.

I went into my services and stopped the MySql service. Trying to start it again however, I get error:

1067: the process terminated unexpectedly

So I suppose MariaDB cannot understand the my.ini configuration.

As I understood it by reading the documentation, this is one of the ways to actually do this. Removing the lower_case_table_names variable from the my.ini file, the service can start again.

How can I set the variable so my tables names are case-sensitive?

EDIT

Setting that variable to either 1or 2 the service can start fine again.

Answer 1

From the manual:

As of MySQL 5.5.46, 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.

Same applies to MariaDB.

Answer 2

Like elenst stated an error message might appear if setting lower_case_table_names to OFF or 0 on a system that has lower_case_file_system ON or 1. The asker develops on a Windows system but, as so many, deploys on a linux system that does offer case sensitivity and might run in to compatibility issues where something might work in developement but not in deployment.

Introduction

Contrary to popular believe Windows (> NT) allows for case sensitive file systems and NTFS supports case sensitive files from its inception and case sensitive even can (must) be set on a per folder basis. Neat.

Configure mariadb or mysql

If the location and content of the data files on mariadb (e.g. mariadb\data) is set to case sensitive behavior the lower_case_table_name value can be set to 0 in my.ini under [mysqld].

lower_case_table_names = 0

Here is my result of

select @@lower_case_file_system, @@lower_case_table_names;

on a Windows 10 system (running wampserver, with mariadb 10.4.10:

+--------------------------+--------------------------+
| @@lower_case_file_system | @@lower_case_table_names |
+--------------------------+--------------------------+
|                        0 |                        0 |
+--------------------------+--------------------------+
1 row in set (0.000 sec)

Perfect.

Configure the data folder

So the question remains how to set the data folder to case sensitive? The fsutil can set the flag for case senstive to folders after the files where created. The command is as follows:

fsutil file setcasesensitiveinfo *data* enable

where data takes a folder name. There is one culprit, the command does not support recursively setting all subfolders at once. A simple PS script does this wonder:

Get-ChildItem -Path C:\wamp64\bin\mariadb\mariadb10.4.10\data -Directory -Recurse | foreach {fsutil file SetCaseSensitiveInfo $_.Fullname}

Obviously you will have to change the path to your data folder, not mine.

Remarks

One of the many bugs in MySQL Workbench is that it does not understand this quite and assumes, wrongly, a lower_case_file_system=1 when on Windows totally ignoring what the server has and therefor complains about Server configuration problems. With PhpMyAdmin you fare much better in that (and other) respect.

And a word of caution. Although the OS and the FS suport case senstivity splenditly many applications do not do so. But you shouldn't be messing with your MySQL or MariaDB data folder with other applications, in the first place.

Please make all these changes before any scheme or tables are added. The RDBMS does some book keeping and you might run into troubles if you perform these steps on a live and kicking database. Instead you should export all databases, perform the steps outlined above and import all databases while keeping your fingers crossed. (But by now you have a backup anyhow)

Let me know if you do run in problems.