Possible solution to MariaDB issues

kingbu commented 3 years ago

Hi all!

I've been able to solve MARIADB connection errors on Rompr version 1.54 adding the following line to "custom.cnf"

innodb_default_row_format = dynamic \ innodb_file_format=barracuda \ innodb_file_per_table=true \ innodb_large_prefix=true \ innodb_flush_log_at_trx_commit = 0 \ skip-log-bin

It works like a charm now.

Until version 1.48 all were working fine. Issues started upgrading to 1.54 version

Mariadb is running on a docker image on a Pi3B+ ;)

Hope to help someone. Cheers

fatg3erman commented 3 years ago

This is great info thanks. Can you confirm which version of Mariadb you are using, as this makes a difference.

kingbu commented 3 years ago

Of corse...

Here it is

"mysql Ver 15.1 Distrib 10.1.47-MariaDB, for debian-linux-gnueabihf"

I struggled a bit o solve that matter. As I said all worked well till the update to 1.54 version.

Thanks for all Your work anyway, I am a long time rompr user.

uriel1998 commented 3 years ago

How does one add/edit the .cnf file? I'm running into memory errors when updating the database, and it might be something like this? (I also can't seem to get my PHP memory for rompr above 128M, even though the master value is higher, so that might be pointing to another point of user error, but I want to check this first.)

fatg3erman commented 3 years ago

What are the exact errors you're seeing?

uriel1998 commented 3 years ago

[Fri Feb 26 17:34:25.569258 2021] [php7:notice] [pid 1101037] [client 192.168.1.101:33870] PHP Notice: ob_flush(): failed to f lush buffer. No buffer to flush in /var/www/ssxyz/rompr/api/collection/index.php on line 323, referer: https://stevesaus.xyz/ro mpr/ [Fri Feb 26 17:35:05.945577 2021] [php7:error] [pid 1101037] [client 192.168.1.101:33870] PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes) in /var/www/ssxyz/rompr/player/base_mpd_player.class.php on line 344, referer: https://stevesaus.xyz/rompr/

From phpinfo:
(local value, master value) memory_limit | 128M | 512M

