Info SqliteItemRepository: Result of PRAGMA integrity_check: wrong # of entries in index UserDatasIndex9

[Paint 10]

My Emby Server will no longer backup via the scheduled task. In the emby log, I get the following error. Any tips or ideas on how to fix this? thank you

 

Info SqliteItemRepository: Result of PRAGMA integrity_check: wrong # of entries in index UserDatasIndex9

 

when I open the library.db file in sqlite3 and run PRAGMA integrity_check, I get these errors:

 

# sqlite3 library.db 
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> PRAGMA integrity_check;
row 15077 missing from index UserDatasIndex9
row 15077 missing from index UserDatasIndex8
row 15077 missing from index UserDatasIndex7
row 15077 missing from index UserDatasIndexUnique1
wrong # of entries in index UserDatasIndex9
wrong # of entries in index UserDatasIndex8
wrong # of entries in index UserDatasIndex7
wrong # of entries in index UserDatasIndexUnique1

 

Edited January 5, 2025 by Paint

[Luke 42077]

Hi, thanks for following up.

[Paint 10]

that actually didnt solve the issue. I ended up restoring the corrupted database and I ran the following:

sqlite> PRAGMA integrity_check;
row 15077 missing from index UserDatasIndex9
row 15077 missing from index UserDatasIndex8
row 15077 missing from index UserDatasIndex7
row 15077 missing from index UserDatasIndexUnique1
wrong # of entries in index UserDatasIndex9
wrong # of entries in index UserDatasIndex8
wrong # of entries in index UserDatasIndex7
wrong # of entries in index UserDatasIndexUnique1
sqlite> REINDEX UserDatasIndex9;
sqlite> REINDEX UserDatasIndex9;
sqlite> REINDEX UserDatasIndex8;
sqlite> REINDEX UserDatasIndex7;
sqlite> REINDEX UserDatasIndexUnique1;
Error: stepping, UNIQUE constraint failed: UserDatas.UserDataKeyId, UserDatas.userId (19)
sqlite> PRAGMA integrity_check;
row 15077 missing from index UserDatasIndexUnique1
wrong # of entries in index UserDatasIndexUnique1

 

@Lukeany ideas what to run to fix this? I am unable to backup because of this

[Luke 42077]

Please attach the emby server log from when you tried to run a backup. Thanks.

[Lessaj 467]

A million prefaces of some kind of backup before you do anything and that I guarantee nothing.

You can try to drop the index and recreate it. This is what I see for the index in a copy of my DB. I tried dropping it and recreating it but I did not try to actually use the file after. Indexes are usually pretty straight forward though.

DROP INDEX UserDatasIndexUnique1;
CREATE UNIQUE INDEX UserDatasIndexUnique1 on UserDatas (UserDataKeyId, userId)

Quote

-- EXECUTING ALL IN 'SQL 1'
--
-- At line 1:
DROP INDEX UserDatasIndexUnique1;
-- Result: query executed successfully. Took 0ms
-- At line 2:
CREATE UNIQUE INDEX UserDatasIndexUnique1 on UserDatas (UserDataKeyId, userId)
-- Result: query executed successfully. Took 19ms

 

Edited January 7, 2025 by Lessaj

[Paint 10]

@Lessajthe first line of your command doesn't work in sqlite3

 

sqlite> DROP INDEX UserDatasIndexUnique1;
Error: in prepare, near "INDEX UserDatasIndexUnique1": syntax error (1)

 

[Lessaj 467]

I used DB Browser for SQLite to run the commands and to run them together I needed the semi colon, sorry not sure if it just doesn't like the semi colon or not but they can be run one at a time without that.

[Paint 10]

@Lessajok I got the drop to work...but then the unique index cant be created....

 

sqlite> drop index UserDatasIndexUnique1;
sqlite> CREATE UNIQUE INDEX UserDatasIndexUnique1 on UserDatas (UserDataKeyId, userId);
Error: stepping, UNIQUE constraint failed: UserDatas.UserDataKeyId, UserDatas.userId (19)

sqlite> pragma integrity_check;
row 15077 missing from index UserDatasIndexUnique1
wrong # of entries in index UserDatasIndexUnique1

 

[Lessaj 467]

Okay I see think I understand what the error is saying, it looks like there's a duplicate entry in the UserDatas table which violates the unique constraints of index. I have no clue if this will give you a result or not, I don't know if the 19 means userId 19 or something else.

select * from UserDatas where userId=19 group by UserDataKeyId HAVING COUNT(UserDataKeyId) > 1

 

[Paint 10]

Im debugging this.  Thanks for the direction @Lessaj

[Paint 10]

okay - I was able to find the duplicate line using DB Browser. 

running this sql delete command, then deleting the index and recreating the index, fixed the problem. Thank you!

 

delete from UserDatas where UserDataKeyId=19837 and userId=36