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

Significant data corruption after dropping/adding database

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.5.11
    • Fix Version/s: None
    • Component/s: Server
    • Labels:
    • Environment:

      Description

      I'm observing significant data corruption after dropping and loading a schema. This is so significant that I thought others would have reported it as well. However, I have searched and found nothing. I can reproduce the error using only SQL commands, so it seems legit.

      1. Starting state: The database is up and running well. I have used the C/C++ API to add several entries to the database
      2. The process writing to the DB is stopped, (tried both killing and stopping cleanly)
      3. Here is one entry in the DB:

      MariaDB [sigmaDB]> select * from path;
      +-----+-------+------------------+--------------------+----------------------+-----------------+--------------+--------------+-------------------+--------------+----------+-----------+---------+------------+--------------+----------+
      | idx | name  | uuid             | primaryCollectUuid | componentId          | sigmaId         | reference_id | wptspacing_m | distanceMarginMet | hdgMarginDeg | approved | completed | aborted | restricted | callbacksSet | priority |
      +-----+-------+------------------+--------------------+----------------------+-----------------+--------------+--------------+-------------------+--------------+----------+-----------+---------+------------+--------------+----------+
      |   1 | 1:Lot | t��~��N������a▒�            | ���4B#�S�����?            | 14481131123844579333 | 220964525205148 |            0 |          100 |                10 |          360 |        1 |         0 |       0 |          0 |            1 |        0 |
      +-----+-------+------------------+--------------------+----------------------+-----------------+--------------+--------------+-------------------+--------------+----------+-----------+---------+------------+--------------+----------+
      1 row in set (0.000 sec)
       
      MariaDB [sigmaDB]> select count(*) from path;
      +----------+
      | count(*) |
      +----------+
      |        1 |
      +----------+
      1 row in set (0.001 sec)
      

      1. Then I drop the DB and it appears to be really gone

      MariaDB [sigmaDB]> drop database sigmaDB;
      Query OK, 37 rows affected (0.101 sec)
       
      MariaDB [(none)]> use sigmaDB;
      ERROR 1049 (42000): Unknown database 'sigmaDB'
      MariaDB [(none)]> select * from path;
      ERROR 1046 (3D000): No database selected
      MariaDB [(none)]>
      

      1. Then I reload the schema (without any inserted data)

      root@c01045:/sigma# grep -i insert sigmaDB.sql
      root@c01045:/sigma# cat sigmaDB.sql | mysql -u sigma -p 
      Enter password: 
      root@c01045:/sigma#
      

      1. Now I look in the db in the path table and magically I have data that existed from before the previous drop database command

      MariaDB [sigmaDB]> select * from path;
      +-----+-------+------------------+--------------------+----------------------+-----------------+--------------+--------------+-------------------+--------------+----------+-----------+---------+------------+--------------+----------+
      | idx | name  | uuid             | primaryCollectUuid | componentId          | sigmaId         | reference_id | wptspacing_m | distanceMarginMet | hdgMarginDeg | approved | completed | aborted | restricted | callbacksSet | priority |
      +-----+-------+------------------+--------------------+----------------------+-----------------+--------------+--------------+-------------------+--------------+----------+-----------+---------+------------+--------------+----------+
      |   1 | 1:Lot | t��~��N������a▒�            | ���4B#�S�����?            | 14481131123844579333 | 220964525205148 |            0 |          100 |                10 |          360 |        1 |         0 |       0 |          0 |            1 |        0 |
      +-----+-------+------------------+--------------------+----------------------+-----------------+--------------+--------------+-------------------+--------------+----------+-----------+---------+------------+--------------+----------+
      1 row in set (0.000 sec)
      

      1. Then I restart mariadb service

      root@c01045:/sigma# systemctl restart mysql
      root@c01045:/sigma#
      

      1. After the restart of the service, the "phantom row" is now gone from the database

      MariaDB [sigmaDB]> select * from path;
      Empty set (0.002 sec)
      MariaDB [sigmaDB]>
      

      This really makes me think that the drop/reload isn't happening as it should. It seems like some amount of data is being stored in memory and not destroyed through the drop then schema load. Once the service is brought down and restarted, this cached data is no longer there and the querries work appropriately. Note that rolling back to 10.3 removes this problem/behavior.

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            njensen Nathan Jensen
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration