Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-31131

Mariadb MyRocks Update Statement Lose Data

    XMLWordPrintable

Details

    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

      Attachments

        1. mysql-error-running.log
          54 kB
          Juan Lago
        2. rdsdbdata_db_LOG.log
          15 kB
          Juan Lago

        Activity

          People

            Unassigned Unassigned
            toan.nguyen.gu TNguyen
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.