[mythtv-users] Mythfrontend on Android-database access

[Stephen Worthington]

On Mon, 16 May 2022 12:20:05 -0500, you wrote:

>On 5/16/2022 9:41 AM, Donald Brett wrote:
>> On 5/16/2022 5:28 AM, Stephen Worthington wrote:
>>> On Mon, 16 May 2022 09:53:27 +0100, you wrote:
>>>
>>>> On 16/05/2022 03:14, Stephen Worthington wrote:
>>>>> On Sun, 15 May 2022 13:11:27 -0500, you wrote:
>>>>>
>>>>>> By the way, a couple more data points; the mysql root user does 
>>>>>> not have
>>>>>> a password.  Also, the mythfrontend password is still mythtv, not the
>>>>>> generated one (y2i1MpJ8).  When migrating the old database to the new
>>>>>> machine, it seems to use the stock 'mythtv' password, so I left it 
>>>>>> as-is
>>>>>> (changed config.xml password).  Any other suggestions?
>>>>>>
>>>>>> Don
>>>>> There is a big difference between
>>>>>
>>>>> sudo mysql
>>>>>
>>>>> and
>>>>>
>>>>> mysql -u root -p
>>>>>
>>>>> When mysql is run from root with no password, it has full permissions
>>>>> for everything possible.  When it is run as user root with "-u root",
>>>>> it only has permissions a specified in the grants for user "root".
>>>>>
>>>>> When you migrate a database (or restore an old one), only the
>>>>> mythconverg database tables are migrated or restored by
>>>>> mythconverg_restore.pl.  Users and their grants are stored in the
>>>>> "mysql" database tables, and are not touched, so you have to manually
>>>>> set them up again.  They must match what you have in your config.xml
>>>>> files in /etc/mythtv, /home/mythtv/.mythtv and /home/<frontend
>>>>> user>/.mythtv.  When I move a database, I normally copy the old
>>>>> config.xml files and re-create the old users and grants, but you can
>>>>> choose to use the new config.xml settings too.  But you still need to
>>>>> create the users and grants.
>>>>>
>>>> Can I ask why the old users and grants are not migrated along with 
>>>> the database? After all, these
>>>> are essential metadata and having to (research and) re-input those 
>>>> commands introduces possibilities
>>>> for mistakes and errors.
>>> My guess would be that it is way too difficult.  Access to the users
>>> and grants requires MySQL/MariaDB permissions on the "mysql" database.
>>> The mythconverg_backup.pl program would need to be run as root to do
>>> that, or as a MySQL/MariaDB user with the special permissions
>>> required.  And then which users need to be backed up and restored?
>>> There would need to be options to allow the usernames to be specified
>>> as there is no automated way of finding all the config.xml files you
>>> are using and getting the usernames from them.  They can be on
>>> different PCs and devices.  And the "create user" and "grant" commands
>>> have been changing, so support would be needed for different versions
>>> of MySQL and MariaDB doing things differently.  And in any case, there
>>> is no way to extract the passwords from MySQL/MariaDB - the design of
>>> the way they work explicitly prevents that.  So the passwords would
>>> also need to be found in the config.xml files.
>>>
>>> The names of the backup and restore programs (mythconverg_backup.pl
>>> and mythconverg_restore.pl) also strongly suggest that they are only
>>> going to work on the mythconverg database, not on data stored anywhere
>>> else.
>>> _______________________________________________
>>> mythtv-users mailing list
>>> mythtv-users at mythtv.org
>>> http://lists.mythtv.org/mailman/listinfo/mythtv-users
>>> http://wiki.mythtv.org/Mailing_List_etiquette
>>> MythTV Forums: https://forum.mythtv.org
>>
>> Hmm, I don't recall creating users or grants, so assumed it was done 
>> for me on another step of the install.  I used this to load the 
>> previous database:
>>
>> sudo /usr/share/mythtv/mythconverg_restore.pl --drop_database 
>> --create_database --filename 
>> /home/mythtv/db_backups/mythconverg-1350-20220501093643.sql.gz
>>
>> Then adjusted config.xml to use the default password.
>>
>> Anyway, I found a workaround for the mysql "identified by" issue here:
>>
>> https://stackoverflow.com/questions/5555328/error-1396-hy000-operation-create-user-failed-for-jacklocalhost 
>>
>>
>> And tried:
>>
>> mysql> drop user mythtv@'%';
>> mysql> flush privileges;
>> mysql> create user mythtv@'%' identified by 'mythtv';
>> mysql> flush privileges;
>> mysql> show grants for mythtv;
>>
>> +------------------------------------+
>> | Grants for mythtv@%                |
>> +------------------------------------+
>> | GRANT USAGE ON *.* TO `mythtv`@`%` |
>> +------------------------------------+
>> 1 row in set (0.00 sec)
>>
>> mysql> grant all privileges on mythconverg.* to 'mythtv'@'%';
>> mysql> show grants for mythtv;
>>
>> +---------------------------------------------------------+
>> | Grants for mythtv@%                                     |
>> +---------------------------------------------------------+
>> | GRANT USAGE ON *.* TO `mythtv`@`%`                      |
>> | GRANT ALL PRIVILEGES ON `mythconverg`.* TO `mythtv`@`%` |
>> +---------------------------------------------------------+
>> 2 rows in set (0.01 sec)
>>
>> The local fe/be still works and now the ubuntu 18.04 box (johnny) has 
>> access.  But the ONN still has the same problem.  By the way, I turned 
>> off the V-32 backend and turned on the ubuntu 18.04 backend (on 
>> johnny).  ONN found it and asked to update the database, so I assume 
>> it was able to connect the it's mysql server.  I'll  check for 
>> differences between them.
>>
>> Don
>>
>
>Users and grants are nearly identical between the two, so I logged into 
>the ONN to look for clues.  From windows powershell:
>
>PS C:\Upgrades\platform-tools_r33.0.1-windows\platform-tools> ./ADB logcat
>...long list of stuff, display scrolls until it goes to end of log, then 
>I try to start mythfrontend on the ONN.  Normal startup, then fails on 
>database connection, with:
>
>05-16 11:41:47.162 10824 10858 E mfe     : 
>mythdbcon.cpp:237:OpenDatabase  [DBManager2] Unable to connect to database!
>05-16 11:41:47.162 10824 10858 E mfe     : 
>mythdbcon.cpp:238:OpenDatabase  Driver error was [1/2058]:
>05-16 11:41:47.162 10824 10858 E mfe     : QMYSQL: Unable to connect
>05-16 11:41:47.162 10824 10858 E mfe     : Database error was:
>05-16 11:41:47.162 10824 10858 E mfe     : Plugin caching_sha2_password 
>could not be loaded: dlopen failed: library 
>"/home/peter/proj/github.com/MythTV/packaging-master/android/libsinstall/lib/plugin/caching_sha2_password.so" 
>not found
>05-16 11:41:52.108  3734  4486 E WifiService: Permission violation - 
>getConfiguredNetworks not allowed for uid=10035, 
>packageName=com.google.android.tvrecommendations, 
>reason=java.lang.SecurityException: Location mode is disabled for the device
>05-16 11:41:52.484 10824 10858 I mfe     : 
>mythcontext.cpp:887:TestDBconnection  Start up testing connections. DB 
>192.168.0.184, BE , attempt 3, status dbStarted, Delay: 2000
>
>
>, then repeats several times.  It looks like the latest mysql has a 
>change in password encryption.  I've seen suggestion to revert it by 
>adding this to /etc/mysql/mysql.conf.d/mysqld.cnf with 
>"mysql_native_password" , or, updating the root user to 
>"caching_sha2_password".  Not sure which way to go, suggestions?
>
>Don

I think you will need to drop the user and re-create them like this:

DROP USER 'mythtv'@'%';
CREATE USER 'mythtv'@'%' IDENTIFIED WITH mysql_native_password;
ALTER USER 'mythtv'@'%' IDENTIFIED BY 'mythtv';

and do the GRANTs again.

Adding this:

[mysqld]
default-authentication-plugin=mysql_native_password

in the .cnf files will only affect new users created after you restart
MySQL.