Different TTL behavior with unique checks in PRIMARY KEY vs. secondary UNIQUE KEY
Description
Environment
Smart Checklist
Activity

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
Reported it upstream as well: https://github.com/facebook/mysql-5.6/issues/1024

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.
Details
Details
Assignee
Reporter

Labels
Upstream Bug URL
Components
Affects versions
Priority
Smart Checklist
Open Smart Checklist
Smart Checklist

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.