[Solved] Myth v31.0 fails to restore database - unable to connect to database.

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

Moderator: Forum Moderators

briggella
Junior
Posts: 38
Joined: Tue Jun 05, 2018 11:52 am
New Zealand

[Solved] Myth v31.0 fails to restore database - unable to connect to database.

Post by briggella »

I have installed Ubuntu 20.04.2.0 and Mythtv 31 and having got through the usual database password problems I am now trying to restore the original database to the system. There was remote access originally from a second machine so IP addresses were specified for the Backend server rather than just localhost. These have been specified in the mysql configuration files also. When try ing to drop the new database and restore the old one, I get an error that it cannot connect to the database. The logs are not particularly helpful. There is an error message when I try to do the restore which I post below..



Code: Select all

/usr/share/mythtv/mythconverg_restore.pl --drop_database --create_database --verbose --directory /media/briggella/Transport\ 2T/mythtv  --filename mythconverg-1348-20210525064119.sql.gz

Configuring environment:
  -    username: briggella
  -        HOME: /home/briggella
  - MYTHCONFDIR: /home/briggella/.mythtv

Parsing configuration files:
  - checking: /home/briggella/.mythtv/config.xml
     parsing: /home/briggella/.mythtv/config.xml
  - checking: /home/briggella/.mythtv/backuprc

Applying command-line arguments.

Checking configuration.

Database Information:
         DBHostName: 192.168.1.140
         DBPort: 3306
         DBUserName: mythtv
         DBPassword: XXX
             DBName: mythconverg
        DBSchemaVer: 
  DBBackupDirectory: /media/briggella/Transport 2T/mythtv
   DBBackupFilename: mythconverg-1348-20210525064119.sql.gz
      drop_database: yes
    create_database: yes

Executables:
       mysql_client: mysql
         uncompress: gzip -d

Miscellaneous:
    partial_restore: no
   restore_xmltvids: no
    change_hostname: no

Checking database.
DBI connect('host=192.168.1.140:database=mythconverg','mythtv',...) failed: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. at /usr/share/mythtv/mythconverg_restore.pl line 933.

Preparing initial database.
DBI connect('host=192.168.1.140','mythtv',...) failed: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. at /usr/share/mythtv/mythconverg_restore.pl line 933.

Unable to connect to database.
           database: mythconverg
               host: 192.168.1.140
           username: mythtv
           password: mythtv

Please check your configuration files to verify the database connection
information is correct.  The files that are used to retrieve connection
information are prefixed with "parsing" in the "Parsing configuration files"
section of the --verbose output.

Also note that any [client] or [mysql] password specified in the MySQL options
file (/etc/my.cnf or /etc/mysql/my.cnf or ~/.my.cnf) will take precedence over
the password specified in the MythTV configuration files.

ERROR: Unable to connect to database.

Code: Select all

/etc/mythtv/config.xml 
<Configuration>
	<LocalHostName>Mythbox</LocalHostName>
	<Database>
    <PingHost>1</PingHost>
    <Host>192.168.1.140</Host>
    <UserName>mythtv</UserName>
    <Password>xxxxx</Password> (same as mythconverg)
    <DatabaseName>mythconverg</DatabaseName>
    <Port>3306</Port>
  </Database>
  <WakeOnLAN>
    <Enabled>0</Enabled>
    <SQLReconnectWaitTime>0</SQLReconnectWaitTime>
    <SQLConnectRetry>5</SQLConnectRetry>
    <Command>echo 'WOLsqlServerCommand not set'</Command>
  </WakeOnLAN>
</Configuration>

Code: Select all

/etc/mysql/mysql.conf.d/mythtv.cnf 
[mysqld]
#bind-address=::
bind-address=192.168.1.140
max_connections=100
I am assuming this is something to do with authentication in Mysql but I cannot find anything amiss in the configuration files or much content in them at all. The Myth fronted and backend can run but just need configuring. However I do not want to spend time doing that if I am going to wipe it and restore my old database.

Many thanks for your assistance.
Last edited by briggella on Wed Jun 02, 2021 10:09 am, edited 1 time in total.
User avatar
pgbennett
Developer
Posts: 503
Joined: Mon Apr 27, 2015 5:41 pm
United States of America

