This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-17. For the latest, interactive version please visit https://mariadb.com/kb/.

Aria Max Table Size for internal on-disk temporary tables

Using MariaDB 5.5.31 (deb file for ubuntu 12.04 amd_64: 5.5.31+maria-1precise).

When a query requires an internal temporary tables, and this tables becomes too large to be kept in ram, the table is moved to disk using Aria engine. If the on-disk .MAD file reaches 4GB, an error occurs:

[ERROR] mysqld: The table '/tmp/#sql_74e_0' is full

How can we change this limit?

In https://kb.askmonty.org/en/aria-max-table-size/ it is recommended to change MAX_ROW, but it only applies for explicitly created temporary tables.

Looking at the code I found this in maria_create():

data_file_length= ((((ulonglong) 1 << ((BLOCK_RECORD_POINTER_SIZE-1) *
                                               8))/2 -1) * maria_block_size);

Which amounts to 4GB with default values (8192 for maria_block_size). I created a new DB with aria-block-size = 32768 (the max value), and it still failed at 4GB. Even if it worked, it would mean the max size is only 16GB, and setting a large block size may have performance penalties.

So what is the proper fix to increase this limit?

With MyISAM as on-disk temporary tables engine the limit seems to be much higher (at least 14GB from experiments).

Answer Answered by Daniel Bartholomew in this comment.

On the maria-discuss list, Monty replied to this question with:

This is a bug. Internal temporary tables should always be able to be 'big enough for any kind of query'. I will fix this ASAP.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.