[mythtv-users] One step forward, two steps back - Frontends not woring properly

[Vincent McIntyre]

On Mon, May 02, 2016 at 12:06:02AM +0100, Damian wrote:
> > 
> > My next suggestion would be posting the mysql config files.
> > This incantation should do the trick
> >   cd /etc/mysql
> >   sudo find . -type f -name "*.cnf" \
> >               -exec egrep -Hv '^(\s*#|$)' {} \; | grep -v debian.cnf
> > 
> > This will print out just the active lines in the config files.
> > The last bit suppresses the content of debian.cnf (it has a password).
> > 
> > Vince
> > 
> OK, here's the output, and a few notes at the end ...
> 
> ./mysql.conf.d/mysqld_safe_syslog.cnf:[mysqld_safe]
> ./mysql.conf.d/mysqld_safe_syslog.cnf:syslog
> ./mysql.conf.d/mysqld.cnf:[mysqld_safe]
> ./mysql.conf.d/mysqld.cnf:socket		= /var/run/mysqld/mysqld.sock
> ./mysql.conf.d/mysqld.cnf:nice		= 0
> ./mysql.conf.d/mysqld.cnf:[mysqld]
> ./mysql.conf.d/mysqld.cnf:user		= mysql
> ./mysql.conf.d/mysqld.cnf:pid-file	= /var/run/mysqld/mysqld.pid
> ./mysql.conf.d/mysqld.cnf:socket		= /var/run/mysqld/mysqld.sock
> ./mysql.conf.d/mysqld.cnf:port		= 3306
> ./mysql.conf.d/mysqld.cnf:basedir		= /usr
> ./mysql.conf.d/mysqld.cnf:datadir		= /var/lib/mysql
> ./mysql.conf.d/mysqld.cnf:tmpdir		= /tmp
> ./mysql.conf.d/mysqld.cnf:lc-messages-dir	= /usr/share/mysql
> ./mysql.conf.d/mysqld.cnf:skip-external-locking
> ./mysql.conf.d/mysqld.cnf:key_buffer_size		= 16M
> ./mysql.conf.d/mysqld.cnf:max_allowed_packet	= 16M
> ./mysql.conf.d/mysqld.cnf:thread_stack		= 192K
> ./mysql.conf.d/mysqld.cnf:thread_cache_size       = 8
> ./mysql.conf.d/mysqld.cnf:myisam-recover-options  = BACKUP
> ./mysql.conf.d/mysqld.cnf:query_cache_limit	= 1M
> ./mysql.conf.d/mysqld.cnf:query_cache_size        = 16M
> ./mysql.conf.d/mysqld.cnf:log_error = /var/log/mysql/error.log
> ./mysql.conf.d/mysqld.cnf:expire_logs_days	= 10
> ./mysql.conf.d/mysqld.cnf:max_binlog_size   = 100M
> ./mysql.cnf:!includedir /etc/mysql/conf.d/
> ./mysql.cnf:!includedir /etc/mysql/mysql.conf.d/
> ./conf.d/mysql.cnf:[mysql]
> ./conf.d/mythtv.cnf:[mysqld]
> ./conf.d/mythtv.cnf:bind-address=192.168.0.2
> ./conf.d/mythtv.cnf:max_connections=1000
> ./conf.d/mysqldump.cnf:[mysqldump]
> ./conf.d/mysqldump.cnf:quick
> ./conf.d/mysqldump.cnf:quote-names
> ./conf.d/mysqldump.cnf:max_allowed_packet	= 16M
> 
> Notes:
> The bind-address in ./conf.d/mythtv.cnf had been "bind-address=::". I
> changed it to "bind-address=192.168.0.2" based on Stephen's advice.
> 
> max_connections in ./conf.d/mythtv.cnf had been 100. Due to Mark's comment,
> I changed this to 250. That seemed to make the frontends more responsive
> (but not fixed). I thought I'd try 1000 just to see if that suddenly made it
> all work, but unfortunately it didn't.
> 
> Also, about half an hour ago I deleted the "mythtv-tweaks.cnf" file. I
> believe this is part of Mythbuntu and it includes the 'table_cache' bit that
> breaks everything, so I had very little faith in it! :-) For completeness,
> here's the contents of that file ...
> 
> [mysqld]
> # The following values were partly taken from:
> # http://www.gossamer-threads.com/lists/mythtv/users/90942#90942
> # key_buffer = 48M
> # max_allowed_packet = 8M
> # table_cache = 128
> sort_buffer_size = 48M
> net_buffer_length = 8M
> # thread_cache_size = 4
> query_cache_type = 1
> query_cache_size = 32M
> # don't do binary logs for mythconverg
> binlog_ignore_db = mythconverg
> 
> Is this helping at all?
> 
> I can't understand what's going on. Surely Mythbuntu is supposed to get rid
> of these configuration headaches for us mere mortal level geeks! Part of my
> feels like wiping the server OS and starting again.
> 

