Bug #33177 Table creation fails after error 305 and tablespace change
Submitted: 12 Dec 2007 17:10 Modified: 15 May 2009 17:04
Reporter: Giuseppe Maxia Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Falcon storage engine Severity:S2 (Serious)
Version:6.0.4 OS:Any (Linux and Mac OS X)
Assigned to: Christopher Powers CPU Architecture:Any
Tags: CREATE TABLE, F_MEMORY, Tablespace

[12 Dec 2007 17:10] Giuseppe Maxia
Description:
After the following actions:
* an error of memory exhaustion
* a removal of a table

Falcon can't re-create the removed table in a different tablespace, due to an error.

For example:

insert into t2 select * from t1

Query OK, 500000 rows affected (18.06 sec)
Records: 500000  Duplicates: 0  Warnings: 0

alter table t2 drop primary key
ERROR 1296 (HY000): Got error 305 'record memory is exhausted' from Falcon

alter table t2 add key (c4)
ERROR 1005 (HY000): Can't create table 'test.#sql-656_1' (errno: 156)

How to repeat:
use test;
set global falcon_record_memory_max=1024*1024*128; 

drop tablespace ts1 engine=falcon;

create tablespace ts1 add datafile 'ts1' engine=falcon;

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  c1 int(11) NOT NULL,
  c2 char(3) NOT NULL,
  c3 char(3) NOT NULL,
  c4 date NOT NULL,
  c5 char(5) NOT NULL,
  c6 char(8) NOT NULL,
  c7 double NOT NULL,
  c8 datetime NOT NULL,
  c9 double NOT NULL,
  c10 datetime NOT NULL,
  c11 char(3) NOT NULL,
  c12 char(3) NOT NULL,
  c13 int(11) NOT NULL,
  c14 char(1) DEFAULT 'n',
  PRIMARY KEY (c4,c1,c3,c2,c5,c11,c10,c12)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

delimiter //
drop procedure if exists fill_table //
create procedure fill_table(nrecords int)
deterministic
begin
    declare i int default 0;
    FILL:
    loop
        set i = i + 1;
        if i > nrecords then
            leave FILL;
        end if;
        insert into t1 values (i, 'xxx', 'xxx', 
                @dt:= date('1900-01-01' + interval i second),
                'xxxx', 'xxxxxxxx', i, @dt, 
                i/2, @dt - interval 1 hour, 'xxx', 'xxx', 1000, 'n'); 
    end loop; 
end//

delimiter ;

# increase the amount of records until you get an exceeded record memory error
call fill_table(500000);

create table t2 like t1;
alter table t2 engine=falcon;
insert into t2 select * from t1;
alter table t2 drop primary key;
alter table t2 add key (c4);
truncate t2;
drop table t2;

create table t2 like t1;
alter table t2 engine=falcon tablespace ts1;
insert into t2 select * from t1;
alter table t2 drop primary key;
alter table t2 add key (c4);
truncate t2;
drop table t2;

create table t2 like t1;
alter table t2 engine=falcon;
insert into t2 select * from t1;
alter table t2 drop primary key;
alter table t2 add key (c4);
truncate t2;
drop table t2;

drop table t1;
[12 Dec 2007 19:20] Giuseppe Maxia
Verified also on Mac OS X 10.4 and 10.5.
[22 Apr 2008 22:03] Kevin Lewis
Sergey Vojtovich wrote;

The problem occurs during "alter table t2 drop primary key" query:
- alter table code creates temporary falcon table (to avoid confusion: here and below I use term "temporary table", but afaics from an engine point of view it is regular table);
- alter table code reads data from real table and writes it to a temporary table;
- at some point (on write_row) falcon gets out of record memory limit and returns an error;
- alter table code detects this error, stops copying data and attempts to remove temporary table;
- when falcon drops a table, it removes related information from system tables (e.g. system.fields);
- to remove information from system table, it must allocate record buffer;
- as we're out of record memory, alloc fails => falcon fails to drop a table.

As a result temporary table is not dropped.

If I understand this problem correctly it is a kind of chicken and egg issue. I believe it could be solved by not applying falcon_record_memory_max limit for system tables handling.
[25 Apr 2008 15:50] Kevin Lewis
Jim says he want to look at this problem.  Why cann't the second drop table complete?  The forced scavenge aught to work.
[25 Nov 2008 16:59] Kevin Lewis
Sergey,  I think the core issue here is not that an error 305 occurs from a drop table, because that is an offline alter.  But it seems like the server and Falcon get mixed up about what tables still exist.  You have fixed errors like this before with a well placed rollback.  So maybe you can have a look at this.
[14 Jan 2009 18:19] Kevin Lewis
Sergey Vojtovich wrote;

I still think that I'm not a person who could fix this one. Below are some
explanations.

> > [25 Apr 2008 17:50] Kevin Lewis
> >
> > Jim says he want to look at this problem.  Why cann't the second drop table
> > complete?  The forced scavenge aught to work.

If I understand correctly, scavanger is intended to release unused blocks.
But when offline ALTER TABLE is issued, it needs memory for all records. And
if table copy requires more memory than record_memory_max, we will end up
with an error.

So the scavenger seems to be useles here.

Anyway an error during ALTER TABLE is absolutely acceptable.

