MySQL Bugs: #25712: insert delayed and check table run together report crashed tables

Bug #25712	insert delayed and check table run together report crashed tables
Submitted:	19 Jan 2007 1:46	Modified:	7 Jun 2007 16:14
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	4.1, 5.0.36BK, 5.1.16BK	OS:	Windows (windows 2003)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any
Tags:	bfsm_2007_02_15, corruption, myisam

Description:
testcase contained the following elements running in 20 threads;

thread_cache_size=512
insert ignore into ...
insert delayed into ...
delete from ...
check table ...

the tables get more and more corrupted as more rows are added, eventually giving errors like;

Table   Op      Msg_type        Msg_text
------------------------------------
test.user       check   warning Size of indexfile is: 1720320      Should be: 1719296
test.user       check   warning Auto-increment value: 12430 is smaller than max used value: 814969
test.user       check   status  OK

How to repeat:
compile and run testcase.c

Suggested fix:
don't get corrupted

will create public testcase later.

testcase causing error 134, 126 on 5.0.36

Attachment: testcase.c (text/plain), 6.73 KiB.

I can very easily repeat the corruptions when the recently uploaded public testcase.c is pointed to hit a windows mysql server.

Can't repeat with linux version of mysql.

all you need to reproduce this table crash is:
>=2 threads running the following : 

insert delayed into ...
check table ...

perhaps the delayed thread doesn't respect table locks properly? not sure why it's not happening on linux.

corruptions test output

Attachment: bug25712_session_output.txt (text/plain), 2.88 KiB.

I tested the following versions on windows, which exhibit this bug (keyfile crashes)

5.1.16BK
5.1.11

5.0.36BK
5.0.27
5.0.26
5.0.24a
5.0.24
5.0.13-rc

4.1.21 (note - didn't see errors in mysqld.err, but CHECK TABLE reported corruption)

In order to realize an error has occurred quicker, the testcase.c can be slightly modified to include a SELECT or a normal INSERT statement in the running loop.

Because currently, the corruption may not be noticed immediately, as INSERT DELAYED doesn't return those errors.

Shane, I repeated the tests on Linux and Solaris because I do not have a working Windows environment. I failed as expected.

Regarding Windows, I think this could be related to a semaphore problem that Kristofer found in Bug#25042.

The fix seems to be part of 5.1.16. But after you added 5.1.16BK to the tested versions list. So I ask you, please to try to repeat it on the latest 5.1 on Windows.

Regards, Ingo

ok, i'll update the bug once i've re-tested with newer sources including that patch.

5.1.16 official binaries still shows corruption on windows when testing.

5.0.40BK on windows:

mysql> check table t1;
+---------+-------+----------+--------------------------------------+
| Table   | Op    | Msg_type | Msg_text                             |
+---------+-------+----------+--------------------------------------+
| test.t1 | check | warning  | Table is marked as crashed           |
| test.t1 | check | error    | Can't read key from filepos: 4262912 |
| test.t1 | check | error    | Corrupt                              |
+---------+-------+----------+--------------------------------------+
3 rows in set, 1 warning (0.02 sec)

I am setting to verified state again.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26602

ChangeSet@1.2650, 2007-05-14 21:43:31+05:00, svoj@mysql.com +2 -0
  BUG#25712 - insert delayed and check table run together report crashed
              tables
  
  In case system doesn't have native pread/pwrite calls (e.g. Windows)
  and there is CHECK TABLE runs concurrently with another statement that
  reads from a table, the table may be reported as crashed.
  
  This is fixed by locking file descriptor when my_seek is executed on
  MyISAM index file and emulated pread/pwrite may be executed concurrently.
  
  Affects MyISAM tables on platforms that do not have native
  pread/pwrite calls (e.g. Windows).
  
  No deterministic test case for this bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26840

ChangeSet@1.2650, 2007-05-16 23:42:32+05:00, svoj@mysql.com +2 -0
  BUG#25712 - insert delayed and check table run together report crashed
              tables
  
  In case system doesn't have native pread/pwrite calls (e.g. Windows)
  and there is CHECK TABLE runs concurrently with another statement that
  reads from a table, the table may be reported as crashed.
  
  This is fixed by locking file descriptor when my_seek is executed on
  MyISAM index file and emulated pread/pwrite may be executed concurrently.
  
  Affects MyISAM tables on platforms that do not have native
  pread/pwrite calls (e.g. Windows).
  
  No deterministic test case for this bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26891

ChangeSet@1.2651, 2007-05-17 12:43:52+05:00, svoj@mysql.com +1 -0
  Addition to fix for
  BUG#25712 - insert delayed and check table run together report
              crashed tables
  
  Let MY_THREADSAFE have distinct value. Some functions call my_seek
  passing MyFlags argument directly to it. This may cause unnecessary
  locks, which may finally lead to a dead-lock (specifically see my_lock).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26900

ChangeSet@1.2652, 2007-05-17 15:23:59+05:00, svoj@mysql.com +1 -0
  Addition to fix for
  BUG#25712 - insert delayed and check table run together report crashed
              tables
  Fixed wrongly applied patch.

Pushed into 5.1.19-beta

Pushed into 5.0.44

Pushed into 4.1.23

A note has been added to the 5.1.19, 5.0.44 and 4.1.23 changelogs.