Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.5.6
-
Windows Server 2012R2 verson 6.3; Xeon E3-1225@3.2Ghz; 16GB ram
Description
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