Bug #44138 Memory engine table full at much less than max_heap_table_size with btree index
Submitted: 7 Apr 2009 22:48 Modified: 29 Nov 2013 19:38
Reporter: Tyson Whitehead Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Memory storage engine Severity:S2 (Serious)
Version:5.1.57, 5.6.14, 5.6.30 OS:Linux (x86_64 rhel5 version from download page)
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[7 Apr 2009 22:48] Tyson Whitehead
I have a x86_64 machine with ~128GiB of memory.  I've set "max_heap_table_size = 100GB" and yet loading a ~3GiB table stored in the memory engine with BTree indexes aborts with a "table is full" error while top only shows mysqld using ~15GiB.

Also strange is that running "show tables" after the load fails gives an data_length of 8060409024 (reasonable), but an index_length of 1806712039906 (way huge).

If "using btree" is not specified in conjunction with the indexes when creating the table (i.e., the default hash index is used), the load succeeds with mysqld only using ~15GiB and data_length and index_length being 10797650560 and 2159630928, respectively.

This seems a lot like bug 12460, but that one is suppose to be fixed.

How to repeat:
0) Install the current release of the mysql community server

1) Download http://www.sharcnet.ca/~tyson/mysql/b129_SNPMapInfo_36_3.bcp.gz
(this is a MySQLifed version of the NCBI file with the same name)

2) Gunzip b129_SNPMapInfo_36_3.bcp.gz

3) Start mysqld with "max_heap_table_size = 100GB"

4) Run mysql and create the table and load the data with

CREATE TABLE b129_SNPMapInfo_36_3 (
        snp_type varchar (2) NOT NULL,
        snp_id int NOT NULL,
        chr_cnt int NOT NULL,
        contig_cnt int NOT NULL,
        loc_cnt int NOT NULL,
        weight int NOT NULL,
        hap_cnt int NOT NULL,
        placed_cnt int NOT NULL,
        grouped_cnt int NOT NULL,
        unplaced_cnt int NOT NULL,
        md5 char (32) NULL,
        assembly varchar (32) NULL,
        UNIQUE INDEX iuc_rs_weight_assembly USING BTREE (snp_id, assembly),
        INDEX idxSnpId USING BTREE (snp_id, weight),
        INDEX idxMD5 USING BTREE (md5)

load data infile 'b129_SNPMapInfo_36_3.bcp' into table b129_SNPMapInfo_36_3;

5) It should abort with the error message

ERROR 1114 (HY000): The table 'b129_SNPMapInfo_36_3' is full
[6 Oct 2009 4:58] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior. Please try if bug exists in current version 5.1.39 and if yes indicate accurate package name you use (filename you downloaded) and send us your configuration file and full error log.
[7 Nov 2009 0:00] Bugs System
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".
[6 May 2011 15:45] Jason Preuss
I am getting the exact same problem running SLES 11. I tried mysql 5.0, 5.1, and 5.5 and got the same behavior.
[11 May 2011 11:34] Sveta Smirnova

thank you for the feedback.

Please indicate accurate package name you use (filename you downloaded) and send us your configuration file and full error log.
[20 May 2011 13:31] Jason Preuss
error log file

Attachment: error.log (application/octet-stream, text), 60.25 KiB.

[20 May 2011 13:33] Jason Preuss
Current configuation file (note we are now testing with Ubuntu and getting same error)

Attachment: my20110520.cnf (application/octet-stream, text), 3.60 KiB.

[20 May 2011 13:34] Jason Preuss
Output from information schema

Attachment: largeindex.xls (application/vnd.ms-excel, text), 18.50 KiB.

[20 May 2011 13:38] Jason Preuss

Thanks for looking into this. Let me know if you need anything more. We thought the problem was SLES related and we are upgrading hardware so I was able to play around on the old server with Ubuntu and got the same problems. Running ubuntu server 10.04 on a dell 900 with 128 Gb Ram. Version of mysql is 5.1.41-3ubuntu12.10.

[3 Jun 2011 17:08] Jason Preuss
Information that might help.
The bug is not present in version 5.0.26 which came with SLES 10, it is present in version 5.0.67 which came with SLES 11 (5.0.26 works fine in SLES 11 and that is what I am using for a hopefully temporary solution).
[4 Jun 2011 10:50] Sveta Smirnova

thank you for the data provided. Version 5.1.41 is very old and many bugs were fixed since. Please try with version 5.1.57 available at dev.mysql.com/downloads and inform us if problem is repeatable with this version.

Please try our binaries to exclude port-specific issues.
[6 Jun 2011 15:08] Jason Preuss
I have installed 5.1.57 and am getting the exact same behavior.

Note: I am back on my SLES 11 machine (I only had temporary access to test Ubuntu on that other server) and downloaded/installed the 64 bit rpm for SLES 11.
[13 Jun 2011 16:40] Simon Hubbard
I am observing the very same problem using

Server version: 5.0.45-log on Fedora

I really hope there's a fix/patch for this soon as it's causing serious production problems . 

 Please let me know if you need anymore info from me to assist in your troubleshooting.

