Welcome Guest, Not a member yet? Register   Sign In
MySQL table names forced to lower-case, and table_exists function case-sensitive ?
#1

[eluser]aidehua[/eluser]
I create a database table like this:

Code:
$sql = "CREATE TABLE IF NOT EXISTS tbUsers (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    email VARCHAR(255),
                    unique_salt VARCHAR(255),
                    password_hash VARCHAR(255),
                    role TINYINT,
                    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    created_IP VARCHAR(50),
                    last_login_date TIMESTAMP,
                    last_login_IP VARCHAR(50)
                    COLLATE utf8_unicode_ci
                    )";
        $query = $this->db->query($sql);

And the table is created correctly, except that it is given the name "tbusers" (all lower-case), and not "tbUsers" (CamelCase, as in my SQL).

Having created the table as above:

$this->db->table_exists('tbusers') returns TRUE, but
$this->db->table_exists('tbUsers') returns FALSE.

I'm curious as to why CI (or MySQL) is over-riding my case preference.

And I wonder why the CI table_exists function appears to be case sensitive.

Pleas forgive my ignorance - can anyone shed light on this, and tell me whether it is a CodeIgniter thing or a MySQL thing? And what you would suggest if I am determined to persist with CamelCased table names (old habit, dies hard...). Thanks.
#2

[eluser]jmadsen[/eluser]
Neither - it's an OS thing.

Windows forces all mysql table names to lowercase, because of the way it stores file names.
#3

[eluser]flaky[/eluser]
this has to do more with the operating system then CI, since Windows isn't a case sensitive OS, MySQL running in Windows won't be case sensitive, while if you run the script in a GNU/Linux server you will notice that the OS is case sensitive and the table names will appear as you have written them.

Basically you should be very careful since, and app written in Windows might crash in GNU/Linux server just because of case sensitivity.
#4

[eluser]aidehua[/eluser]
Thanks for the tip-off jmadsen & flaky.

I had thought/hoped that I was covering myself by using the "COLLATE utf8_unicode_ci" statement, on the basis that the "_ci" part makes the database case-insensitive (irrespective of OS).

But I guess that the fact that

$this->db->table_exists(‘tbusers’) returns TRUE, but
$this->db->table_exists(‘tbUsers’) returns FALSE.

shows I was wrong about that...

Since I'm stuck (for the time being at least) with developing on a Windows system, but deploying on a Linux server, what do you suggest? Is it time to abandon my taste for CamelCase, and stick to all_lower_case for table names?

UPDATE:

Quote from http://dev.mysql.com/doc/refman/5.0/en/i...ivity.html:

"This means database and table names are not case sensitive in Windows"

Since I'm working in Windows, this leaves me quite surprised that

$this->db->table_exists(‘tbusers’) returns TRUE, but
$this->db->table_exists(‘tbUsers’) returns FALSE.

Any comments?
#5

[eluser]flaky[/eluser]
Code:
COLLATE utf8_unicode_ci
only makes sure your table is in UTF-8 encoding.

You can continue with your taste of CamelCase, just be careful when querying the database.
#6

[eluser]aidehua[/eluser]
Wow, this gets fiddly.

I'm sure I'm not the only person out there who is developing on Windows for eventual deployment to Linux, and who has a fondness for CamelCasing table names. So here's what I've found:

Read this page to find out about the lower_case_table_names system variable.

The default value of lower_case_table_names on Windows is 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."

Quote:If you are using MySQL on only one platform, you do not normally have to change the lower_case_table_names variable from its default value. However, you may encounter difficulties if you want to transfer tables between platforms that differ in file system case sensitivity. For example, on Unix, you can have two different tables named my_table and MY_TABLE, but on Windows these two names are considered identical. To avoid data transfer problems arising from lettercase of database or table names, you have two options:

- Use lower_case_table_names=1 on all systems. The main disadvantage with this is that when you use SHOW TABLES or SHOW DATABASES, you do not see the names in their original lettercase.

- Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. This preserves the lettercase of database and table names. The disadvantage of this is that you must ensure that your statements always refer to your database and table names with the correct lettercase on Windows. If you transfer your statements to Unix, where lettercase is significant, they do not work if the lettercase is incorrect.

I've gone for the second option, to set my Windows system to lower_case_table_names=2.

Easy? Not quite yet.

In theory you can set this via the my.ini Setup tab in WinMySQLadmin. But on my system at least (Windows XP, MySQL installed with XAMPP), this did not work.

So, to here: http://www.phpbuilder.com/board/showthre...t=10341962. Look at the 15th post. If running bradgrafelman's suggestion there works, then move on to the 17th post (here).

He suggests doing a registry edit.

I did something slightly different, which also seems to work:

I went to C:\xampp\mysql\bin (the path may be different depending on your system - if you can't find it, then the MySQL registry key as noted in bradgrafelman's 17th post (see above) will help you find it).

Open the my.ini file.

In the section which begins
Code:
# The MySQL server
[mysqld]

Add the following line:
Code:
lower_case_table_names = 2

Save the file, then re-start your MySQL service.

This seems to be working for me.




Theme © iAndrew 2016 - Forum software by © MyBB