Mailing List Archive

mysql mythtv user issue - fixes/31
I am using fixes/31 branch installed via mythbuntu ppa on Ubuntu 18.04 and
as of the package release yesterday (11-Feb-2020), mythbackend startup
fails with error:

mythbackend[9180]: E CoreContext mythdbcon.cpp:236 (OpenDatabase) Driver
error was [1/1045]:#012QMYSQL: Unable to connect#012Database error
was:#012Access denied for user 'mythtv'@'localhost' (using password: YES)

I was also unable to login to mysql as the mythtv user using the previously
configured password in the mythtv user config.xml file. To fix, I had to
log into the mysql root account and reconfigure the mythtv mysql user
password via:

ALTER USER 'mythtv'@'%' IDENTIFIED BY 'my_prior_password';
FLUSH PRIVILEGES;

After doing the above, the backend would start without issue. However, the
problem recurred when updating to the current mythbuntu package release
today.

I assume this relates to commit beb8843
<https://github.com/MythTV/mythtv/commit/beb8a43e78976b08eca0401ac7fa9830379ce408>in
fixes/31 or 5c632ca
<https://github.com/MythTV/packaging/commit/5c632ca1df84e257e57ab894456ebc25fff2479c>
in the packaging repo. Has anyone else experienced this issue?

Thanks,
Tim
Re: mysql mythtv user issue - fixes/31 [ In reply to ]
On 2/12/20 2:47 PM, Tim Pletcher wrote:
> I am using fixes/31 branch installed via mythbuntu ppa on Ubuntu 18.04 and
> as of the package release yesterday (11-Feb-2020), mythbackend startup
> fails with error:
>
> mythbackend[9180]: E CoreContext mythdbcon.cpp:236 (OpenDatabase) Driver
> error was [1/1045]:#012QMYSQL: Unable to connect#012Database error
> was:#012Access denied for user 'mythtv'@'localhost' (using password: YES)
>
> I was also unable to login to mysql as the mythtv user using the previously
> configured password in the mythtv user config.xml file. To fix, I had to
> log into the mysql root account and reconfigure the mythtv mysql user
> password via:
>
> ALTER USER 'mythtv'@'%' IDENTIFIED BY 'my_prior_password';
> FLUSH PRIVILEGES;
>
> After doing the above, the backend would start without issue. However, the
> problem recurred when updating to the current mythbuntu package release
> today.
>
> I assume this relates to commit beb8843
> <https://github.com/MythTV/mythtv/commit/beb8a43e78976b08eca0401ac7fa9830379ce408>in
> fixes/31 or 5c632ca
> <https://github.com/MythTV/packaging/commit/5c632ca1df84e257e57ab894456ebc25fff2479c>
> in the packaging repo. Has anyone else experienced this issue?

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.

Thanks.

--
Bill
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org
Re: mysql mythtv user issue - fixes/31 [ In reply to ]
>
> 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
Re: mysql mythtv user issue - fixes/31 [ In reply to ]
From the MySQL reference manual for both 5.7 & 8 (link
<https://dev.mysql.com/doc/refman/5.7/en/alter-user.html>), executing ALTER
USER .... IDENTIFIED WITH sets the existing password to expired. I assume
this is the issue at hand.

Tim
Re: mysql mythtv user issue - fixes/31 [ In reply to ]
On 2/12/20 6:11 PM, Tim Pletcher wrote:
> From the MySQL reference manual for both 5.7 & 8 (link
> <https://dev.mysql.com/doc/refman/5.7/en/alter-user.html>), executing ALTER
> USER .... IDENTIFIED WITH sets the existing password to expired. I assume
> this is the issue at hand.

Thanks for testing.

It is. Trying plan D now. My guess is that at some point, it will be necessary
to get the SQL version to adjust for syntax. That's another story.

I'll push this later (works with MariaDB 10.3 and MySQL 5.7 and 8.0):

diff --git a/deb/debian/mythtv-database.postinst b/deb/debian/mythtv-database.postinst
index e04ebc5..fbb6ebc 100644
--- a/deb/debian/mythtv-database.postinst
+++ b/deb/debian/mythtv-database.postinst
@@ -37,8 +37,8 @@ ask_root_pw() {

update_database() {
#Set up privs for mythtv@network
- if ! echo "CREATE USER IF NOT EXISTS '$mythtv_username'@'%' IDENTIFIED BY '$mythtv_password'; \
- ALTER USER '$mythtv_username'@'%' IDENTIFIED WITH mysql_native_password; \
+ if ! echo "CREATE USER IF NOT EXISTS '$mythtv_username'@'%' IDENTIFIED WITH mysql_native_password; \
+ ALTER USER '$mythtv_username'@'%' IDENTIFIED BY '$mythtv_password'; \
GRANT ALL ON $database.* TO '$mythtv_username'@'%';" | \
mysql $SECURITY_INFO "$database" >/dev/null 2>&1; then
fail_database

--
Bill
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org
Re: mysql mythtv user issue - fixes/31 [ In reply to ]
On 12/02/2020 21:41, Tim Pletcher wrote:
> 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             |
> +-----------+------------------+
>

The alter here appears to have failed because you don't have a user
mythtv@localhost, only the user mythtv@'%'

It should fail with a no such user error, but it doesn't.

Regards
Stuart

> 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
>
>
>
> _______________________________________________
> mythtv-users mailing list
> mythtv-users@mythtv.org
> http://lists.mythtv.org/mailman/listinfo/mythtv-users
> http://wiki.mythtv.org/Mailing_List_etiquette
> MythTV Forums: https://forum.mythtv.org
>

_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org