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..
/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.
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.
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
/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.
/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]
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
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.
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.
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.
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)
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.
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.
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
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.
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.
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.