MySQL Bugs: #2831: --external-locking does not fully work with --myisam-recover

Bug #2831	--external-locking does not fully work with --myisam-recover
Submitted:	16 Feb 2004 15:06	Modified:	27 Sep 2008 9:51
Reporter:	Peter Zaitsev (Basic Quality Contributor)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	4.0	OS:	Any (all)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
If --external-locking is used together with --myisam-recover this still does not 
guaranty  you will not run on corrupted table as table is not checked if other node modifying table crashes.

How to repeat:
Start 2 MySQL servers on the same data files, using --external-locking 

Node1:

create table mt(i int, key(i));

Node2:  
# open for the first time.
select * from mt; 

Node1: 

lock tables mt write;
insert into mt values (1);
insert into mt values (3);

Console:  kill -9 Node1

Node2:

select * from mt;
check table mt; 

Suggested fix:
Perhaps the same code which detects what table was changed and we need to flush 
key blocks should detect what it was not closed properly and initiate myisam-recover process.

We do now check if the table has been properly unlocked after each external lock. If it is not, we check and repair it.

For the technical interested:
We set the field 'open_count' before each modification to the table and reset it before each unlock. If the open_count is set after a successful locking, another server 'forgot' to unlock the table (because it crashed).

Fixed in 4.0.22.

Sorry, but the suggested approch doesn't work so I had to revert the fix.

The better fix would be to mark the old myisam index file as invalid and force MyISAM to reopen the table when it finds such a header.  This is however too big change in MySQL 4.0 and has to wait until MySQL 5

In the mean time: Don't run repair or optimize table on a table that is shared between multiple MySQL severs.

I believe we have no plans to fix --external-locking.