[Solved] MySQL user/password question

For discussion of topics specific to MythTV on linux
Post Reply
yonkiman
Newcomer
Posts: 6
Joined: Tue Feb 23, 2016 2:29 am
United States of America

[Solved] MySQL user/password question

Post by yonkiman »

I just installed a fresh copy of Ubuntu 23.04. Downloaded and installed MythTV 0.33, successfully restored my old database with mythconverg_restore.pl, but the front end can't connect to the backend.

The log says:

Code: Select all

QMYSQL: Unable to connect
Database error was:
Access denied for user 'mythtv'@'MediaPC.local' (using password: YES)
My original system's config.xml had

Code: Select all

<UserName>mythtv</UserName>
<Password>mythtv</Password>
But this new install doesn't (yet) have a mythtv user, which I'm guessing is why it can't connect.

Where should I go from here? I'd rather not create a mythtv user if there's some way to make it work with my username, but I'll be happy to get any solutions. Thanks.
User avatar
pgbennett
Developer
Posts: 503
Joined: Mon Apr 27, 2015 5:41 pm
United States of America

Re: MySQL user/password question

Post by pgbennett »

The user id for the database is not the same as the linux user id.

If you successfully restored your database, then you likely do have a database user id set up.

Is the backend running? Is this a remote frontend? If remote, Does the local frontend work?
white_haired_uncle
Senior
Posts: 244
Joined: Thu Feb 23, 2023 8:55 pm
Location: Safe outside my gilded cage
United States of America

Re: MySQL user/password question

Post by white_haired_uncle »

I upgraded from v32 to v33 today, and it hosed up my mysql permissions (without aborting the upgrade, lucky I happened to look at that window just in time to catch the error). I need to post details separately, as I'm not the first to see this, but it would not surprise me if the db/user(s) never got configured properly during the OP's install.

I've never ran mythconverg_restore, always just used mysql directly, so I could be way off base here, but I know restoring with sudo w/o that script would be possible even w/o a mythtv user configured.

Personally, the first thing I'd do is to select user,host from mysql.user and see if 'mythtv'@'MediaPC.local' (and/or 'maybe even 'mythtv'@'%') even exists. But then, I'm no expert.

P.S. The error looked like this. I thought it was just omitting some of the data from the log, but no, it actually did create a user '' with host '%'. Looks to me like it's missing at least "mythtv", "mythtv", and "mythconverg", and I can't say I'm crazy about using '%';

Code: Select all

Setting up mythtv-database (2:33.1+fixes.202302200304.c273ed0f9a~ubuntu22.04.1) ...
mysql 'hidden SECURITY_INFO' --execute=CREATE USER IF NOT EXISTS ''@'%' IDENTIFIED WITH mysql_native_password;             ALTER USER ''@'%' IDENTIFIED BY '';             GRANT ALL ON .* TO ''@'%';  failed, (incorrect admin username/password or syntax?)
Try:
sudo dpkg-reconfigure mythtv-database
yonkiman
Newcomer
Posts: 6
Joined: Tue Feb 23, 2016 2:29 am
United States of America

Re: MySQL user/password question

Post by yonkiman »

white_haired_uncle wrote:
Sun Feb 26, 2023 7:09 am
Personally, the first thing I'd do is to select user,host from mysql.user and see if 'mythtv'@'MediaPC.local' (and/or 'maybe even 'mythtv'@'%') even exists. But then, I'm no expert.
Well you're more expert than me. Saying "I know enough to be dangerous" would be bragging. But thanks for the response.

So, any chance you could talk me through "select user,host from mysql.user and see if 'mythtv'@'MediaPC.local' (and/or 'mythtv'@'%') even exists."? I tried to poke around with a GUI database viewer (dbeaver-ce), but couldn't connect (possibly because of this exact issue.
white_haired_uncle
Senior
Posts: 244
Joined: Thu Feb 23, 2023 8:55 pm
Location: Safe outside my gilded cage
United States of America

Re: MySQL user/password question

Post by white_haired_uncle »

Try this and let us know the results. You want to run this on the database host, which is almost certainly where you installed mythtv/mythbackend:

mythbackend$ sudo mysql --defaults-extra-file=/etc/mysql/debian.cnf
MariaDB [(none)]> select user,host,Grant_priv from mysql.user;
MariaDB [(none)]> exit

You'll probably see "mysql>" instead of "MariaDB [(none)]>", which is totally fine.
yonkiman
Newcomer
Posts: 6
Joined: Tue Feb 23, 2016 2:29 am
United States of America

