[mythtv-users] Can't connect to database

[Damian]

On 03/05/2020 17:27, Stephen Worthington wrote:
> On Sun, 3 May 2020 15:57:04 +0100, you wrote:
>
>> Hi All,
>>
>> I have just updated Xubutu on my server and backend computer.
>>
>> I also updated MythTV, from 0.29 to 0.31 (via an incremental 0.30 step).
>> This is because the idea of "Greatly improved Channel Scanning" was too
>> much to resist (I've not been able to use Live TV on Myth for months).
>>
>> After all of this, I've tried to run, "sudo mythtv-setup" and just ger
>> presented with a "Waiting for database" screen.
>>
>> My only lead on solving this is one of the files that the Xubuntu update
>> wanted to replace. It was mysqld.cnf
>>
>> I saved a copy of the changes between the old and new files (see below).
>> Does this help at all, or is it a blind alley?
>>
>> And if the text below is a blind alley, how can I check what's going on
>> with the database?
>>
>> If any log are needed, please let me know where/how to find them.
>>
>> Thanks,
>> Damian
> Is the "Waiting for database" message a GUI one from
> mythtv-setup.real, or from the mythtv-setup wrapper script?
Hi Stephen,

It was a GUI message/screen (with options to search or exit etc) after 
running "sudo mythtv-setup"
> The
> wrapper script has had significant changes in v31 so it can shut down
> mythbackend in the current environment.  I would suggest manually
> shutting down mythbackend (sudo systemctl stop mythtv-backend) and
> then running mythtv-setup.real directly, to see if that works.
OK, just tried that. It took me to the same GUI "Waiting for database to 
start" screen, with Retry, Search, Setup and Exit options.

The options in Setup look just like they always have.
> Also, mythtv-setup (and .real) should be run from a mythtv user,
> rather than root.
OK. Thanks for letting me know. I feel like I've had problems with that 
in the past, but I ran it as the user that last time.
> Looking at the mysqld.cnf changes, I am not sure which of the two
> diffed files is the one that is being loaded by MySQL or MariaDB.
This is my current /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
#
# * 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

> If
> it is the +++ one, then the bindaddress=127.0.0.1 line has been
> uncommented, so it could be overriding your real bindaddress line in
> another settings file.
I had my fingers crossed that the solution would be that simple. So, I 
added # before the line, logged out and logged back in again.

I was still taken to the same waiting for database screen.

(I've taken the # back out of the bind-address. Let me know if it should 
definitely be there).
>    Then your config.xml file might be using an
> external IP address, and anything using that would not be able to
> connect to the database.
>
> BTW The config.xml file should be set connect using 127.0.0.1 or
> localhost if it is on the same machine as the backend, as I believe
> that using that setting tells MythTV programs to actually use a Unix
> socket connection which is significantly faster and uses lower
> resources than a TCP/IP connection to 127.0.0.1, which is in turn much
> faster than using a TCP/IP connection to a real Ethernet port.
This is my /etc/mythtv/config.xml

<Configuration>
   <Database>
     <PingHost>1</PingHost>
     <Host>192.168.0.4</Host>
     <UserName>mythtv</UserName>
     <Password>hidden</Password>
     <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>

The ip is set to the ip of the computer. That's the same as the ip 
address that my frontends use. The server/backend doesn't even have a 
screen most of the time (it does while I'm doing this), so I don't mind 
how fast or slow the local frontend on the same machine is.

My main front end (different machine) is also unable to connect to the 
backend database.

Any idea what else I can do?

Thanks for your help!
Damian