LinHES Forums
http://forum.linhes.org/

[SOLVED} Mythtv cannot connect to mysql database on boot up
http://forum.linhes.org/viewtopic.php?f=5&t=24761
Page 1 of 1

Author:  drhood [ Mon May 28, 2018 4:23 am ]
Post subject:  [SOLVED} Mythtv cannot connect to mysql database on boot up

Hello,

First, I want to thank all those who helped me solve previous problems. I try to provide details to the problem, as well as the solution.

My current problem occurred after a power loss and the system (frontend and backend on the same machine).

When we boot up LinHES (8.5 I think), a screen appears titled 'MythTV Startup Status' and the message 'Unable to connect to Database. Select an option below'. The message seems to indicate the mythbackend cannot communicate to the mysql database.

Note: I am able to access the mythtv machine across the network.

I verified that the contents of /etc/mysql/my.cnf is correct.
Quote:
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/run/mysqld/mysqld.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /var/run/mysqld/mysqld.sock
datadir = /data/srv/mysql

The contents of /home/mythtv/.mythtv/config.xml also looks correct.
Quote:
<LocalHostName>my-unique-identifier-goes-here</LocalHostName>
<Database>
<UserName>mythtv</UserName>
<PingHost>0</PingHost>
<Host>192.168.0.151</Host>
<DatabaseName>mythconverg</DatabaseName>
<Password>mythtv</Password>
<Port>3306</Port>
</Database>

This file also appears in a few other locations (/usr/share/mythtv/contrib/config_files/config.xml and /usr/share/mythtv/config.xml; however, the content looks as it should.

I can verify that mysql server is running with the command...
Quote:
sudo sv status mysql
run: mysql: (pid 967) 3810s

If I run this command for mysql, the output is...
Quote:
ps ax | grep mysql
943 ? Ss 0:00 runsv mysql
1636 ? R 22:43 /bin/sh usr/bin/mysqld_safe --user=mysql
22907 pts/3 S+ 0:00 grep mysql

Is the above normal output?

At first I thought the database might be corrupted, so I tried to optimize/repair.

Quote:
sudo /usr/share/mythtv/contrib/maintenance/optimize_mythdb.pl
DBI connect('database=mythconverg:host=192.168.0.151;port=3306','mythtv',...) failed: Can't connect to MySQL server on '192.168.0.151' (111) at /usr/share/perl5/site_perl/MythTV.pm line 373.
Cannot connect to database:

The following alternative command has similar output.
Quote:
sudo /usr/bin/mysqlcheck mythconverg
/usr/bin/mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect

The above commands give a clue to the problem. I checked the permissions of /var/run/mysqld, which appears correct.
Quote:
drwxr-xr-x 2 mysql mysql 40 May 27 19:25 mysqld


I have run out of ideas on a next step.

I will appreciate any help with re-connecting mysql to mythtvbackend.

:-)

Author:  brfransen [ Mon May 28, 2018 7:53 am ]
Post subject:  Re: Mythtv cannot connect to mysql database on boot up

It looks like mysql is not actually running. The cmd ps ax | grep mysql should also show a /usr/bin/mysqld process that is spawned by mysqld_safe. Since sv runs mysqld_safe that is why sv status shows it is running.

A couple of things to do:
- Use df -h | grep mysql and make sure your sql partition isn't full
- Check the end of the error file: sudo less /data/srv/mysql/mythtv.err

Author:  drhood [ Mon May 28, 2018 10:25 am ]
Post subject:  Re: Mythtv cannot connect to mysql database on boot up

brfransen wrote:
It looks like mysql is not actually running. The cmd ps ax | grep mysql should also show a /usr/bin/mysqld process that is spawned by mysqld_safe. Since sv runs mysqld_safe that is why sv status shows it is running.

A couple of things to do:
- Use df -h | grep mysql and make sure your sql partition isn't full
- Check the end of the error file: sudo less /data/srv/mysql/mythtv.err

Thank you for these suggestions. The output of the first command above is the following.

Quote:
df -h | grep mysql
/dev/sda6 1.9G 1.8G 0 100% /data/srv/mysql

Thus, it appears that /dev/sda6 is completely full.

I am unfamiliar with /data/srv/mysql, so I listed the contents of this directory.
Quote:
[mythtv@linhes ~]$ sudo ls -lh /data/srv/mysql
total 1.4G
-rw-rw---- 1 mysql mysql 56 Apr 16 2017 auto.cnf
-rw-rw---- 1 mysql mysql 76M Mar 3 00:17 ibdata1
-rw-rw---- 1 mysql mysql 48M May 28 11:54 ib_logfile0
-rw-rw---- 1 mysql mysql 48M Mar 3 00:17 ib_logfile1
-rw-rw---- 1 mysql mysql 1.3G May 28 11:54 linhes.err
drwx------ 2 root root 16K Apr 16 2017 lost+found
drwx------ 2 mysql mysql 4.0K Feb 15 2017 mysql
drwx------ 2 mysql mysql 20K Mar 3 00:17 mythconverg
drwx------ 2 mysql mysql 4.0K Jun 8 2017 ncid
drwx------ 2 mysql mysql 4.0K Feb 15 2017 performance_schema
drwx------ 2 mysql mysql 4.0K Feb 15 2017 test


It appears that linhes.err file has taken up a tremendous amount of space.

I executed the second recommended command and there is a repeating error that has gone rogue (i.e., repeated a huge number of times).
Quote:
sudo less /data/srv/mysql/linhes.err
2017-11-23 07:03:46 2107 [ERROR] /usr/bin/mysqld: Table './mythconverg/recordedseek' is marked as crashed and last (automatic?) repair failed


Note: The mysql.err is not found in /data/srv/mysql

I think I know how to fix the error associated with the recordedseek; however, how do I free up space in /data/srv/mysql?

Thanks again for all the help!

Author:  brfransen [ Mon May 28, 2018 10:43 am ]
Post subject:  Re: Mythtv cannot connect to mysql database on boot up

Right, I forgot that the mysql .err files is based on the hostname. So I am guessing your hostname is linhes?

First stop mysql: sudo sv stop mysql
Then move the linhes.err file to another disk/partition since it is so large and is taking up 70% of the mysql partition. It will be recreated when mysql is next started.
Also check /data/srv/mysql/mythconverg for any *.TMD files. These are mysql temp files that may not have been cleaned up when you lost power. Rename them or move them to another disk. I have found that these TMD files can keep the optimize and repair operations from running. After the db is repaired you can delete these TMDs.
Start mysql: sudo sv start mysql and check the .err file or use ps to see if mysql is running.
If no errors you should be able to sudo sv restart mythbackend and be back in business.
I would also run optimize_mythdb.py.

Author:  drhood [ Mon May 28, 2018 3:15 pm ]
Post subject:  Re: Mythtv cannot connect to mysql database on boot up

brfransen wrote:
Right, I forgot that the mysql .err files is based on the hostname. So I am guessing your hostname is linhes?

First stop mysql: sudo sv stop mysql
Then move the linhes.err file to another disk/partition since it is so large and is taking up 70% of the mysql partition. It will be recreated when mysql is next started.
Also check /data/srv/mysql/mythconverg for any *.TMD files. These are mysql temp files that may not have been cleaned up when you lost power. Rename them or move them to another disk. I have found that these TMD files can keep the optimize and repair operations from running. After the db is repaired you can delete these TMDs.
Start mysql: sudo sv start mysql and check the .err file or use ps to see if mysql is running.
If no errors you should be able to sudo sv restart mythbackend and be back in business.
I would also run optimize_mythdb.py.


Thank you so much! I was going to use optimize_mythdb.py to repair the mysql database tables. Is there a better command to repair?

Al

Author:  drhood [ Mon May 28, 2018 7:27 pm ]
Post subject:  Re: Mythtv cannot connect to mysql database on boot up

