Bug #424 Case problem with MYISAM table on Redhat
Submitted: 13 May 2003 8:24 Modified: 1 Aug 2003 6:01
Reporter: Clinton Gormley Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1a OS:Linux (Redhat 9)
Assigned to: CPU Architecture:Any

[13 May 2003 8:24] Clinton Gormley
Description:
Using Redhat 9, and the 4.1 server and command line client, I typed in 'desc user' (forgetting that my table is actually called User).  I got the correct output for desc. When I tried to alter it, it failed with error 2.  Altering "User" worked though.

When I tried "desc user" after that, it reported that the table wasn't found:

Output included below

mysql> desc user;
+-------------------+-----------------------------------+-------------------+------+-----+---------------------+-------+
| Field             | Type                              | Collation         | Null | Key | Default             | Extra |
+-------------------+-----------------------------------+-------------------+------+-----+---------------------+-------+
| usr_UID           | varchar(32) character set latin1  | latin1_swedish_ci |      | PRI |                     |       |
| usr_username      | varchar(100) character set latin1 | latin1_swedish_ci |      | MUL |                     |       |
| usr_passwd        | varchar(40) character set latin1  | latin1_swedish_ci |      |     |                     |       |
| usr_nickname      | varchar(40) character set latin1  | latin1_swedish_ci |      | MUL |                     |       |
| usr_home          | varchar(100) character set latin1 | latin1_swedish_ci |      |     |                     |       |
| usr_realname      | varchar(100) character set latin1 | latin1_swedish_ci |      |     |                     |       |
| usr_description   | text character set latin1         | latin1_swedish_ci |      |     |                     |       |
| usr_dpa           | tinyint(4)                        | binary            |      |     | 0                   |       |
| usr_allow_contact | tinyint(4)                        | binary            |      |     | 0                   |       |
| usr_image_id      | int(10) unsigned                  | binary            |      |     | 0                   |       |
| usr_status        | tinyint(4)                        | binary            |      |     | 0                   |       |
| usr_role          | tinyint(3) unsigned               | binary            |      |     | 1                   |       |
| usr_score         | int(11)                           | binary            |      |     | 0                   |       |
| usr_score_hist    | int(11)                           | binary            |      |     | 0                   |       |
| usr_visits        | int(10) unsigned                  | binary            |      |     | 0                   |       |
| usr_creationdate  | datetime                          | latin1_swedish_ci |      |     | 0000-00-00 00:00:00 |       |
| usr_lastvisit     | datetime                          | latin1_swedish_ci |      |     | 0000-00-00 00:00:00 |       |
| usr_search        | text character set latin1         | latin1_swedish_ci |      |     |                     |       |
| usr_SID           | varchar(32) character set latin1  | latin1_swedish_ci |      |     |                     |       |
+-------------------+-----------------------------------+-------------------+------+-----+---------------------+-------+
19 rows in set (0.00 sec)
 
mysql> alter table user add index usr_image_id (usr_image_id);
ERROR 7: Error on rename of './traveljury/user.MYI' to './traveljury/#sql2-353-6a.MYI' (Errcode: 2)
mysql> check table user;
+-----------------+-------+----------+---------------------------------------+
| Table           | Op    | Msg_type | Msg_text                              |
+-----------------+-------+----------+---------------------------------------+
| traveljury.user | check | error    | Table 'traveljury.user' doesn't exist |
+-----------------+-------+----------+---------------------------------------+
1 row in set (0.00 sec)
 
mysql> alter table User add index usr_image_id (usr_image_id);
Query OK, 12 rows affected (0.01 sec)

mysql> desc user;
ERROR 1146: Table 'traveljury.user' doesn't exist

How to repeat:
First time I've seen this, and unable to make it occur again
[23 May 2003 8:40] Indrek Siitan
It's hard to tell from this bug report what happened, if it isn't repeatable.
[23 May 2003 8:48] Clinton Gormley
Actually, I've found it is repeatable.