Re: MySQL user/password question

Post by yonkiman »

OK, tried that and got this:

Code: Select all

mysql> select user,host,Grant_priv from mysql.user;
+------------------+-----------+------------+
| user             | host      | Grant_priv |
+------------------+-----------+------------+
| mythtv           | %         | N          |
| debian-sys-maint | localhost | Y          |
| mysql.infoschema | localhost | N          |
| mysql.session    | localhost | N          |
| mysql.sys        | localhost | N          |
| root             | localhost | Y          |
+------------------+-----------+------------+
6 rows in set (0.04 sec)

mysql> 
When I tried starting mythbackend I still got this error:

Code: Select all

2023-03-02 12:48:56.646756 I  Start up testing connections. DB 192.168.1.100, BE , attempt 6, status dbStarted, Delay: 2000
2023-03-02 12:48:56.693211 E  [DBManager6] Unable to connect to database!
2023-03-02 12:48:56.693218 E  Driver error was [1/1045]:
QMYSQL: Unable to connect
Database error was:
Access denied for user 'mythtv'@'192.168.1.100' (using password: YES)
Also tried with my username instead of mythtv (keeping "mythtv" as password because I don't have a better idea) and got the same results.
white_haired_uncle
Senior
Posts: 244
Joined: Thu Feb 23, 2023 8:55 pm
Location: Safe outside my gilded cage
United States of America

Re: MySQL user/password question

Post by white_haired_uncle »

Okay, that first line tells us there is a mysql user named mythtv which can connect from anywhere. Since we get an error from mysql, it's probably not a firewall issue or something like that, we're talking, but being denied by mysql. Most likely, the problem is with the password.

[Your username will not work, unless it's "mythtv" or "debian-sys-maint" or "root", as we can see from that table. Remember, these are mysql users, NOT linux users].

You say ~/.mythtv/config.xml is missing, so if a password is set for mythtv in mysql, you're trying to connect without it. Probably, you need to run mythtv-setup from the linux user you're trying to run mythbackend from. Assuming you set that up right, you should then be able to connect.

You can also test connectivity outside mythtv with

$ mysql -h 192.168.1.100 -u mythtv -p mythconverg # Enter the mysql user mythtv password, probably mythtv, when prompted.

P.S. You mentioned earlier that you didn't want a mythtv (linux) user. I'm sure this is possible, but it will require some monkeying around with mysql and systemd at a minimum, and will probably cause all sorts of sneaky little problems in future upgrades. IMO, way more trouble than it's worth. You can always set the shell to /sbin/nologin (it probably is already).
User avatar
paulh
Developer
Posts: 909
Joined: Thu Feb 06, 2014 6:09 pm
Great Britain

Re: MySQL user/password question

Post by paulh »

If this was MythTV installed from the Ubuntu packages then the MySQL password is not mythtv it's a random sequence of 8 letters and numbers and would have been saved to config.xml either in /etc/mythtv/config.xml or in the home directory of the mythtv user ~/.mythtv/config.xml.

It sounds like you created the mythconverg database and restored it from a backup which will restore the data OK but it wont create the mysql user, password and permissions. It's up to you to create and set those yourself for the mythconverg database.
yonkiman
Newcomer
Posts: 6
Joined: Tue Feb 23, 2016 2:29 am
United States of America

Re: MySQL user/password question

Post by yonkiman »

Sorry, I didn't mean to imply that ~/.mythtv/config.xml was missing, I was guessing that the fact that there was no user "mythtv" might be the problem. So I found the correct password to the database (it wasn't 'mythtv' - as paulh suggested it was a random 8 character pw I had stashed away in a backup), and mysql -h 192.168.1.100 -u mythtv -p mythconverg got me in.

And one database upgrade later, everything works! I'm in and can see all my old recordings. Thanks, white_haired_uncle and paulh - I owe you two a beer!

(And I'm not even going to ask why everything's working fine with no linux mythtv user - could have been from one of the several prior mythtv installs I did on my old machine.)
User avatar
paulh
Developer
Posts: 909
Joined: Thu Feb 06, 2014 6:09 pm
Great Britain

Re: MySQL user/password question

Post by paulh »

Glad you got working.

There is no hard and fast rule that says you have to have a mythtv user. I think it just makes lots of things easier for packagers so they know where to find things and can make sure the user that is running things like the master backend has the right permissions like being in the the video user group so it can access it's tuner etc.
Post Reply