Mailing List Archive

On 7/2/20 11:29 AM, John Pilkington wrote:
> I ran Master under Fedora 30 and was able to use local and remote frontends and DLNA.  During the upgrade to Fedora 31 the network address
> somehow got changed, and I have been struggling ever since with that system.
>
> I'm using locally built rpms from GaryB's script, so some file locations differ from those used in the rpmfusion (or *buntu) builds.  This only
> matters when trying to interpret posts referring to other systems.
>
> Early on I re-ran the DB setup process using /usr/share/doc/mythtv/database/mc.sql, but that appears to have disabled network access to the DB.
> It's all about 'localhost'.  mythtv-setup can't make persistent changes now, and frontend startup is unreliable. The commands suggested in the
> wiki to 'restrict by IP subnet' give a 'mistake in syntax'.
>
> The thread below had possible updates involving ALTER but seemed to fade away without reaching a good conclusion.  Is there a version of mc.sql
> that would work?   Or any additional lines?   MariaDB 10.3.22
>
> https://lists.gt.net/mythtv/users/629679#629679
>
> Thanks,
>
> John P

Hi,

Not sure what the full command is that's not working.

You might cite the Wiki page that isn't working. I found one
https://www.mythtv.org/wiki/Installing_MythTV_on_Fedora that
was last updated in 2017.

Here's the current mc.sql:

https://code.mythtv.org/cgit/mythtv/tree/mythtv/database/mc.sql

There were a couple of threads about the MySQL v8 changes that
are reflected in the new mc.sql, so the issue was solved, but
for *buntu packaging (for MariaDB and MySQL).

--
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

On 02/07/2020 18:33, Bill Meek wrote:
> On 7/2/20 11:29 AM, John Pilkington wrote:
>> I ran Master under Fedora 30 and was able to use local and remote
>> frontends and DLNA.  During the upgrade to Fedora 31 the network
>> address somehow got changed, and I have been struggling ever since
>> with that system.
>>
>> I'm using locally built rpms from GaryB's script, so some file
>> locations differ from those used in the rpmfusion (or *buntu) builds.
>> This only matters when trying to interpret posts referring to other
>> systems.
>>
>> Early on I re-ran the DB setup process using
>> /usr/share/doc/mythtv/database/mc.sql, but that appears to have
>> disabled network access to the DB. It's all about 'localhost'.
>> mythtv-setup can't make persistent changes now, and frontend startup
>> is unreliable. The commands suggested in the wiki to 'restrict by IP
>> subnet' give a 'mistake in syntax'.
>>
>> The thread below had possible updates involving ALTER but seemed to
>> fade away without reaching a good conclusion.  Is there a version of
>> mc.sql that would work?   Or any additional lines?   MariaDB 10.3.22
>>
>> https://lists.gt.net/mythtv/users/629679#629679
>>
>> Thanks,
>>
>> John P
>
> Hi,
>
> Not sure what the full command is that's not working.
>
> You might cite the Wiki page that isn't working. I found one
> https://www.mythtv.org/wiki/Installing_MythTV_on_Fedora that
> was last updated in 2017.

In https://www.mythtv.org/wiki/Database_Setup
===========
The section below gave me the 'mistake in syntax' response

Finally, if you just want to restrict by IP subnet (in this example, the
192.168.1. network):

$ mysql -u root mythconverg

===========
>
> Here's the current mc.sql:
>
>   https://code.mythtv.org/cgit/mythtv/tree/mythtv/database/mc.sql

Yes, that's the one I used, with (IIUC) the equivalents of
mythtv-setup.real, mythbackend and mythfrontend.real.

>
> There were a couple of threads about the MySQL v8 changes that
> are reflected in the new mc.sql, so the issue was solved, but
> for *buntu packaging (for MariaDB and MySQL).
>

_______________________________________________
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

On 7/2/20 1:05 PM, John Pilkington wrote:
> The section below gave me the 'mistake in syntax' response
>
> Finally, if you just want to restrict by IP subnet (in this example, the 192.168.1. network):
>
>     $ mysql -u root mythconverg
>     mysql> create user "mythtv"@"192.168.1.%" identified by "mythtv";
>     mysql> grant all on mythconverg.* to mythtv@"192.168.1.%";
>     mysql> flush privileges;

