MySQL Bugs: #30670: max_heap_table_size is not enforced correctly

Bug #30670	max_heap_table_size is not enforced correctly
Submitted:	28 Aug 2007 13:35	Modified:	31 Jul 2008 1:44
Reporter:	Peter Zaitsev (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.45, 4.1, 5.0, 5.1, 5.2 BK	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
max_heap_table_size behavior is not consistent.
It seems to be connected to the table on table creation stage rather than when data is being written to the table. 

However if you restart the server the value is reset to current one when you access the table which makes it crazy as you can create the table with large session max_heap_table_size and use it but it will break after server restart.

How to repeat:
mysql> set session max_heap_table_size=500000000;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into gtm2 select * from gtm;
ERROR 1114 (HY000): The table 'gtm2' is full

mysql> show table status like "gtm2" \G
*************************** 1. row ***************************
           Name: gtm2
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 299650
 Avg_row_length: 55
    Data_length: 16846960
Max_data_length: 16477615
   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)

mysql> set global max_heap_table_size=500000000;
Query OK, 0 rows affected (0.00 sec)

mysql> show table status like "gtm2" \G
*************************** 1. row ***************************
           Name: gtm2
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 299650
 Avg_row_length: 55
    Data_length: 16846960
Max_data_length: 16477615
   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)

mysql> insert into gtm2 select * from gtm;
ERROR 1114 (HY000): The table 'gtm2' is full

NOTE: Neither global nor session max_heap_table_size increase do not help. Table still has default 16M size limit.

mysql> truncate table gtm2;
Query OK, 0 rows affected (0.00 sec)

mysql> show table status like "gtm2" \G
*************************** 1. row ***************************
           Name: gtm2
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 55
    Data_length: 0
Max_data_length: 491071130
   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)

NOTE: Truncate recreates the table and sets proper size limit.

mysql> set max_heap_table_size=10000;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into gtm2 select * from gtm;
Query OK, 1000000 rows affected (0.70 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

NOTE: Here is the real issue - now session with very small max_heap_table_size is able to create huge temporary table.

Now restart the server and check table properties again:

mysql> show table status like "gtm2" \G
*************************** 1. row ***************************
           Name: gtm2
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 55
    Data_length: 0
Max_data_length: 16477615
   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)

It is back to 16M Limit

Thank you for the report.

According to http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#option_mysqld_max_heap...:

max_heap_table_size

This variable sets the maximum size to which MEMORY tables are allowed to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE.

First 2 parts of the report is not a bug.

Verified last part: changing Max_data_length of the existing table after server restart.

Workaround: use MAX_ROWS

Internally MEMORY table gets created when it is opened for the first time (after server restart). That's why it uses current max_heap_table_size.

I'm reassigning this bug to Docs team, to clarify that fact. As I think that current max_heap_table_size purpose is misleading, I created WL#4313.

Docs team: please update Server variables section around max_heap_table_size description. It should state that max_heap_table_size has no effect on any MEMORY tables that were accessed after server restart:

Setting this variable has no effect on any MEMORY table that were accessed after server restart, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.