fixes/31 mythweb on Xubuntu 20.04 LTS Daily Build

[Mike Bibbings]

Built debs locally using mythtv/packaging/deb/ and installed mythtv.

Basics work, channel scanning (HD HomeRun? Quattro) Live TV, Recordings.

mythweb does not work, something to do with mysql 8 in focal.

mythtv user has been created with default of caching_sha2_password

mysql> select host,user,plugin from user where user="mythtv";
+------+--------+-----------------------+
| host | user   | plugin                |
+------+--------+-----------------------+
| %    | mythtv | caching_sha2_password |
+------+--------+-----------------------+
1 row in set (0.00 sec)

Confirmed my suspicion by manually changing user mythtv in mysql

ALTER USER 'mythtv'@'%' IDENTIFIED WITH mysql_native_password BY 'password from /etc/mythtv/config.xml';

and rebooting, after which mythweb works.

I believe mc.sql (in mythtv) and mythtv-database.postinst need changing for mysql 8 to make sure mysql_native_password is used, alternatively fix mythweb so it works with caching_sha2_password or mysql_native_password (I have no idea on how to do this!).

Note: trac needs updating to allow Version v31-fixes to be selected.

Mike

[Bill Meek <billmeek@…>]

In beb8a43e78/mythtv:

Database: choose mysql_native_password

In mysql version 8.x (*buntu 20.04), mythweb fails because
the PHP mysqli connector doesn't support the new default
caching_sha2_authentication. Existing DB users work OK,
but newly created ones won't.

Somewhat ugly solution as MySQL 5.x/8.x and MariaDB CREATE USER
syntax is slightly different. Use ALTER USER after creation.

See: ​https://mysqlserverteam.com/upgrading-to-mysql-8-0-default-authentication-plugin-considerations/

​https://mariadb.com/kb/en/create-user/#identified-viawith-authentication_plugin

Tested on MariaDB 10.3 and mysql 5.7 and 8.0.

Closes #13576

(cherry picked from commit 6d43d669d07ae0ea46b78ac74c569c525ddce533)

[Bill Meek <billmeek@…>]

In 6d43d669d0/mythtv:

Database: choose mysql_native_password

In mysql version 8.x (*buntu 20.04), mythweb fails because
the PHP mysqli connector doesn't support the new default
caching_sha2_authentication. Existing DB users work OK,
but newly created ones won't.

Somewhat ugly solution as MySQL 5.x/8.x and MariaDB CREATE USER
syntax is slightly different. Use ALTER USER after creation.

See: ​https://mysqlserverteam.com/upgrading-to-mysql-8-0-default-authentication-plugin-considerations/

​https://mariadb.com/kb/en/create-user/#identified-viawith-authentication_plugin

Tested on MariaDB 10.3 and mysql 5.7 and 8.0.

Closes #13576

[Bill Meek <billmeek@…>]

In 12706dac9/mythtv:

Database: choose mysql_native_password

Fix fc3e6a8b686dde755594638a0e06457ca6ef328a

Refs: #13576

[Bill Meek <billmeek@…>]

In a51f21ab23/mythtv:

Database: choose mysql_native_password

Fix fc3e6a8b686dde755594638a0e06457ca6ef328a

Refs: #13576
(cherry picked from commit 12706dac98a6ffe1b256bed0f67d8bee6f377e61)