lower_case_table_names variable for MariaDB, how to enable it.

marrowyung
marrowyung used Ask the Experts™
on
hi,

I found this:

https://stackoverflow.com/questions/44648343/mysql-upper-case-table-schema-name

and i try that setting in server.cnf for mariaDB and it doesn't work, lower_case_table_names is STILL 0 if I double check from UI when MariaDB is up !:

SHOW VARIABLES LIKE 'lower_case_table_names';

for the content of my server.cnf please refer to this :

https://www.experts-exchange.com/questions/29151495/MariaDB-export-to-a-mariaDB-Docker.html?anchor=a42906273¬ificationFollowed=233535265#a42906273

when I do this inside MariaDB via UI :

SET lower_case_table_names=2;

Open in new window


error returns by saying:

Lookup Error - MySQL Database Error: Variable 'lower_case_table_names' is a read only variable

Open in new window



what is the problem? MariaDB seems can't see the setting in mysqlid section.

is it say it once MariaDB is installed we can't change it, what if it is a MariaDB docker ?

any doc detail how to set this setting during installation ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorFractional CTO
Distinguished Expert 2018

Commented:
Oh... I think I see why lower_case_table_names appears to be a NOP (no operation/effect) on Linux.

If you read the docs correctly, on Linux systems setting lower_case_table_names to a non-zero value this has no effect, so looks like MariaDB forces lower_case_table_names == 0 for clarity.

Only OSX seems to provide any functional change if lower_case_table_names != 0.

Seems to me the docs could be more clear about this.
theGhost_k8Database Consultant

Commented:
I saw similar issue while migrating between OSes and made a note here about 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.
Basically it depends on the underlying system.

Now, coming back to your problem... this is not a dynamic variable afaik. You need to reboot mysqld after making this change in config file. So edit my.cnf and add "lower_case_table_names=2" -> restart mariadb -> see if that works.


PS: Ignore the last question on the post. ;)
Fractional CTO
Distinguished Expert 2018
Commented:
@theGhost_k8 said... "Now, coming back to your problem... this is not a dynamic variable afaik. You need to reboot mysqld after making this change in config file. So edit my.cnf and add "lower_case_table_names=2" -> restart mariadb -> see if that works."

I should have mentioned I did this in my last update.

I reached my conclusion about Linux after setting lower_case_table_names=2 on a MariaDB-10.4.6 instance, then doing a hard stop/restart on the instance.

Checking DB VARIABLES, lower_case_table_names=0 was set again, since this is a NOP on Linux my guess is MariaDB quietly ignores the setting, leaving it set to 0.

Nothing in docs or logs about this. Just my guess.
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

marrowyungSenior Technical architecture (Data)

Author

Commented:
Hi,

I set it in my.cnf already and it seema mariadb ignore it even i restart mariadb services many times!

So i need to restart the whole linux box ?

I agree that no doc to mention that!

That's why i come here!

What if it is a mariadb docker?
marrowyungSenior Technical architecture (Data)

Author

Commented:
david:

"If you read the docs correctly, on Linux systems setting lower_case_table_names to a non-zero value this has no effect, so looks like MariaDB forces lower_case_table_names == 0 for clarity."

which doc you are referring to.
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

"I reached my conclusion about Linux after setting lower_case_table_names=2 on a MariaDB-10.4.6 instance, then doing a hard stop/restart on the instance."

restart of the box do not fix it!
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
Commented:
Hi,

Note that on both MySQL and MariaDB then
"lower_case_table_names can only be configured when initializing the server. Changing the lower_case_table_names setting after the server is initialized is prohibited. "

See here and here for further info.

Running the command

SET lower_case_table_names=2;

Open in new window


after the database startup and initialization is prohibited and thus cannot be done.

Also note that MySQL/MariaDB won't allow you to use 'lower_case_table_names=2' on a case-sensitive file system.

Regards,
    Tomas Helgi
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Also note that MySQL/MariaDB won't allow you to use 'lower_case_table_names=2' on a case-sensitive file system."

so must be 1?
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi,

Consult the first link I provided in previous comment to decide whether or not you set the lower_case_table_names to 1 or 0.

Regards,
    Tomas Helgi
marrowyungSenior Technical architecture (Data)

Author

Commented:
""lower_case_table_names can only be configured when initializing the server. Changing the lower_case_table_names setting after the server is initialized is prohibited. ""

initiallization is a operation when installing MariaDB, right?

this:

systemctl start mariaDB

is just start MariaDB?
marrowyungSenior Technical architecture (Data)

Author

Commented:
so seems once installed we can't change it ! it seems mariaDB installation do not need initialization but MySQL.
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi,

The initialization mentioned occurs when the database is started. It applies to both MySQL and MariaDB.
Thus the .cnf files are read and the database server system-variables initialized accordingly.

You can change the variables in the .cnf files or IF the variables are DYNAMIC then and only then you can use the SET .. command.
Note that the variables that are initialized using the SET command holds only the values during either a lifetime of a SESSION or in case of GLOBAL variables then until a restart  is done on the MySQL/MariaDB server.

Regards,
    Tomas Helgi
David FavorFractional CTO
Distinguished Expert 2018

Commented:
If you think you really must change this setting, then yes you must change the setting before starting MariaDB/MySQL for the first time (based on dev/bug tracker tickets related to this setting).

You can also likely do this...

# Dump all your databases.

service mysql stop
rm -rfv /var/lib/mysql
mkdir /var/lib/mysql
chown mysql:mysql /var/lib/mysql

# Change your lower_case_table_names setting in your database instance config file.

service mysql start

# Reload all your databases.

Open in new window


Note: I haven't done this, so just a guess this is how to change this setting.
marrowyungSenior Technical architecture (Data)

Author

Commented:
Tomas,

"The initialization mentioned occurs when the database is started. It applies to both MySQL and MariaDB. "

for MySQL it is, but for MariaDB, when mariaDB teach me how to install mariaDB, after install I run this following to start MariaDB for the first time:

sudo systemctl start mariadb

Open in new window


no initialization is needed ! that's why I am wondering on how to do it for MariaDB!

MySQL yes!  but you probably can also conclude when the first time I start MariaDB I need to do it in server.cnf, then it is making a lot of sense!

"Note that the variables that are initialized using the SET command holds only the values during either a lifetime of a SESSION or in case of GLOBAL variables then until a restart  is done on the MySQL/MariaDB server."

tks. it should be. only do it in server.cnf can do it permanently you mean ?


David Favor,

"you must change the setting before starting MariaDB/MySQL for the first time"

exactly what i just said, should be that.
marrowyungSenior Technical architecture (Data)

Author

Commented:
how about for docker, how can we enable this option ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks all
David FavorFractional CTO
Distinguished Expert 2018

Commented:
You're welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial