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

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.

Environment

None

Smart Checklist

Activity

Show:

Julia Vural March 4, 2025 at 9:03 PM

It appears that this issue is no longer being worked on, so we are closing it for housekeeping purposes. If you believe the issue still exists, please open a new ticket after confirming it's present in the latest release.

George Lorch May 23, 2019 at 9:03 PM

yes, we actually ask that people report to us first, then we can validate and work with upstream for a fix.  Which I why I stated that I would take it to the Facebook team.  This particular issue is more of a question first, then based on the answer, this will become either a behavioral bug or a doc bug that we would report upstream and possibly attempt to fix ourselves.

Alexey Kopytov May 23, 2019 at 8:43 PM

Alexey Kopytov May 23, 2019 at 8:37 PM

Yes, I know it's upstream. But my previous bug reports upstream have been staying without any response for weeks, so I thought they actually expect users to report bugs to a "supported" MyRocks distribution.

 

George Lorch May 23, 2019 at 7:17 PM

This behavior comes from upstream Facebook MySQL, I do not know what the correct behavior is supposed to be.  It would be best to redirect the question to the Facebook team for clarification.  I can ask this during our next meeting in June.

Won't Do

Details

Assignee

Reporter

Labels

Components

Affects versions

Priority

Smart Checklist

Created May 22, 2019 at 1:22 PM
Updated March 4, 2025 at 9:03 PM
Resolved March 4, 2025 at 9:03 PM