Large playlist, Large collection: Something went wrong retrieving the playlist!

First thanks for that very nice web client. I have integrated RompR into my rompr branch for my multiroom setup.

My issue:

after reading in my collection (takes quite some time, but completes successfully), I almost never get to see the current playlist with over 600 entries. When I shorten the list to 174 tracks (total 13h30m) and do a reload, it takes 22 seconds until the list is displayed. As I usually work with that long playlists, this is really a showstopper for me.

I rose the log level to 7 (DEBUG) to see about every other seconds a DEBUG message about an album art of one of the albums on my playlist. But this was just a red herring...
when I move away temporarily the database file (rompr/prefs/collection.sq3), the current playlist is displayed almost instantaneously, even with the album art. This playlist has currently about 600 items, a few of them are radio stations. Of course I cannot browse the database or do anything else useful that needs the database.

So: I am sure that one or more database queries slow down the build up of the playlist considerabely. However, after setting the log level to 8 (CORE), I see a lot of SQL statements but I am overwhelmed and fail to track to down that "bad" statement that kills the display of the playlist. What information is collected from the database when the playlist is shown? I don't seem to miss anything in the playlist when the DB is empty.

Any ideas how to proceed?

I don't think this is related, but rompr has no access to the files of the collection, they are visible via mpd only. This is because a) rompr is running in a container and b) even if this container runs on the raspi that runs rompr, the collection is "mounted" for mpd via an smb url:

music_directory         "smb://mysambahost.example.net/mysambsharename"

Some more stats:

# ls -l rompr/prefs/collection.sq3 
-rw-r--r-- 1 www-data www-data 53678080 Oct  5 18:42 rompr/prefs/collection.sq3

# sqlite3 rompr/prefs/collection.sq3 "select * from statsTable where value > 0;"
CollType|999
SchemaVer|105
LastCache|1728138983
ListVersion|6
ArtistCount|4716
AlbumCount|12744
TrackCount|109442
TotalTime|31883197

Debug Information

* **BACKEND INFO**
  * **Version** 2.17
  * **System** Linux f02cfd56ca76 6.6.31+rpt-rpi-v8 #1 SMP PREEMPT Debian 1:6.6.31-1+rpt1 (2024-05-29) aarch64
  * **mysql_database** [Redacted]
  * **mysql_host** [Redacted]
  * **mysql_password** [Redacted]
  * **mysql_port** [Redacted]
  * **mysql_user** [Redacted]
  * **spotify_token** [Redacted]
  * **spotify_token_expires** [Redacted]
* **SERVER INFO**
  * **PHP_SELF** /utils/debuginfo.php
  * **SERVER_ADDR** 172.17.0.2
  * **SERVER_NAME** _
  * **SERVER_SOFTWARE** nginx/1.22.1
  * **DOCUMENT_ROOT** /app/rompr
  * **HTTP_HOST** REDACTED.REDACTED.net
  * **HTTP_USER_AGENT** Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:130.0) Gecko/20100101 Firefox/130.0
  * **REMOTE_ADDR** r.e.d.acted
  * **SERVER_PORT** 80
  * **REQUEST_URI** /utils/debuginfo.php
* **COOKIES**
  * **skin** phone
  * **clickmode** double
  * **browser_id** 1728127371009
  * **sortbydate** 1
  * **sort_ab_bydate** 1
  * **currenthost** Default
  * **player_backend** mpd
* **PHP INFO**
  * **Version** 8.2.24
  * **mbstring** 8.2.24
  * **PDO** 8.2.24
  * **pdo_mysql** 8.2.24
  * **pdo_sqlite** 8.2.24
  * **curl** 8.2.24
  * **date** 8.2.24
  * **fileinfo** 8.2.24
  * **json** 8.2.24
  * **SimpleXML** 8.2.24
  * **GD** 8.2.24
  * **GD Info** GD Version=2.3.3, FreeType Support=1, FreeType Linkage=with freetype, GIF Read Support=1, GIF Create Support=1, JPEG Support=1, PNG Support=1, WBMP Support=1, XPM Support=1, XBM Support=1, WebP Support=1, BMP Support=1, AVIF Support=1, TGA Read Support=1, JIS-mapped Japanese Font Support=
  * **ImageMagick** Installed
  * **date.timezone** global_value=UTC, local_value=UTC, access=7
  * **default_charset** global_value=UTF-8, local_value=UTF-8, access=7
  * **default_socket_timeout** global_value=60, local_value=60, access=7
  * **display_errors** global_value=1, local_value=1, access=7
  * **error_log** global_value=, local_value=, access=7
  * **error_reporting** global_value=22527, local_value=22527, access=7
  * **file_uploads** global_value=1, local_value=1, access=4
  * **log_errors** global_value=1, local_value=1, access=7
  * **max_execution_time** global_value=1800, local_value=1800, access=7
  * **mbstring.language** global_value=neutral, local_value=neutral, access=7
  * **memory_limit** global_value=128M, local_value=128M, access=7
  * **pdo_mysql.default_socket** global_value=/var/run/mysqld/mysqld.sock, local_value=/var/run/mysqld/mysqld.sock, access=4
  * **session.use_cookies** global_value=1, local_value=1, access=7
  * **upload_tmp_dir** global_value=, local_value=, access=4
