Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-5647

Different TTL behavior with unique checks in PRIMARY KEY vs. secondary UNIQUE KEY

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: On Hold
    • Priority: Medium
    • Resolution: Unresolved
    • Affects Version/s: 5.7.25-28
    • Fix Version/s: None
    • Component/s: MyRocks
    • Labels:

      Description

      With the following table:

      create table t (a int primary key, ts bigint unsigned not null, b int unique key) engine=rocksdb comment "ttl_duration=3600;ttl_col=ts;";

      MyRocks disallows duplicate PK values to be inserted with read filtering disabled, and allows conflicts with filtered rows with read filtering enabled:

      mysql> insert into t values (1, 0, 1);
      Query OK, 1 row affected (0.00 sec)

      mysql> set global rocksdb_enable_ttl_read_filtering=0;
      Query OK, 0 rows affected (0.00 sec)

      mysql> insert into t values (1, 0, 2);
      ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

      mysql> select * from t;
      ----++-----
      | a | ts | b |
      ----++-----
      | 1 | 0 | 1 |
      ----++-----
      1 row in set (0.00 sec)

      mysql> set global rocksdb_enable_ttl_read_filtering=1;
      Query OK, 0 rows affected (0.00 sec)

      mysql> select * from t;
      Empty set (0.00 sec)

      mysql> insert into t values (1, 0, 2);
      Query OK, 1 row affected (0.00 sec)

      mysql> select * from t;
      Empty set (0.00 sec)

      However, unique keys conflicts with filtered rows occur no matter if rows filtering is enabled or not:

      mysql> set global rocksdb_enable_ttl_read_filtering=0;
      Query OK, 0 rows affected (0.01 sec)

      mysql> select * from t;
      ---------
      | a | ts | b |
      ---------
      | 1 | 0 | 2 |
      ---------
      1 row in set (0.00 sec)

      mysql> insert into t values (2, 0, 1);
      ERROR 1062 (23000): Duplicate entry '1' for key 'b'

      mysql> set global rocksdb_enable_ttl_read_filtering=1;
      Query OK, 0 rows affected (0.00 sec)

      mysql> select * from t;
      Empty set (0.00 sec)

      mysql> insert into t values (2, 0, 1);
      ERROR 1062 (23000): Duplicate entry '1' for key 'b'

      If the above behavior is expected, I've been unable to find any traces of it in the docs.

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              akopytov Alexey Kopytov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated: