Configuring new front end; database connect question

Have a MythTV related problem? Ask for help from other MythTV users here.

Moderator: Forum Moderators

Post Reply
StefanJ
Junior
Posts: 27
Joined: Wed Oct 08, 2014 11:38 pm
United States of America

Configuring new front end; database connect question

Post by StefanJ »

I have combined frontend / backend in good working order.

I installed mythtv on a modest PC in my spare room; I'd like to use it as a frontend.

When I start mythfrontend it cannot find the database.

I entered the connect information, which was saved in ~/.mythtv/config.xml:

<Database>
<PingHost>1</PingHost>
<Host>192.168.0.xxx</Host>
<UserName>mythtv</UserName>
<Password>mythtv</Password>
<DatabaseName>mythconverg</DatabaseName>
<Port>3306</Port>
</Database>

I can ping the backend host system.

My question is the port. On the backend, the port in it's ~/.mythtv/config.xml shows port 0.

Is this for "internal" connections, where 3306 is a default for external connections? And where would that be set?

Do I need to stop my back end and reconfigure the database connect information (to set the port to 3306) and restart mariadb?

Sorry if this seems obvious; I really don't want to muss things up.


Thanks

Stefan
StefanJ
Junior
Posts: 27
Joined: Wed Oct 08, 2014 11:38 pm
United States of America

Re: Configuring new front end; database connect question

Post by StefanJ »

I found another thread which suggested a solution:

viewtopic.php?f=36&t=4198

I shut down the front end, used mythtvsetup to change the hostname to the static IP address, and restarted.

The combined front end / back end works, but the remote front end still can't connect.

I tried using mysql to connect from the remote front end host:

mysql -umythtv -h192.168.0.xxx -p mythconverg

It returned "ERROR 1045 (28000): Access denied"

Any suggestions?
User avatar
bill6502
Developer
Posts: 2299
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Configuring new front end; database connect question

Post by bill6502 »

Is that the full error message? I'd expect a user name.

To be clear, you restarted mysql then mythtv-backend.

Perhaps this will help add a remote user: viewtopic.php?f=36&t=4737&p=23252#p23252
StefanJ
Junior
Posts: 27
Joined: Wed Oct 08, 2014 11:38 pm
United States of America

Re: Configuring new front end; database connect question

Post by StefanJ »

Thanks, Bill.

I stopped mythtv-backend, stopped and restarted mysql, restarted the backend.

I confirmed that I could schedule new programs so I guess the internal connection is working.

This is what I get when I try to correct from other systems:

[stefan@linuxgame ~]$ mysql -umythtv -h192.168.0.xxx -p mythconverg
Enter password:
ERROR 1045 (28000): Access denied for user 'mythtv'@'192.168.0.yyy' (using password: YES)

I'll check out the article on adding a remote user.
StefanJ
Junior
Posts: 27
Joined: Wed Oct 08, 2014 11:38 pm
United States of America

Re: Configuring new front end; database connect question

Post by StefanJ »

OK. Found some interesting stuff.

I took a break to update the combined BE/FE to Fedora 35. Checked to see that MythTV was operational. Found that the front end couldn't connect to the database! I used the settings in front end to change the dotted quad IP address (of the host computer) back to 'Localhost'. The front end was able connect, and mythtv was operational. (I could watch TV, make recordings, etc.)

Since I had a console up on the BE/FE, I thought I'd try connecting to the database via mysql:

[stefan@linuxgame ~]$ mysql -umythtv -h192.168.0.xxx -p mythconverg
Enter password:
ERROR 1045 (28000): Access denied for user 'mythtv'@'192.168.0.xxx' (using password: YES)

Hmmm! But when I used "-hLocalhost", mysql was able to connect!

Again, this was on the host running the combined back end / front end.

I tried adding the port on the mysql command line as well.

I installed firewall-config and checked the "mysql" box. TBH, I'm not conversant with using the firewall tool, and I'm not sure if I did it right. In any case, I couldn't connect to the BE/FE host database from my main workstation.

Any help appreciated.
Last edited by StefanJ on Sun Jan 16, 2022 12:18 am, edited 1 time in total.
User avatar
bill6502
Developer
Posts: 2299
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Configuring new front end; database connect question

Post by bill6502 »

When logged in to mysql as user mythtv (and localhost), type: SHOW GRANTS;
StefanJ
Junior
Posts: 27
Joined: Wed Oct 08, 2014 11:38 pm
United States of America

Re: Configuring new front end; database connect question

Post by StefanJ »

Thanks, Bill!

Code: Select all

