MySQL error: Out of memory (Needed 1046596 bytes)

MySQL memory setup issue, adjustable tmp_table_size size resolution

Increases the value of the Query_cache_limit.
and Max_heap_table_size.
and tmp_table_size values.
Because we use a lot of preprocessing statements in our stored procedures. And the result of the statement is very large.
At first my results:

The code is as follows

Copy Code

Mysql> Show variables like ' max_heap_table_size '; +---------------------------------+----------------------+ | variable_name | Value | +---------------------------------+----------------------+ | Max_heap_table_size | 16777216 | +---------------------------------+----------------------+ Mysql> Show variables like ' tmp_table_size '; +---------------------------------+----------------------+ | variable_name | Value | +---------------------------------+----------------------+ | Tmp_table_size | 16777216 | +---------------------------------+----------------------+ I changed the my.cnf file. Mysql> Show variables like ' max_heap_table_size '; +---------------------------------+----------------------+ | variable_name | Value | +---------------------------------+----------------------+ | Max_heap_table_size | 67108864 | +---------------------------------+----------------------+ Mysql> Show variables like ' tmp_table_size '; +---------------------------------+----------------------+ | variable_name | Value | +---------------------------------+----------------------+ | Tmp_table_size | 67108864 | +---------------------------------+----------------------+

My configuration method is as follows

[Client]
#password = Your_password
Port = 3306
Socket =/var/lib/mysql/mysql.sock

# The MySQL server
[Mysqld]
Port = 3306
Socket =/var/lib/mysql/mysql.sock
Skip-locking

# Cache & Buffer Size
Max_allowed_packet = 1G
Key_buffer_size =768m
Table_cache =256m
Sort_buffer_size =64m
Read_buffer_size =64m
Read_rnd_buffer_size =64m
Myisam_sort_buffer_size = 64M
tmp_table_size=256m ==================== to make changes here
query_cache_type=1
query_cache_limit=32m
connect_timeout=100000

# Binary logging is required for replication
Log-bin=mysql-bin

# Binary Logging format-mixed recommended
Binlog_format=mixed

# required Unique ID between 1 and 2^32-1
# defaults to 1 if master-host are not set
# but would not function as a master if omitted
Server-id = 1

# Uncomment the following if you are using InnoDB tables
Innodb_data_home_dir =/var/lib/mysql/
Innodb_data_file_path = Ibdata1:10m:autoextend
Innodb_log_group_home_dir =/var/lib/mysql/
Innodb_buffer_pool_size = 768M
Innodb_additional_mem_pool_size = 512M
Innodb_log_file_size = 5M
Innodb_log_buffer_size = 8M

[Mysqldump]
Quick
Max_allowed_packet = 16M

[MySQL]
No-auto-rehash
# Remove The next comment character if you are not familiar with SQL
#safe-updates

[Myisamchk]
Key_buffer_size = 32M
Sort_buffer_size = 32M
Read_buffer = 8M
Write_buffer = 8M

[Mysqlhotcopy]
Interactive-timeout