Decided to upgrade older 10.3.13 with the currently latest version 10.5.6.
One of my sql selects started throwing SQL Error (1038): Out of sort memory.
Versions are running on the same machine (sharing the same port, so just one instance is running at a time). Did mysqldump to have identical conditions. Using default settings (attached system variables for both versions).
Need to set sort_buffer_size to ~120MB to make script working again while older one works well with default 2MB.
I managed to simlify the script while still keeping an error (I understand it does not makes sense now, its really a shortened version so I could share something with you):
DROP TABLE IF EXISTS test_products;
|
CREATE TABLE test_products (
|
product_name VARCHAR(7),
|
id_country INT,
|
PRIMARY KEY (product_name, id_country)
|
) COLLATE='utf8_general_ci' ENGINE=INNODB;
|
|
DELIMITER //
|
FOR i IN 1..3700 DO INSERT INTO test_products (product_name, id_country) VALUES ('product', i); END FOR; //
|
DELIMITER ;
|
|
SELECT p.product_name, p.id_country, hierarchy.hierarchy
|
FROM test_products p
|
LEFT JOIN (
|
SELECT p2.product_name, p2.id_country, h.hierarchy, MAX(test) AS test
|
FROM test_products p2
|
LEFT JOIN (
|
SELECT product_name, id_country, '_exactly_' AS hierarchy, 1 AS test -- removing one single character from string '_exactly_' will stop producing error
|
FROM test_products
|
) h ON h.product_name = p2.product_name AND h.id_country = p2.id_country
|
GROUP BY product_name, id_country, h.hierarchy
|
) hierarchy ON hierarchy.product_name = p.product_name AND hierarchy.id_country = p.id_country
|
Is this behaviour correct?
Is increasing sort_buffer_size a recommended way to make the script working again?
Thank you
Bug
Blocker
