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

Stale data returned after TRUNCATE PARTITION operation

Details

    • 5.5.44

    Description

      MariaDB 5.5.38-galera, from MariaDB-hosted apt repo.

      After an "ALTER TABLE ... TRUNCATE PARTITION" operation on a LIST partition, a query against the table returns data which has been removed:

      CREATE TABLE `test` (
      		 
        `uniqueId` INT NOT NULL,
      		 
        `partitionId` INT NOT NULL,
      		 
        PRIMARY KEY (`uniqueId`,`partitionId`)
      		 
      ) ENGINE=InnoDB PARTITION BY LIST (partitionId) (
      		 
       PARTITION p01 VALUES IN (1),
      		 
       PARTITION p02 VALUES IN (2)
      		 
      );
      		 
       
      		 
      INSERT INTO `test`(`uniqueId`,`partitionId`) VALUES(407237055, 2);
      		 
       
      		 
      SELECT * FROM `test`;
      		 
      -- Returns 1 row
      		 
      SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS where TABLE_NAME = 'test';
      		 
      -- Confirms 1 row in partition 'p02'
      		 
      ALTER TABLE `test` TRUNCATE PARTITION `p02`;
      		 
      -- Query OK
      		 
      SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS where TABLE_NAME = 'test';
      		 
      -- Both zero
      		 
      SELECT * FROM `test`;
      		 
      -- Still returns 1 row(!!)
      		 
      SELECT SQL_CACHE * FROM `test`;
      		 
      -- Correctly returns zero rows
      		 
      SELECT SQL_NO_CACHE * FROM `test`;
      		 
      -- Correctly returns zero rows

      A colleague raised this issue with me, and I have been able to reproduce by the above method. When I tried with a RANGE partition rather than a LIST partition, I then saw the correct results whereas my colleague still reports seeing the same incorrect behaviour as above, with the different partitioning method. This may suggest that the issue is non-deterministic.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          stuart.shelton Stuart Shelton created issue -
          stuart.shelton Stuart Shelton made changes -
          Field Original Value New Value
          Description MariaDB 5.5.38-galera, from MariaDB-hosted apt repo.

          After an "ALTER TABLE ... TRUNCATE PARTITION" operation on a LIST partition, a query against the table returns data which has been removed:

          CREATE TABLE `test` (
            `uniqueId` INT NOT NULL,
            `partitionId` INT NOT NULL,
            PRIMARY KEY (`uniqueId`,`partitionId`)
          ) ENGINE=InnoDB PARTITION BY LIST (partitionId) (
           PARTITION p01 VALUES IN (1),
           PARTITION p02 VALUES IN (2)
          );
          INSERT INTO `test`(`uniqueId`,`partitionId`) VALUES(407237055, 2);
          SELECT * FROM `test`;
          -- Returns 1 row
          SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS where TABLE_NAME = 'test';
          -- Confirms 1 row in partition 'p02'
          ALTER TABLE `test` TRUNCATE PARTITION `p02`;
          -- Query OK
          SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS where TABLE_NAME = 'test';
          -- Both zero
          SELECT * FROM `test`;
          -- Still returns 1 row(!!)
          SELECT SQL_CACHE * FROM `test`;
          -- Correctly returns zero rows
          SELECT SQL_NO_CACHE * FROM `test`;
          -- Correctly returns zero rows

          A colleague raised this issue with me, and I have been able to reproduce by the above method. When I tried with a RANGE partition rather than a LIST partition, I then got the correct results whereas my colleague still reports seeing the same incorrect behaviour as above, with the different partitioning method. This may suggest that the issue is non-deterministic.           		MariaDB 5.5.38-galera, from MariaDB-hosted apt repo.

          After an "{{ALTER TABLE ... TRUNCATE PARTITION}}" operation on a {{LIST}} partition, a query against the table returns data which has been removed:

          {noformat}
          CREATE TABLE `test` (
            `uniqueId` INT NOT NULL,
            `partitionId` INT NOT NULL,
            PRIMARY KEY (`uniqueId`,`partitionId`)
          ) ENGINE=InnoDB PARTITION BY LIST (partitionId) (
           PARTITION p01 VALUES IN (1),
           PARTITION p02 VALUES IN (2)
          );

          INSERT INTO `test`(`uniqueId`,`partitionId`) VALUES(407237055, 2);

          SELECT * FROM `test`;
          -- Returns 1 row
          SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS where TABLE_NAME = 'test';
          -- Confirms 1 row in partition 'p02'
          ALTER TABLE `test` TRUNCATE PARTITION `p02`;
          -- Query OK
          SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS where TABLE_NAME = 'test';
          -- Both zero
          SELECT * FROM `test`;
          -- Still returns 1 row(!!)
          SELECT SQL_CACHE * FROM `test`;
          -- Correctly returns zero rows
          SELECT SQL_NO_CACHE * FROM `test`;
          -- Correctly returns zero rows
          {noformat}

          A colleague raised this issue with me, and I have been able to reproduce by the above method. When I tried with a {{RANGE}} partition rather than a {{LIST}} partition, I then saw the correct results whereas my colleague still reports seeing the same incorrect behaviour as above, with the different partitioning method. This may suggest that the issue is non-deterministic.
          serg Sergei Golubchik made changes -
          Assignee Elena Stepanova [ elenst ]
          elenst Elena Stepanova made changes -
          Fix Version/s 5.5 [ 15800 ]
          Assignee Elena Stepanova [ elenst ] Oleksandr Byelkin [ sanja ]
          cvicentiu Vicențiu Ciorbaru made changes -
          Assignee Oleksandr Byelkin [ sanja ] Vicentiu Ciorbaru [ cvicentiu ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Workflow MariaDB v2 [ 51304 ] MariaDB v3 [ 67125 ]
          serg Sergei Golubchik made changes -
          Sprint Sprint 1 [ 4 ]
          cvicentiu Vicențiu Ciorbaru made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          cvicentiu Vicențiu Ciorbaru made changes -
          Rank Ranked higher
          cvicentiu Vicențiu Ciorbaru made changes -
          Assignee Vicentiu Ciorbaru [ cvicentiu ] Oleksandr Byelkin [ sanja ]
          Status In Progress [ 3 ] In Review [ 10002 ]
          sanja Oleksandr Byelkin made changes -
          Assignee Oleksandr Byelkin [ sanja ] Vicentiu Ciorbaru [ cvicentiu ]
          Status In Review [ 10002 ] Stalled [ 10000 ]
          cvicentiu Vicențiu Ciorbaru made changes -
          Component/s Data Definition - Alter Table [ 10114 ]
          Component/s Query Cache [ 10120 ]
          Fix Version/s 5.5.44 [ 19100 ]
          Fix Version/s 5.5 [ 15800 ]
          Resolution Fixed [ 1 ]
          Status Stalled [ 10000 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 67125 ] MariaDB v4 [ 148074 ]

          People

            cvicentiu Vicențiu Ciorbaru
            stuart.shelton Stuart Shelton
            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.