I've been experiencing some strange behaviour
in which mysqld will get into an odd state.
When doing certain long-running operations
(see below) on large tables (5-10 million rows;
20-30 columns per row -- mix of varchar and int)
with unique indeces mysqld will occasioanlly get
into a confused state where new table accesses
will hang forever in the "Opening Tables" state;
and other medium-to-long running writers
(and delayed-insert threads) will get stuck
in the "Closing Tables" state. Once mysql
has entered this state, the only way to clear
the problem is to "kill -9" the daemon. (Though
data corruption can usually be avoided by first
doing a "flush tables", then wating a minute,
then doing a "kill -TERM" on the daemon,
then waiting another minute, THEN doing
the "kill -9".)
I have not yet been able to isolate the exact conditions
which cause this behavoiur. Perhaps other people
have experienced something simmilar. I have seen it
happen during "alter table", "optimize table" and
"load data infile" into an empty table. In call cases,
the table had 5-10 million rows and handful of
indeces -- including a primary key or unique index.
This is happening with 3.23.36 an 3.23.34 running
on Solaris 7 / sparc.
I'll post a follow-up if I can isolate a simple test
case which demonstrates the problem.