I just discovered some weird behaviour with MySQL 4.0 (4.0.24 and
4.0.18) using InnoDB.
If you have two connections to mysql (I use the mysql client), one of
which has autocommit turned on, an the other turned off, a row deleted
from the client with autocommit turned on still shows up in the client
with autocommit turned off, even after a commit.
That's complicated, so here's an example.
CREATE TABLE bug_find (col1 VARCHAR(10) NOT NULL);
Now open two windows (I'll call them Window A and Window B).
Leave Window A alone (I am assuming your client is in auto-commit mode).
In Window B, type,
SET autocommit = 0;
In Window A, type
INSERT INTO bug_find (col1) VALUES ('a');
This should be committed automatically.
In Window B, type
SELECT * from bug_find;
The column should be there.
In Window A, type,
DELETE FROM bug_find;
Again, this should be committed.
In Window B, type,
SELECT * FROM bug_find;
Whoops - still there, even though it's been removed.
In Window A, type,
commit;
In Window B, type,
SELECT * FROM bug_find;
Still there.
To make it disappear from Window B, type,
commit;
That makes no sense. The changes Window B sees (that are made by Window
A) should not depend on issuing a commit - it has to see any data
committed by Window A (unless it's trying to avoid dirty reads, which
isn't the case here).
If Window B is in autocommit mode, you see the deletion right away. It
seems to be the autocommit=0 that's screwing stuff up. I haven't tested
this with the JDBC drivers, or with the Query Browser, or anything else.
It may just be a MySQL client issue.
This is a big problem with data consistency. Note that this bug also
exists for updates (any updates made in Window A are not seen by Window
B until Window B issues a commit). Also, turning autocommit off in a
session half way, and the same behaviour happens.
Is this a known bug?
David.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
