[mythtv-users] Can't connect to remote backend database (again)

[Stephen Worthington]

On Wed, 25 Aug 2021 22:30:33 +1000, you wrote:

>> What that suggests is that the mythfrontend loaded on the CGTV was
>>> compiled using an out of date version of the MySQL client library.  It
>>> is possible that the MySQL server may have an option to accept older
>>> authorisation methods, and setting such an option might fix the
>>> problem.  But I think it would be better to use an up to date version
>>> of mythfrontend.
>>>
>>
>> Wow, great detective work. I'll see if there's another version somewhere I
>> can get hold of. Although I think I got my APK from a link on the wiki so
>> it should have been ok.
>>
>
>I've installed every version of the app I can get my hands on and still
>can't connect, so I guess they must all have the same issue.
>
>Could you please advise how I can change the MySQL settings to accept older
>auth methods?

It looks like you need to do this to the MySQL server config:

[mysqld]
default_authentication_plugin=mysql_native_password

See here:

https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password

I am running MariaDB these days, so I can not test that setting, but I
think all you need to do is to put those lines in a new file in
/etc/mysql/conf.d.  Call it "old_auth.cnf" for example.  The file
should be "chown root:root" and "chmod u=rw,g=r,o=r".  Then restart
mysqld:

sudo systemctl restart mysqld

This problem should only have happened if you have installed MySQL
version >=8 and then created users for MythTV.  If you created the
MySQL users for MythTV on an older version of MySQL and then upgraded
MySQL to v8 it should have retained the older auth settings for all
the existing users.

It should also be possible to change the auth setting per user back to
the old method:

ALTER USER <user>
  IDENTIFIED WITH mysql_native_password
  BY '<password>';

You may need to do the ALTER USER command or delete and re-create the
user for the CGTV device even though you have added that config file
as new users created on v8+ get the then current default
authentication setting, which was "caching_sha2_password" at the time
that user was created.