BACKEND INFO
- Version 1.55
- mysql_database [Redacted]
- mysql_host [Redacted]
- mysql_password [Redacted]
- mysql_port [Redacted]
- mysql_user [Redacted]
- lastfm_session_key [Redacted]
- spotify_token [Redacted]
- spotify_token_expires [Redacted]
SERVER INFO
- PHP_SELF /rompr/utils/debuginfo.php
- SERVER_ADDR 192.168.1.101
- SERVER_NAME stevesaus.xyz
- SERVER_SOFTWARE Apache/2.4.46 (Debian)
- DOCUMENT_ROOT /var/www/ssxyz/
- HTTP_HOST stevesaus.xyz
- HTTP_USER_AGENT Mozilla/5.0 (X11; Linux x86_64; rv:87.0) Gecko/20100101 Firefox/87.0
- REMOTE_ADDR 192.168.1.101
- SERVER_PORT 80
- REQUEST_URI /rompr/utils/debuginfo.php
COOKIES
- netjukebox_sid wt6IAltEjAfNRfQS3x1m1l9CSwm0ldcDrawuCZCw
- currenthost wombat
- skin desktop
- update_dir /media/_Music/
- collectionrange 0
- sortcollectionby genre
- actuallysortresultsby genre
- sortresultsby sameas
- sortbydate false
- player_backend mpd
PHP INFO
- Version 7.3.27-1~deb10u1
- mbstring 7.3.27-1~deb10u1
- PDO 7.3.27-1~deb10u1
- pdo_mysql 7.3.27-1~deb10u1
- pdo_sqlite 7.3.27-1~deb10u1
- curl 7.3.27-1~deb10u1
- date 7.3.27-1~deb10u1
- fileinfo 7.3.27-1~deb10u1
- json 1.7.0
- SimpleXML 7.3.27-1~deb10u1
- GD 7.3.27-1~deb10u1
- GD Info GD Version=2.2.5, 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, JIS-mapped Japanese Font Support=
- ImageMagick Installed
- date.timezone global_value=, local_value=, access=7
- default_charset global_value=UTF-8, local_value=UTF-8, access=7
- default_socket_timeout global_value=300, local_value=300, access=7
- display_errors global_value=, local_value=, 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=3600, local_value=1800, access=4
- mbstring.language global_value=neutral, local_value=neutral, access=7
- memory_limit global_value=512M, local_value=128M, access=4
- 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=/var/www/html/rompr/prefs/temp, local_value=/var/www/html/rompr/prefs/temp, access=4
PLAYER INFORMATION
- Connection Status Connection Successful
- MPD Interface Version 0.22.4
- tagtype Artist, Album, Title, Track, Name, Genre, Date, Composer, Performer, Comment, Disc, MUSICBRAINZ_ARTISTID, MUSICBRAINZ_ALBUMID, MUSICBRAINZ_ALBUMARTISTID, MUSICBRAINZ_TRACKID, MUSICBRAINZ_RELEASETRACKID
- Commands 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, 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
- URL Handlers alsa://
- URL Handlers cdda://
- URL Handlers ftp://
- URL Handlers ftps://
- URL Handlers gopher://
- URL Handlers hls+http://
- URL Handlers hls+https://
- URL Handlers http://
- URL Handlers https://
- URL Handlers mms://
- URL Handlers mmsh://
- URL Handlers mmst://
- URL Handlers mmsu://
- URL Handlers nfs://
- URL Handlers rtmp://
- URL Handlers rtmpe://
- URL Handlers rtmps://
- URL Handlers rtmpt://
- URL Handlers rtmpte://
- URL Handlers rtmpts://
- URL Handlers rtp://
- URL Handlers rtsp://
- URL Handlers rtsps://
- URL Handlers scp://
- URL Handlers sftp://
- URL Handlers smb://
- URL Handlers srtp://
- plugin mad, mpg123, vorbis, oggflac, flac, opus, sndfile, audiofile, dsdiff, dsf, faad, mpcdec, wavpack, 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, wav, au, aiff, aif, dff, dsf, aac, mpc, wv, 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, 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, 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
- rgbs r=100, g=50, b=1, a=1
- skin desktop
- music_directory_albumart /media/_Music
- sortbycomposer true
- composergenre true
- composergenrename 0=Classical
- preferlocalfiles false
- mopidy_collection_folders 0=Spotify Playlists, 1=Local media, 2=SoundCloud/Liked
- lastfm_country_code US
- country_userset true
- debug_enabled 0
- custom_logfile
- cleanalbumimages true
- do_not_show_prefs 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 1615133295837
- linkchecker_isrunning false
- linkchecker_frequency 604800000
- linkchecker_polltime 5000
- audiobook_directory spoken word
- collection_player mpd
- snapcast_server 192.168.1.104
- snapcast_port 1705
- snapcast_http 1780
- http_port_for_mopidy 6680
- multihosts wombat=[host=192.168.1.104, port=6600, password=4TmUAFS4TQsC, socket=, mopidy_remote=false, radioparams=[radiomode=, radioparam=, radiomaster=, radioconsume=0]], bunyip=[host=192.168.1.105, port=6600, password=4TmUAFS4TQsC, socket=, radioparams=[radiomode=, radioparam=null, radiomaster=, radioconsume=0], mopidy_remote=false], Default=[host=192.168.1.104, port=6600, password=4TmUAFS4TQsC, socket=, mopidy_remote=false, radioparams=[radiomode=, radioparam=, radiomaster=, radioconsume=0]]
- old_style_sql true
- displaycomposer false
- artistsatstart 0=Various Artists, 1=Soundtracks
- nosortprefixes 0=The
- sortcollectionby genre
- sortresultsby sameas
- actuallysortresultsby genre
- sync_lastfm_playcounts true
- sync_lastfm_at_start true
- next_lastfm_synctime 1614532220533
- lastfm_sync_frequency 3600000
- lfm_importer_start_offset 0
- lfm_importer_last_import 1608306990
- hide_master_volume false
- sortbydate false
- notvabydate false
- currenthost wombat
- player_backend mpd
- collectionrange 0
- lastfm_user [Redacted]
- autotagname
- lastfm_logged_in true
- tradsearch false
- lastfm_scrobbling false
- lastfm_autocorrect false
- sourceshidden false
- playlisthidden false
- infosource lyrics
- sourceswidthpercent 25
- playlistwidthpercent 25
- downloadart true
- clickmode double
- chooser albumlist
- hide_albumlist false
- hide_filelist false
- hide_radiolist false
- hide_podcastslist true
- hide_playlistslist false
- hide_audiobooklist false
- hide_searcher false
- hidebrowser false
- shownupdatewindow 1.55
- scrolltocurrent true
- alarm_ramptime 30
- alarm_snoozetime 8
- lastfmlang default
- synctags true
- synclove true
- synclovevalue 5
- theme Darkness.css
- icontheme Modern-Light
- coversize 40-Large.css
- fontsize 04-Grande.css
- fontfamily Alegreya.css
- displayresultsas collection
- crossfade_duration 1
- newradiocountry countries/GB
- search_limit_limitsearch false
- scrobblepercent 50
- updateeverytime false
- fullbiobydefault true
- mopidy_search_domains 0=local, 1=spotify
- mopidy_radio_domains 0=local, 1=spotify
- outputsvisible false
- wheelscrollspeed 150
- searchcollectiononly false
- displayremainingtime true
- cdplayermode false
- auto_discovembobulate false
- sleeptime 30
- sleepon false
- sortwishlistby artist
- player_in_titlebar false
- communityradioorderby name
- browser_id 1596076086577
- playlistswipe true
- default_podcast_display_mode 0
- default_podcast_refresh_mode 4
- default_podcast_sort_mode 0
- podcast_mark_new_as_unlistened false
- use_albumart_in_playlist false
- podcast_sort_levels 4
- podcast_sort_0 Title
- podcast_sort_1 Artist
- podcast_sort_2 Category
- podcast_sort_3 new
- alarms
- lastversionchecked 1.00
- lastversionchecktime 1613997558580
- playlistbuttons_isopen false
- collectionbuttons_isopen true
- advsearchoptions_isopen true
- podcastbuttons_isopen true
- last_cache_clean 1614468544941
- next_podcast_refresh 1616507939113
- use_original_releasedate true
- bgimgparms dummy=baby
- last_lastfm_synctime 1614528620533
- google_api_key
- google_search_engine_id
- user_lang en
- showartistbanners true
- playlistcontrolsvisible false
- collectioncontrolsvisible true
- ratman_sortby Rating
- ratman_showletters false
- advanced_search_open false
- communityradiocountry united kingdom
- communityradiolanguage
- communityradiotag
- communityradiolistby country
- podcastcontrolsvisible true
- mpd_host 192.168.1.104
- mpd_port 6600
- mpd_password 4TmUAFS4TQsC
- unix_socket
- collection_type mysql
- separate_collections false
- test_width 1024
- test_height 1142
- hide_pluginplaylistslist true
- interface_language en-GB
- mopidy_http_port false
- mysql_version 10.3.27-MariaDB-0+deb10u1
- maxrgbs r=255, g=75, b=1, a=1
- bgdrgbs r=0, g=0, b=0, a=0
- radiomode
- radioparam
- radiomaster
- radioconsume 0

fatg3erman commented 3 years ago

Local value of 128MB will override the master value. I'm not in front of a computer now to check, but I think there is a memory_limit in the rompr.cnf Apache config file that you should have created when you installed romor. You can just remove that.

i'm surprised you're hitting that limit though. Do you have a lot of albums that do not have an Album Artist in the tags?

uriel1998 commented 3 years ago

Oh my goodness, looks like a lot of problem exists between chair and keyboard here. The memory limit's in the .conf file for the site, which I didn't look at (or realize that it overrode php.ini or .htaccess commands in the document root). And I'd neglected/not realized how to/bit of both for the proper cnf tweaks for MySQL. After tuning those two, it works just fine.

I do have a bunch of mp3 without Album Artist, for what it's worth.

Thank you for your patience with me being a doofus and for all the work you've done.

fatg3erman commented 3 years ago

No problem, glad it's working.

I'm still surprised you're hitting that memory limit though, I shall have to take a look atthat code gain, so I'm leaving this open.

fatg3erman commented 3 years ago

I was right, I did introduce a regression where it could potentially use a huge amount of memory if you have a lot of albums with no Album Artist. That will be fixed in the next release. It might make those albums sort differently - it shouldn't but it might - but versions pre-1.52 would have sorted them that way too.

fatg3erman / RompR

Possible solution to MariaDB issues #110