[mythtv-users] sanity check or repairing DB for 0.22 upgrade.

[Alan Anderson]

Sanity check please.

I have one master backend  mythbackend version: 0.21.20080304-1 and three 
front ends.   I have had the same data base for 5 or more years.  Typically 
when I upgrade I backup /var/lib/mysql upgrade the version  of fedora and 
restore /var/lib/mysql.  So far it has always worked.  I have backups of 
mythconverg but never needed them.  I also run the script 
/usr/share/doc/mythtv-docs-0.21/contrib/optimize_mythdb.pl weekly.


When I tried to install V0.22 I ran into the following scheme update issue.

Query was:
ALTER DATABASE mythconverg DEFAULT CHARACTER SET latin1;
Driver error was [2/1283]:
QMYSQL3: Unable to execute statement
Database error was:
Column 'filename' cannot be part of FULLTEXT index

Every time I start a front end the error occurs.


From my understanding this is issue where some UTF characters got into the 
data base when everything should be latin1.   I need to do a partial restore 
to fix this.  I need to fix the data base before I can upgrade to V0.22.

So I put everything back to v0.21 of mythtv

In my attempt to fix this corruption I do the following:


service  mythbackend stop

Run mythconverg_backup.pl to backup the database

service mysqld stop

tar up /var/lib/mysql
mv /var/lib/mysql /var/lib/mysql-old
mkdir mysql
service mysqld start

recreate the db
# mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('PASSWD') WHERE user='root';
mysql> FLUSH PRIVILEGES;
mysql> quit

mysql -u root -p < /usr/share/doc/mythtv-docs-0.21/database/mc.sql

run mythtv-setup  reconfigure the tuners schedules direct etc   The local 
backend is 192.168.123.20 ports 6543 and 6544   The master backend is 
192.168.123.20 port 6543 on the general setup screen. 

allow access to the front ends
$ mysql -u root -p mythconverg
grant all on mythconverg.* to mythtv@"192.168.123.%" identified by "mythtv";
flush privileges;
quit

Do a partial restore
mythconverg_restore.pl --partial_restore --filename=filebackedupabove

service mythbackend start
But the front ends can access the database but not the master backend.  

The mythfrontend.log says its trying to connect to 192.168.123.20 6543 and the 
tcpdump confirms this.  The master BE resets the connection attempt.

I have also tried all three IP address for the front ends into the data base.   
The setup on the frontends is saved so the database part works.  Why the 
rmaster backend is not listening on the correct port bothers me.


If I stop mysqld and mythbackend put the old database back in /var/lib/mysql 
start everything back up its working.

So what silly thing am I doing wrong and where does mythbackend gets its port 
beside from mythtv-setup?


Running on the old data base the FE's get in on port 6543.   But mythbackend 
listens on tcp 6543 6543 and udp 6549

# netstat -anp | grep mythbackend
tcp        0      0 0.0.0.0:6543                0.0.0.0:*                   
LISTEN      2349/mythbackend
tcp        0      0 0.0.0.0:6544                0.0.0.0:*                   
LISTEN      2349/mythbackend
tcp        0      0 192.168.123.20:6543         192.168.123.22:40112        
ESTABLISHED 2349/mythbackend
tcp        0      0 192.168.123.20:6543         192.168.123.22:40117        
ESTABLISHED 2349/mythbackend
tcp        0      0 192.168.123.20:6543         192.168.123.22:40116        
ESTABLISHED 2349/mythbackend
tcp        0      0 192.168.123.20:49877        192.168.123.230:65001       
ESTABLISHED 2349/mythbackend
tcp        0      0 192.168.123.20:6543         192.168.123.22:40111        
ESTABLISHED 2349/mythbackend
tcp        0      0 192.168.123.20:49878        192.168.123.230:65001       
ESTABLISHED 2349/mythbackend
udp        0      0 255.255.255.255:1900        0.0.0.0:*                               
2349/mythbackend
udp        0      0 239.255.255.250:1900        0.0.0.0:*                               
2349/mythbackend
udp        0      0 0.0.0.0:6549                0.0.0.0:*                               
2349/mythbackend
unix  3      [ ]         STREAM     CONNECTED     9740   2349/mythbackend
unix  3      [ ]         STREAM     CONNECTED     9728   2349/mythbackend
unix  3      [ ]         STREAM     CONNECTED     9726   2349/mythbackend
unix  3      [ ]         STREAM     CONNECTED     9672   2349/mythbackend