MariaDB [mythconverg]> show grants;
+---------------------------------------------------------------------------------------------------------------+
| Grants for mythtv@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mythtv`@`localhost` IDENTIFIED BY PASSWORD '*CC8F35F587CA5A556B4132C2407E556D92172FFC' |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO `mythtv`@`localhost`                                               |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
Doesn't look like the database is allowing outside connections on this level.

The official docs for configuring a remote frontend don't go into this; they may need updating.

I also checked for what mysql was doing, listener-wise. Not seeing port 3306.

Code: Select all

stefan@hp-fedora etc]$ sudo netstat -l -n -p | grep mysql
[sudo] password for stefan: 
unix  2      [ ACC ]     STREAM     LISTENING     23340    995/mariadbd         /var/lib/mysql/mysql.sock
User avatar
bill6502
Developer
Posts: 2299
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Configuring new front end; database connect question

Post by bill6502 »

Login to mysql as the root user, you'll need to do that if you want to add a remote user.
Then do SHOW GRANTS like this (my output added):

Code: Select all

mysql> SHOW GRANTS FOR mythtv;
+---------------------------------------------------------+
| Grants for mythtv@%                                     |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO `mythtv`@`%`                      |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO `mythtv`@`%` |
+---------------------------------------------------------+
viewtopic.php?f=36&t=4737#p23252 has the
commands valid for mysql v8, which were tested on an Ubuntu MariaDB
host. Change localhost to whatever you want, like an IP or 192.168.1.%

You may have a mc.sql file on your distribution that has the commands
required.

To answer your question about having 0 as a port number in config.xml,
that's OK because internally, MythTV converts it to 3306.

I like this for checking if the server is running: nmap -p3306 --reason localhost
StefanJ
Junior
Posts: 27
Joined: Wed Oct 08, 2014 11:38 pm
United States of America

Re: Configuring new front end; database connect question

Post by StefanJ »

Thanks for the fast reply! Results later.


Where can I find "mc.sql?" Is it part of mythtv? Searching repositories doesn't find mention of it.

(Oh, found a location in another thread: /usr/share/mythtv/sql/mc.sql. I'll try it after morning routines are done.)

(I got the mythtv package from rpmfusion-free.)
StefanJ
Junior
Posts: 27
Joined: Wed Oct 08, 2014 11:38 pm
United States of America

Re: Configuring new front end; database connect question

Post by StefanJ »

Well, this is interesting.

I started mysql with sudo, then:

Code: Select all

MariaDB [mythconverg]> SHOW GRANTS FOR mythtv;
ERROR 1044 (42000): Access denied for user 'mythtv'@'localhost' to database 'mysql'
But:

Code: Select all

MariaDB [mythconverg]> show grants;
+---------------------------------------------------------------------------------------------------------------+
| Grants for mythtv@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mythtv`@`localhost` IDENTIFIED BY PASSWORD '*CC8F35F587CA5A556B4132C2407E556D92172FFC' |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO `mythtv`@`localhost`                                               |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
This worked better:

Code: Select all

MariaDB [mythconverg]> show grants for mythtv@localhost;
+---------------------------------------------------------------------------------------------------------------+
| Grants for mythtv@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mythtv`@`localhost` IDENTIFIED BY PASSWORD '*CC8F35F587CA5A556B4132C2407E556D92172FFC' |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO `mythtv`@`localhost`                                               |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

I found mc.sql in /usr/share/doc/mythtv-docs/database. Is this the kind of edit you'd suggest?

Code: Select all

CREATE DATABASE IF NOT EXISTS mythconverg;
CREATE USER IF NOT EXISTS 'mythtv'@'localhost' IDENTIFIED WITH mysql_native_password;
ALTER USER 'mythtv'@'192.168.0.%' IDENTIFIED BY 'mythtv';
GRANT ALL ON mythconverg.* TO mythtv@192.168.0.%;
FLUSH PRIVILEGES;
GRANT CREATE TEMPORARY TABLES ON mythconverg.* TO mythtv@192.168.0.%;
FLUSH PRIVILEGES;
ALTER DATABASE mythconverg DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Output of nmap:

Code: Select all

[stefan@hp-fedora ~]$  nmap -p3306 --reason localhost
Starting Nmap 7.91 ( https://nmap.org ) at 2022-01-16 10:59 PST
Nmap scan report for localhost (127.0.0.1)
Host is up, received conn-refused (0.00035s latency).
Other addresses for localhost (not scanned): ::1

PORT     STATE SERVICE REASON
3306/tcp open  mysql   syn-ack

Nmap done: 1 IP address (1 host up) scanned in 0.05 seconds
Thanks again,

stefan
User avatar
bill6502
Developer
Posts: 2299
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Configuring new front end; database connect question

Post by bill6502 »

I don't know what command line you used: sudo mysql --user=root... It looks like the mysql user was mythtv.

Some distributions have SQL configured to allow: sudo mysql mysql

The SQL in /usr/share/doc/mythtv-docs/database is up to date. Works for MariaDB and MySQL v8.
StefanJ
Junior
Posts: 27
Joined: Wed Oct 08, 2014 11:38 pm
United States of America

Re: Configuring new front end; database connect question

Post by StefanJ »

Ahh, user root, not just using sudo. I logged into mysql as user root this time:

Code: Select all

[stefan@hp-fedora ~]$ sudo  mysql -uroot -hLocalhost -p mythconverg
[sudo] password for stefan: 
Enter password: 

SNIP

MariaDB [mythconverg]> show grants for mythtv;
+-------------------------------------------------------------------------------------------------------+
| Grants for mythtv@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mythtv`@`%` IDENTIFIED BY PASSWORD '*7A1099EACF54D15948AF9429999D3448207BE68F' |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO `mythtv`@`%`                                               |
+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
It looks like it should allow incoming, not just local host, if I understand `mythtv`@`%` correctly.

