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

DUPLICATE KEY Errors on SELECT .. GROUP BY that uses temporary and filesort

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.37, 10.0.10
    • 10.0.14
    • Optimizer
    • None
    • Windows, Linux

    Description

      Import the table provided into a MariaDB 5.5 and/or 10.0 instance, ensure your max_heap_table_size and tmp_table_size are 32M or less, and issue these queries:

      SELECT * FROM t_album2 GROUP BY Composer;
      SELECT Composer FROM t_album2 GROUP BY Composer;

      It returns DUPLICATE KEY errors:
      ERROR 1034 (HY000): Duplicate key 1 for record at 583508 against record at 11380

      Here is the table structure:

      CREATE TABLE `t_album` (
      `id_album` int(11) NOT NULL AUTO_INCREMENT,
      `Composer` varchar(255) COLLATE utf8_swedish_ci NOT NULL DEFAULT '',
      PRIMARY KEY (`id_album`)
      ) ENGINE=InnoDB AUTO_INCREMENT=1050654 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

      The GROUP BY must be in the query to trigger the error.

      Notice that if I increase max_heap_table_size and tmp_table_size (512M seems to work here), then the query runs as expected.

      SET SESSION max_heap_table_size=536870912; 
      SET SESSION tmp_table_size=536870912;

      After-thought: With the same tmp/heap table sizes, the query runs fine in MySQL 5.5.37, though the bug looks somewhat similar to this open MySQL bug:

      http://bugs.mysql.com/bug.php?id=58081

      Attachments

        Activity

          People

            monty Michael Widenius
            ccalender Chris Calender (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.