> > [25 Nov 2008 17:59] Kevin Lewis
> >
> > Sergey,  I think the core issue here is not that an error 305 occurs from a
> > drop table, because that is an offline alter.  But it seems like the server
> > and Falcon get mixed up about what tables still exist.  You have fixed errors
> > like this before with a well placed rollback.  So maybe you can have a look
> > at this.
Yes and no. First of all, just to clarify, this error occurs not from the DROP
TABLE statement, but from the ALTER TABLE when it is attempting to copy
data.

If we get this error with DROP TABLE, it will be detected by the server. In
this case server will refuse to drop, that is preserve the .frm file,
keep the table and return an error.

With offline ALTER TABLE it is slightly different. Server is creating
non-temporary altered table in Falcon with some special name, like
'#sql-656_1'. There is no .frm file for this table. This table is a kind of
non-temporary temporary table (sorry for jam).

When copying data to this table, at some point Falcon gets out of record
memory and returns an error. Server detects this error, aborts alter
table process and cleaning up.

Everything is fine until this point. The problem occurs when the server is
cleaning up, it is attempting to remove the table with special name. At
this point server doesn't expect that table deletion may fail. But Falcon
cannot delete this table because it is out of record memory even for
operations on "system" tables. As a result table with name '#sql-656_1' is
preserved.

When doing next offline ALTER TABLE, the server is attempting to create
another table with the same "special" name - '#sql-656_1'. And it fails
because this table still exists in Falcon.

Now, how I think it may be solved:
- probably chill/thaw may help, if I understand correctly, this writes pages
  to disk, when we're running out of record memory.
- probably do not apply record memory limit for system tables.
- (server) do not reset a counter for special table names. That is with the
  second alter table create a table with name '#sql-656_2'. Which is evil -
  I don't think we want to preserve trash in Falcon datafiles.
- (server) keep trying to drop a table until it gets dropped. Dummy - may be
  a dead loop.
- (server) keep table names list, that we failed to drop during ALTER TABLE
  and drop them later. Dummy as above.

Regards,
Sergey
[14 Jan 2009 18:29] Kevin Lewis
Chris has found an imcmompatibility between anything that holds SyncSysDDL and the scavenger.  At the top of Database::scavenge() is a call to updateCardinalities().  This function gets a shared lock on SyncSysDDL which makes the scavenger wait for any DDL activity to finish before records can get scavenged. This prevents the creation of ANY large indexes. 

He is currently implementing a solution in which updateCardinalities() is moved into its own thread with its own schedule.  It needs to be independent of the scavenger, becasue it is not related.  That was just a convenient place to put it.

That activity might make this bug moot, by preventing an error 305 during almost all DDLs.  BUT, what if it does still occur?  I still do not understand the chicken and egg issue.  If an error occurs on a drop temporary table, why can't the server retry it or avoid using that temporary name again?
[15 Jan 2009 13:28] Sergey Vojtovich
Another option is to change temporary tables naming rules in ALTER TABLE. Currently it's something like '#sql-' process id '_' thread id. Either add a counter to this name, or keep creating a table with different temporary names until succeeded.

This would also require trash detector in an engine, as ALTER TABLE may leave unreferenced tables. It may be executed on engine startup/shutdown and remove all tables starting with '#sql'.

I believe this would be more than just a bug fix.
[3 Mar 2009 7:33] Bugs System
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/68093

3046 Christopher Powers	2009-03-03
      Bug #42651 "Regression: falcon_bug_22169-big started to fail with error 305"
      Bug #33177 "Table creation fails after error 305 and tablespace change"
      Bug #32838 "Falcon; error 1296 : Got error 305 'record memory is exhausted'"
      
      The fix for these bugs is the first of several improvements
      to Falcon's memory management (Worklog TBD).
      
      Falcon out-of-memory errors are caused by a combination of things.
      Recent improvements to the Scavenger and to the Backlogging subsystem
      (Bug#42592) have contributed to the resolution of these bugs, however,
      certain operations can still fill the record cache to the point where
      scavenging is ineffective.
      
      Scavenging efficiency will be greatly improved by allocating record
      data and metadata separately. The record cache now stores only
      actual record data, and Record and RecordVersion objects (metadata)
      are allocated from separate memory pools.
      
      The metadata memory pools are completely homogeneous, with no memory
      fragmentation. The record cache will also be far less fragmented,
      because large blocks of record data will no longer be interspersed
      with very small blocks of object data.
      
      Decoupling the data and metadata will also greatly reduce the number of
      out-of-memory conditions--typically seen during large inserts and
      updates--because the memory pools are allowed to grow independently.
      
      These memory pools may flucuate considerably during massive transactions,
      depending upon the record makeup and type of operation. This flucuation,
      however, serves only to emphasize the value managing these memory pools
      separately.
      
      One side-effect of this change is that, while the record cache max size
      remains fixed, the record metadata caches can grow unbounded. Although
      this is not unprecedented (Falcon's general purpose memory pool has
      always been unbounded), one remaining challenge is to ensure that
      the Falcon memory manager releases resources back to the system as
      soon as possible.
[2 Apr 2009 17:38] Bugs System
Pushed into 6.0.11-alpha (revid:hky@sun.com-20090402144811-yc5kp8g0rjnhz7vy) (version source revid:christopher.powers@sun.com-20090303070929-ig36zlo3luoxrm2t) (merge vers: 6.0.11-alpha) (pib:6)
[15 May 2009 17:04] MC Brown
A note has been added to the 6.0.11 changelog: 

If Falcon runs out of memory while inserting records and you try to alter the affected table, you may get a record memory is exhausted error, and the table can no longer be used or accessed.