The following worked for MySQL v5.7 and v8.0 plus MariaDB at least 10.3.iforget,
although initially, it was tested with localhost and is in the mc.sql.

But I just tried it with an IP and it worked fine (that is, no syntax errors.)

CREATE USER IF NOT EXISTS 'mythtv'@'192.168.1.%' IDENTIFIED WITH mysql_native_password;
ALTER USER 'mythtv'@'localhost' IDENTIFIED BY 'mythtv';

--
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

On 7/2/20 4:09 PM, Bill Meek wrote:
> CREATE USER IF NOT EXISTS 'mythtv'@'192.168.1.%' IDENTIFIED WITH mysql_native_password;
> ALTER USER 'mythtv'@'192.168.1.%' IDENTIFIED BY 'mythtv';

I didn't change the ALTER line. Fixed above.

--
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

On 02/07/2020 22:09, Bill Meek wrote:
> On 7/2/20 1:05 PM, John Pilkington wrote:
>> The section below gave me the 'mistake in syntax' response
>>
>> Finally, if you just want to restrict by IP subnet (in this example,
>> the 192.168.1. network):
>>
>>      $ mysql -u root mythconverg
>>      mysql> create user "mythtv"@"192.168.1.%" identified by "mythtv";
>>      mysql> grant all on mythconverg.* to mythtv@"192.168.1.%";
>>      mysql> flush privileges;
>
> The following worked for MySQL v5.7 and v8.0 plus MariaDB at least
> 10.3.iforget,
> although initially, it was tested with localhost and is in the mc.sql.
>
> But I just tried it with an IP and it worked fine (that is, no syntax
> errors.)
>
{{{

CREATE USER IF NOT EXISTS 'mythtv'@'192.168.1.%' IDENTIFIED WITH
mysql_native_password;
ALTER USER 'mythtv'@'192.168.1.%' IDENTIFIED BY 'mythtv';

}}}

Thanks Bill. I may try it with a specific address first, because I want
to be able to run other systems independently. This stuff works for me
for years and it's scary to have to fiddle with it when it stops.

I'm expecting that grant and flush lines will be needed too.

John




_______________________________________________
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

On 7/2/20 5:26 PM, John Pilkington wrote:
> On 02/07/2020 22:09, Bill Meek wrote:
>> On 7/2/20 1:05 PM, John Pilkington wrote:
>>> The section below gave me the 'mistake in syntax' response
>>>
>>> Finally, if you just want to restrict by IP subnet (in this example, the 192.168.1. network):
>>>
>>>      $ mysql -u root mythconverg
>>>      mysql> create user "mythtv"@"192.168.1.%" identified by "mythtv";
>>>      mysql> grant all on mythconverg.* to mythtv@"192.168.1.%";
>>>      mysql> flush privileges;
>>
>> The following worked for MySQL v5.7 and v8.0 plus MariaDB at least 10.3.iforget,
>> although initially, it was tested with localhost and is in the mc.sql.
>>
>> But I just tried it with an IP and it worked fine (that is, no syntax errors.)
>>
> {{{
>
> CREATE USER IF NOT EXISTS 'mythtv'@'192.168.1.%' IDENTIFIED WITH mysql_native_password;
> ALTER USER 'mythtv'@'192.168.1.%' IDENTIFIED BY 'mythtv';
>
> }}}
>
> Thanks Bill.  I may try it with a specific address first, because I want to be able to run other systems independently.  This stuff works for me
> for years and it's scary to have to fiddle with it when it stops.
>
> I'm expecting that grant and flush lines will be needed too.

^^^ Yes.

I expect more of the same as MySQL changes (v8 has already caused issues) and MariaDB
doesn't have the same changes, at least not yet.

<PedPeeve>Too many places with similar information. Makes it tough to find/update all of them.</PetPeeve>

--
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

