advancedtelematic/treehub

Primary Key restraints issue with `object` table

doanac opened this issue · 3 comments

doanac commented

This whole bug sounds doesn't sound like it should be possible, but I'm seeing this on a server I was testing on. Prior to commit 1211ed1 the object table contained one namespace/object-id combination. This is dictated by the primary key restraint placed on the table. However, after updating to this commit we now have a status column. The problem I've somehow hit in my testing is that I get "impossible to happen" rows in mysql like:

   namespace    object_id    status
   default      deadbeef     UPLOADING
   default      deadbeef     UPLOADED

This is violating primary key restraints, but somehow mysql allowed the new row to be created. After that mysql goes nuts and can't do a select statement like: select * from object where namespace = "default" and object_id = "deadbeef";. It actually returns 0 rows.

If I take a DB dump and try to import it into a clean mariadb container, mysql will refuse to allow it.

I'm not sure the exact intent of this commit. However, I think one of 2 things needs to happen:

  1. ensure you update status of an existing row and keep with the one row per object approach
  2. add "status" as a primary key restraint to the table and that might lead to some other findQuery type changes I haven't thought through.
doanac commented

Think this might be related to a bug in mariadb 10.3.2: https://jira.mariadb.org/browse/MDEV-14837.

I've been on 10.3.2 because that's what OTA community edition sets, but looking at the release history I see 10.3.14 is out and there have been numerous fixes. It would be nice to know what version you guys use?

houcros commented

Yes, it seems that could be it 😕 We use 10.2.12.

simao commented

Wow can't believe this... Yes we use 10.2.12. Sounds like we need to upgrade.

I will also create a task for us to fix this at the app level in the next few weeks.

Thanks a lot for reporting this.