(Perl Script mysqlbug not used as Perl is not installed) SUBJECT: Table locking under ISAM can fail ORGANIZATION: Owens Services B.O.P. Ltd, New Zealand LICENCE: None (expired support license) SYNOPSIS: User can cause table locking to fail by USE'ing the same database but spelt with different case sensitivity SEVERITY: Serious PRIORITY: High CLASS: sw-bug RELEASE: 3.23.46-max-nt DESCRIPTION: Table locking in ISAM and MyISAM tables can be made to fail by simply specifying the database name differently (upper/lower case). A user performing a table lock in database 'test' has no locking effect on another user in database 'TEST'.
HOW_TO_REPEAT: Execute the following script to create a database called 'test' with a table called 'dummy', which consists of a single field called 'bogus'. ===== database script starts ===== CREATE DATABASE test; USE test; CREATE TABLE dummy ( bogus VARCHAR(50) ) TYPE=ISAM; ===== database script ends ===== I have two examples of how to repeat this. The first example, (A) is not as simple as (B) but tries a different tact on the same problem. For simplicity, try example (B) first. Example (A) =========== Open two instances of mysql.exe (referred to as (1) and (2) from here on in) In (1) enter: USE test; LOCK TABLES dummy WRITE; In (2) enter: USE test; SELECT * FROM dummy; As expected, (2) will appear to be waiting for the table 'dummy' to be unlocked. In (1) enter: USE mysql; USE TEST; UNLOCK TABLES; (2) will be released by the 'unlock' statement, and display the contents of the table 'dummy' as expected. From here on, the error can be seen. Note that the database name has changed from 'test' to 'TEST'. In (1) enter: LOCK TABLES dummy WRITE; In (2) enter: SELECT * FROM dummy; (2) will not wait, and will display the result immediately. We seem to have confused the server as to which 'database/table' we are locking, and (2) has the ability to query the table. ===== End Example (A) ===== Example (B) =========== Open two instances of mysql.exe (referred to as (1) and (2) from here on in) In (1) enter: USE test; LOCK TABLES dummy WRITE; In (2) enter: USE Test; SELECT * FROM dummy; (2) is using the same database but has used an uppercase 'T' in the database name. The result of this small inconsistency is that the locking does not work. (2) is seen to return the result immediately ===== End Example (B) ===== We had been trying to find a problem in a MyISAM database where table corruption would occur, and the error message 'Got error 134 from Table Handler'. We figured that we should try to repeat the error consistently before we reported it. We currently suspect that the table corruption is caused by the Table Handler being unable to synchronize updates from multiple clients where the database name differs (case sensitivity). The table locking problem could be to blame for this. We are able to repeat this problem with MyISAM tables as well. FIX: We will be ensuring all our applications lower the case of the database name before using. We believe this will work satisfactorily for the meantime. The danger exists that later applications may not use the same case, and access the database, possibly causing this corruption. ===== Other information ===== Platform: Win2000 (5.00.2195 Service Pack 2) Client and Server information: mysql> \s -------------- mysql Ver 11.15 Distrib 3.23.46, for Win95/Win98 (i32) Connection id: 23 Current database: Current user: ODBC@localhost Server version: 3.23.46-max-nt Protocol version: 10 Connection: . via named pipe Client characterset: latin1 Server characterset: latin1 UNIX socket: MySQL Uptime: 51 min 13 sec Threads: 1 Questions: 205 Slow queries: 0 Opens: 63 Flush tables: 1 Open tables: 4 Queries per second avg: 0.067 -------------- Jason. Jason Garrett Owens Services BOP Ltd Phone: +64 7 5756274 Fax: +64 7 5750262 Cell: +64 27 2212246 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php