CODEWITHSUNDEEP
mysqlphpmyadmincase-sensitive
BeNice
BeNice

Reputation: 2295

How to use UPPER case in (existing) mySQL names in Windows

Does anyone know HOW I might preserve case in my table names please (Win 10 using phpMyAdmin,latest xampp distribution). Could this be because xampp is using MariaDB instead of mySQL?

Table names being rewritten to all lowercase.

Read on phpMyAdmin site that to use preserve case I should add:

'set-variable=lower_case_table_names=0'

this stopped mySQL starting with an error message:

[ERROR] c:\xampp\mysql\bin\mysqld.exe: unknown variable 'set-variable=lower_case_table_names=0'

Then after reading an answer here I learned I should use lower_case_table_names=0so aded that to my.ini and still got the same error.

I then spent 20 mins trying to work out where my set-variable=lower_case_table_names=0 was set. (Thought it must be cached or duplicated or something.)

In desperation I deleted the lower_case_table_names=0 and the error disappeared. I put it back and got the [ERROR] c:\xampp\mysql\bin\mysqld.exe: unknown variable 'set-variable=lower_case_table_names=0' error. IE they seem to be aliases with the error reporting translating. Somewhat irritating.

Upvotes: 0

Views: 5480

Answers (2)

BeNice
BeNice

Reputation: 2295

Putting lower_case_table_names=2 in your my.ini files allows the use of upper and lower case in tabel names... BUT .... BIG BUT... it does NOT allow you to put upper case letters into EXISTING file names.

The solution is to COPY your table to a new table with the "correct" name.

However as this only changes the appearance of the table names (tables are stored in lower case files) you must use an intermediate table.

EG you want the table name to be HelloWorld but you discover it is stored as helloworld. Add the line above to your my.ini file then make a copy helloworld to a new table, say "helloTemp", delete helloworld copy helloTemp to HelloWorld delete helloTemp and you have a table with HelloWorld as the name.

Upvotes: 0

Shadow
Shadow

Reputation: 34231

You should read the documentation bit more carefully, specifically identifier case sensitivity section.

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.

Value Meaning

0 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. 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 OS X). If you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result. 1 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. This behavior also applies to database names and table aliases. 2 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.

To summarise: on windows you should not set lower_case_table_names to 0. If you want to preserve the letter case for table names on windows, then set lower_case_table_names to 2.

The using system variables section explains how to set a system variable in various ways.

Upvotes: 2

Related Questions