MySQL Bugs: #41709: myisam external locking doesn't work as advertised

Bug #41709	myisam external locking doesn't work as advertised
Submitted:	23 Dec 2008 10:59	Modified:	1 May 2009 18:42
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.70, 5.1.30	OS:	Any (windows+linux+solaris)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	corruption, external locking, myisam

Description:
I read http://dev.mysql.com/doc/refman/5.0/en/external-locking.html
and it says you can enable external locking if you have to run other programs such as myisamchk while the mysqld server is running.

mysql> show global variables like '%external%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| skip_external_locking | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)

I still got index corruptions though, while server accessed the table:

E:\mysql-enterprise-gpl-5.0.70-winx64\bin>myisamchk -o ..\data\test\t1
- recovering (with keycache) MyISAM-table '..\data\test\t1'
Data records: 254

In the other window.... things aren't so pretty:
(1015) - HY000 (Can't lock file (errno: 11)): insert into t1 values (-24108,' 
(1015) - HY000 (Can't lock file (errno: 11)): insert into t1 values (28380,'f (1015) - HY000 (Can't lock file (errno: 11)): update t1 set name='mishi\'s'  <cut ..>
(1194) - HY000 (Table 't1' is marked as crashed and should be repaired)
(1194) - HY000 (Table 't1' is marked as crashed and should be repaired)
(126) - HY000 (Incorrect key file for table '.\test\t1.MYI'; try to rep

How to repeat:
run server:

mysqld --external-locking=1 --delay_key_write=off --query_cache_size=0 --key_buffer_size=0 --concurrent-insert=0

now create a table:
create table t1(id int,name varchar(255),key(id,name),key(name,id),key(name),key(id))engine=myisam;

in some threads do basic inserts/updates/selects to that table.

in a shell window run myisamchk:
myisamchk -o ..\data\test\t1

I can upload a proper testcase later if needed.

testcase:  run this SP and while it runs, run myisamchk -o on table t1.
the last check table will report corruption..

delimiter //
drop procedure if exists `p1`//
create procedure `p1`(`num` int unsigned)
begin
  declare `i` int unsigned default '0';
  declare continue handler for sqlwarning begin end;
  drop table if exists `t1`;
  create table `t1`(`id` int,`name` varchar(255),key(`id`,`name`),key(`name`,`id`),key(`name`),key(`id`))engine=myisam;
  repeat
    insert into `t1` values (`i`*`num`,`num`*`i`);
    set `i`:=`i`+1;
  until `i` > `num` end repeat;
end//
call `p1`(200000)//
delimiter ;
check table `t1` extended;

Stealing this one, as it seem to be pure myisam issue.

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

2860 Narayanan V	2009-04-20
      Bug#41709 myisam external locking doesn't work as advertised
      
      When an extended repair is run on a table, the datafile of
      the table is re-written, but the server does not refresh its
      datafile pointer. This causes check to report  corruptions, 
      because there is a mis-match between the datafile sizes 
      recorded in the index file and the size of the actual datafile.
      
      This patch fixes the problem by refreshing the datafile pointer
      everytime a rewrite of the datafile associated with the table is
      detected.
     @ mysql-test/r/myisam.result
        Contains the result file for the tests.
     @ mysql-test/t/myisam.test
        Run insert and myisamchk on the table being
        inserted into to ensure that the server is
        pointing to the correct datafile pointer, after
        myisamchk rewrites it.
     @ storage/myisam/mi_locking.c
        close and open the datafile pointer everytime
        the datafile is re-written.

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

2729 Narayanan V	2009-04-20
      Bug#41709 myisam external locking doesn't work as advertised
            
      When an extended repair is run on a table, the datafile of
      the table is re-written, but the server does not refresh its
      datafile pointer. This causes check to report  corruptions, 
      because there is a mis-match between the datafile sizes 
      recorded in the index file and the size of the actual datafile.
      
      This patch fixes the problem by refreshing the datafile pointer
      everytime a rewrite of the datafile associated with the table is
      detected.
     @ myisam/mi_locking.c
        close and open the datafile pointer everytime
        the datafile is re-written.
     @ mysql-test/r/myisam.result
        Contains the result file for the tests.
     @ mysql-test/t/myisam.test
        Run insert and myisamchk on the table being
        inserted into to ensure that the server is
        pointing to the correct datafile pointer, after
        myisamchk rewrites it.

The proposed fix will not be pushed. It only takes care of situations when myisamchk did rename of data file. It doesn't take into account that index file may (or may not) be renamed as well.

According to mysqld --help output we never supported running myisamchk to repair/analyze/reorder/etc tables concurrently with running server.

Manual section "7.3.4. External Locking" should say that running a server with external_locking enabled, makes safe to run myisamchk in read-only mode, that is to check tables. Running myisamchk to repair a table will likely result in massive table corruption. To avoid this corruption one must ensure that the table is closed by all running mysqld instances.

Relevant to this issue:

http://dev.mysql.com/doc/refman/5.1/en/crash-recovery.html

"If the server is run with external locking enabled, you can use myisamchk to check tables at any time. In this case, if the server tries to update a table that myisamchk is using, the server will wait for myisamchk to finish before it continues.

If you use myisamchk to repair or optimize tables, you must always ensure that the mysqld server is not using the table (this also applies if external locking is disabled). If you don't stop mysqld, you should at least do a mysqladmin flush-tables before you run myisamchk. Your tables may become corrupted if the server and myisamchk access the tables simultaneously."

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated text for http://dev.mysql.com/doc/refman/5.1/en/external-locking.html:

If you use myisamchk to perform table maintenance operations on
MyISAM tables, you must either ensure that the server is not running,
or that the server has external locking enabled so that it locks
table files as necessary to coordinate with myisamchk for access to
the tables. The same is true for use of myisampack to pack MyISAM
tables.   

If the server is run with external locking enabled, you can use
myisamchk at any time for read operations such a checking tables. In
this case, if the server tries to update a table that myisamchk is
using, the server will wait for myisamchk to finish before it
continues. 

If you use myisamchk for write operations such as repairing or
optimizing tables, or if you use myisampack to pack tables, you must
always ensure that the mysqld server is not using the table. If you
don't stop mysqld, you should at least do a mysqladmin flush-tables
before you run myisamchk. Your tables may become corrupted if the
server and myisamchk access the tables simultaneously.