Music collection update fails due to SQL error

hypnotoad commented 11 months ago

The music collection update fails for me for some undefined time. It never completes successfully and leaves a lock. Clearing the lock does not improve the situation on the next scan. The log file indicates that it is due to some SQL command.

Setup

I use MariaDB Ver 15.1 Distrib 10.11.4 from Debian 12 and do not see any option related to the problem on the Rompr installation page.

Investigation and Workaround

After investigating, I think it is either some sql command that mariadb does not understand or about some escaping: The error message below from the debug logis about the SQL insert syntax which indeed does not seem available in mariadb (https://mariadb.com/kb/en/insert/ vs. https://dev.mysql.com/doc/refman/8.0/en/insert.html). But on the other side, it only seems to happen for some files which have.

Setting old_style_sql";b:1; in prefs.var seems to resolve the problem.

Log Output without Workaround

Before I found this workaround, I extended the logging to have the failing entry printed as well. Here is the relevant output:

After removing the parentheses from the file name, I get another similar error for another file:

Nov 03 10:01:28 [352765] ERROR : GENERIC SQL                                : Object track Object
(
    [tags] => Array
        (
            [file] => undefined-musik-nb4eF/Geräusche/rosa.ogg
            [domain] => local
            [type] => local
            [station] => 
            [stream] => 
            [folder] => undefined-musik-nb4eF/Geräusche
            [Title] => Rosa Rauschen
            [Album] => Geräusche
            [Artist] => Array
                (
                    [0] => Foo
                )

            [Track] => 0
            [Name] => 
            [AlbumArtist] => Array
                (
                    [0] => Foo
                )

            [Time] => 3600
            [X-AlbumUri] => 
            [playlist] => 
            [X-AlbumImage] => 
            [Date] => 
            [OriginalDate] => 
            [Last-Modified] => 2020-12-10T14:52:55Z
            [Disc] => 1
            [Composer] => 
            [Performer] => 
            [Genre] => None
            [ImgKey] => aec4bcfc3d94495f2beaf1622196d7a7
            [StreamIndex] => 
            [Searched] => 0
            [Playcount] => 0
            [Comment] => 
            [MUSICBRAINZ_ALBUMID] => 
            [MUSICBRAINZ_ARTISTID] => Array
                (
                    [0] => 
                )

            [MUSICBRAINZ_ALBUMARTISTID] => 
            [MUSICBRAINZ_TRACKID] => 
            [Id] => 
            [Pos] => 
            [trackartist_index] => 
            [albumartist_index] => 786
            [album_index] => 780
            [hidden] => 0
            [year] => 
            [isaudiobook] => 0
            [is_classical] => 
            [is_tracklist] => 
            [Format] => 48000:f:2
            [duration] => 3600.000
            [unmopfile] => undefined-musik-nb4eF/Geräusche/rosa.ogg
            [albumartist] => Foo
            [trackartist] => Foo
        )

)

fatg3erman commented 11 months ago

Sigh. MariaDB is an absolute arse to work with. I'm also running that same version, on Rasberry Pi OS, and old_style_sql needs to be 0 for me - which makes it use the format of SQL that is causing you that issue. There is no consistency in versioning so there's no way I can make it work on every system. Is there some reason you need to use mariadb? Unless you have an enormous music collection SQLite is always better - it's faster and uses less resources. If you really need an SQL server I can only suggest you use MySQL instead - they have a deb repo here https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/

hypnotoad commented 11 months ago

I mean for me, using the old_style_sql=1 works, so no need to fix something for me (until this is deprecated). From your documentation I already realized that you are not happy about mysql/mariadb. I would have switched already but I am not sure what I would loose upon using a clean sqlite database.

Others face the same issue and use the following code to enable the alias in insert duplicates: sqlalchemy .

fatg3erman commented 11 months ago

You can switch to SQLite without losing anything. First you need to create a metadta backup, using the plugin from the drop down menu above the info panel. Then switch to SQLite, rebuild your music colection, then restore the metadata backup.

basos9 commented 8 months ago

Yes it happens here.

Debian (raspbian) 12.4
ii  mariadb-server-10.5                              1:10.11.4-1~deb12u1 
rompr 2.10

I switched to Sqlite, and this is fixed. At least this should be documented, if not fixed.

fatg3erman / RompR