On 02/07/2020 23:36, Bill Meek wrote:
> On 7/2/20 5:26 PM, John Pilkington wrote:
>> On 02/07/2020 22:09, Bill Meek wrote:
>>> On 7/2/20 1:05 PM, John Pilkington wrote:
>>>> The section below gave me the 'mistake in syntax' response
>>>>
>>>> Finally, if you just want to restrict by IP subnet (in this example,
>>>> the 192.168.1. network):
>>>>
>>>>      $ mysql -u root mythconverg
>>>>      mysql> create user "mythtv"@"192.168.1.%" identified by "mythtv";
>>>>      mysql> grant all on mythconverg.* to mythtv@"192.168.1.%";
>>>>      mysql> flush privileges;
>>>
>>> The following worked for MySQL v5.7 and v8.0 plus MariaDB at least
>>> 10.3.iforget,
>>> although initially, it was tested with localhost and is in the mc.sql.
>>>
>>> But I just tried it with an IP and it worked fine (that is, no syntax
>>> errors.)
>>>
>> {{{
>>
>> CREATE USER IF NOT EXISTS 'mythtv'@'192.168.1.%' IDENTIFIED WITH
>> mysql_native_password;
>> ALTER USER 'mythtv'@'192.168.1.%' IDENTIFIED BY 'mythtv';
>>
>> }}}
>>
>> Thanks Bill.  I may try it with a specific address first, because I
>> want to be able to run other systems independently.  This stuff works
>> for me for years and it's scary to have to fiddle with it when it stops.
>>
>> I'm expecting that grant and flush lines will be needed too.
>
> ^^^ Yes.
>
> I expect more of the same as MySQL changes (v8 has already caused
> issues) and MariaDB
> doesn't have the same changes, at least not yet.
>
> <PedPeeve>Too many places with similar information. Makes it tough to
> find/update all of them.</PetPeeve>
>

Bill: I haven't tried that ALTER on the Fedora box but did it on a
laptop with *buntu PPA 31 fixes (bionic, mythtv-setup.real, mythbackend,
mythtfrontend.real) which is fine as a FE/BE.

The ALTER doesn't work as I expected, though. Looking at the GRANTS,
the % is not recognised as a wildcard. Perhaps the susbstitution
happens later? 133 is the laptop, 68 the F31 box.

{{{
mysql> show grants ;
+-----------------------------------------------------------------+
| Grants for mythtv@localhost |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mythtv'@'localhost' |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO 'mythtv'@'localhost' |
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for mythtv@'localhost' ;
+-----------------------------------------------------------------+
| Grants for mythtv@localhost |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mythtv'@'localhost' |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO 'mythtv'@'localhost' |
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for 'mythtv'@'192.168.1.%' ;
+-------------------------------------------------------------------+
| Grants for mythtv@192.168.1.% |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mythtv'@'192.168.1.%' |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO 'mythtv'@'192.168.1.%' |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for 'mythtv'@'192.168.1.133' ;
ERROR 1141 (42000): There is no such grant defined for user 'mythtv' on
host '192.168.1.133'
mysql> show grants for 'mythtv'@'192.168.1.68' ;
ERROR 1141 (42000): There is no such grant defined for user 'mythtv' on
host '192.168.1.68'
mysql>

}}}



_______________________________________________
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