Perplexing, indeed. The mysql config looks mostly sensible.
I noticed one minor thing, in my config I have this in my.cnf

  [client]
  port            = 3306
  socket          = /var/run/mysqld/mysqld.sock

which seems to be absent in yours. I'm not sure why and not sure
it matters. I believe these settings are used just by the mysql
'client' program, for when you want to get in and type scary sql.

The note about the bind-address was interesting to me.
IPv6 is a new internet addressing scheme, it has enough IP addresses
in it for every fridge in the universe (actually maybe every _atom_).
The '::' is IPv6 for the loopback address, which in IPv4 is 127.0.0.1.
This means mysql was not listening to external machines, but it should
be now after your changes.

max_connections has to do with how many simultaneous connections to
the database there can be. With a couple of frontends on a home network
I would be surprised if there were more than 20 in any one second,
but I've never used separate frontends.
The following is what my combined fe/be shows. I had to login to
mysql to run this (I used the username and password in debian.cnf)
mysql> show status like '%onn%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 5     |
| Connections              | 7563  |
| Max_used_connections     | 27    |
| Ssl_client_connects      | 0     |
| Ssl_connect_renegotiates | 0     |
| Ssl_finished_connects    | 0     |
| Threads_connected        | 12    |
+--------------------------+-------+
7 rows in set (0.00 sec)
There have been a lot of Connections over time but it looks like I
have about 12 at the current time.


Ok what next?
Given what you have changed so far the following tests should now pass.
Can you confirm on the backend & frontends?
   backend$ telnet 192.168.0.2 3306   - do you get a 'connect' or not?
   fe1$ telnet 192.168.0.2 3306   - do you get a 'connect' or not?
   fe2$ telnet 192.168.0.2 3306   - do you get a 'connect' or not?

Quite a few things have changed so it might be worth standing back
and once again on a clean piece of paper writing down what the problem
is NOW, as succinctly as possible (the '1-minute elevator pitch').
Might be worth trying to start with the simplest possible setup and
gradually adding pieces. Give each host 10-15min before moving on.
 - shut all the computers down.
   Unplug and reinsert all network connections at both ends.
 - start the backend, no frontends at all.
   is mythweb working ok?
   is mysql listening on 192.168.0.2:3306 ? 
   does 'netstat -in' show errors or dropped packets?
 - start a frontend on the backend machine, if possible.
   does it connect ok, can you view the recording schedule, etc.
   How long until it misbehaves?
   does 'netstat -in' show errors or dropped packets?
 - start a remote frontend machine.
   does it connect ok, can you view the recording schedule, etc.
   How long until it misbehaves?
   What seems to happen when it misbehaves?
 - start the other frontend, same questions as before.

Time is running short so you should consider other options.
Think through whether you need the multiple frontends for the big day.
Could a combined fe/be do the job, with some creative audience seating?

Downgrade? I would not try to downgrade in-place, btw.
If you have a database backup from before the 0.28 upgrade you could
install 14.04+0.27 on a fresh disk, and import the backup to that.
Unfortunately you need the same version on backend and frontends
so it's a fair chunk of work and time.

I keep the media (recordings, rips etc) on disks separate to the OS
so that when I upgrade I put can put a fresh OS disk in and install
to that, then import the database from backup. I don't know if that
is possible for you - just put back the previous system disk. Again
you'd need to do it on all three hosts.

Hope this is of some help
Vince