SQL logic error integer overflow

NIronwolf · August 29, 2022, 8:03am

Sonarr version (exact version): 3.0.9.1549
Mono version (if Sonarr is not running on Windows): 6.12.0
OS: Docker on unRAID
Debug logs:
2022-08-29 00:49:35,671 DEBG ‘sonarr’ stdout output:
[Fatal] SonarrErrorPipeline: Request Failed. GET /api/v3/series

[v3.0.9.1549] code = Error (1), message = System.Data.SQLite.SQLiteException (0x800007BF): SQL logic error
integer overflow
at System.Data.SQLite.SQLite3.Reset (System.Data.SQLite.SQLiteStatement stmt) [0x00088] in :0
at System.Data.SQLite.SQLite3.Step (System.Data.SQLite.SQLiteStatement stmt) [0x0006e] in :0
at System.Data.SQLite.SQLiteDataReader.Read () [0x00043] in :0

2022-08-29 00:49:35,671 DEBG ‘sonarr’ stdout output:
at Marr.Data.DataMapper.Query[T] (System.String sql, System.Collections.Generic.ICollection1[T] entityList, System.Boolean useAltName) [0x000a2] in M:\BuildAgent\work\63739567f01dbcc2\src\Marr.Data\DataMapper.cs:569 at Marr.Data.DataMapper.Query[T] (System.String sql, System.Collections.Generic.ICollection1[T] entityList) [0x00000] in M:\BuildAgent\work\63739567f01dbcc2\src\Marr.Data\DataMapper.cs:546
at Marr.Data.DataMapper.Query[T] (System.String sql) [0x00000] in M:\BuildAgent\work\63739567f01dbcc2\src\Marr.Data\DataMapper.cs:537
at NzbDrone.Core.SeriesStats.SeriesStatisticsRepository.SeriesStatistics () [0x00057] in M:\BuildAgent\work\63739567f01dbcc2\src\NzbDrone.Core\SeriesStats\SeriesStatisticsRepository.cs:35
at NzbDrone.Core.SeriesStats.SeriesStatisticsService.SeriesStatistics () [0x00000] in M:\BuildAgent\work\63739567f01dbcc2\src\NzbDrone.Core\SeriesStats\SeriesStatisticsService.cs:23
at Sonarr.Api.V3.Series.SeriesModule.AllSeries () [0x0002f] in M:\BuildAgent\work\63739567f01dbcc2\src\Sonarr.Api.V3\Series\SeriesModule.cs:108
at Sonarr.Http.REST.RestModule1[TResource].<set_GetResourceAll>b__33_0 (System.Object options) [0x00000] in M:\BuildAgent\work\63739567f01dbcc2\src\Sonarr.Http\REST\RestModule.cs:124 at Nancy.NancyModule+<>c__DisplayClass14_01[T].b__0 (System.Object args) [0x00047] in :0
at Nancy.NancyModule+<>c__DisplayClass16_01[T].<Get>b__0 (System.Object args, System.Threading.CancellationToken ct) [0x00047] in <e31b230dbd29407fadcc55713e0bbd0f>:0 at Nancy.Routing.Route1[T].Invoke (Nancy.DynamicDictionary parameters, System.Threading.CancellationToken cancellationToken) [0x00017] in :0
at Nancy.Routing.DefaultRouteInvoker.Invoke (Nancy.Routing.Route route, System.Threading.CancellationToken cancellationToken, Nancy.DynamicDictionary parameters, Nancy.NancyContext context) [0x00086] in :0
at Nancy.Routing.DefaultRequestDispatcher.Dispatch (Nancy.NancyContext context, System.Threading.CancellationToken cancellationToken) [0x002b7] in :0
at Nancy.NancyEngine.InvokeRequestLifeCycle (Nancy.NancyContext context, System.Threading.CancellationToken cancellationToken, Nancy.Bootstrapper.IPipelines pipelines) [0x0011d] in :0

Description of issue: When trying to connect it shows the login from, then after authentication it only displays “SQL logic error integer overflow
Version 3.0.9.1549” from the root of the web server.

I attempted to run: sqlite3 sonarr.db “.recover” | sqlite3 new.db
and copy this over. Neither db will work. After many years of use, I’m almost afraid I am overflowing some integer total.

LMK if sending the db for review is necessary. It is over 700M and the recovered one is about 650M. Both open fine in an SQLite db Browser.

NIronwolf · August 29, 2022, 8:04am

Oh I should mention that it’s still processing shows! I just cannot get to the interface.

markus101 · August 29, 2022, 8:18pm

Looks like something in this query:

github.com

Sonarr/Sonarr/blob/v3/src/NzbDrone.Core/SeriesStats/SeriesStatisticsRepository.cs#L54-L69


private string GetSelectClause(bool series)
{
    return @"SELECT Episodes.*, SUM(EpisodeFiles.Size) as SizeOnDisk, GROUP_CONCAT(EpisodeFiles.ReleaseGroup, '|') AS ReleaseGroupsString FROM
             (SELECT
             Episodes.SeriesId,
             Episodes.SeasonNumber,
             COUNT(*) AS TotalEpisodeCount,
             SUM(CASE WHEN AirdateUtc <= @currentDate OR EpisodeFileId > 0 THEN 1 ELSE 0 END) AS AvailableEpisodeCount,
             SUM(CASE WHEN (Monitored = 1 AND AirdateUtc <= @currentDate) OR EpisodeFileId > 0 THEN 1 ELSE 0 END) AS EpisodeCount,
             SUM(CASE WHEN EpisodeFileId > 0 THEN 1 ELSE 0 END) AS EpisodeFileCount,
             MIN(CASE WHEN AirDateUtc < @currentDate OR EpisodeFileId > 0 OR Monitored = 0 THEN NULL ELSE AirDateUtc END) AS NextAiringString,
             MAX(CASE WHEN AirDateUtc >= @currentDate OR EpisodeFileId = 0 AND Monitored = 0 THEN NULL ELSE AirDateUtc END) AS PreviousAiringString
             FROM Episodes" +
             (series ? " WHERE Episodes.SeriesId = @seriesId" :  "") + @"
             GROUP BY Episodes.SeriesId, Episodes.SeasonNumber) as Episodes";
}

(seriesId and currentDate are passed in as parameters to the query) is throwing the error, the most logical cause would be the SUM(EpisodeFiles.Size), but sqlite’s integer and .net’s long support values +/- 9223372036854775807 so it should be a non-issue.

If you’re able to grab a backup where you have this issue and clear out your indexers, download clients and connections (notifications) and DM me a copy I can deeper look into what might be causing it.

NIronwolf · September 2, 2022, 4:41pm

I didn’t get an opportunity to see it listed in the database, but this has resolved itself.

Somehow I had a pair of files that were put in the filesystem as sparse files totaling 9 EXAbytes between them. I’m guessing that rolled over the file size totals.

I corrected those files and now that I checked Sonarr after it was running and I hadn’t thought to check it right away it’s loading up just fine.

Not sure how those files got messed up in the first place, but luckily I ran across them just sitting there being huge in another unrelated utility.

Thanks for the offer to look at it though!

system · November 1, 2022, 4:41pm

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.