CODEWITHSUNDEEP
mysql
99823
99823

Reputation: 2457

MySql Two Tables Same Name Different Casing

I'm starting to freak out because I seem to have a phantom table... it's not showing up in Navicat however if I run these two queries:

SELECT count(*) from messages;

SELECT count(*) from Messages;

I get two different sets of results!

However, the weird thing is if i run show tables I only see one table called messages

This freaks me out because i have no clue if data is going to mistakenly get throwing into the incorrect table Messages

Has anyone ever seen this before?

I'm not sure what to do.

Per Request

After running show table status like 'messages';

messages InnoDB 10 Compact 224163 222 49889280 0 53608448 8388608 208683 2014-08-23 20:16:11 latin1_swedish_ci

One more update

I've ran both:

SELECT * FROM information_schema.tables WHERE table_name = 'Messages';

SELECT * FROM information_schema.tables WHERE table_name = 'messages';

It's showing multiple records with different record counts

The scary part is I ran the same query for other tables in the database and all the other tables i tested with the same technique had the same problem.

It's as if I have two copies of each table, one with a capital first letter, the other with a lowercase, and it seems that the lowercase is the "freshest" of the two.

Upvotes: 0

Views: 1427

Answers (1)

spencer7593
spencer7593

Reputation: 108400

I'd recommend you check for the table using a query of information_schema.tables.

SELECT * FROM information_schema.tables WHERE table_name = 'Messages';

Also consider the possibility that someone created a view.

SELECT * FROM information_schema.views  WHERE table_name = 'Messages';

There's a MySQL variable lower_case_tables_names that has an effect; the default value for this variable depends on the OS (Linux, Windows, OS X). (We have that explicitly set to 1 on our MySQL systems.) According to the reference manual:

"If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase."

(This section of the manual is does not describe the behavior you'd observe with InnoDB tables if this variable were set to something other than 1.)

Ref: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_lower_case_table_names

Upvotes: 1

Related Questions