Re: Myth 0.31 fails to restore database - unable to connect to database.

Post by pgbennett »

To fix the "Authentication requires secure connection"
Add this line to your /etc/mysql/mysql.conf.d/mysqld.cnf
default_authentication_plugin=mysql_native_password
User avatar
paulh
Developer
Posts: 909
Joined: Thu Feb 06, 2014 6:09 pm
Great Britain

Re: Myth 0.31 fails to restore database - unable to connect to database.

Post by paulh »

I assume the problem here is the PERL DBD-mysql connector used by the backup/restore scripts currently does not have caching_sha2_password support.
See https://mysqlserverteam.com/upgrading-t ... derations/
briggella
Junior
Posts: 38
Joined: Tue Jun 05, 2018 11:52 am
New Zealand

Re: Myth 0.31 fails to restore database - unable to connect to database.

Post by briggella »

Thanks tried that


Restarted Mysql but still getting authentication errors.

Error log this time.


Code: Select all

 
 
 /usr/share/mythtv/mythconverg_restore.pl --drop_database --create_database --verbose --directory /home/briggella  --filename mythconverg-1348-20210525064119.sql.gz

Configuring environment:
  -    username: briggella
  -        HOME: /home/briggella
  - MYTHCONFDIR: /home/briggella/.mythtv

Parsing configuration files:
  - checking: /home/briggella/.mythtv/config.xml
     parsing: /home/briggella/.mythtv/config.xml
  - checking: /home/briggella/.mythtv/backuprc

Applying command-line arguments.

Checking configuration.

Database Information:
         DBHostName: 192.168.1.140
             DBPort: 3306
         DBUserName: mythtv
         DBPassword: XXX
             DBName: mythconverg
        DBSchemaVer: 
  DBBackupDirectory: /home/briggella
   DBBackupFilename: mythconverg-1348-20210525064119.sql.gz
      drop_database: yes
    create_database: yes

Executables:
       mysql_client: mysql
         uncompress: gzip -d

Miscellaneous:
    partial_restore: no
   restore_xmltvids: no
    change_hostname: no

Checking database.
DBI connect('host=192.168.1.140:database=mythconverg','mythtv',...) failed: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. at /usr/share/mythtv/mythconverg_restore.pl line 933.

Preparing initial database.
DBI connect('host=192.168.1.140','mythtv',...) failed: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. at /usr/share/mythtv/mythconverg_restore.pl line 933.

Unable to connect to database.
           database: mythconverg
               host: 192.168.1.140
           username: mythtv
           password: xxxxxx

Please check your configuration files to verify the database connection
information is correct.  The files that are used to retrieve connection
information are prefixed with "parsing" in the "Parsing configuration files"
section of the --verbose output.

Also note that any [client] or [mysql] password specified in the MySQL options
file (/etc/my.cnf or /etc/mysql/my.cnf or ~/.my.cnf) will take precedence over
the password specified in the MythTV configuration files.

ERROR: Unable to connect to database.
briggella
Junior
Posts: 38
Joined: Tue Jun 05, 2018 11:52 am
New Zealand

Re: Myth 0.31 fails to restore database - unable to connect to database.

Post by briggella »

Sorry Posts crossed.

Interesting read, did seem to fit the problem

I did what was suggested but no change. Here is my file.
Suggested entry in bold.


Code: Select all

/etc/mysql/mysql.conf.d/mysqld.cnf 
#
# The MySQL database server configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld]
#
# * Basic Settings
#
user		= mysql
# pid-file	= /var/run/mysqld/mysqld.pid
# socket	= /var/run/mysqld/mysqld.sock
# port		= 3306
# datadir	= /var/lib/mysql


# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir		= /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address		= 127.0.0.1
bind-address		= 192.168.1.140
#mysqlx-bind-address	= 127.0.0.1
mysqlx-bind-address	= 192.168.1.140
#
# * Fine Tuning
#
key_buffer_size		= 16M
# max_allowed_packet	= 64M
# thread_stack		= 256K

# thread_cache_size       = -1

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP

# max_connections        = 151

