lower_case_table_names dangerous ?

[MisterM]

Hello everyone
I have a customer who has asked me to activate the following function on his server:

lower_case_table_names

I'd like you to give me a clear, precise answer as to whether this is dangerous for other sites on the same machine or not.

 

[Zhenyapan]

MySQL :: MySQL 8.0 Reference Manual :: 11.2.3 Identifier Case Sensitivity

dev.mysql.com

There is no security issues, but some other cases may go wrong. So if user asks for this on his clean new vps - you can configure this. But if this is already production server with another users - you can't do this.

 

[MisterM]

MySQL :: MySQL 8.0 Reference Manual :: 11.2.3 Identifier Case Sensitivity

dev.mysql.com

There is no security issues, but some other cases may go wrong. So if user asks for this on his clean new vps - you can configure this. But if this is already production server with another users - you can't do this.

What damage does it do if it's activated on the server?

 

[Ohm J]

Don't do this for just one customer.

MySQL :: MySQL 8.0 Reference Manual :: 11.2.3 Identifier Case Sensitivity

dev.mysql.com

if you set this to 1, Other customer can't create table with upper case anymore
(Don't set to "2" on linux, it dangerous).


Example: MyTableName

 

[Zhenyapan]

@jamgames2 answered on this link all described, it must be activated on clean mysql while there no databases and tables created, also if your CMS want to create tables with uppercase letters - it can't and if it hardcoded with requests to fixed names - it will not find this tables etc.

 

[MisterM]

My additional answer is to know that I have a client site that uses these tables in upper case and I have other sites in Wordpress CMS.
If I activate it like this :

lower_case_table_names = 1

Is this going to cause a problem or is it going to change nothing?

 

[Ohm J]

MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.

I don't know this will apply to all exists table or only create new table. because from the past to now, I don't testing too much.

 

[MisterM]

I don't know this will apply to all exists table or only create new table. because from the past to now, I don't testing too much.

Because the client was under ISPConfig (**) before, it went to DA, because of a lot of things, especially on the security of sites is before (**) even by activating this function and installing other CMS, there was no problem.

 

[MisterM]

The following question :
So, if I understand correctly, if we set the value to 0, there is no security or compatibility risk for any user of the server? Bearing in mind that for the moment this value is 1, and that the request from the client concerned is to set the value to 0?

 

[Ohm J]

it's "0" by default on Linux system.
Where're you seeing this value ?


Please carefully, if you change this value from 1 to 0 on already have table running, Something might not working as expect.

Example: I want to running this query

SELECT * FROM MyTableName

this will not work for me anymore. because table already convert into "mytablename"

 

[MisterM]

The question comes from my customer @jamgames2
Currently, the value of lower_case_table_names is 1. Can we go back to 0 without taking risk for all the sites present on the server? Knowing that we have more than 50 active sites? Is this action irreversible?

 

[Ohm J]

I think you need to monitor all their site, if any site use query to fetch/update table with any uppercase. you can't.

 

[BillyS]

If it's set to 1 and someone created this table in WordPress

MyTableSpace

It was stored by mySQL as the below and all queries were to this table

mytablespace


If you set it to 0 and WordPress tries to access the table, it will not find the table because it is looking for

MyTableSpace and it was written as mytablespace

 

[LawsHosting]

Hello everyone
I have a customer who has asked me to activate the following function on his server:

Well, if it's "his" server, then sure....... If it's a shared server, I'd be reluctant.