Bug #25042 OPTIMIZE TABLE cause race condition in IO CACHE SHARE

Description:
When optimizing a MySQL table I get a very probable dead lock in the io cache code on windows XP and a BK-clone from the maint-tree.

The test is repeatable with the below test case (which also is found in myisam.test) when runned from mysql-test-run. With the --debug option I'm much less likely to dead lock as I can't repeat it.

At dead lock:
        5180    __tmainCRTStartup       pthread_cond_wait       
        4560    _callthreadstart        handle_shutdown	        
        6000    _callthreadstart        pthread_cond_timedwait
 	3968    _callthreadstart        _beginthread
(mi_repair_parallel) _callthreadstart        pthread_cond_wait
(thr_find_all_keys)  _callthreadstart        pthread_cond_wait
(thr_find_all_keys)  _callthreadstart        pthread_cond_wait

The (thr_find_all_keys)-threads are halted in the lock_io_cache function:
**Thread 1:
while ((!cshare->read_end || (cshare->pos_in_file < pos)) &&
           cshare->source_cache)
    {
      DBUG_PRINT("io_cache_share", ("reader waits in lock"));
      pthread_cond_wait(&cshare->cond, &cshare->mutex);
    }

**Thread 2:
while ((!cshare->read_end || (cshare->pos_in_file < pos)) &&
           cshare->running_threads)
    {
      DBUG_PRINT("io_cache_share", ("reader waits in lock"));
      pthread_cond_wait(&cshare->cond, &cshare->mutex);
    }

How to repeat:
# Test OPTIMIZE. This creates a new data file.
CREATE TABLE t1 (
  `_id` int(11) NOT NULL default '0',
  `url` text,
  `email` text,
  `description` text,
  `loverlap` int(11) default NULL,
  `roverlap` int(11) default NULL,
  `lneighbor_id` int(11) default NULL,
  `rneighbor_id` int(11) default NULL,
  `length_` int(11) default NULL,
  `sequence` mediumtext,
  `name` text,
  `_obj_class` text NOT NULL,
  PRIMARY KEY  (`_id`),
  UNIQUE KEY `sequence_name_index` (`name`(50)),
  KEY (`length_`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
#
INSERT INTO t1 VALUES
  (1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample1',''),
  (2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample2',''),
  (3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample3',''),
  (4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample4',''),
  (5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample5',''),
  (6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample6',''),
  (7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample7',''),
  (8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample8',''),
  (9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample9','');
#
SELECT _id FROM t1;
DELETE FROM t1 WHERE _id < 8;
--replace_column 6 # 7 # 8 # 9 # 11 # 12 # 13 # 14 # 15 # 16 #
SHOW TABLE STATUS LIKE 't1';
CHECK TABLE t1 EXTENDED;
OPTIMIZE TABLE t1;
CHECK TABLE t1 EXTENDED;
--replace_column 6 # 7 # 8 # 9 # 11 # 12 # 13 # 14 # 15 # 16 #
SHOW TABLE STATUS LIKE 't1';
SELECT _id FROM t1;
DROP TABLE t1;

Test that causes dead lock on windows XP when run from mtr

Attachment: myisam2.test (application/octet-stream, text), 1.62 KiB.

Duplicate of http://bugs.mysql.com/bug.php?id=24685

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/18852

ChangeSet@1.2353, 2007-01-26 13:54:11+01:00, 
Kristofer.Pettersson@naruto. +2 -0
  Bug#25042 OPTIMIZE TABLE cause race condition in IO CACHE SHARE
 
  - The condition variable implementation "lost" a signal to
    WaitOnSingleObject when a semaphore was released.
  - The signal could be consumed by a new call to pthread_cond_wait
    before all waiting threads had awoken.
  - The new implementation of pthread_cond_* uses events
    instead of semaphores. It also uses an extra lock to protect entry
    into a new cond wait before the broadcast has finished.

Laterst change set_
http://lists.mysql.com/commits/19941

Pushed to BK-5.0-maint, BK-5.1-maint, BK-5.1-runtime.

According to my findings the patch seems to be in the 5.1 main tree, versions 5.1.16. But I do not see it in the 4.1 or 5.0 main trees.

Fixed in 5.0.40

Noted in 5.0.40, 5.1.16 changelogs.

OPTIMIZE TABLE caused a race condition in the I/O cache.

It still exists in version 5.0.45 under centos 5 64bit or freebsd 6.2 32bit

when the set myisam_repair_threads = 1

mysql>repair table cdb_posts;
+-----------------+--------+----------+----------+
| Table           | Op     | Msg_type | Msg_text |
+-----------------+--------+----------+----------+
| Forum.cdb_posts | repair | status   | OK       | 
+-----------------+--------+----------+----------+
1 row in set (1 min 14.30 sec)

but when the set myisam_repair_threads = 2 (or more, for example: 10)

mysql>show processlist;
+----+------+-----------+-------+---------+------+-----------------------+------------------------+
| Id | User | Host      | db    | Command | Time | State                 | Info                   |
+----+------+-----------+-------+---------+------+-----------------------+------------------------+
|  1 | root | localhost | Forum | Query   |  1330 | Repair with 7 threads | repair table cdb_posts | 
|  2 | root | localhost | Forum | Query   |    0 | NULL                  | show processlist       | 
+----+------+-----------+-------+---------+------+-----------------------+------------------------+

mysql>select version();
+------------+
| version()  |
+------------+
| 5.0.45-log | 
+------------+

the table `cdb_posts` has 883671 rows，and 7 indexes;

under the FreeBSD 6.2 64bit/32bit or CentOS 5 64bit OS