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

[2 Nov 2007 21:26] Peter Gulutzan
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.
[20 Nov 2007 4:11] Kevin Lewis
Jim, this test is causing some weirdness, with apparent hangs, and at least one crash.  Maybe you can get a better understanding of this.
[20 Nov 2007 17:18] Miguel Solorzano
Thank you for the bug report. Verified as described.
[27 Nov 2007 18:51] Jim Starkey
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.
[28 Nov 2007 15:57] Kevin Lewis
Code reviewed
[3 Dec 2007 17:51] Hakan Küçükyılmaz
Fix is in 6.0.4
[31 Dec 2007 14:05] MC Brown
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.