On 7/4/20 6:01 AM, John Pilkington wrote:
> On 02/07/2020 23:36, Bill Meek wrote:
>> On 7/2/20 5:26 PM, John Pilkington wrote:
>>> On 02/07/2020 22:09, Bill Meek wrote:
>>>> On 7/2/20 1:05 PM, John Pilkington wrote:
>>>>> The section below gave me the 'mistake in syntax' response
>>>>>
>>>>> Finally, if you just want to restrict by IP subnet (in this example, the 192.168.1. network):
>>>>>
>>>>>      $ mysql -u root mythconverg
>>>>>      mysql> create user "mythtv"@"192.168.1.%" identified by "mythtv";
>>>>>      mysql> grant all on mythconverg.* to mythtv@"192.168.1.%";
>>>>>      mysql> flush privileges;
>>>>
>>>> The following worked for MySQL v5.7 and v8.0 plus MariaDB at least 10.3.iforget,
>>>> although initially, it was tested with localhost and is in the mc.sql.
>>>>
>>>> But I just tried it with an IP and it worked fine (that is, no syntax errors.)
>>>>
>>> {{{
>>>
>>> CREATE USER IF NOT EXISTS 'mythtv'@'192.168.1.%' IDENTIFIED WITH mysql_native_password;
>>> ALTER USER 'mythtv'@'192.168.1.%' IDENTIFIED BY 'mythtv';
>>>
>>> }}}
>>>
>>> Thanks Bill.  I may try it with a specific address first, because I want to be able to run other systems independently.  This stuff works for
>>> me for years and it's scary to have to fiddle with it when it stops.
>>>
>>> I'm expecting that grant and flush lines will be needed too.
>>
>> ^^^ Yes.
>>
>> I expect more of the same as MySQL changes (v8 has already caused issues) and MariaDB
>> doesn't have the same changes, at least not yet.
>>
>> <PedPeeve>Too many places with similar information. Makes it tough to find/update all of them.</PetPeeve>
>>
>
> Bill:  I haven't tried that ALTER on the Fedora box but did it on a laptop with *buntu PPA 31 fixes (bionic, mythtv-setup.real, mythbackend,
> mythtfrontend.real) which is fine as a FE/BE.
>
> The ALTER doesn't work as I expected, though.  Looking at the GRANTS, the % is not recognised as a wildcard.  Perhaps the susbstitution happens
> later?  133 is the laptop, 68 the F31 box.
>
> {{{
> mysql> show grants ;
> +-----------------------------------------------------------------+
> | Grants for mythtv@localhost                                     |
> +-----------------------------------------------------------------+
> | GRANT USAGE ON *.* TO 'mythtv'@'localhost'                      |
> | GRANT ALL PRIVILEGES ON `mythconverg`.* TO 'mythtv'@'localhost' |
> +-----------------------------------------------------------------+
> 2 rows in set (0.00 sec)
>
> mysql> show grants for mythtv@'localhost' ;
> +-----------------------------------------------------------------+
> | Grants for mythtv@localhost                                     |
> +-----------------------------------------------------------------+
> | GRANT USAGE ON *.* TO 'mythtv'@'localhost'                      |
> | GRANT ALL PRIVILEGES ON `mythconverg`.* TO 'mythtv'@'localhost' |
> +-----------------------------------------------------------------+
> 2 rows in set (0.00 sec)
>
> mysql> show grants for 'mythtv'@'192.168.1.%' ;
> +-------------------------------------------------------------------+
> | Grants for mythtv@192.168.1.%                                     |
> +-------------------------------------------------------------------+
> | GRANT USAGE ON *.* TO 'mythtv'@'192.168.1.%'                      |
> | GRANT ALL PRIVILEGES ON `mythconverg`.* TO 'mythtv'@'192.168.1.%' |
> +-------------------------------------------------------------------+
> 2 rows in set (0.00 sec)
>
> mysql> show grants for 'mythtv'@'192.168.1.133' ;
> ERROR 1141 (42000): There is no such grant defined for user 'mythtv' on host '192.168.1.133'
> mysql> show grants for 'mythtv'@'192.168.1.68' ;
> ERROR 1141 (42000): There is no such grant defined for user 'mythtv' on host '192.168.1.68'
> mysql>
>
> }}}

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';

Just tested on a Fedora 33 host running 10.4.13-MariaDB.

--
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

On Sat, 4 Jul 2020 12:01:00 +0100, you wrote:

