1

I have a client with a relatively large (or at least not tiny) dataset. Around ~2Gb on disk.

When running a reporting function of the CRM application running with this DB, we are hit with 

Database Error Code: The table 'tablexyz' is full, Error 1114

This is puzzling for a few reasons.. The first is that it manages to decide that within around 3 seconds of the page attempting to load the report. The others are that:

A) It's an InnoDB table, so there are no explicit row limits as such (or am I wrong?)..

B) It's an x64 system, with x64 MySQL installed so the effective size limit of an InnoDB table is the filesystem's single filesize limite of 4Tb..

C) The disk is not full, it has around 700Gb available. The ibdata1 file on disk is 1.89Gb, the ib log files are 5Mb each.

MySQL was custom compiled from source by myself, as the client requested a custom MAX_INDEXES value of 250. As far as I am aware, this would have no effect on this scenario.

To compound the issue, the table doesn't even exist normally... It appears it is a temporary table created on the fly during this report run. Or at least I think it does.. At no point throughout the page loading in the CRM Application, can I find that table within MySQL. I'm not sure if there is a deeper issue that I'm not seeing, and its a red herring that its telling me it's full? Ideas welcome.

Dave Byrne
  • 429
  • 4
  • 21
  • Any idea what query was running? – Rick James Jul 29 '16 at 22:27
  • What is the setting of tmpdir? It is pointing to a "small" filesystem? – Rick James Jul 29 '16 at 22:27
  • Hi Rick, I think tmpdir was introduced in 5.7? Unless im thinking about something else. This system is running 5.5. But that aside, the system itself is very simple, there is only a / mount, which contains /tmp /var etc etc. In answer to your first question, actually no, i have no idea what the SQL is, as its very deeply embedded into a very complex prebuilt CMS. It's rather annoying. I am tempted to say it's a bug with the CMS, but I'd rather address all possibilities on the system first. – Dave Byrne Aug 02 '16 at 11:52
  • `tmpdir` dates back at least to version 4.0. The prebuilt version may have chosen to set it. If it is not set in `my.cnf`, there is a default. – Rick James Aug 02 '16 at 16:30
  • 1
    Possible duplicate of [ERROR 1114 (HY000): The table is full](https://stackoverflow.com/questions/730579/error-1114-hy000-the-table-is-full) – Uberhumus Apr 01 '19 at 15:48

2 Answers2

0

Seem to have achieve the maximum size for your innodb_data_file_path.

This could help: ERROR 1114 (HY000): The table is full

Community
  • 1
  • 1
enriqueojedalara
  • 74
  • 6
  • Hi, thanks for that. I decalred "innodb_data_file_path = ibdata1:10M:autoextend" in my.cnf however this has not help. I also tried a few other suggestions from that link without any positive success. such as "tmp_table_size=512M" and "max_heap_table_size=512M". – Dave Byrne Jul 29 '16 at 14:56
  • 1
    Those 512M settings are dangerous. They control the max _memory_ size for temp tables in complex selects. It is not just "per connection", but also "per tmp table". So, those values could easily cause you to run out of RAM. – Rick James Jul 29 '16 at 22:26
  • @RickJames, thankyou for that, after taking a closer look you are right. I've reversed those changes now anyway, as they did not solve the original problem, and I'd rather not introduce any more scope for trouble. Thanks. – Dave Byrne Aug 02 '16 at 11:53
0

I have been fighting with this issue for quite some time. Plenty of ram and free disk space.

What finally solved the issue for me was increasing temptable_max_ram. This in tandem with increasing tmp_table_size and max_heap_table_size seems to have stopped the temp tables being pushed to disc and thus somehow hitting its max size.

Of course concerns raised by Rick James should be considered.

buddemat
  • 4,552
  • 14
  • 29
  • 49
Nord
  • 1