Until MySQL actually has to do something with the files on disk (eg with an alter command), it is quite happy to use whatever case you please to refer to the tables, so :

The table is actually called "URL".

I can do any desc Url, select from UrL etc, then I try an alter : 

mysql> alter table Url add index url(url);
ERROR 7: Error on rename of './traveljury/Url.MYI' to './traveljury/#sql2-353-17.MYI' (Errcode: 2)
mysql> select * from Url;
+----------------+---------------------------------+-------------+-------+------------+-------+
| url            | handler                         | need_cookie | https | registered | login |
+----------------+---------------------------------+-------------+-------+------------+-------+
| /              | TravelJury::Home                |           1 |     0 |          0 |     0 |
| /home          | TravelJury::Home                |           1 |     0 |          1 |     0 |

<<snip>>

0 |     0 |
+----------------+---------------------------------+-------------+-------+------------+-------+
19 rows in set (0.00 sec)
 
mysql> alter table Url add index url(url);
ERROR 1146: Table 'traveljury.Url' doesn't exist

And after this, "desc UrL" will no longer work
[1 Jul 2003 5:26] Michael Widenius
Thank you for this bug report. To properly diagnose the problem, we
need a backtrace to see what is happening behind the scenes. To
find out how to generate a backtrace, please read
http://www.mysql.com/doc/en/Making_trace_files.html

Once you have generated a backtrace, please submit it to this bug
report and change the status back to 'Open'. Thank you for helping
us make our products better.

There is something strange about your setup.

MySQL is by default case sensitive when it comes to table names on Linux.

If you can access a table with both Url and UrL then either of the
following should be true:

- You have started mysqld with --lower-case-table-names=1
- The file system you are using to store tables is case insensitive.
- There is a new unknown bug in the character set handling in 4.1 that   causes this.

If the second case is true, and you are not running with
--lower-case-table-names=1 you will get a lot of strange problems,
similar to the ones that you are describing.

Please verify if the above is the case.

If not, please post the output for the following commands:

SHOW VARIABLES LIKE "lower_case_table_names";
CREATE TABLE foo (a int);
INSERT INTO FOO VALUES (1);
ALTER TABLE Foo ADD b int;

Regrads,
Monty
[1 Jul 2003 8:55] Clinton Gormley
I haven't been able to download the debug version yet, but 
(1) Lower case table names are off
(2) I'm using an ext3 file system

While the test that you asked for fails (as shown below), the original problem (ie select * from User; select * from user;) still occurs.

TEST RESULTS:
================
mysql> SHOW VARIABLES LIKE "lower_case_table_names";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | OFF   |
+------------------------+-------+
1 row in set (0.00 sec)
 
mysql> CREATE TABLE foo (a int);
Query OK, 0 rows affected (0.04 sec)
 
mysql> INSERT INTO FOO VALUES (1);
ERROR 1146: Table 'traveljury.FOO' doesn't exist
mysql> ALTER TABLE Foo ADD b int;
ERROR 1146: Table 'traveljury.Foo' doesn't exist
mysql>
[10 Jul 2003 4:47] Clinton Gormley
I have uploaded a trace file tracking the following commands:

mysql -p
> use traveljury;
> select * from User; (fails-->no table found)
> select * from user; (works)
> select * from User; (works)
[16 Jul 2003 4:32] Alexander Keremidarski
I tested it under RedHat 9 with both ext3 and reiserfs filesystems. Result is always correct:

mysql> select * from User;
ERROR 1146 (42S02): Table 'mysql.User' doesn't exist
mysql> select * from user;

-- some rows
 
mysql> select * from User;
ERROR 1146 (42S02): Table 'mysql.User' doesn't exist
[17 Jul 2003 4:30] Michael Widenius
I can't find any matching trace file in our 'secret' directory.
Can you please upload it again as bug-424.trace
[17 Jul 2003 11:11] Clinton Gormley
I have reloaded the trace file onto your ftp server called "bug-424.trace".