>On 02/07/2020 23:36, Bill Meek wrote:
>> On 7/2/20 5:26 PM, John Pilkington wrote:
>>> On 02/07/2020 22:09, Bill Meek wrote:
>>>> On 7/2/20 1:05 PM, John Pilkington wrote:
>>>>> The?section?below?gave?me?the?'mistake?in?syntax'?response
>>>>>
>>>>> Finally, if you just want to restrict by IP subnet (in this example,
>>>>> the 192.168.1.?network):
>>>>>
>>>>> ?????$?mysql?-u?root?mythconverg
>>>>> ?????mysql>?create?user?"mythtv"@"192.168.1.%"?identified?by?"mythtv";
>>>>> ?????mysql>?grant?all?on?mythconverg.*?to?mythtv@"192.168.1.%";
>>>>> ?????mysql>?flush?privileges;
>>>>
>>>> The following worked for MySQL v5.7 and v8.0 plus MariaDB at least
>>>> 10.3.iforget,
>>>> although initially, it was tested with localhost and is in the mc.sql.
>>>>
>>>> But I just tried it with an IP and it worked fine (that is, no syntax
>>>> errors.)
>>>>
>>> {{{
>>>
>>> CREATE USER IF NOT EXISTS 'mythtv'@'192.168.1.%' IDENTIFIED WITH
>>> mysql_native_password;
>>> ALTER USER 'mythtv'@'192.168.1.%' IDENTIFIED BY 'mythtv';
>>>
>>> }}}
>>>
>>> Thanks Bill.? I may try it with a specific address first, because I
>>> want to be able to run other systems independently.? This stuff works
>>> for me for years and it's scary to have to fiddle with it when it stops.
>>>
>>> I'm expecting that grant and flush lines will be needed too.
>>
>> ^^^ Yes.
>>
>> I expect more of the same as MySQL changes (v8 has already caused
>> issues) and MariaDB
>> doesn't have the same changes, at least not yet.
>>
>> <PedPeeve>Too many places with similar information. Makes it tough to
>> find/update all of them.</PetPeeve>
>>
>
>Bill: I haven't tried that ALTER on the Fedora box but did it on a
>laptop with *buntu PPA 31 fixes (bionic, mythtv-setup.real, mythbackend,
>mythtfrontend.real) which is fine as a FE/BE.
>
>The ALTER doesn't work as I expected, though. Looking at the GRANTS,
>the % is not recognised as a wildcard. Perhaps the susbstitution
>happens later? 133 is the laptop, 68 the F31 box.
>
>{{{
>mysql> show grants ;
>+-----------------------------------------------------------------+
>| Grants for mythtv@localhost |
>+-----------------------------------------------------------------+
>| GRANT USAGE ON *.* TO 'mythtv'@'localhost' |
>| GRANT ALL PRIVILEGES ON `mythconverg`.* TO 'mythtv'@'localhost' |
>+-----------------------------------------------------------------+
>2 rows in set (0.00 sec)
>
>mysql> show grants for mythtv@'localhost' ;
>+-----------------------------------------------------------------+
>| Grants for mythtv@localhost |
>+-----------------------------------------------------------------+
>| GRANT USAGE ON *.* TO 'mythtv'@'localhost' |
>| GRANT ALL PRIVILEGES ON `mythconverg`.* TO 'mythtv'@'localhost' |
>+-----------------------------------------------------------------+
>2 rows in set (0.00 sec)
>
>mysql> show grants for 'mythtv'@'192.168.1.%' ;
>+-------------------------------------------------------------------+
>| Grants for mythtv@192.168.1.% |
>+-------------------------------------------------------------------+
>| GRANT USAGE ON *.* TO 'mythtv'@'192.168.1.%' |
>| GRANT ALL PRIVILEGES ON `mythconverg`.* TO 'mythtv'@'192.168.1.%' |
>+-------------------------------------------------------------------+
>2 rows in set (0.00 sec)
>
>mysql> show grants for 'mythtv'@'192.168.1.133' ;
>ERROR 1141 (42000): There is no such grant defined for user 'mythtv' on
>host '192.168.1.133'
>mysql> show grants for 'mythtv'@'192.168.1.68' ;
>ERROR 1141 (42000): There is no such grant defined for user 'mythtv' on
>host '192.168.1.68'
>mysql>
>
>}}}

My reading of 192.168.1.133 not showing as having grants is that it is
doing an exact match in the "show grants" command, rather than the
wildcard grant not actually working. Can you log in to MariaDB as
mythtv from 192.168.1.133 or 192.168.1.68?

mysql -h <mythbackend server> -u mythtv -p

If your network has a nameserver, you might also need to do grants
using the name rather than the IP address.
_______________________________________________
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

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          |
> +--------+--------------------+
>

Hi Bill: Thanks again. I'm afraid these posts had gone off-list. I'm
puzzled. The command above doesn't appear to be looking at mythconverg,
and my users table contains only 'admin'

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.

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

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

It's getting late...

... and I didn't hit 'send'

_______________________________________________
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

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@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
_______________________________________________
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

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@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


_______________________________________________
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