MySQL Bugs: #32062: Falcon: slow shutdown after many blob updates, then crash

Bug #32062	Falcon: slow shutdown after many blob updates, then crash
Submitted:	2 Nov 2007 21:26	Modified:	31 Dec 2007 14:05
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S2 (Serious)
Version:	6.0.4-alpha-debug	OS:	Linux (SUSE 10 64-bit)
Assigned to:	Jim Starkey	CPU Architecture:	Any

Description:
I create a Falcon table with a BLOB or TEXT column.
I update the column many times.
Eventually the procedure appears to hang,
so I say 'mysqladmin shutdown' to stop the server.
This succeeds, but takes several hours.
Then I restart the server and say 'update'.
This crashes.
But the crash only happens once -- I restart
the server and say 'update' again, and see no
crash.

The procedure is very similar to the one in
Bug#26643 Falcon: no blob space recovery
but the concerns here are not about space recovery.
The concerns are: the apparent hang (which is only
a 'performance' bug if the hang eventually ends),
and the crash (which isn't as bad as it sounds if
only happens once on startup).

The procedure in Bug#27719 (closed) was also similar. 

How to repeat:
Create and run a procedure as follows:

delimiter //
use test//
drop database k//
create database k//
use k//
create table t (s1 mediumtext) engine=falcon//
insert into t values (repeat('a',1000))//
create procedure p ()
  begin
    declare v int default 0;
    while v < 1000000 do
    if v mod 1000 = 0 then select v; end if;
    update t set s1 = repeat('a',rand()*1000);
    set v = v + 1;
    end while;
  end//
call p()//

I did it four times.
Twice it succeeded.
Once it stopped displaying after 39000 rows.
Once it stopped displaying after 76000 rows.
Since it uses RAND(), variation is understandable.

For the times when it stopped displaying, I waited
a long time. I'm reasonably sure it was over an hour
but I wasn't timing this. After waiting, on another
shell, I said:
mysqladmin --user=root shutdown
Immediately the client program said
"ERROR 2013 (HY000): Lost connection to MySQL server during query"
and mysqld said
"071102 13:05:38 [Warning] /usr/local/mysql/libexec/mysqld:
Forcing close of thread 1  user: 'root'"
... but mysqld did not shut down immediately.
On the second occasion (after cancelling for 76000 rows)
the shutdown took four hours.

After that, both times (i.e. after slow but successful
'mysqladmin shutdown' for the two jobs that stopped
displaying), I restarted mysqld and the client and said:
  use k
  show create procedure p;
  show create table t;
  update t set s1 = repeat('a',100);
Both times the result was a mysqld crash.

After that, both times, I restarted mysqld and executed
the same statements, without crashing.

Jim, this test is causing some weirdness, with apparent hangs, and at least one crash.  Maybe you can get a better understanding of this.

Thank you for the bug report. Verified as described.

Switch blob garbage collection from nested loop (n-squared) to a blob id bitmap.  This reduces the garbage collection time from about four hours (reported) to approximately 5 seconds.

Code reviewed

Fix is in 6.0.4

A note has been added to the 6.0.4 changelog: 

Inserting, updating and deleting a large number of BLOB records in a Falcon table would take significant amount of time and may prevent shutdown.