Music collection update fails due to SQL error

[hypnotoad]

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:

Nov 03 09:27:15 [241276] MARK  : COLLECTION                                 : Finding AlbumArtist for album The Very Best Of
Nov 03 09:27:15 [241276] LOG   : COLLECTION      (sortTracks)               :   Album The Very Best Of has too few tracks to determine album artist
Nov 03 09:27:15 [241276] LOG   : COLLECTION      (decideOnArtist)           :   ... Setting artist to Ennio Morricone
Nov 03 09:27:15 [241276] ERROR : SQL                                        : find_track execution failed
Nov 03 09:27:15 [241276] ERROR : GENERIC SQL                                : Code 42000 SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS new
				ON DUPLICATE KEY UPDATE
					Duration = new.Duration,
					Uri = n...' at line 4
Nov 03 09:27:15 [241276] ERROR : GENERIC SQL                                : Stack Trace Array
(
    [0] => Array
        (
            [file] => /var/www/rompr2/collection/musiccollection.class.php
            [line] => 523
            [function] => execute
            [class] => PDOStatement
            [type] => ->
        )

    [1] => Array
        (
            [file] => /var/www/rompr2/collection/album.class.php
            [line] => 88
            [function] => check_and_update_track
            [class] => musicCollection
            [type] => ->
        )

    [2] => Array
        (
            [file] => /var/www/rompr2/collection/musiccollection.class.php
            [line] => 97
            [function] => check_database
            [class] => album
            [type] => ->
        )

    [3] => Array
        (
            [file] => /var/www/rompr2/collection/musiccollection.class.php
            [line] => 52
            [function] => sort_badly_tagged_albums
            [class] => musicCollection
            [type] => ->
        )

    [4] => Array
        (
            [file] => /var/www/rompr2/api/collection/index.php
            [line] => 199
            [function] => newTrack
            [class] => musicCollection
            [type] => ->
        )

    [5] => Array
        (
            [file] => /var/www/rompr2/api/collection/index.php
            [line] => 56
            [function] => update_collection
        )

)

Nov 03 09:27:15 [241276] ERROR : GENERIC SQL                                : Object track Object
(
    [tags] => Array
        (
            [file] => undefined-musik-nb4eF/Ennio Morricone/Ennio Morricone - Mein Name Ist Nobody (copy).mp3
            [domain] => local
            [type] => local
            [station] => 
            [stream] => 
            [folder] => undefined-musik-nb4eF/Ennio Morricone
            [Title] => Mein Name Ist Nobody
            [Album] => The Very Best Of
            [Artist] => Array
                (
                    [0] => Ennio Morricone
                )

            [Track] => 21
            [Name] => 
            [AlbumArtist] => Array
                (
                    [0] => Ennio Morricone
                )

            [Time] => 183
            [X-AlbumUri] => 
            [playlist] => 
            [X-AlbumImage] => 
            [Date] => 
            [OriginalDate] => 
            [Last-Modified] => 2023-01-08T08:39:41Z
            [Disc] => 1
            [Composer] => 
            [Performer] => 
            [Genre] => (12)
            [ImgKey] => 24091f9616e39d829de1877ccad80673
            [StreamIndex] => 
            [Searched] => 0
            [Playcount] => 0
            [Comment] => 
            [MUSICBRAINZ_ALBUMID] => 
            [MUSICBRAINZ_ARTISTID] => Array
                (
                    [0] => 
                )

            [MUSICBRAINZ_ALBUMARTISTID] => 
            [MUSICBRAINZ_TRACKID] => 
            [Id] => 
            [Pos] => 
            [trackartist_index] => 
            [albumartist_index] => 785
            [album_index] => 779
            [hidden] => 0
            [year] => 
            [isaudiobook] => 0
            [is_classical] => 
            [is_tracklist] => 
            [Format] => 44100:24:2
            [duration] => 182.757
            [unmopfile] => undefined-musik-nb4eF/Ennio Morricone/Ennio Morricone - Mein Name Ist Nobody (copy).mp3
            [albumartist] => Ennio Morricone
            [trackartist] => Ennio Morricone
        )

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]

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]

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]

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]

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.