# table_open_cache       = 4000

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
#
# Log all queries
# Be aware that this log type is a performance killer.
# general_log_file        = /var/log/mysql/query.log
# general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
# slow_query_log		= 1
# slow_query_log_file	= /var/log/mysql/mysql-slow.log
# long_query_time = 2
# log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
# server-id		= 1
# log_bin			= /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds	= 2592000
max_binlog_size   = 100M
# binlog_do_db		= include_database_name
# binlog_ignore_db	= include_database_name
#
#[b] entry to fix Authentication reuires secure connection error suggested by Myth forum.
#
default_authentication_plugin=mysql_native_password[/b]

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

Re: Myth 0.31 fails to restore database - unable to connect to database.

Post by bill6502 »

@briggella did you manually create the mythtv user in MySQL after upgrading
to 20.04?
User avatar
pgbennett
Developer
Posts: 503
Joined: Mon Apr 27, 2015 5:41 pm
United States of America

Re: Myth 0.31 fails to restore database - unable to connect to database.

Post by pgbennett »

In this case I believe you need to recreate your mythtv user and password and grant the privileges. I don't know how the ubuntu install is supposed to do it, but since MySQL 8.0, with manual setup in new installs I have had to first set up the

Code: Select all

default_authentication_plugin=mysql_native_password
and then create the user ids and set their privileges, as described in https://www.mythtv.org/wiki/Build_from_ ... base_Setup
User avatar
bill6502
Developer
Posts: 2307
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Myth 0.31 fails to restore database - unable to connect to database.

Post by bill6502 »

Hi Peter, in the packaging repo, the SQL version is detected and then proper
commands are used to create the user. But that's for a new install. I'm guessing
that the user was recreated here and, as you know, the default changed.

https://github.com/MythTV/packaging/blo ... stinst#L49

For anyone else reading this, upgrades should be done without dropping the DB.
There's code that upgrades mythconverg as needed. Although mythconverg is
automatically backed up before the schema change, unless something evil
happens, it's not necessary to reload it.
briggella
Junior
Posts: 38
Joined: Tue Jun 05, 2018 11:52 am
New Zealand

Re: Myth 0.31 fails to restore database - unable to connect to database.

Post by briggella »

Hi again. 13 hr time difference here. Just had a few hrs sleep. I installed Ubuntu then ran the myth install from the repositories. Then ran my normal user accounf for myself as I need to connect from other computers. I the create the database by importing mc.sql into mysql then did the usual messing around with passwords to change the password to what need with mysql ( Why can this not be simpler?), then ran mythtv-setup to get some basic parameters to check it was fiunctioning. Then I normally import the old database and then run myth again and hopefully it should upgrade to the modern version. I only do this every few years so it is always a voyage of discovery and it never goes smoothly.


I have noticed that there is a mythtv group and /home/mythtv directory but no user account and I am slightly puzzled by that.
briggella
Junior
Posts: 38
Joined: Tue Jun 05, 2018 11:52 am
New Zealand

Re: Myth 0.31 fails to restore database - unable to connect to database.

Post by briggella »

Now checked Mythtv is setup in the database with both types of password enabled.

Code: Select all

mysql>
 use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user, host, plugin, authentication_string from user where User= 'mythtv';
