[mythtv-users] MariaDB 10.3.22 Database access with Master and Fedora 31

[Bill Meek]

On 7/5/20 2:34 AM, John Pilkington wrote:
> On 04/07/2020 20:01, Bill Meek wrote:
>> On 7/4/20 10:40 AM, John Pilkington wrote:
>>> On 04/07/2020 15:26, Bill Meek wrote:
>>>
>>>> I <think> you're expecting the last two commands to work, but there are
>>>> no such users.
>>>>
>>>> I'd check to see if the wildcard is working with:
>>>>
>>>>    mysql --user=mythtv --host=192.168.1.68 --password mythconverg
>>>>
>>>> For everything except MariaDB versions below v10.3, I expect this to
>>>> work, including things other than localhost:
>>>>
>>>> CREATE USER IF NOT EXISTS 'mythtv'@'localhost' IDENTIFIED WITH mysql_native_password;
>>>> ALTER USER 'mythtv'@'localhost' IDENTIFIED BY 'mythtv';
>>>> GRANT ALL ON mythconverg.* TO 'mythtv'@'localhost';
>>>
>>> That is a part of the current master mc.sql that I used after the
>>> F30-to-F31 upgrade.
>>>
>>> I haven't made any later changes.  Before the upgrade the subnet number was 66.  Somehow that changed to 68.
>>>
>>> Your suggested test gives me
>>>
>>> Access denied for user 'mythtv'@'HPFed.home' (using password=YES)
>>
>> I'd get the mysql... test working first, since if that fails, MythTV will.
>> May need to go after config.xml issues if it's using IPs rather than
>> hostnames.
>>
>> I did omit the FLUSH PRIVILEGES; command from the above.
>>
>> I've got IPv6 working (since MythTV 0.25) and had to add more than
>> the IPv4 solution. I missed the HPFed.home reference before, but I'll
>> guess that it's one of the  IP's that changed. So, I'll assume it
>> was changed in /etc/hosts (or however you resolve things.)
>>
>> You could reserve the old IPv4 address in your router etc., but that's
>> another thread.
>>
>> Here's mine (which works):
>>
>> MariaDB [mysql]> SELECT User,Host FROM user WHERE User='mythtv' ORDER BY User,Host;
>> +--------+--------------------+
>> | User   | Host               |
>> +--------+--------------------+
>> | mythtv | 192.168.1.%        |
>> | mythtv | fdf9:db8:db8:1::%  |
>> | mythtv | localhost          |
>> +--------+--------------------+
>>
> 
> I'm afraid these posts had gone off-list.

My mistake.

I'm puzzled.  The command above doesn't appear to be looking at mythconverg,
> and my users table contains only 'admin'

The user table in mythconverg is for MythTV sessions, doesn't apply here.

The user table in the mysql database is in the dump above, you can see between []s.
Full commands below.

> I don't know how the hostname HPFed gained the .home suffix, but none of  HPFed, HPFed.home or hp_fed1 can get DB access.  Only localhost seems 
> to work, but isn't active on frontend start.

If 192.168.1.68 is where the DB is, then you must get this to work
1st. No need to try the frontend yet.

mysql --user=mythtv --host=192.168.1.68 --password mythconverg

> Stephen's suggestion last month of "select * from settings where data like '%192.168%' ; " brings up only 3 lines;  3 instances of 192.168.1.68 
> of which one is NULL and the other 2 are the 'profile' hostname hp_fed1

Address this after DB access is fixed, if required.

> /etc/hosts has only 127.0.0.1 as localhost and ::1 as localhost6

But what does: grep HPFed /etc/hosts show. A tool like: host 192.168.1.68
may help. Or, avahi-discover (bit of a long shot).

Here's the entire set of commands:

$ mysql --user=root --password --host=localhost mysql
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 153
Server version: 10.3.22-MariaDB-1ubuntu1 Ubuntu 20.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

CREATE USER IF NOT EXISTS 'mythtv'@'192.168.1.%' IDENTIFIED WITH mysql_native_password;
ALTER USER 'mythtv'@'192.168.1.%' IDENTIFIED BY 'mythtv';
GRANT ALL ON mythconverg.* TO 'mythtv'@'192.168.1.%';
FLUSH PRIVILEGES;

MariaDB [mysql]> SHOW GRANTS FOR 'mythtv'@'192.168.1.%';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for mythtv at 192.168.1.%                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mythtv'@'192.168.1.%' IDENTIFIED BY PASSWORD '*FC406FC209950AB64E721B1AD7649F8EF7852789' |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO 'mythtv'@'192.168.1.%'                                               |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [mysql]> SELECT User,Host FROM user WHERE User='mythtv' ORDER BY User,Host;
                  ^^^ only if you want to look, output in my last post

-- 
Bill