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: | |
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
[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".