Opened 4 years ago
Closed 3 years ago
#13577 closed Bug Report - General (Fixed)
fixes/31 mythfrontend interworking with mythbackend on Xubuntu 20.04 Daily build
Reported by: | Mike Bibbings | Owned by: | Bill Meek |
---|---|---|---|
Priority: | minor | Milestone: | 31.1 |
Component: | MythTV - General | Version: | v31-fixes |
Severity: | medium | Keywords: | |
Cc: | Ticket locked: | no |
Description
fixes/31 mythfrontend built from source on Xubuntu 18.04 LTS to mythbackend built from debs on Xubuntu 20.04 Daily build.
The mythfrontend tries to upgrade various schema (mytharchive, mythmusic) and silently fails. mythfrontend actually works in to the remote mythbackend e.g. LiveTV. Xubuntu 18.04 LTS uses mysql 5.7.29 components.
Attached is frontend log which shows sql errors when attempting to upgrade schema. mythbackend is running mysql 8.
mike@mike-GL62-7QF:~$ mythfrontend --version Please attach all output as a file in bug reports. MythTV Version : v31-Pre-1874-g35cb9ed0c5 MythTV Branch : fixes/31 Network Protocol : 91 Library API : 31.20200101-1 QT Version : 5.9.5 Options compiled in: linux profile use_hidesyms using_alsa using_oss using_pulse using_pulseoutput using_backend using_bindings_perl using_bindings_python using_bindings_php using_dvb using_firewire using_frontend using_hdhomerun using_vbox using_ceton using_hdpvr using_ivtv using_joystick_menu using_libcec using_libcrypto using_libdns_sd using_libfftw3 using_libxml2 using_lirc using_mheg using_opengl using_egl using_qtwebkit using_qtscript using_qtdbus using_taglib using_v4l2 using_v4l2prime using_x11 using_libbluray_external using_xrandr using_profiletype using_systemd_notify using_systemd_journal using_drm using_bindings_perl using_bindings_python using_bindings_php using_freetype2 using_mythtranscode using_opengl using_egl using_drm using_vaapi using_nvdec using_vdpau using_ffmpeg_threads using_mheg using_libass using_libxml2
Mike
Attachments (2)
Change History (14)
Changed 4 years ago by
Attachment: | mythfrontend.20200207160948.3691.log added |
---|
comment:1 Changed 4 years ago by
Changed 4 years ago by
Attachment: | mythfrontend.20200207163703.3462.log added |
---|
comment:2 Changed 4 years ago by
fwiw, mysql 8 changes the default character set from latin1 to utf8mb4, and I would not be surprised if "DEFAULT CHARACTER SET default" no longer means what it used to mean.
comment:3 Changed 4 years ago by
A user on mythtv forum has come up with a possible solution (a patch) see https://forum.mythtv.org/viewtopic.php?p=17769#p17769
This changes the sql in dbcheck to be compatible with mysql 8.
I have not tested this and would like someone with mysql expertise to comment, particularly with respect to possible impact of such changes on earlier versions of mysql and mariadb.
Mike
comment:4 Changed 4 years ago by
13637 is a duplicate of this ticket. https://code.mythtv.org/trac/ticket/13637
Can we raise the priority of this ticket? It seems no one with MySQL v8 (std in Ubuntu 20.04 LTS) can apply mythtv upgrades that include DB changes until this is fixed.
comment:5 Changed 4 years ago by
Is there a manual way to update the individual tables in mysql 8? I updated the archiveitems with the correct syntax, but the archivedbversion is checked and of course the old nr. How can that be updated? This is really annoying.. Thanks.
comment:6 Changed 4 years ago by
A quick scan of the patch suggests that it presumes that the DB upgrade has failed (which it will presumably do so only if one already has upgraded to MySQL 8). A further schema change needs to be added in the case that the upgrade has already succeeded if one is going to change the default charset (so it applies to the other cases and produces a consistent result for all various DBs after both updates are applied). I admit I have not tracked the entire set of charset default defaults across the various mysql/mariadb versions. Charset adjustments can get rather tricky to get right.
comment:7 Changed 4 years ago by
This should be classified as major or critical imo.Far from a minor bug imo when none of the plugins work.
comment:8 Changed 4 years ago by
I agree, should be major. I applied the patch file on the source, and compiled the mythtv and plugins, and all worked. Compiling just the plugins does not work. I tried to create a .deb of the working platform to apply on my livingroom platform, but the database schema version was not properly updated so that failed.
But when you want to use the standard Lubuntu or Xubuntu distribution or even add the ppa's, to keep a simple to maintain platform, getting this fixed is important.
comment:9 Changed 4 years ago by
Milestone: | needs_triage → 31.1 |
---|---|
Owner: | set to Bill Meek |
Status: | new → accepted |
comment:11 Changed 4 years ago by
I can confirm the patch/fix in the PPA works as designed. Adding the PPA to the (x/l)ubuntu 20.04 sources and then upgrade 0.31 to at least load the july 16th patch fixes the plugin issues for at least mytharchive and mythmusic (that I tested). Import of old recordings works OK.
Thanks Bill.
comment:12 Changed 3 years ago by
Resolution: | → Fixed |
---|---|
Status: | accepted → closed |
Manually closing this:
Plugins/dbcheck: Replace CHARACTER SET 'default' with 'utf8'
Fix required for MySQL v8 because using 'default' CHARACTER SET results in a "You have an error in your SQL syntax" message.
There are two issues to solve.
- New systems that will execute the DBSchemaVer changes for the mytharchive, mythgame, mythmusic & mythweather plugin tables.
- Existing systems that have up to date DBSchemaVer for the above but need a new version to use the unambiguous character set. So
Note that the 'old' DBSchemaVers were changed in 2/2020 to:
ArchiveDBSchemaVer GameDBSchemaVer MusicDBSchemaVer WeatherDBSchemaVer
Tested with: MySQL 5.7.30 and 8.0.20, MariaDB 10.3.22 and 10.4.13.
Using utf8 is only going to be a temporary solution because MySQL v8 issues this warning when doing the ALTER TABLEs in the fix:
Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
Modified version of: https://forum.mythtv.org/viewtopic.php?p=17769#p17769
To see current character sets:
SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME='mythconverg';
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION FROM INFORMATION_SCHEMA.
TABLES
WHERE TABLE_SCHEMA='mythconverg';
SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,CHARACTER_SET_NAME
FROM INFORMATION_SCHEMA.
COLUMNS
WHERE TABLE_SCHEMA='mythconverg' AND CHARACTER_SET_NAME != 'utf8';
Closes #trac-13577
Done some more testing on Xubuntu 20.04 Daily build. Turns out mythfrontend schema upgrade fails with local mythfrontend.
I installed mythmusic plugin and ran mythfrontend, and schema upgrade silently fails.
Frontend log attached