brfransen wrote:
Right, I forgot that the mysql .err files is based on the hostname. So I am guessing your hostname is linhes?

First stop mysql: sudo sv stop mysql
Then move the linhes.err file to another disk/partition since it is so large and is taking up 70% of the mysql partition. It will be recreated when mysql is next started.
Also check /data/srv/mysql/mythconverg for any *.TMD files. These are mysql temp files that may not have been cleaned up when you lost power. Rename them or move them to another disk. I have found that these TMD files can keep the optimize and repair operations from running. After the db is repaired you can delete these TMDs.
Start mysql: sudo sv start mysql and check the .err file or use ps to see if mysql is running.
If no errors you should be able to sudo sv restart mythbackend and be back in business.
I would also run optimize_mythdb.py.

brfransen - Your instructions worked. Thank you so much. Below is a bit more information.

Step 1. Stop mysql
Quote:
[mythtv@linhes ~]$ sudo sv stop mysql

Step 2. Rename and move linhes.err to new location
Quote:
[mythtv@linhes ~]$ sudo mv /data/srv/mysql/linhes.err /home/mythtv/linhes.err.deleteme
[mythtv@linhes ~]$ sudo rm /data/srv/mysql/linhes.err

Step 3. Find and rename *.TMD files in /data/srv/mysql/mythconverg
Quote:
[mythtv@linhes ~]$ sudo find /data/srv/mysql/mythconverg -name *.TMD
[mythtv@linhes ~]$ program.TMD
recordedseek.TMD
[mythtv@linhes ~]$ sudo mv sudo mv /data/srv/mysql/mythconverg/program.TMD /data/srv/mysql/mythconverg/program.TMD.deleteme
[mythtv@linhes ~]$ sudo mv sudo mv /data/srv/mysql/mythconverg/recordedseek.TMD /data/srv/mysql/mythconverg/recordedseek.TMD.deleteme

Step 4. Repair mysql database table(s)
Quote:
[mythtv@linhes ~]$ sudo /usr/bin/mysqlcheck mythconverg
[mythtv@linhes ~]$ sudo /usr/bin/mysqlcheck mythconverg recordedseek
[mythtv@linhes ~]$ sudo optimize_mythdb.py

Note: The /usr/bin/mysqlcheck mythconverg command resulted in a list of tables and the recordedseek table was reported to be corrupted.

Step 5. Remove unneeded TMD files
Quote:
[mythtv@linhes ~]$ sudo rm /data/srv/mysql/mythconverg/recordedseek.TMD.deleteme
[mythtv@linhes ~]$ sudo rm /data/srv/mysql/mythconverg/program.TMD.deleteme

Step 6. Restart mysql
Quote:
[mythtv@linhes ~]$ sudo sv start mysql

Step 7. Check whether mysqld is running
Quote:
[mythtv@linhes ~]$ ps ax | grep mysql
943 ? Ss 0:00 runsv mysql
26035 ? S 0:00 /bin/sh /usr/bin/mysqld_safe --user=mysql
26364 ? Sl 0:00 /usr/bin/mysqld --basedir=/usr --datadir=/data/srv/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/data/srv/mysql/linhes.err --pid-file=/data/srv/mysql/linhes.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
27485 pts/3 S+ 0:00 grep mysql

Step 8. Alt+tab to mythbackend error screen and click on the 'Retry' button. Frontend appeared as normal with program listing etc.

Author:  brfransen [ Mon May 28, 2018 7:54 pm ]
Post subject:  Re: Mythtv cannot connect to mysql database on boot up

I see you got it fixed. Great!

drhood wrote:
Thank you so much! I was going to use optimize_mythdb.py to repair the mysql database tables. Is there a better command to repair
optimize_mythdb.py does do a repair on each of the tables...so not better, just different.

Page 1 of 1 All times are UTC - 6 hours
Powered by phpBB® Forum Software © phpBB Group
http://www.phpbb.com/