Thanks, Simon
[22 Jun 2011 9:53] Simon Hubbard
Please, anyone, is there any update on this.  I notice the bug is still open and is classed a serious bug but there doesn't seem to be any feeback as to what's happening towards finding a resolution/patch.
[29 Jun 2011 14:49] Jon Stephens
No feedback was provided. The bug is being suspended because we assume that you are no longer experiencing the problem. If this is not the case and you are able to provide the information that was requested earlier, please do so and change the status of the bug back to "Open". Thank you.
[29 Jun 2011 15:58] Tyson Whitehead
As the original submitter, in support of Jason and Simon here, I would like to add that in no way should my lack of follow up be taken as any sort of indication that we got btree working with the memory engine.  What happened instead is we simply stopped using it.  I haven't had the time to follow up.

To be honest though, I am still a bit amazed you guys haven't reproduce it.  We aren't paying you anything, so I'm not pushing it, but, really, did you actually try following the steps I laid out in my initial report on a larger memory 64bit machine and not got the same results?  The file is still there.

Cheers!  -Tyson

PS:  I found it quite humorous that Jason was recently told 5.1.41 was quite old and asked to try 5.1.57 as 5.1.57 is the very release I reported to bug against and was instead asked to try the even older 5.1.39.
[29 Jun 2011 16:20] Jason Preuss
I am not sure why this was closed for no feedback provided when I have provided all feedback that has been asked of me.

This is still a problem, and for me a major one at that (so much so that I cannot use MySQL for the project I want to use it for until this is fixed). Again, if there is any more information you need from me I can provide it.
[30 Jun 2011 10:53] Jon Stephens
I'm not sure I can tell correctly whether there's additional information that could enable us to reproduce the issue, so I've asked someone better qualified than I am to take another look.

[8 Jul 2011 11:11] Sveta Smirnova
Thank you all for the feedback.

Bug was closed as "No feedback" by mistake.

Tyson, you could not report bug against 5.1.57 in 2009 year as it was released at 18 Apr 2011
[8 Jul 2011 14:27] Tyson Whitehead
Hi Sveta,

You are correct.  I thought I had set the version in the bug report, which would have implied it was 5.1.57, but I just checked the machine we were working on at that time, and it was distribution 5.1.34-ndb-7.0.6.

Apologizes for giving you guys a hard time over that, and glad to see you've reopened the ticket.

Cheers!  -Tyson
[3 Feb 2013 18:50] Shane Bester
Waiting to download, I made a synthetic data and cannot repeat on 5.5.29. Please check this output.  I'll try 5.1.57 too...

Attachment: bug44138.sql (application/octet-stream, text), 3.36 KiB.

[3 Feb 2013 20:31] Shane Bester
using my synthetic test, i get identical good results on 5.0.67, 5.1.57, and 5.5.29. tomorrow i will test that b129_SNPMapInfo_36_3.bcp.gz file....
[4 Feb 2013 6:22] Shane Bester
on windows, still cannot repeat any problem, even with supplied datafile. next, i will find a linux machine....

Attachment: bug44138_test_with_bcp_data.sql (application/octet-stream, text), 3.24 KiB.

[4 Feb 2013 7:29] Shane Bester
exactly the same good results on 5.5.29 on RHEL5 . Sorry, but this is really not repeatable for me.
[4 Feb 2013 11:41] Jason Preuss
Thank you for taking a look into this.

I am going to attempt to save you and your users a huge amount of frustration. Last year after waiting so long to get this fixed I went to the MariaDB folks to see if they wanted to fix the bug (it was in their version too). Look at the bug request, https://bugs.launchpad.net/maria/+bug/794680 , and you will see they had the same problem fixing it as your are. There is something fundamentally different when a developer installs on their machine, needing several versions on the same machine at the same time, and a regular user who does a regular install and only one instance.

How the Maria DB guy finally nailed it was that I gave him direct access to my server and instead of trying to replicate the error, he saw exactly what was happening and my machine. And yes at first he tried to do what I call a developer install and couldn't replicate the error on my equipment doing it his way.

In short if you want to replicate just set up a virtual machine or something, pick a common linux distro, and install and use mysql the way a typical user would. I believe you will find the same behavior as I did. Or just look at the MariaDB change logs shortly after april 16 2012 and just take their fix.

Please feel free to contact me if I didn't provide enough detail.
[4 Feb 2013 12:05] Shane Bester
Thanks for the feedback Jason.  Maria fix is http://lists.askmonty.org/pipermail/commits/2012-April/003198.html

I'll check again with this knowledge :)
[29 Nov 2013 19:37] Sveta Smirnova
I finally verified it, but I had to load the dump two times.

To repeat:

1. Drop unique key
2. Run LOAD DATA INFILE until "ERROR 1114 (HY000): The table 'b129_SNPMapInfo_36_3' is full" happens
[2 Feb 2014 13:31] Shane Bester
related problem filed here, please fix both at same time:
[9 Jun 2016 7:33] Umesh Shastry
Bug #81781 marked as duplicate of this one