Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24015

SQL Error (1038): Out of sort memory when enough memory for the sort buffer is provided

    XMLWordPrintable

    Details

      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

        Attachments

          Activity

            People

            Assignee:
            varun Varun Gupta (Inactive)
            Reporter:
            andrej8080 Andrej Piatnica
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: