search

LibreELEC / LibreELEC.tv

Just enough OS for KODI
http://libreelec.tv
2.28k stars 1.12k forks source link

[BUG] Errors in kodi.log after mariadb add-on update with LE11 #7701

Closed ksooo closed 1 year ago

ksooo commented 1 year ago

Describe the bug

I just updated from LE 9 to 11. I'm using mariadb add-on to host shared database for several Kodi clients. Since the update I have the following errors in Kodi log, everytime Kodi starts.

To Reproduce

Steps to reproduce the behavior:

  1. Install LE 9 + mariadb add-on
  2. Configure at least one kodi client to use the LE hosted maria db as shared music database + put in content
  3. Update LE + mariadb add-on to LE 11
  4. Start a Kodi client that uses the just updated mariadb
  5. See error in kodi.log

Informations

Log file

2023-03-26 14:13:19.390 T:1185038   error <general>: SQL: [MyMusic82] Undefined MySQL error: Code (1728)
                                                   Query: SELECT albumview.*, CASE WHEN albumview.strAlbum LIKE 'The 42317637040R albumview.strAlbum LIKE 'The.42317637040R albumview.strAlbum LIKE 'The_ 0.000000E+00SCAPE '_' THEN SUBSTR(albumview.strAlbum, 5) ELSE albumview.strAlbum END AS titlesortname FROM albumview  WHERE (((CAST(albumview.iTimesPlayed as DECIMAL(6,1)) > 0))) AND (albumview.strReleaseType = 'album') ORDER BY albumview.iTimesPlayed DESC, udfNaturalSortFormat(titlesortname, 8, '.') DESC, albumview.idAlbum DESC LIMIT 15
2023-03-26 14:13:19.391 T:1185038   error <general>: GetAlbumsByWhere () failed
2023-03-26 14:13:19.392 T:1185038   error <general>: GetDirectory - Error getting special://skin/playlists/mostplayed_albums.xsp

Additional context

Root cause is the updated mariadb version in LE 11, which causes some incompatibilities. AFAIK, fix would be easy, just run 'mysql_upgrade' once, but this tool is not provided with the mariadb add-on.

This also happened after a former update and I have a (nowadays no longer applying) Patch laying around, which used back in the days to solve the problem:

diff --git a/packages/addons/service/mariadb/changelog.txt b/packages/addons/service/mariadb/changelog.txt
index 96f7c6f28add..2de6b78055d3 100644
--- a/packages/addons/service/mariadb/changelog.txt
+++ b/packages/addons/service/mariadb/changelog.txt
@@ -1,3 +1,6 @@
+105
+- include mysql_upgrade and mysqlcheck
+
 104
 - update MariaDB to 10.4.17

diff --git a/packages/addons/service/mariadb/package.mk b/packages/addons/service/mariadb/package.mk
index 00a85b3eaabd..2838aa2d9a94 100644
--- a/packages/addons/service/mariadb/package.mk
+++ b/packages/addons/service/mariadb/package.mk
@@ -3,11 +3,12 @@

 PKG_NAME="mariadb"
 PKG_VERSION="10.4.17"
-PKG_REV="104"
+PKG_REV="105"
 PKG_SHA256="a7b104e264311cd46524ae546ff0c5107978373e4a01cf7fd8a241454548d16e"
 PKG_LICENSE="GPL2"
 PKG_SITE="https://mariadb.org"
-PKG_URL="https://downloads.mariadb.org/interstitial/${PKG_NAME}-${PKG_VERSION}/source/${PKG_NAME}-${PKG_VERSION}.tar.gz"
+#PKG_URL="https://downloads.mariadb.org/interstitial/${PKG_NAME}-${PKG_VERSION}/source/${PKG_NAME}-${PKG_VERSION}.tar.gz"
+PKG_URL="https://archive.mariadb.org//mariadb-10.4.17/source/mariadb-10.4.17.tar.gz"
 PKG_DEPENDS_HOST="toolchain:host ncurses:host"
 PKG_DEPENDS_TARGET="toolchain binutils bzip2 libaio libxml2 lzo ncurses openssl systemd zlib mariadb:host"
 PKG_SHORTDESC="MariaDB is a community-developed fork of the MySQL."
@@ -84,9 +85,11 @@ addon() {

   cp ${MARIADB}/bin/mysql \
      ${MARIADB}/bin/mysqld \
+     ${MARIADB}/bin/mysqlcheck \
      ${MARIADB}/bin/mysqladmin \
      ${MARIADB}/bin/mysqldump \
      ${MARIADB}/bin/mysql_secure_installation \
+     ${MARIADB}/bin/mysql_upgrade \
      ${MARIADB}/bin/my_print_defaults \
      ${MARIADB}/bin/resolveip \
      ${MARIADB}/scripts/mysql_install_db \

Note: mysql_upgrade calls mysqlcheck, so we need both tools.

ksooo commented 1 year ago

I now built LE incl the maridb add-on myself and grepped mariadb-upgrade and mariadb-check binaries from the build, copied them over to my LE box:

TV-Server:~/.kodi/addons/service.mariadb/bin # ls -la
total 55116
drwxr-xr-x    2 root     root          4096 Mar 27 08:22 .
drwxr-xr-x    8 root     root          4096 Mar 25 12:11 ..
lrwxrwxrwx    1 root     root             5 Mar 27 08:22 mariadb -> mysql
-rwxr-xr-x    1 root     root       4978640 Mar 27 00:04 mariadb-check
-rwxr-xr-x    1 root     root       5058288 Mar 27 08:21 mariadb-upgrade
-rwxr-xr-x    1 root     root          1854 Mar 25 12:11 mariadb.start
-rwxr-xr-x    1 root     root           149 Mar 25 12:11 mariadb.stop
-rwxr-xr-x    1 root     root      22252272 Mar 25 12:11 mariadbd
-rwxr-xr-x    1 root     root       4474240 Mar 25 12:11 my_print_defaults
-rwxr-xr-x    1 root     root       5427056 Mar 25 12:11 mysql
-rwxr-xr-x    1 root     root         22495 Mar 25 12:11 mysql_install_db
-rwxr-xr-x    1 root     root         13699 Mar 25 12:11 mysql_secure_installation
-rwxr-xr-x    1 root     root       4813984 Mar 25 12:11 mysqladmin
-rwxr-xr-x    1 root     root       4890464 Mar 25 12:11 mysqldump
-rwxr-xr-x    1 root     root       4473920 Mar 25 12:11 resolveip

Please note the symlink mariadb -> mysql. mariadb-upgrade calls mariadb.

Running `mariadb-upgrade then fixed the problem! No more MySQL errors in kodi.log on Kodi startup.

TV-Server:~/.kodi/addons/service.mariadb/bin # ln -s mysql mariadb
TV-Server:~/.kodi/addons/service.mariadb/bin # ./mariadb-upgrade -u kodi -p
Enter password: 
Major version upgrade detected from 10.4.17-MariaDB to 10.11.2-MariaDB. Check required!
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.global_priv                                  OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry                         OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
MyMusic82.albumartistview                          OK
MyMusic82.albumview                                OK
MyMusic82.artistview                               OK
MyMusic82.songartistview                           OK
MyMusic82.songview                                 OK
MyVideos119.episode_view                           OK
MyVideos119.movie_view                             OK
MyVideos119.musicvideo_view                        OK
MyVideos119.season_view                            OK
MyVideos119.tvshow_view                            OK
MyVideos119.tvshowcounts                           OK
MyVideos119.tvshowlinkpath_minview                 OK
MyVideos121.episode_view                           OK
MyVideos121.movie_view                             OK
MyVideos121.musicvideo_view                        OK
MyVideos121.season_view                            OK
MyVideos121.tvshow_view                            OK
MyVideos121.tvshowcounts                           OK
MyVideos121.tvshowlinkpath_minview                 OK
mysql.user                                         OK
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Processing databases
MyMusic82
MyMusic82.album                                    OK
MyMusic82.album_artist                             OK
MyMusic82.album_source                             OK
MyMusic82.art                                      OK
MyMusic82.artist                                   OK
MyMusic82.audiobook                                OK
MyMusic82.discography                              OK
MyMusic82.genre                                    OK
MyMusic82.infosetting                              OK
MyMusic82.path                                     OK
MyMusic82.removed_link                             OK
MyMusic82.role                                     OK
MyMusic82.song                                     OK
MyMusic82.song_artist                              OK
MyMusic82.song_genre                               OK
MyMusic82.source                                   OK
MyMusic82.source_path                              OK
MyMusic82.version                                  OK
MyMusic82.versiontagscan                           OK
MyVideos119
MyVideos119.actor                                  OK
MyVideos119.actor_link                             OK
MyVideos119.art                                    OK
MyVideos119.bookmark                               OK
MyVideos119.country                                OK
MyVideos119.country_link                           OK
MyVideos119.director_link                          OK
MyVideos119.episode                                OK
MyVideos119.files                                  OK
MyVideos119.genre                                  OK
MyVideos119.genre_link                             OK
MyVideos119.movie                                  OK
MyVideos119.movielinktvshow                        OK
MyVideos119.musicvideo                             OK
MyVideos119.path                                   OK
MyVideos119.rating                                 OK
MyVideos119.seasons                                OK
MyVideos119.sets                                   OK
MyVideos119.settings                               OK
MyVideos119.stacktimes                             OK
MyVideos119.streamdetails                          OK
MyVideos119.studio                                 OK
MyVideos119.studio_link                            OK
MyVideos119.tag                                    OK
MyVideos119.tag_link                               OK
MyVideos119.tvshow                                 OK
MyVideos119.tvshowlinkpath                         OK
MyVideos119.uniqueid                               OK
MyVideos119.version                                OK
MyVideos119.writer_link                            OK
MyVideos121
MyVideos121.actor                                  OK
MyVideos121.actor_link                             OK
MyVideos121.art                                    OK
MyVideos121.bookmark                               OK
MyVideos121.country                                OK
MyVideos121.country_link                           OK
MyVideos121.director_link                          OK
MyVideos121.episode                                OK
MyVideos121.files                                  OK
MyVideos121.genre                                  OK
MyVideos121.genre_link                             OK
MyVideos121.movie                                  OK
MyVideos121.movielinktvshow                        OK
MyVideos121.musicvideo                             OK
MyVideos121.path                                   OK
MyVideos121.rating                                 OK
MyVideos121.seasons                                OK
MyVideos121.sets                                   OK
MyVideos121.settings                               OK
MyVideos121.stacktimes                             OK
MyVideos121.streamdetails                          OK
MyVideos121.studio                                 OK
MyVideos121.studio_link                            OK
MyVideos121.tag                                    OK
MyVideos121.tag_link                               OK
MyVideos121.tvshow                                 OK
MyVideos121.tvshowlinkpath                         OK
MyVideos121.uniqueid                               OK
MyVideos121.version                                OK
MyVideos121.writer_link                            OK
information_schema
performance_schema
sys
sys.sys_config                                     OK
test
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK
vpeter4 commented 1 year ago

I see mariadb-10.4.17 is old and should probably be bumped too. The question is to which version? 10.4.28 or 10.11.2?

ksooo commented 1 year ago

If I read the output from mariadb-upgrade correctly, LE11 comes with 10.11.2

vpeter4 commented 1 year ago

True. I was looking your patch above where is some old version.

So only mysql_upgrade and mysqlcheck is missing in addon? Then I will send PR.

ksooo commented 1 year ago

And we need a symlink! Read what I wrote. ;-)

ksooo commented 1 year ago

We seem to rename the binaries from mariadb to mysql when packaging. But mariadb-upgrade seems to have a hardcoded call to „mariadb“ binary!

vpeter4 commented 1 year ago

PR send. Did test with commands mariadb-upgrade and mariadb-check.