Event search fails on album page

[riipah]

I tried typing "Miku birthday" in the event search box. But the loading spinner never completes. The response gets logged as 500 in browser console. Possibly database timeout?

It looks like other searches work, but if I search anything "Miku" then it fails. Database timeout is a bit odd, there's not that many events...

[ycanardeau]

The log file says:

ERROR An unhandled exception has occurred while executing the request. - NHibernate.Exceptions.GenericADOException could not execute query
...
Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Win32Exception The wait operation timed out.

The raw SQL query is:

select
    releaseeve0_.Id as id1_40_,
    releaseeve0_.[Category] as column2_40_,
    releaseeve0_.[CreateDate] as column3_40_,
    releaseeve0_.[CustomName] as column4_40_,
    releaseeve0_.[Deleted] as column5_40_,
    releaseeve0_.[Description] as column6_40_,
    releaseeve0_.[PictureMime] as column7_40_,
    releaseeve0_.[SeriesNumber] as column8_40_,
    releaseeve0_.[SeriesSuffix] as column9_40_,
    releaseeve0_.[Status] as column10_40_,
    releaseeve0_.VenueName as venuename11_40_,
    releaseeve0_.[Version] as column12_40_,
    releaseeve0_.[Series] as column13_40_,
    releaseeve0_.[SongList] as column14_40_,
    releaseeve0_.[Venue] as column15_40_,
    releaseeve0_.[AdditionalNamesString] as column16_40_,
    releaseeve0_.DefaultNameLanguage as defaultnamelanguage17_40_,
    releaseeve0_.JapaneseName as japanesename18_40_,
    releaseeve0_.EnglishName as englishname19_40_,
    releaseeve0_.RomajiName as romajiname20_40_,
    releaseeve0_.[Date] as column21_40_,
    releaseeve0_.[EndDate] as column22_40_
from dbo.AlbumReleaseEvents releaseeve0_
where
    not (releaseeve0_.[Deleted]=1) and
    1=1 and
    (exists (
        select names1_.Id
        from dbo.EventNames names1_
        where
            releaseeve0_.Id=names1_.[Event] and
            (names1_.[Value] like ('%'+'miku'+'%'))
    )) and
    (exists (
        select names2_.Id
        from dbo.EventNames names2_
        where
            releaseeve0_.Id=names2_.[Event] and
            (names2_.[Value] like ('%'+'bir'+'%'))
    ))
    order by releaseeve0_.EnglishName asc
    OFFSET 0 ROWS
    FETCH FIRST 20 ROWS ONLY

I tried this query on SSMS as well and it has been executed successfully. Would this issue be related to ASP.NET Core or NHibernate?

[riipah]

MSSQL does that sometimes. It chooses a bad query plan for whatever reason. It may use that particular plan only if the parameters are exactly the same, that's why it can be difficult to replicate in SSMS. There is no way to prevent that from happening, that I know. Restarting MSSQL flushes the query plans and should make the problem go away, until the next time. Of course for me as a user it's impossible to say why the server is returning 500. I would recommend looking into migrating the database to Postgres at some point, because currently you can't update MSSQL.

[riipah]

I think there might be nothing that can be done here for now. It's a "feature" I guess.