[mythtv-users] mysql mythtv user issue - fixes/31

[Tim Pletcher]

>
> The new line is: ALTER USER 'mythtv'@'localhost' IDENTIFIED WITH
> mysql_native_password;
> (assuming mythtv is the user being created if it doesn't exist and then
> altered.)
>
> Please mention your SQL version.
>
> If you try the above ALTER... does it remove your password. It didn't in
> my tests
> and per the help pages (at least my reading of them.) There is a RETAIN
> CURRENT PASSWORD
> option, but I'm not certain that it works for all versions of MariaDB and
> MySQL. There's
> also mentioned of it applying to a secondary password.
>

Hi Bill,

This is an existing installation carried forward from 0.29 --> 30 --> 31
and the mythtv mysql user has existed for some time across database
restores with server upgrades etc.  I assume the mythtv user was created
during a prior mythbuntu version install as I don't recall setting up
mythtv user in mysql manually in the past.

I logged into mysql and ran the ALTER command you requested but it does not
work with the syntax provided.  See output below:

Server version: 5.7.29-0ubuntu0.18.04.1 (Ubuntu)

mysql> ALTER USER 'mythtv'@'localhost' IDENTIFIED WITH
mysql_native_password;
ERROR 1396 (HY000): Operation ALTER USER failed for 'mythtv'@'localhost'

mysql> select host, user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | mythtv           |
| localhost | debian-sys-maint |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+

mysql> ALTER USER 'mythtv'@'%' IDENTIFIED WITH mysql_native_password;
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

However, after executing these commands, the backend immediately
disconnected from database and I had to reset the mythtv mysql user back to
the original password via ALTER USER ...IDENTIFIED BY to get it to
reconnect as per my original message.

Tim
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.mythtv.org/pipermail/mythtv-users/attachments/20200212/58374dfa/attachment.htm>