0

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.

BeNice
  • 2,165
  • 2
  • 23
  • 38
  • From mysql docs: "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" , so in your case too, you may have to restart mysqld after doing the change? – SKY Dec 30 '15 at 20:20
  • Possible duplicate of [How to force case sensitive table names?](http://stackoverflow.com/questions/6248735/how-to-force-case-sensitive-table-names) – Richard St-Cyr Dec 30 '15 at 20:22
  • Not a dupe as solution there `lower_case_table_names=2` put at the end does not work (nor in the place I was trying before). Have restarted and rebooted to check. That solution MAY work on other OS but NOT on Windows apparently. – BeNice Dec 30 '15 at 20:39
  • @SKY the current DB is all lower case table names (well just the one table in fact). I am trying to use camel case to clarify long names. (Yes I know I could us name_name but does not fit in with naming convention I am applying) – BeNice Dec 30 '15 at 20:43
  • And I have just got a -1 for "NO research effort" - checked here, checked myAdmin checked other places. I hate these people who just go around marking down. I looked at about five or more answers here and could not see a solution. And if I can' others following can't. I was going to edit this to give other people an easy to find solution. Grrrr – BeNice Dec 30 '15 at 20:46
  • BTW This all started with seeing reference 1.23 in http://docs.phpmyadmin.net/en/latest/faq.html which seems to be wrong. Grateful for feedback from any other users. If wrong will let phpMyAdmin know. – BeNice Dec 30 '15 at 21:42
  • Quote: "Before MySQL 4.0.2, the only syntax for setting program variables was --set-variable=option=value (or set-variable=option=value in option files). Underscores cannot be given as dashes, and the variable name must be specified in full. This syntax is deprecated and was removed in MySQL 5.5.3." Reference: https://dev.mysql.com/doc/refman/5.5/en/program-variables.html – Shadow Dec 30 '15 at 21:54
  • Not sure how that relates to the phpMyAdmin - am I right in thin king the PMA FAQ is wrong? If so would be useful to let them know. Their wrong info contributed to my wasted time. – BeNice Dec 31 '15 at 03:24

2 Answers2

2

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.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • lower_case_table_names IS set to 2 and has been for a while. One thing I did wonder was to force lower_case-file_system variable but just getting error messages. – BeNice Dec 30 '15 at 21:18
  • 1. You should NOT set lower_case_table_names to 0 on windows. On windows you cannot really force table names to be case sensitive because the file system of windows is not case sensitive either. 2. You tried in a way that is not described in the mysql documentation I linked in my answer. – Shadow Dec 30 '15 at 21:23
  • Shadow as I said 0 did not work but I was trying anything at that point. But you said "If you want to preserve the letter case for table names on windows, then set lower_case_table_names to 2" and that did not work. What OS are u using because I think this is NOT solvable on Windows (or Win 10 anyway). But thanks for trying. – BeNice Dec 30 '15 at 21:28
  • I am using windows. If you read the description of option 2, then you can see that it stores the database and table names (with the exception of innodb table names) in a case sensitive manner, but compares them in a case insensitive way. You cannot have 2 tables in the same databasethose names only differ in letter casing because windows cannot differenciate between them. These limitations are clearly described in the linked documentation. – Shadow Dec 30 '15 at 21:40
  • I have found my problem. For THREE HOURS I have been trying to CHANGE an existing table from aaabbb to aaaBBB and this DOES NOT work BUT new tables ARE case sensitive. Sorry you were right in the main. – BeNice Dec 30 '15 at 21:49
  • The problem with trying to change the letter casing of an existing table on windows is that mysql will not see the difference between the 2 names, since the comparisons are done in a case insensitive manner. – Shadow Dec 30 '15 at 22:01
  • Yep realize that now - after three hours. Thanks for your help. – BeNice Dec 30 '15 at 22:02
0

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.

BeNice
  • 2,165
  • 2
  • 23
  • 38
  • What is this got to do with your original question? It is rather unfair to come back years laters, post some additional info you never asked about and then move acceptance to your own answer. This is simply farming rep. – Shadow Sep 30 '17 at 05:36
  • The original question was about PRESERVING the case of existing tables. I very much appreciate your help on this. I would not have changed this except I was so fed up with the -1 I got for the question and I thought this might help cure it one day. The `lower_case_table` was the first part of the solution and the copying file names and so on the rest. I was trying to repair the -1 as I was prohibited asking questions for ages. Didn't mean to offend. I was trying to make this question useful. If this was NOT about EXISTING table names it was a dupe of a previous question. All the best Steve – BeNice Oct 02 '17 at 18:25