MySQL Bugs: #55559: MAX_HEAP_TABLE_SIZE does not work as specified

Bug #55559	MAX_HEAP_TABLE_SIZE does not work as specified
Submitted:	26 Jul 2010 14:15	Modified:	27 Jul 2010 15:10
Reporter:	Sven Sandberg	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Memory storage engine	Severity:	S2 (Serious)
Version:	5.1, trunk	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MAX_HEAP_TABLE_SIZE is supposed to limit the size of tables using the MEMORY storage engine, according to http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_heap_table_...

However, the limit is much bigger than the value specified by this variable.

How to repeat:
# In this test case, we set the max size to 16384 bytes, but the server allows
# us to insert 100000 bytes, more than 6 times more.
# If we insert one more row of 10000 bytes, it fails with error
# "1114: The table 't1' is full"

SET SESSION MAX_HEAP_TABLE_SIZE = 16384;
CREATE TABLE t1 (a VARCHAR(10000)) ENGINE = MEMORY;
SELECT @@SESSION.MAX_HEAP_TABLE_SIZE;
SHOW CREATE TABLE t1;
--let $n= 10
while ($n) {
  INSERT INTO t1 VALUES (REPEAT('x', 10000));
  --dec $n
}

Verified with current mysql-5.1 from bzr:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.1.50-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET SESSION MAX_HEAP_TABLE_SIZE = 16384;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (a VARCHAR(10000)) ENGINE = MEMORY;
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT @@SESSION.MAX_HEAP_TABLE_SIZE;
+-------------------------------+
| @@SESSION.MAX_HEAP_TABLE_SIZE |
+-------------------------------+
|                         16384 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE t1;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                 |
+-------+----------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` varchar(10000) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>  INSERT INTO t1 VALUES (REPEAT('x', 10000));
Query OK, 1 row affected (0.00 sec)

mysql>  INSERT INTO t1 VALUES (REPEAT('x', 10000));
Query OK, 1 row affected (0.00 sec)

mysql>  INSERT INTO t1 VALUES (REPEAT('x', 10000));
Query OK, 1 row affected (0.00 sec)

mysql>  INSERT INTO t1 VALUES (REPEAT('x', 10000));
Query OK, 1 row affected (0.00 sec)

mysql>  INSERT INTO t1 VALUES (REPEAT('x', 10000));
Query OK, 1 row affected (0.00 sec)

mysql>  INSERT INTO t1 VALUES (REPEAT('x', 10000));
Query OK, 1 row affected (0.00 sec)

mysql>  INSERT INTO t1 VALUES (REPEAT('x', 10000));
Query OK, 1 row affected (0.00 sec)

mysql>  INSERT INTO t1 VALUES (REPEAT('x', 10000));
Query OK, 1 row affected (0.00 sec)

mysql>  INSERT INTO t1 VALUES (REPEAT('x', 10000));
Query OK, 1 row affected (0.00 sec)

mysql>  INSERT INTO t1 VALUES (REPEAT('x', 10000));
Query OK, 1 row affected (0.00 sec)

mysql>  INSERT INTO t1 VALUES (REPEAT('x', 10000));
ERROR 1114 (HY000): The table 't1' is full

mysql> show table status like 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 10
 Avg_row_length: 10003
    Data_length: 100040
Max_data_length: 10003
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

See bug #29178 for possible explanation.

BTW, I had default read_buffer_size:

| read_buffer_size                | 131072               |

that is close enough to that 100000+ size limit...

Duplicate of bug#29178