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

[John Pilkington]

On 05/07/2020 16:42, Bill Meek wrote:
> 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:  Many thanks for those details.  It's all much clearer to me now, 
and it looks as if the system is working again.  Bookmarked!

I think that when I re-used mc.sql after the Fedora 31 upgrade I 
probably omitted the final 'mysql' in the 'root' command line.  But I 
decided to try that reset because things weren't working...

This time I copied in mc.sql line-by-line and followed it by the segment 
above, but replacing % by 68 to limit the likelihood of interference 
with other systems.  Then 'systemctl restart mariadb'.

mythtv-setup didn't crash, previews are being generated again, and I'm 
hopeful.  The privileges granted are listed in more detail than in your 
example and my other systems, though.

Cheers,

John