* **RELEVANT RUNNING PROCESSES**
  * ****
```
www-data     167  0.0  1.7 119908 32224 ?        S    10:33   0:00 php /app/rompr/rompr_backend.php
www-data     177  0.0  1.6 119756 31068 ?        S    10:33   0:00 php /app/rompr/romonitor.php --currenthost Default

```

* **PLAYER INFORMATION**
  * **Connection Status** Connection Successful
  * **MPD Interface Version** 0.23.5
  * **config** []
  * **tagtypes** {"tagtype":["Artist", "ArtistSort", "Album", "AlbumSort", "AlbumArtist", "AlbumArtistSort", "Title", "Track", "Name", "Genre", "Date", "OriginalDate", "Composer", "ComposerSort", "Performer", "Conductor", "Work", "Movement", "MovementNumber", "Ensemble", "Location", "Grouping", "Disc", "Label", "MUSICBRAINZ_ARTISTID", "MUSICBRAINZ_ALBUMID", "MUSICBRAINZ_ALBUMARTISTID", "MUSICBRAINZ_TRACKID", "MUSICBRAINZ_RELEASETRACKID", "MUSICBRAINZ_WORKID"]}
  * **commands** {"command":["add", "addid", "addtagid", "albumart", "binarylimit", "channels", "clear", "clearerror", "cleartagid", "close", "commands", "config", "consume", "count", "crossfade", "currentsong", "decoders", "delete", "deleteid", "delpartition", "disableoutput", "enableoutput", "find", "findadd", "getfingerprint", "getvol", "idle", "kill", "list", "listall", "listallinfo", "listfiles", "listmounts", "listpartitions", "listplaylist", "listplaylistinfo", "listplaylists", "load", "lsinfo", "mixrampdb", "mixrampdelay", "mount", "move", "moveid", "moveoutput", "newpartition", "next", "notcommands", "outputs", "outputset", "partition", "password", "pause", "ping", "play", "playid", "playlist", "playlistadd", "playlistclear", "playlistdelete", "playlistfind", "playlistid", "playlistinfo", "playlistmove", "playlistsearch", "plchanges", "plchangesposid", "previous", "prio", "prioid", "random", "rangeid", "readcomments", "readmessages", "readpicture", "rename", "repeat", "replay_gain_mode", "replay_gain_status", "rescan", "rm", "save", "search", "searchadd", "searchaddpl", "seek", "seekcur", "seekid", "sendmessage", "setvol", "shuffle", "single", "stats", "status", "sticker", "stop", "subscribe", "swap", "swapid", "tagtypes", "toggleoutput", "unmount", "unsubscribe", "update", "urlhandlers", "volume"]}
  * **notcommands** true
  * **urlhandlers** ["alsa:\/\/", "cdda:\/\/", "ftp:\/\/", "ftps:\/\/", "gopher:\/\/", "hls+http:\/\/", "hls+https:\/\/", "http:\/\/", "https:\/\/", "mms:\/\/", "mmsh:\/\/", "mmst:\/\/", "mmsu:\/\/", "nfs:\/\/", "rtmp:\/\/", "rtmpe:\/\/", "rtmps:\/\/", "rtmpt:\/\/", "rtmpte:\/\/", "rtmpts:\/\/", "rtp:\/\/", "rtsp:\/\/", "rtsps:\/\/", "scp:\/\/", "sftp:\/\/", "smb:\/\/", "srtp:\/\/"]
  * **decoders** {"plugin":["mad", "mpg123", "vorbis", "oggflac", "flac", "opus", "sndfile", "dsdiff", "dsf", "faad", "mpcdec", "wavpack", "openmpt", "modplug", "mikmod", "sidplay", "fluidsynth", "adplug", "ffmpeg", "gme", "pcm"], "suffix":["mp3", "mp2", "mp3", "ogg", "oga", "ogg", "oga", "flac", "opus", "ogg", "oga", "wav", "aiff", "aif", "au", "snd", "paf", "iff", "svx", "sf", "voc", "w64", "pvf", "xi", "htk", "caf", "sd2", "dff", "dsf", "aac", "mpc", "wv", "mptm", "mod", "s3m", "xm", "it", "669", "amf", "ams", "c67", "dbm", "digi", "dmf", "dsm", "dtm", "far", "imf", "ice", "j2b", "m15", "mdl", "med", "mms", "mt2", "mtm", "nst", "okt", "plm", "psm", "pt36", "ptm", "sfx", "sfx2", "st26", "stk", "stm", "stp", "ult", "wow", "gdm", "mo3", "oxm", "umx", "xpk", "ppm", "mmcmp", "669", "amf", "ams", "dbm", "dfm", "dsm", "far", "it", "med", "mdl", "mod", "mtm", "mt2", "okt", "s3m", "stm", "ult", "umx", "xm", "amf", "dsm", "far", "gdm", "imf", "it", "med", "mod", "mtm", "s3m", "stm", "stx", "ult", "uni", "xm", "sid", "mus", "str", "prg", "P00", "mid", "amd", "d00", "hsc", "laa", "rad", "raw", "sa2", "16sv", "3g2", "3gp", "4xm", "8svx", "aa3", "aac", "ac3", "adx", "afc", "aif", "aifc", "aiff", "al", "alaw", "amr", "anim", "apc", "ape", "asf", "atrac", "au", "aud", "avi", "avm2", "avs", "bap", "bfi", "c93", "cak", "cin", "cmv", "cpk", "daud", "dct", "divx", "dts", "dv", "dvd", "dxa", "eac3", "film", "flac", "flc", "fli", "fll", "flx", "flv", "g726", "gsm", "gxf", "iss", "m1v", "m2v", "m2t", "m2ts", "m4a", "m4b", "m4v", "mad", "mj2", "mjpeg", "mjpg", "mka", "mkv", "mlp", "mm", "mmf", "mov", "mp+", "mp1", "mp2", "mp3", "mp4", "mpc", "mpeg", "mpg", "mpga", "mpp", "mpu", "mve", "mvi", "mxf", "nc", "nsv", "nut", "nuv", "oga", "ogm", "ogv", "ogx", "oma", "ogg", "omg", "opus", "psp", "pva", "qcp", "qt", "r3d", "ra", "ram", "rl2", "rm", "rmvb", "roq", "rpl", "rvc", "shn", "smk", "snd", "sol", "son", "spx", "str", "swf", "tak", "tgi", "tgq", "tgv", "thp", "ts", "tsp", "tta", "xa", "xvid", "uv", "uv2", "vb", "vid", "vob", "voc", "vp6", "vmd", "wav", "webm", "wma", "wmv", "wsaud", "wsvga", "wv", "wve", "ay", "gbs", "gym", "hes", "kss", "nsf", "nsfe", "rsn", "sap", "spc", "vgm", "vgz"], "mime_type":["audio\/mpeg", "application\/ogg", "application\/x-ogg", "audio\/ogg", "audio\/vorbis", "audio\/vorbis+ogg", "audio\/x-ogg", "audio\/x-vorbis", "audio\/x-vorbis+ogg", "application\/ogg", "application\/x-ogg", "audio\/ogg", "audio\/x-flac+ogg", "audio\/x-ogg", "application\/flac", "application\/x-flac", "audio\/flac", "audio\/x-flac", "audio\/ogg", "application\/ogg", "audio\/opus", "audio\/wav", "audio\/aiff", "audio\/x-wav", "audio\/x-aiff", "application\/x-dff", "audio\/x-dff", "audio\/x-dsd", "application\/x-dsf", "audio\/x-dsf", "audio\/x-dsd", "audio\/aac", "audio\/aacp", "audio\/x-wavpack", "application\/flv", "application\/m4a", "application\/mp4", "application\/octet-stream", "application\/ogg", "application\/x-ms-wmz", "application\/x-ms-wmd", "application\/x-ogg", "application\/x-shockwave-flash", "application\/x-shorten", "audio\/8svx", "audio\/16sv", "audio\/aac", "audio\/aacp", "audio\/ac3", "audio\/aiff", "audio\/amr", "audio\/basic", "audio\/flac", "audio\/m4a", "audio\/mp4", "audio\/mpeg", "audio\/musepack", "audio\/ogg", "audio\/opus", "audio\/qcelp", "audio\/vorbis", "audio\/vorbis+ogg", "audio\/wav", "audio\/x-8svx", "audio\/x-16sv", "audio\/x-aac", "audio\/x-ac3", "audio\/x-adx", "audio\/x-aiff", "audio\/x-alaw", "audio\/x-au", "audio\/x-dca", "audio\/x-eac3", "audio\/x-flac", "audio\/x-gsm", "audio\/x-mace", "audio\/x-matroska", "audio\/x-monkeys-audio", "audio\/x-mpeg", "audio\/x-ms-wma", "audio\/x-ms-wax", "audio\/x-musepack", "audio\/x-ogg", "audio\/x-vorbis", "audio\/x-vorbis+ogg", "audio\/x-pn-realaudio", "audio\/x-pn-multirate-realaudio", "audio\/x-speex", "audio\/x-tta", "audio\/x-voc", "audio\/x-wav", "audio\/x-wma", "audio\/x-wv", "video\/anim", "video\/quicktime", "video\/msvideo", "video\/ogg", "video\/theora", "video\/webm", "video\/x-dv", "video\/x-flv", "video\/x-matroska", "video\/x-mjpeg", "video\/x-mpeg", "video\/x-ms-asf", "video\/x-msvideo", "video\/x-ms-wmv", "video\/x-ms-wvx", "video\/x-ms-wm", "video\/x-ms-wmx", "video\/x-nut", "video\/x-pva", "video\/x-theora", "video\/x-vid", "video\/x-wmv", "video\/x-xvid", "audio\/x-mpd-ffmpeg", "audio\/L16", "audio\/L24", "audio\/x-mpd-float", "audio\/x-mpd-cdda-pcm", "audio\/x-mpd-cdda-pcm-reverse", "audio\/x-mpd-alsa-pcm"]}
