2

I have upgraded MariaDB 10.3.13 to the latest version 10.5.6. Once deployed, one of my queries started giving this error:

SQL Error (1038): Out of sort memory, consider increasing server sort buffer

I managed to simplify query while keeping the error on fresh 10.5.6 install (default settings). This works without issues on 10.3.13:

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 will make this query working again
        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

Increasing sort_buffer_size helps. However on original query I need to increase this buffer from default 2MB to around 80MB (no ORDER BY involved at all; products table filtered to around 15k-20k entries - not that big numbers at all). According to https://www.xaprb.com/blog/2010/05/09/how-to-tune-mysqls-sort_buffer_size/ I would rather keep original settings. Also I don't understand why 10.3.13 with default 2MB buffer works well while newer version does not.
Should I submit a bug on MariaDB? Or am I doing something wrong? Or should I investigate a week/two to simplify the query? Lost here.

tested on Windows Server 2012R2; Xeon E3-1225@3.2Ghz; 16GB ram

Thanks for help.

Andrej Piatnica
  • 41
  • 4
  • Are the indexes same on both machines? – Salman A Oct 22 '20 at 10:37
  • Talking about the same physical machine. 10.3.13 service name si "MySql" while 10.5.6 service name is "MariaDB". They use the same port so just one can be running at a time. Script I posted is standalone - there is just a single primary key. Running this on one version works, on the another does not. If you ask about original query then yes - I exported database from older version to new version to keep 100% equal conditions. – Andrej Piatnica Oct 22 '20 at 10:46
  • 1
    submitted it as a bug to MariaDB team (https://jira.mariadb.org/browse/MDEV-24015?page=com.atlassian.jira.plugin.system.issuetabpanels%3Aall-tabpanel). They changed priority to critical. Should be fixed in 10.5.x version – Andrej Piatnica Oct 24 '20 at 13:45

1 Answers1

0

This bahaviour looked strange so I submitted a bug at MariaDB team.

Fix will be available since version 10.5.7.

For more details check https://jira.mariadb.org/browse/MDEV-24015

Andrej Piatnica
  • 41
  • 4