(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