* **CONFIG**
  * **click_event** click
  * **use_mouse_interface** true
  * **use_touch_interface** false
  * **has_custom_scrollbars** true
  * **rgbs** {"r":1, "g":1, "b":1, "a":"0.75"}
  * **interface_language** en-GB
  * **music_directory_albumart** 
  * **sortbycomposer** false
  * **composergenrename** ["Classical"]
  * **useclassicalrules** false
  * **classical_rule** 1
  * **classicalgenres** ["Classical"]
  * **classicalfolder** Classical
  * **preferlocalfiles** false
  * **mopidy_collection_folders** ["Local media"]
  * **lastfm_country_code** CH
  * **country_userset** false
  * **debug_enabled** 0
  * **custom_logfile** 
  * **cleanalbumimages** false
  * **do_not_show_prefs** false
  * **clear_update_lock** false
  * **load_plugins_at_loadtime** false
  * **beets_server_location** 
  * **dev_mode** false
  * **live_mode** false
  * **use_mopidy_scan** false
  * **collection_load_timeout** 3600000
  * **smartradio_chunksize** 5
  * **linkchecker_nextrun** 12.10.2024, 16:37:23
  * **link_checker_frequency** 604800
  * **link_checker_is_running** false
  * **audiobook_directory** 
  * **collection_player** mpd
  * **snapcast_server** 
  * **snapcast_port** 1705
  * **snapcast_http** 1780
  * **http_port_for_mopidy** 6680
  * **multihosts** {"Default":{"host":"REDACTED.REDACTED.net", "port":"6600", "password":"", "socket":"", "mopidy_remote":false, "do_consume":false, "websocket":false, "websocket_port":"6680", "radioparams":{"radiomode":"", "radioparam":"", "radioconsume":[], "radiodomains":["local", "spotify", "youtube", "ytmusic", "qobuz"], "toptracks_current":1, "toptracks_total":1, "stationname":""}}}
  * **old_style_sql** false
  * **auto_audiobook** [""]
  * **backend_version** 2.17
  * **spotify_mark_unplayable** false
  * **consume_workaround** false
  * **we_do_consume** false
  * **smartradio_clearfirst** true
  * **collection_type** sqlite
  * **displaycomposer** true
  * **artistsatstart** ["Various Artists", "Soundtracks"]
  * **nosortprefixes** ["The", "Der", "Die", "Das"]
  * **sync_lastfm_playcounts** false
  * **sync_lastfm_at_start** false
  * **next_lastfm_synctime** 1.1.1970, 01:00:00
  * **last_lastfm_synctime** 1728138976
  * **lastfm_sync_frequency** 86400
  * **lfm_importer_start_offset** 0
  * **lfm_importer_last_import** 0
  * **hide_master_volume** false
  * **alarm_ramptime** 30
  * **alarm_snoozetime** 8
  * **communityradioorderby** name
  * **default_podcast_display_mode** 0
  * **default_podcast_refresh_mode** 4
  * **default_podcast_sort_mode** 0
  * **podcast_mark_new_as_unlistened** false
  * **podcast_sort_levels** 4
  * **podcast_sort_0** Title
  * **podcast_sort_1** Artist
  * **podcast_sort_2** Category
  * **podcast_sort_3** new
  * **lastversionchecked** 1.00
  * **lastversionchecktime** 18.7.56732, 16:57:50
  * **use_original_releasedate** false
  * **chartoption** 0
  * **lastfm_user** 
  * **autotagname** 
  * **lastfm_logged_in** false
  * **lastfm_scrobbling** false
  * **noscrobble_audiobook** false
  * **noscrobble_podcast** false
  * **use_mopidy_search** false
  * **sortby_upgraded** true
  * **currenthost** Default
  * **player_backend** mpd
  * **browser_id** 1728127371009
  * **clickmode** double
  * **skin** phone
  * **sortbydate** 1
  * **sort_ab_bydate** 1
  * **notvabydate** false
  * **collectionrange** 0
  * **collectiondomains** All
  * **sortcollectionby** artist
  * **sortresultsby** sameas
  * **actuallysortresultsby** artist
  * **headerbar_position** bottom
  * **alarmindex** null
  * **sleeptime** 30
  * **fadetime** 1
  * **snooze** null
  * **tradsearch** false
  * **lastfm_autocorrect** false
  * **sourceshidden** false
  * **playlisthidden** false
  * **infosource** lastfm
  * **sourceswidthpercent** 25
  * **playlistwidthpercent** 25
  * **downloadart** false
  * **chooser** prefsm
  * **hide_albumlist** false
  * **hide_filelist** false
  * **hide_radiolist** true
  * **hide_podcastslist** true
  * **hide_playlistslist** true
  * **hide_audiobooklist** true
  * **hide_searcher** false
  * **hide_pluginplaylistslist** true
  * **hidebrowser** false
  * **shownupdatewindow** 2.17
  * **scrolltocurrent** false
  * **lastfmlang** interface
  * **synctags** false
  * **synclovevalue** 0
  * **theme** Numismatist.css
  * **icontheme** Bobalophagus-Dark
  * **coversize** 48
  * **fontsize** 11
  * **fontfamily** Nunito.css
  * **displayresultsas** collection
  * **crossfade_duration** 0
  * **newradiocountry** countries/GB
  * **updateeverytime** false
  * **fullbiobydefault** true
  * **mopidy_search_domains** ["local", "spotify"]
  * **outputsvisible** false
  * **wheelscrollspeed** 150
  * **displayremainingtime** true
  * **cdplayermode** false
  * **auto_discovembobulate** false
  * **sleepon** false
  * **sortwishlistby** artist
  * **player_in_titlebar** false
  * **use_albumart_in_playlist** true
  * **bgimgparms** {"dummy":"baby", "Numismatist.css":{"timeout":60000, "lastchange":1728134929305, "random":false, "position":"top left"}}
  * **playlistbuttons_isopen** false
  * **collectionbuttons_isopen** false
  * **abcollectionbuttons_isopen** false
  * **advsearchoptions_isopen** true
  * **podcastbuttons_isopen** false
  * **somafm_quality** highest_available_quality
  * **stupid_rounded_corner_buffer_size** 0
  * **skip_amount** 10
  * **maxrgbs** {"r":150, "g":150, "b":150, "a":"0.75"}
  * **bgdrgbs** {"r":1, "g":1, "b":1, "a":"0"}

fatg3erman / RompR

Large playlist, Large collection: Something went wrong retrieving the playlist! #168