Mysql The table 'xxxx' is full to set The temporary table size

In the temporary mysql table, xxx is full. You only need to modify the tmp_table_size and max_heap_table_size parameters in my. ini.

Tmp_table_size if the temporary table in the memory exceeds this value, MySQL automatically converts it to the MyISAM table on the hard disk. If you execute many advanced group by queries with a large amount of memory, you can increase the value of tmp_table_size. Max_heap_table_size this variable sets the maximum size of MEMORY (HEAP) tables that can be increased

Tmp_table_size

If the temporary table in the memory exceeds this value, MySQL automatically converts it to the MyISAM table on the hard disk. If you execute many advanced group by queries with a large amount of memory, you can increase the value of tmp_table_size.

Max_heap_table_size
This variable sets the maximum size of the MEMORY (HEAP) Table space that can be increased. This variable is used to calculate the MAX_ROWS value of the MEMORY table. Setting this variable on an existing memory table does not work unless you use the create table or truncate table statement to recreate the TABLE.

Method 1: modify my. ini or my. cnf

The Code is as follows:	Copy code
Tmp_table_size = 64 M Max_heap_table_size = 32 M

The biggest drawback of this method is that, once restarted, everything is restored to the default value.

Method 2: mysqld command line

The Code is as follows:	Copy code
Mysqld_safe-key_buffer_size = 512 K-sort_buffer_size = 16 K -Tmp_table_size = 64 M-max_heap_table_size = 32 M &

Method 3: Dynamic Modification

Because tmp_table_size and max_heap_table_size both have session scopes, do as much as possible.

The Code is as follows:	Copy code
Set session tmp_table_size = 64*1024*1024 Set session max_heap_table_size = 32*1024*1024

If you have the Server Management permission, we recommend that you use the first method. The last two methods only need to restart the mysql server and restore everything to the previous configuration. Therefore, it is best to modify my. ini.