Exception Thrown when moving to 4.8.1.0

[earratia14 11]

Emby Server 4.8.1.0 crashes and logs this exception:
2024-02-13 10:14:23.747 Error Main: Error in appHost.Init
    *** Error Report ***
    Version: 4.8.1.0
    Command line: C:\Users\emby\AppData\Roaming\Emby-Server\system\EmbyServer.dll
    Operating system: Microsoft Windows 10.0.14393
    Framework: .NET 6.0.26
    OS/Process: x64/x64
    Runtime: C:/Users/emby/AppData/Roaming/Emby-Server/system/System.Private.CoreLib.dll
    Processor count: 32
    Data path: C:\Users\emby\AppData\Roaming\Emby-Server\programdata
    Application path: C:\Users\emby\AppData\Roaming\Emby-Server\system
    SQLitePCL.pretty.SQLiteException: Error: no such column: Album - insert into fts_search8(RowId, Name, OriginalTitle, SeriesName, Album) select id, replace(replace(Name,'''',''),'.',''), replace(replace(OriginalTitle,'''',''),'.',''), replace(replace(SeriesName,'''',''),'.',''), replace(replace(Album,'''',''),'.','') from MediaItems
    SQLitePCL.pretty.SQLiteException: Exception of type 'SQLitePCL.pretty.SQLiteException' was thrown.
       at SQLitePCL.pretty.SQLiteDatabaseConnection.PrepareStatement(String sql, ReadOnlySpan`1& tail)
       at SQLitePCL.pretty.DatabaseConnection.PrepareAllEnumerator(IDatabaseConnection This, String sql)+MoveNext()
       at SQLitePCL.pretty.DatabaseConnection.ExecuteAll(IDatabaseConnection This, String sql)
       at Emby.Server.Implementations.Data.SqliteItemRepository.Initialize(SqliteUserDataRepository userDataRepo, SyncRepository syncRepo)
       at Emby.Server.Implementations.ApplicationHost.InitDatabases()
       at Emby.Server.Implementations.ApplicationHost.Init()
       at EmbyServer.HostedService.StartAsync(CancellationToken cancellationToken)
    Source: SQLitePCL.pretty
    TargetSite: SQLitePCL.pretty.IStatement PrepareStatement(System.String, System.ReadOnlySpan`1[System.Byte] ByRef)
    

 

I am trying to move from 4.9.0.2 to 4.8.1.0. I do get the same error if I try to go to 4.8.0.80

[Happy2Play 8282]

2 hours ago, earratia14 said:

I am trying to move from 4.9.0.2 to 4.8.1.0. I do get the same error if I try to go to 4.8.0.80

I will guess there is already an incompatibility in downgrading server versions, or your server did not upgrade correctly, and you have a broken database, but dev will have to comment further.

[earratia14 11]

23 minutes ago, Happy2Play said:

I will guess there is already an incompatibility in downgrading server versions, or your server did not upgrade correctly, and you have a broken database, but dev will have to comment further.

Yeah, I don't know which is true (incompatibility or broken DB).

So what I did was to add a Text column called "Album" to the table, then run an UPDATE - FROM to repopulate it. I was then able to start Emby 4.8.1.0 with no exceptions.

[Happy2Play 8282]

Comparing 4.8 to 4.9 db there is no Album column in 4.9 so I would assume a change to make versions incompatible to downgrade.

But glad you were able to find a workaround.

[PeteBete 4]

On 14/02/2024 at 04:58, earratia14 said:

Yeah, I don't know which is true (incompatibility or broken DB).

So what I did was to add a Text column called "Album" to the table, then run an UPDATE - FROM to repopulate it. I was then able to start Emby 4.8.1.0 with no exceptions.

@earratia14Would you mind outlining the steps you used to update the SQL data and populate the text column "Album" ?

[Luke 37068]

@earratia14?

[earratia14 11]

Oops, sorry I missed that post.

I think I wrote the sql down on a txt file in my computer. I am answering this from my phone, so I'll look it up when I get a chance to look for it.

[earratia14 11]

OK, sorry for the delay. I can't seem to find the SQL script I used. The only thing I was able to find was a script to re-create two triggers that need to be dropped before adding the new Album column.

The absence of this column was causing Emby to crash on startup. I was able to fix this crash, however looking at the log file I found that SQL exceptions were still being thrown. This did not affect playback of media, but it did cause "flaky" behavior when identifying new media or retaining information on existing DVR related media. I do not think the Album column or the fix caused these exceptions or flaky behavior, I think I just had some generalized database corruption.

Long story short, I ended up removing Emby entirely and deleting all its files. I then installed it fresh and slowly re-added all my media. The new install has been working flawlessly since then.

 

These are the scripts to recreate the two triggers that need to be dropped before adding the Album column:

CREATE TRIGGER mediaitems_insert_fts9
  AFTER INSERT ON MediaItems BEGIN
insert into fts_search9(RowId, Name, OriginalTitle, SeriesName, Album)
values (new.Id, replace(replace(new.Name,'''',''),'.',''), replace(replace(new.OriginalTitle,'''',''),'.',''), replace(replace(new.SeriesName,'''',''),'.',''), replace(replace((Select case when new.AlbumId is null then null Else (Select Name from MediaItems where Id = new.AlbumId limit 1) end),'''',''),'.',''));
END

CREATE TRIGGER mediaitems_delete_fts9
  AFTER DELETE ON MediaItems BEGIN
  delete from fts_search9 where RowId=old.RowId;
END
 

Add "Album" column (Text) to the MediaItems table. If the AlbumID column is missing, add it as well (INT). No SQL needed, you can use SQLite's GUI for this.

 

Not having the original script I tried to re-create it here. This query will show all the rows that will be updated, the sub.* shows the data that will be placed into the Album and AlbumID columns.

SELECT mi.id, mi.type,mi.path,mi.name, mi.album, mi.AlbumId, sub.*
FROM MediaItems mi
INNER JOIN (SELECT mis.id, mis.Name AlbumName, li.ItemId
            FROM MediaItems mis
            INNER JOIN ItemLinks2 li ON li.LinkedId = mis.id
            WHERE mis.type=12) sub ON sub.ItemId = mi.id

This  query can be modified into an UPDATE FROM

UPDATE MediaItems
SET Album = sub.AlbumName, AlbumId = sub.id
FROM (SELECT mis.id, mis.Name AlbumName, li.ItemId
      FROM MediaItems mis
      INNER JOIN ItemLinks2 li ON li.LinkedId = mis.id
      WHERE mis.type=12) sub
WHERE sub.ItemId = MediaItems.id;

 

The statements above works only for music albums ("mis.Type = 12").