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)

mythfrontend.20200207160948.3691.log (69.8 KB) - added by Mike Bibbings 4 years ago.
mythfrontend.20200207163703.3462.log (61.3 KB) - added by Mike Bibbings 4 years ago.

Download all attachments as: .zip

Change History (14)

Changed 4 years ago by Mike Bibbings

comment:1 Changed 4 years ago by Mike Bibbings

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

mike@xubuntu2004-focal:~$ mythfrontend --version
Please attach all output as a file in bug reports.
MythTV Version : v31-Pre-1874-g35cb9ed0c5-dirty
MythTV Branch : fixes/31
Network Protocol : 91
Library API : 31.20200101-1
QT Version : 5.12.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 using_libmp3lame

Changed 4 years ago by Mike Bibbings

comment:2 Changed 4 years ago by Gary Buhrmaster

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 Mike Bibbings

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 kensheldon

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 martin-paleis

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 Gary Buhrmaster

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 wesnewell

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 martin-paleis

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 Bill Meek

Milestone: needs_triage31.1
Owner: set to Bill Meek
Status: newaccepted

comment:10 Changed 4 years ago by Bill Meek <billmeek@…>

In 167c8d56e2/mythtv:

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 cases to solve.

  1. New systems that will execute the DBSchemaVer changes for the mytharchive, mythgame, mythmusic & mythweather plugin tables. The origial Trac ticket.
  1. Existing systems that have up to date DBSchemaVer for the above but need a new version to use the unambiguous character set. Not solved here, just to get the the above fixed.

Refs #13577

comment:11 Changed 4 years ago by martin-paleis

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 Bill Meek

Resolution: Fixed
Status: acceptedclosed

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.

  1. New systems that will execute the DBSchemaVer changes for the mytharchive, mythgame, mythmusic & mythweather plugin tables.

  1. 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';

master: aa683ba5b99815a1197c3380de6c3d992d4f651e

v31: 167c8d56e20167a5cc39f1ab8b301313a562b929

Closes #trac-13577

Note: See TracTickets for help on using tickets.