UNIQUE constraint failed: filme._id

[cc13com]

Since today I run in an issue when I do the update:

mtv_cli.py -A
[INFO] [2023-03-13 10:31:23] Erzeuge /home/carsten/.mediathek3/filme.sqlite aus https://liste.mediathekview.de/Filmliste-akt.xz
...........................................................[ERROR] [2023-03-13 10:31:41] Update der Filmliste gescheitert. Fehler: UNIQUE constraint failed: filme._id

Also after deleting the local filme.sqlite the same issue. Any idea?

[bablokb]

Thanks for the issue. I will check this (but it will take until end of this week). Maybe the format of the data-file has changed.

[cc13com]

As a work around I decresed the number of how many days the list should be imported in the DB. It was 180 days (without any issue until last week) and now it's 15.

[bablokb]

Interessting: I use 60 days and it works, so maybe there is a problem with the data-file creation which nobody noticed yet.

[gameshacker]

Hi, I've also get exact this problem and also the workaround don't work. I've set the number to 5 Days and get this failure.

 [INFO] [2025-02-28 09:16:15] Erzeuge /home/user/.mediathek3/filme.sqlite aus https://liste.mediathekview.de/Filmliste-akt.xz
/usr/local/bin/mtv_cli.py:49: DeprecationWarning: ssl.SSLContext() without protocol argument is deprecated.
  return request.urlopen(url,context=ssl.SSLContext())
/usr/local/bin/mtv_cli.py:49: DeprecationWarning: ssl.PROTOCOL_TLS is deprecated
  return request.urlopen(url,context=ssl.SSLContext())
[DEBUG] [2025-02-28 09:16:15] Anzahl Sätze: 12
[ERROR] [2025-02-28 09:16:15] Update der Filmliste gescheitert. Fehler: UNIQUE constraint failed: filme._id

Also you can see, the SSL info is there.

[bablokb]

Thanks. I am running this every evening and since yesterday it fails. I will need to check why. And I will have to update my scripts so that I will be alarmed.

I also have a fix for the SSL message, but I forgot to publish it.

[cc13com]

Good to see that the development is in progress and not EOL. :-)

[bablokb]

It seems they have changed the format of the film-list. There is no more _id field. Not sure if this is an error (we had that before). Too bad that I did not keep an old film-list to check.

I will see what I can do about that. Maybe I just auto-create the field.

[cc13com]

Hello @bablokb, I can check in one of my VMs if I will find an old film-list. Let me know if you need help.

[magcode]

for me adding or replace to https://github.com/bablokb/mtv_cli/blob/deb3d3980cdb5cc7fe5ffde0eff25c2f1452b027/files/usr/local/bin/mtv_filmdb.py#L132 fixed it

[bablokb]

@cc13com

Hello @bablokb, I can check in one of my VMs if I will find an old film-list. Let me know if you need help.

That would be great. I don't need the complete filmlist. Just unpack it and post the first 256-1024 chars. It is some sort of json and one very large string but the header is upfront and I would only need the header and maybe one additional line.

[bablokb]

for me adding or replace to

mtv_cli/files/usr/local/bin/mtv_filmdb.py

Line 132 in deb3d39 INSERT_STMT = 'INSERT INTO filme VALUES (' + 20 * '?,' + '?)' fixed it

@magcode have you checked the filmdb after insert? I would guess that it only contains a single line. The _id field is missing and that is the key, so every record the script adds has the same empty key (a special feature of sqlite).

Nevertheless, I will probably add the or replace when I create my own _id (probably a hash from sender, thema, titel, dauer, datum). This should guard against duplicate entries. Hopefully I will have new code tomorrow.

[magcode]

The issue for me was not a missing id but the same ID coming again. I did simply print out the id and called mtv_cli.py -A. Then I saw the same ID (4aef1a0c0c1d25c4ca3ef172346a63e7 coming again and the script stopped.

a2acd544ed3c2b5827bb631c48f660d1
4aef1a0c0c1d25c4ca3ef172346a63e7
2dc493cbd3b98cb15aea8ed9fa09bc73
aeb404217a7e106ca50bc1f65d635ac4
4aef1a0c0c1d25c4ca3ef172346a63e7
[ERROR] [2025-03-03 17:35:17] Update der Filmliste gescheitert. Fehler: UNIQUE constraint failed: filme._id

[bablokb]

Thanks, this helps. Actually I am a bit embarrassed, because if I look at my code I can see that I added this feature (create _id if it does not exist) already back in 2017. Totally forgot about that.

So this now boils down to check if the hash-function is not good enough or if we indeed just have duplicate lines in the filmlist. If so, your fix does the trick.

[bablokb]

Please download, install and test the code in the 'next' branch. This should also fix #22. If this also works for you, I will merge it to main.

[gameshacker]

Hallo, thanks the next branch version works without failure.

[INFO] [2025-03-04 09:09:06] Anzahl Buffer:              65911
[INFO] [2025-03-04 09:09:06] Anzahl Sätze (gesamt):      842393
[INFO] [2025-03-04 09:09:06] Anzahl Sätze (gespeichert): 80676
[INFO] [2025-03-04 09:09:06] Anzahl Sätze (Dubletten):   7329

[cc13com]

@cc13com

Hello @bablokb, I can check in one of my VMs if I will find an old film-list. Let me know if you need help.

That would be great. I don't need the complete filmlist. Just unpack it and post the first 256-1024 chars. It is some sort of json and one very large string but the header is upfront and I would only need the header and maybe one additional line.

That means you don't need an old film list anymore? Sorry, I have no access to my server during the week.

[cc13com]

Hallo, thanks the next branch version works without failure.

[INFO] [2025-03-04 09:09:06] Anzahl Buffer:              65911
[INFO] [2025-03-04 09:09:06] Anzahl Sätze (gesamt):      842393
[INFO] [2025-03-04 09:09:06] Anzahl Sätze (gespeichert): 80676
[INFO] [2025-03-04 09:09:06] Anzahl Sätze (Dubletten):   7329

For me the "next" branch works fine. Thanks!

[bablokb]

Fixed with 4d1335e043f5cf8b24c9502a14c7e498a5635fc2