Now, is the next goal here to create a NEW user, 'mythtv'@'192.168.0.%', using these lines?

Code: Select all

CREATE USER IF NOT EXISTS 'mythtv'@'192.168.0.%' IDENTIFIED WITH mysql_native_password;
ALTER USER 'mythtv'@'192.168.0.%' IDENTIFIED BY 'mythtv';
GRANT ALL ON mythconverg.* TO mythtv@192.168.0.%;
FLUSH PRIVILEGES;
GRANT CREATE TEMPORARY TABLES ON mythconverg.* TO mythtv@192.168.0.%;
FLUSH PRIVILEGES;
I don't want to mess up the existing user so I want to be sure I'm doing this correctly. It has been about 15 years since I used SQL extensively. (Oddly enough, while on the development group for Oracle's video server.)
User avatar
bill6502
Developer
Posts: 2299
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Configuring new front end; database connect question

Post by bill6502 »

I'd do the above. If you want to try it 1st, substitute some other name, like 'justtesting'@'192. ...'
and you can do DROP USER 'justtesting'@'192. ...'
StefanJ
Junior
Posts: 27
Joined: Wed Oct 08, 2014 11:38 pm
United States of America

SOLVED Re: Configuring new front end; database connect question

Post by StefanJ »

Logged into mysql as root, I created a new user and checked grants:

Code: Select all

MariaDB [mythconverg]> CREATE USER IF NOT EXISTS 'testuser'@'192.168.0.%' IDENTI
FIED WITH mysql_native_password;
Query OK, 0 rows affected (0.250 sec)

MariaDB [mythconverg]> ALTER USER 'testuser'@'192.168.0.%' IDENTIFIED BY 'mythtv';
Query OK, 0 rows affected (0.019 sec)

MariaDB [mythconverg]> GRANT ALL ON mythconverg.* TO 'testuser'@'192.168.0.%';
Query OK, 0 rows affected (0.029 sec)

MariaDB [mythconverg]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

MariaDB [mythconverg]> GRANT CREATE TEMPORARY TABLES ON mythconverg.* TO 'testuser'@'192.168.0.%';
Query OK, 0 rows affected (0.024 sec)

MariaDB [mythconverg]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

MariaDB [mythconverg]> show grants for 'testuser'@'192.168.0.%';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for testuser@192.168.0.%                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `testuser`@`192.168.0.%` IDENTIFIED BY PASSWORD '*CC8F35F587CA5A556B4132C2407E556D92172FFC' |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO `testuser`@`192.168.0.%`                                               |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)
AND I was able to log into mysql using "testuser" from both the myth BE/FE host, and my primary workstation.

Now, to create mythtv@192.168.0.%:

(also logged in as root)

Code: Select all

MariaDB [mythconverg]> CREATE USER IF NOT EXISTS 'mythtv'@'192.168.0.%' IDENTIFIED WITH 'mythtv';
ERROR 1524 (HY000): Plugin 'mythtv' is not loaded
MariaDB [mythconverg]> CREATE USER IF NOT EXISTS 'mythtv'@'192.168.0.%' IDENTIFIED WITH  mysql_native_password;
Query OK, 0 rows affected (0.033 sec)
Based on the other thread whre the fellow is having trouble with mc.sql, I have a bad feeling about the above . . . but I continued creating the user. This looks promising at least:

Code: Select all

MariaDB [mythconverg]> show grants for 'mythtv'@'192.168.0.%';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for mythtv@192.168.0.%                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mythtv`@`192.168.0.%` IDENTIFIED BY PASSWORD '*CC8F35F587CA5A556B4132C2407E556D92172FFC' |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO `mythtv`@`192.168.0.%`                                               |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
AND . . . . (drumroll)

I could log into sql using the new user locally and on my main workstation.

And when I ran upstairs to the spare room and started mythfrontend, it immediately found the backend and I was able to stream video.

Mission accomplished. Thank you, Bill!
Post Reply