[MDEV-31131] Mariadb MyRocks Update Statement Lose Data Created: 2023-04-26  Updated: 2023-06-14

Status: Open
Project: MariaDB Server
Component/s: Data Manipulation - Update, Storage Engine - RocksDB
Affects Version/s: 10.10.2
Fix Version/s: None

Type: Bug Priority: Major
Reporter: TNguyen Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Mariadb 10.10.2-MariaDB-log MariaDB Server on Centos 7
Engine: MyRocks

Attachments: Text File mysql-error-running.log     Text File rdsdbdata_db_LOG.log    

 Description   

Summary

  • I have table location with fields: device_id, unix_time
  • I use an update statement from device_id "1000002912" to "1234567890"
  • And I have not found data for device_id "1234567890" also "1000002912"

Environment:

  • Mariadb 10.10.2-MariaDB-log MariaDB Server on Centos 7
  • Engine: MyRocks

Reproduce

Step 1: Set variable rocksdb_bulk_load to update many data

set session rocksdb_bulk_load=1;

Step 2: Change device_id from "1000002912" to "1234567890" 

update location set device_id="1234567890" where device_id="1000002912" and unix_time between 1682355600 and 1682355600 + 86400
 
 
 
Query OK, 30786 rows affected (2,009 sec)
 
Rows matched: 30786  Changed: 30786  Warnings: 0

Step 3: Query data of new device_id

select count(*) from location where device_id="1234567890" and unix_time between 1682355600 and 1682355600 + 86400
 
 
 
+----------+
 
| count(*) |
 
+----------+
 
|        0 |
 
+----------+
 
1 row in set (0,792 sec)

Step 4: Query data of old device_id

select count(*) from location where device_id="1000002912" and unix_time between 1682355600 and 1682355600 + 86400
 
 
 
+----------+
 
| count(*) |
 
+----------+
 
|        0 |
 
+----------+
 
1 row in set (0,229 sec)

[![Capture Summary][1]][1]

show create table location;
 
 
 
| Table                 | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
 
| location | CREATE TABLE `location` (
 
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 
  `device_id` char(10) NOT NULL,
 
  `unix_time` int(11) unsigned NOT NULL,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `device_id` (`device_id`,`unix_time`),
 
  KEY `unix_time` (`unix_time`)
 
) ENGINE=ROCKSDB AUTO_INCREMENT=4407998942 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci |

How can I fix this problem?

[1]: https://i.stack.imgur.com/awxhA.png

Link Ref: https://stackoverflow.com/questions/76105396/mariadb-myrocks-update-statement-lose-data

 Comments   
Comment by Alice Sherepa [ 2023-04-26 ]

I think this option is not, what you need for updating a lot of data. Please read https://github.com/facebook/mysql-5.6/wiki/Data-Loading , it explains conditioins for rocksdb_bulk_load and says that the data may not be visible - that explains why there is zero in select count.
When I tried to repeat your case - I get :

MariaDB [test]> update location set device_id="1234567890" where device_id="1000002912" and unix_time between 1682355600 and 1682355600 + 86400;
 
Query OK, 10000 rows affected (0,276 sec)
 
Rows matched: 10000  Changed: 10000  Warnings: 0
 
 
 
MariaDB [test]> 
MariaDB [test]> SET session rocksdb_bulk_load=0;
 
ERROR 1105 (HY000): [(null)] bulk load error: Invalid argument: External file requires flush

and then data is not updated. (https://mariadb.com/kb/en/loading-data-into-myrocks/ - data conflicts with the data already in the database)

Comment by Juan Lago [ 2023-06-01 ]

I experience the same problem inserting data into an empty MyRocks table in an environment with a read-replica.

After to finished inserting the reports it displayed the "Query OK" message, however the table was empty and the read-replica crashed displaying a lag of "-1".

I also used 

set session rocksdb_bulk_load=1;

I didn't use the option "sql_log_bin" because I need a copy into the read-replica.

Environment for master and read-replica
Cloud: AWS RDS
Version: 10.6.11-MariaDB - managed by https://aws.amazon.com
Instance type: db.t3.medium.

Steps to reproduce
1. Create a empty table that uses ROCKSDB engine
2. set session rocksdb_bulk_load=1
3. Insert more than 300.000 records

I enclose log files.
rdsdbdata_db_LOG.log mysql-error-running.log

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