MySQL Bugs: #19138: The table 'foo' is full displayed on HEAP tables which shouldn't be full

Bug #19138	The table 'foo' is full displayed on HEAP tables which shouldn't be full
Submitted:	17 Apr 2006 12:52	Modified:	3 May 2007 6:02
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Memory storage engine	Severity:	S3 (Non-critical)
Version:	5.0.20, 5.0.19, 4.1.20	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
Hi,

I'm experiencing a strange issue.
I've the following heap table :

CREATE TABLE `guest` (
  `pseudo` int(10) unsigned NOT NULL default '0',
  `date` int(10) unsigned NOT NULL default '0',
  `forum` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`pseudo`),
  KEY `forum` (`forum`),
  KEY `date` USING BTREE (`date`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1 MAX_ROWS=10000

It contains exactly 2000 rows when it begins to fail. (notice it matches MAX_ROWS / 5)

SELECT COUNT(*) FROM guest; 
+----------+
| COUNT(*) |
+----------+
| 2000     |
+----------+

If I try to insert a new element in the table, it fails with a table is full error : 

INSERT INTO guest (pseudo,date,forum) VALUES (INET_ATON('81.49.151.246'),1145276916,1) ON DUPLICATE KEY UPDATE date=1145276916,forum=1;
ERROR 1114 (HY000): The table 'guest' is full

However if I recreate another heap table "guest1" with the same definition and the same content, the insert works properly.

Note I'm doing a lot of concurrent select, insert and delete on the guest table.

Any idea of what could be wrong ? (sorry to not provide a more reproducable testcase)

Thanks and regards,
  Jocelyn

How to repeat:
CREATE TABLE `guest` (
  `pseudo` int(10) unsigned NOT NULL default '0',
  `date` int(10) unsigned NOT NULL default '0',
  `forum` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`pseudo`),
  KEY `forum` (`forum`),
  KEY `date` USING BTREE (`date`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1 MAX_ROWS=10000

Try to insert more than 2000 entries while doing a lot of select and DELETE FROM guest WHERE date<1234.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.20, and inform about the results. Do you have a complete test case according to your description:

"Try to insert more than 2000 entries while doing a lot of select and DELETE FROM guest WHERE date<1234."

I had inserted random 2074 rows and was not able to repeat the behaviour you described with your insert:

mysql> select count(*) from `guest`;
+----------+
| count(*) |
+----------+
|     2074 |
+----------+
1 row in set (0.00 sec)

mysql> INSERT INTO guest (pseudo,date,forum) VALUES
    -> (INET_ATON('81.49.151.246'),1145276916,1) ON DUPLICATE KEY UPDATE
    -> date=1145276916,forum=1;
Query OK, 1 row affected (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.21    |
+-----------+
1 row in set (0.00 sec)

Hence the questions above...

Hi,

I'll try with 5.0.20.
Unfortunately I've no complete testcase yet to reproduce the issue, but I'll do my best to find how to reproduce the issue (note than changing the MAX_ROWS from 10000 to 100000 solves the issue).

Regards,
  Jocelyn

Please, reopen this bug report if you'll find a way to repeat the problem on 5.0.20.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi,

The issue just occured again for me with 4.1.20.
I've still no real clues on why it occurs. (still time it began to fail with about 1000 entries in the table)

  Jocelyn

Is it 4.1.20 or 5.0.20? Last time we discussed version 5.0.x. Anyway, please, send the dump of the table when it demonstrates this behaviour to you. 

Send you my.cnf content also.

Can it be just a hardware fault?

Hi,

This time is on 4.1.20, and on a different server, so it couldn't be a hardware fault.
The dump doesn't help as well : if I reload the dump in a new heap table, the problem doesn't occur.
I suspect the issue to be related to some block fragmentation issue (because there is a lot of delete/insert done on this heap table).

  Jocelyn

Any ideas on how to repeat the behaviour described are still welcomed. Can you send typical "uage profile" for this HEAP table?

Hi,

The typical usage :

INSERT INTO guest (pseudo,date,forum) VALUES (INET_ATON('$ip_adress'),NOW(),$my_id) ON DUPLICATE KEY UPDATE date=NOW(),forum=$my_id
DELETE FROM guest WHERE date < NOW() - INTERVAL 5 minute
SELECT SQL_NO_CACHE COUNT(*) FROM guest WHERE forum=$my_id

No other queries are used on this table.

Those queries are executed each time a page is loaded (and the site has more than 1M pageviews a day)

  Jocelyn

Please, send your my.cnf content. Try to repeat with a newer versions, 5.0.24 and 4.1.21, just released, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

It's a duplicate of this bug:
http://bugs.mysql.com/bug.php?id=18160

Duplicate of bug #18160, likely.