+--------+-------------+-----------------------+------------------------------------------------------------------------+
| user   | host        | plugin                | authentication_string                                                  |
+--------+-------------+-----------------------+------------------------------------------------------------------------+
| mythtv | %           | mysql_native_password | *0B7DA1889B498C71B56B223F0E589804B5BA6070                              |
| mythtv | 192.168.1.% | caching_sha2_password | $A$005$<4&%EF*/&pB?S	>[bYb3JKCIKQnR/lpRWby6OHBnRiwyIJ35YGJ8Ip.VsSu3 |
| mythtv | localhost   | mysql_native_password | *CC8F35F587CA5A556B4132C2407E556D92172FFC                              |
+--------+-------------+-----------------------+------------------------------------------------------------------------+
3 rows in set (0.00 sec)

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

Re: Myth 0.31 fails to restore database - unable to connect to database.

Post by bill6502 »

Code: Select all

DROP USER 'mythtv'@'192.168.1.%';
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;
Your mythtv@localhost password is 'mythtv'.

The above is what the packaging scripts do (only for mythtv@localhost). The
mysql_native_password probably isn't necessary with Peter's fix, but it works.
briggella
Junior
Posts: 38
Joined: Tue Jun 05, 2018 11:52 am
New Zealand

Re: Myth 0.31 fails to restore database - unable to connect to database.

Post by briggella »

So I decided to drop the database from the new install and go with the advice from PG Bennett in his post earlier

In this case I believe you need to recreate your mythtv user and password and grant the privileges. I don't know how the ubuntu install is supposed to do it, but since MySQL 8.0, with manual setup in new installs I have had to first set up the
CODE: SELECT ALL

default_authentication_plugin=mysql_native_password
and then create the user ids and set their privileges, as described in https://www.mythtv.org/wiki/Build_from_ ... base_Setup
Top



I followed the advise in the build form base setup, much of this has changed with Mysql 8.0 particularlyt setting passwords which apparently is now done with the command 'Alter User'

So now place my backed up old database in the backup directory, new database created and try and run mythtv-setup to see if it will upgrade it during the setup process. However I am getting another error stating mythtv-setup cannot find the database to open. Mysql is running. What should I do now. Should I try and update the database first as I was trying before or continue to let myth do so and if so how do I get it to proceed. There is a lot of out of date documentation out there, esp around Mysql, which does make life very confusing.
briggella
Junior
Posts: 38
Joined: Tue Jun 05, 2018 11:52 am
New Zealand

Re: Myth 0.31 fails to restore database - unable to connect to database.

Post by briggella »

Well the issue there was I needed to grant privileges to user mythtv@192.168.1.% om mythconverg. Then I could access mythtv-setup. Then it converted my schema. After that I tried to update my database and this time it worked.

So updated some settings in the system now try to start myth-frontedn but unable to connect . Backend logs unhelpful

Frontend logs just give me the following.

Code: Select all

May 31 00:13:56 Mythbox mythfrontend.real: mythfrontend[28318]: E CoreContext devices/mythcecadapter.cpp:173 (Open) CECAdapter: Failed to load libcec.
May 31 00:14:01 Mythbox mythfrontend.real: mythfrontend[28318]: I CoreContext mythcontext.cpp:885 (TestDBconnection) Start up testing connections. DB 192.168.1.140, BE 192.168.1.140, attempt 2, status beAwake, Delay: 2000
May 31 00:14:06 Mythbox mythfrontend.real: mythfrontend[28318]: I CoreContext mythcontext.cpp:885 (TestDBconnection) Start up testing connections. DB 192.168.1.140, BE 192.168.1.140, attempt 3, status beAwake, Delay: 2000
May 31 00:14:11 Mythbox mythfrontend.real: mythfrontend[28318]: I CoreContext mythcontext.cpp:885 (TestDBconnection) Start up testing connections. DB 192.168.1.140, BE 192.168.1.140, attempt 4, status beAwake, Delay: 2000
May 31 00:14:16 Mythbox mythfrontend.real: mythfrontend[28318]: I CoreContext mythcontext.cpp:885 (TestDBconnection) Start up testing connections. DB 192.168.1.140, BE 192.168.1.140, attempt 5, status beAwake, Delay: 2000
May 31 00:14:21 Mythbox mythfrontend.real: mythfrontend[28318]: I CoreContext mythcontext.cpp:1008 (TestDBconnection) Start up failure. host 192.168.1.140, status beAwakeFail


Not sure where to go from here. Will take up in the morning.
User avatar
bill6502
Developer
Posts: 2307
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Myth 0.31 fails to restore database - unable to connect to database.

Post by bill6502 »

The frontend is testing the connection to the backend at 192.168.1.140, usually on the default port 6543.
It's failing. Verify that the backend is running.

In: mythtv-setup->General->Host Address Backend Setup->Port = 6543
User avatar
bill6502
Developer
Posts: 2307
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Myth v31.0 fails to restore database - unable to connect to database.

Post by bill6502 »

FYI, in /etc/mythtv/config.xml, on a master backend that also has the database server,
<Host>192.168.1.140</Host> should be set to <Host>localhost</Host>. That way, a
socket is used rather than TCP.
Post Reply