CODEWITHSUNDEEP
mariadbcentos7
user1980099
user1980099

Reputation: 643

MariaDB select with group_concat() - Out of memory

we have centos 7 machine with mariadb installed.

When I run:

SELECT h.id,
       h.name,
       group_concat(distinct d.name ORDER BY d.name SEPARATOR " ") AS descriptions 
FROM inventar h 
LEFT JOIN descriptions d ON(FIND_IN_SET(d.id, h.description_id) > 0) GROUP BY h.id,h.description_id 
ORDER BY h.name asc;

ERROR 5 (HY000): Out of memory (Needed 65535816 bytes)

I read that it probably limit of the size of temporary table.

I checked the size:

MariaDB [wexac_hosts]> show variables like "%table_size%";

Variable_name Value
max_heap_table_size 1048576000
tmp_disk_table_size 18446744073709551615
tmp_memory_table_size 12572426240
tmp_table_size 12572426240

it's bigger then 65535816 bytes.

Which mariadb variable should I increase?

Upvotes: 0

Views: 193

Answers (1)

markusjm
markusjm

Reputation: 2562

If it's GROUP_CONCAT that's running out of memory, you need to increase group_concat_max_len.

From the GROUP_CONCAT documentation:

The maximum returned length in bytes is determined by the group_concat_max_len server system variable, which defaults to 1M (>= MariaDB 10.2.4) or 1K (<= MariaDB 10.2.3).

Upvotes: 1

Related Questions