[MDEV-21897] Rocksdb does not respect unique indexes Created: 2020-03-09  Updated: 2020-04-05  Resolved: 2020-04-05

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - RocksDB
Affects Version/s: 10.4.10
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Philip orleans Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Linux


 Description    

 CREATE TABLE `sip404` (
 
  `number` bigint(20) unsigned NOT NULL,
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `UK_callerid_number` (`number`)
 
) ENGINE=ROCKSDB AUTO_INCREMENT=3898030 DEFAULT CHARSET=latin1
 
 
 
select number,count(*) from sip404 group by number having count(*) > 1 limit 10;
 
+------------+----------+
 
| number     | count(*) |
 
+------------+----------+
 
| 2012053466 |        4 |
 
| 2012084337 |        4 |
 
| 2012087180 |        4 |
 
| 2012088653 |        5 |
 
| 2012109447 |        4 |
 
| 2012120237 |        4 |
 
| 2012120241 |        4 |
 
| 2012121846 |        4 |
 
| 2012185042 |        4 |
 
| 2012204752 |        4 |
 
+------------+----------+
 
10 rows in set (0.001 sec)

This breaks the basic functionality of a database engine.
How did it happen?
I have a file ${FILE} with a single column of numbers, ordered randomly, and the I do this 

mysql "$DATAB" --execute="set session rocksdb_bulk_load_allow_unsorted=ON;set session rocksdb_bulk_load=1;LOAD DATA INFILE '${FILE}' IGNORE INTO TABLE ${sqltable} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' IGNORE 0 LINES (${field}); SHOW WARNINGS;set session rocksdb_bulk_load=0;"

every time I do this the same numbers get added again, notwithstanding the fact that duplicate numbers should be ignored. The unique index on (number) is not being respected.

I can give access to Elena and she can verify the issue.

 Comments   
Comment by Elena Stepanova [ 2020-04-05 ]

It is easily reproducible; but the behavior is caused by the non-default value of rocksdb_bulk_load variable which is documented very explicitly:

This disables unique_checks and enables rocksdb_commit_in_the_middle.

So I don't see any violation here.

Generated at Thu Feb 08 09:10:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.