[mythtv-users] MySql broken after updates

Fri Dec 5 01:13:14 UTC 2014

On Thu, 04 Dec 2014 13:53:05 -0600, you wrote:

>A couple of weeks ago Mythbuntu had some updates available.  I ran the 
>updates.  Since then, MythTV hasn't been running correctly.  On my 
>frontend, when I try to access a video or recording, I get an error 
>saying the file could not be found.  On my backend, when I try to start 
>mythbackend manually, I get an error saying, "Cannot login to database," 
>followed by "Would you like to configure the database now?"  It does not 
>give me the option to answer.  I get some logging messages, then 
>finally, I get, "Can't connect to local MySQL server through socket 
>'/var/run/mysqld/mysqld.sock'".
>
>I don't have a mysql.sock or a mysqld.sock file on my system.
>
>Earlier, I was getting an error saying mythtv (the user) could not login 
>to the database due to a permission problem.  I don't remember the exact 
>message.  I was trying to duplicate that message when I got the stuff above.
>
>It seems like my database is corrupt, but I don't know much about 
>MySQL.  Can anybody offer any suggestions?
>
>BTW, I'm running Mythbuntu 14.04.1.  MythTV version is 0.27.
>
>Mike

I am also running Mythbuntu 14.04.1 with MythTV 0.27+fixes.

First I think you should check that mysql is actually running.  From a
terminal, run:

   ps -e | grep sql

This is what I get:

 2311 ?        01:18:43 mysqld

If it is running, then try to check the database for errors.  Run
this:

  sudo /usr/bin/mysqlcheck --defaults-extra-file=/etc/mysql/debian.cnf
mythconverg

(all one line - it may wrap in my email).

The results should be an OK for each table in the database:

mythconverg.archiveitems                           OK
mythconverg.callsignnetworkmap                     OK
mythconverg.capturecard                            OK
mythconverg.cardinput                              OK
mythconverg.channel                                OK
mythconverg.channelgroup                           OK
mythconverg.channelgroupnames                      OK
mythconverg.channelscan                            OK
mythconverg.channelscan_channel                    OK
mythconverg.channelscan_dtv_multiplex              OK
mythconverg.codecparams                            OK
mythconverg.credits                                OK
mythconverg.customexample                          OK
mythconverg.diseqc_config                          OK
mythconverg.diseqc_tree                            OK
mythconverg.displayprofilegroups                   OK
mythconverg.displayprofiles                        OK
mythconverg.dtv_multiplex                          OK
mythconverg.dtv_privatetypes                       OK
mythconverg.dvdbookmark                            OK
mythconverg.dvdinput                               OK
mythconverg.dvdtranscode                           OK
mythconverg.eit_cache                              OK
mythconverg.filemarkup                             OK
mythconverg.gallerymetadata                        OK
mythconverg.gamemetadata                           OK
mythconverg.gameplayers                            OK
mythconverg.housekeeping                           OK
mythconverg.inputgroup                             OK
mythconverg.internetcontent                        OK
mythconverg.internetcontentarticles                OK
mythconverg.inuseprograms                          OK
mythconverg.iptv_channel                           OK
mythconverg.jobqueue                               OK
mythconverg.jumppoints                             OK
mythconverg.keybindings                            OK
mythconverg.keyword                                OK
mythconverg.livestream                             OK
mythconverg.logging                                OK
mythconverg.movies_movies                          OK
mythconverg.movies_showtimes                       OK
mythconverg.movies_theaters                        OK
mythconverg.music_albumart                         OK
mythconverg.music_albums                           OK
mythconverg.music_artists                          OK
mythconverg.music_directories                      OK
mythconverg.music_genres                           OK
mythconverg.music_playlists                        OK
mythconverg.music_radios                           OK
mythconverg.music_smartplaylist_categories         OK
mythconverg.music_smartplaylist_items              OK
mythconverg.music_smartplaylists                   OK
mythconverg.music_songs                            OK
mythconverg.music_stats                            OK
mythconverg.mythexport                             OK
mythconverg.mythexport_job_queue                   OK
mythconverg.mythlog                                OK
mythconverg.mythweb_sessions                       OK
mythconverg.networkiconmap                         OK
mythconverg.oldfind                                OK
mythconverg.oldprogram                             OK
mythconverg.oldrecorded                            OK
mythconverg.people                                 OK
mythconverg.phonecallhistory                       OK
mythconverg.phonedirectory                         OK
mythconverg.pidcache                               OK
mythconverg.playgroup                              OK
mythconverg.powerpriority                          OK
mythconverg.powerpriority_tmp                      OK
mythconverg.profilegroups                          OK
mythconverg.program                                OK
mythconverg.programgenres                          OK
mythconverg.programrating                          OK
mythconverg.recgrouppassword                       OK
mythconverg.record                                 OK
mythconverg.record_tmp                             OK
mythconverg.recorded                               OK
mythconverg.recordedartwork                        OK
mythconverg.recordedcredits                        OK
mythconverg.recordedfile                           OK
mythconverg.recordedmarkup                         OK
mythconverg.recordedprogram                        OK
mythconverg.recordedrating                         OK
mythconverg.recordedseek                           OK
mythconverg.recordfilter                           OK
mythconverg.recordingprofiles                      OK
mythconverg.recordmatch                            OK
mythconverg.romdb                                  OK
mythconverg.scannerfile                            OK
mythconverg.scannerpath                            OK
mythconverg.schemalock                             OK
mythconverg.settings                               OK
mythconverg.storagegroup                           OK
mythconverg.tvchain                                OK
mythconverg.tvosdmenu                              OK
mythconverg.upnpmedia                              OK
mythconverg.videocast                              OK
mythconverg.videocategory                          OK
mythconverg.videocollection                        OK
mythconverg.videocountry                           OK
mythconverg.videogenre                             OK
mythconverg.videometadata                          OK
mythconverg.videometadatacast                      OK
mythconverg.videometadatacountry                   OK
mythconverg.videometadatagenre                     OK
mythconverg.videopart                              OK
mythconverg.videopathinfo                          OK
mythconverg.videosource                            OK
mythconverg.videotypes                             OK
mythconverg.weatherdatalayout                      OK
mythconverg.weatherscreens                         OK
mythconverg.weathersourcesettings                  OK

The recordedseek table is larger than the rest of the database put
together, so the check may seem to stall for a while just before it
reports on the recordedseek table (with the OK for recordedrating
showing).  Just wait for it to finish the recordedseek table.  This
mysqlcheck is automatically run before the weekly database backup that
is done automatically unless you have disabled it.  You can see it in
the /etc/cron.weekly/mythtv-database file which does the backups.

If it passes that check, then try running a full repair & optimise on
the database.  Run:

  /usr/share/doc/mythtv-backend/contrib/maintenance/optimize_mythdb.pl

This is what I get:

Repaired/Optimized: `mythconverg`.`archiveitems`
Analyzed: `mythconverg`.`archiveitems`
Repaired/Optimized: `mythconverg`.`callsignnetworkmap`
Analyzed: `mythconverg`.`callsignnetworkmap`
Repaired/Optimized: `mythconverg`.`capturecard`
Analyzed: `mythconverg`.`capturecard`
Repaired/Optimized: `mythconverg`.`cardinput`
Analyzed: `mythconverg`.`cardinput`
Repaired/Optimized: `mythconverg`.`channel`
Analyzed: `mythconverg`.`channel`
Repaired/Optimized: `mythconverg`.`channelgroup`
Analyzed: `mythconverg`.`channelgroup`
Repaired/Optimized: `mythconverg`.`channelgroupnames`
Analyzed: `mythconverg`.`channelgroupnames`
Repaired/Optimized: `mythconverg`.`channelscan`
Analyzed: `mythconverg`.`channelscan`
Repaired/Optimized: `mythconverg`.`channelscan_channel`
Analyzed: `mythconverg`.`channelscan_channel`
Repaired/Optimized: `mythconverg`.`channelscan_dtv_multiplex`
Analyzed: `mythconverg`.`channelscan_dtv_multiplex`
Repaired/Optimized: `mythconverg`.`codecparams`
Analyzed: `mythconverg`.`codecparams`
Repaired/Optimized: `mythconverg`.`credits`
Analyzed: `mythconverg`.`credits`
Repaired/Optimized: `mythconverg`.`customexample`
Analyzed: `mythconverg`.`customexample`
Repaired/Optimized: `mythconverg`.`diseqc_config`
Analyzed: `mythconverg`.`diseqc_config`
Repaired/Optimized: `mythconverg`.`diseqc_tree`
Analyzed: `mythconverg`.`diseqc_tree`
Repaired/Optimized: `mythconverg`.`displayprofilegroups`
Analyzed: `mythconverg`.`displayprofilegroups`
Repaired/Optimized: `mythconverg`.`displayprofiles`
Analyzed: `mythconverg`.`displayprofiles`
Repaired/Optimized: `mythconverg`.`dtv_multiplex`
Analyzed: `mythconverg`.`dtv_multiplex`
Repaired/Optimized: `mythconverg`.`dtv_privatetypes`
Analyzed: `mythconverg`.`dtv_privatetypes`
Repaired/Optimized: `mythconverg`.`dvdbookmark`
Analyzed: `mythconverg`.`dvdbookmark`
Repaired/Optimized: `mythconverg`.`dvdinput`
Analyzed: `mythconverg`.`dvdinput`
Repaired/Optimized: `mythconverg`.`dvdtranscode`
Analyzed: `mythconverg`.`dvdtranscode`
Repaired/Optimized: `mythconverg`.`eit_cache`
Analyzed: `mythconverg`.`eit_cache`
Repaired/Optimized: `mythconverg`.`filemarkup`
Analyzed: `mythconverg`.`filemarkup`
Repaired/Optimized: `mythconverg`.`gallerymetadata`
Analyzed: `mythconverg`.`gallerymetadata`
Repaired/Optimized: `mythconverg`.`gamemetadata`
Analyzed: `mythconverg`.`gamemetadata`
Repaired/Optimized: `mythconverg`.`gameplayers`
Analyzed: `mythconverg`.`gameplayers`
Repaired/Optimized: `mythconverg`.`housekeeping`
Analyzed: `mythconverg`.`housekeeping`
Repaired/Optimized: `mythconverg`.`inputgroup`
Analyzed: `mythconverg`.`inputgroup`
Repaired/Optimized: `mythconverg`.`internetcontent`
Analyzed: `mythconverg`.`internetcontent`
Repaired/Optimized: `mythconverg`.`internetcontentarticles`
Analyzed: `mythconverg`.`internetcontentarticles`
Repaired/Optimized: `mythconverg`.`inuseprograms`
Analyzed: `mythconverg`.`inuseprograms`
Repaired/Optimized: `mythconverg`.`iptv_channel`
Analyzed: `mythconverg`.`iptv_channel`
Repaired/Optimized: `mythconverg`.`jobqueue`
Analyzed: `mythconverg`.`jobqueue`
Repaired/Optimized: `mythconverg`.`jumppoints`
Analyzed: `mythconverg`.`jumppoints`
Repaired/Optimized: `mythconverg`.`keybindings`
Analyzed: `mythconverg`.`keybindings`
Repaired/Optimized: `mythconverg`.`keyword`
Analyzed: `mythconverg`.`keyword`
Repaired/Optimized: `mythconverg`.`livestream`
Analyzed: `mythconverg`.`livestream`
Repaired/Optimized: `mythconverg`.`logging`
Analyzed: `mythconverg`.`logging`
Repaired/Optimized: `mythconverg`.`movies_movies`
Analyzed: `mythconverg`.`movies_movies`
Repaired/Optimized: `mythconverg`.`movies_showtimes`
Analyzed: `mythconverg`.`movies_showtimes`
Repaired/Optimized: `mythconverg`.`movies_theaters`
Analyzed: `mythconverg`.`movies_theaters`
Repaired/Optimized: `mythconverg`.`music_albumart`
Analyzed: `mythconverg`.`music_albumart`
Repaired/Optimized: `mythconverg`.`music_albums`
Analyzed: `mythconverg`.`music_albums`
Repaired/Optimized: `mythconverg`.`music_artists`
Analyzed: `mythconverg`.`music_artists`
Repaired/Optimized: `mythconverg`.`music_directories`
Analyzed: `mythconverg`.`music_directories`
Repaired/Optimized: `mythconverg`.`music_genres`
Analyzed: `mythconverg`.`music_genres`
Repaired/Optimized: `mythconverg`.`music_playlists`
Analyzed: `mythconverg`.`music_playlists`
Repaired/Optimized: `mythconverg`.`music_radios`
Analyzed: `mythconverg`.`music_radios`
Repaired/Optimized: `mythconverg`.`music_smartplaylist_categories`
Analyzed: `mythconverg`.`music_smartplaylist_categories`
Repaired/Optimized: `mythconverg`.`music_smartplaylist_items`
Analyzed: `mythconverg`.`music_smartplaylist_items`
Repaired/Optimized: `mythconverg`.`music_smartplaylists`
Analyzed: `mythconverg`.`music_smartplaylists`
Repaired/Optimized: `mythconverg`.`music_songs`
Analyzed: `mythconverg`.`music_songs`
Repaired/Optimized: `mythconverg`.`music_stats`
Analyzed: `mythconverg`.`music_stats`
Repaired/Optimized: `mythconverg`.`mythexport`
Analyzed: `mythconverg`.`mythexport`
Repaired/Optimized: `mythconverg`.`mythexport_job_queue`
Analyzed: `mythconverg`.`mythexport_job_queue`
Repaired/Optimized: `mythconverg`.`mythlog`
Analyzed: `mythconverg`.`mythlog`
Repaired/Optimized: `mythconverg`.`mythweb_sessions`
Analyzed: `mythconverg`.`mythweb_sessions`
Repaired/Optimized: `mythconverg`.`networkiconmap`
Analyzed: `mythconverg`.`networkiconmap`
Repaired/Optimized: `mythconverg`.`oldfind`
Analyzed: `mythconverg`.`oldfind`
Repaired/Optimized: `mythconverg`.`oldprogram`
Analyzed: `mythconverg`.`oldprogram`
Repaired/Optimized: `mythconverg`.`oldrecorded`
Analyzed: `mythconverg`.`oldrecorded`
Repaired/Optimized: `mythconverg`.`people`
Analyzed: `mythconverg`.`people`
Repaired/Optimized: `mythconverg`.`phonecallhistory`
Analyzed: `mythconverg`.`phonecallhistory`
Repaired/Optimized: `mythconverg`.`phonedirectory`
Analyzed: `mythconverg`.`phonedirectory`
Repaired/Optimized: `mythconverg`.`pidcache`
Analyzed: `mythconverg`.`pidcache`
Repaired/Optimized: `mythconverg`.`playgroup`
Analyzed: `mythconverg`.`playgroup`
Repaired/Optimized: `mythconverg`.`powerpriority`
Analyzed: `mythconverg`.`powerpriority`
Repaired/Optimized: `mythconverg`.`powerpriority_tmp`
Analyzed: `mythconverg`.`powerpriority_tmp`
Repaired/Optimized: `mythconverg`.`profilegroups`
Analyzed: `mythconverg`.`profilegroups`
Repaired/Optimized: `mythconverg`.`program`
Analyzed: `mythconverg`.`program`
Repaired/Optimized: `mythconverg`.`programgenres`
Analyzed: `mythconverg`.`programgenres`
Repaired/Optimized: `mythconverg`.`programrating`
Analyzed: `mythconverg`.`programrating`
Repaired/Optimized: `mythconverg`.`recgrouppassword`
Analyzed: `mythconverg`.`recgrouppassword`
Repaired/Optimized: `mythconverg`.`record`
Analyzed: `mythconverg`.`record`
Repaired/Optimized: `mythconverg`.`record_tmp`
Analyzed: `mythconverg`.`record_tmp`
Repaired/Optimized: `mythconverg`.`recorded`
Analyzed: `mythconverg`.`recorded`
Repaired/Optimized: `mythconverg`.`recordedartwork`
Analyzed: `mythconverg`.`recordedartwork`
Repaired/Optimized: `mythconverg`.`recordedcredits`
Analyzed: `mythconverg`.`recordedcredits`
Repaired/Optimized: `mythconverg`.`recordedfile`
Analyzed: `mythconverg`.`recordedfile`
Repaired/Optimized: `mythconverg`.`recordedmarkup`
Analyzed: `mythconverg`.`recordedmarkup`
Repaired/Optimized: `mythconverg`.`recordedprogram`
Analyzed: `mythconverg`.`recordedprogram`
Repaired/Optimized: `mythconverg`.`recordedrating`
Analyzed: `mythconverg`.`recordedrating`
Repaired/Optimized: `mythconverg`.`recordedseek`
Analyzed: `mythconverg`.`recordedseek`
Repaired/Optimized: `mythconverg`.`recordfilter`
Analyzed: `mythconverg`.`recordfilter`
Repaired/Optimized: `mythconverg`.`recordingprofiles`
Analyzed: `mythconverg`.`recordingprofiles`
Repaired/Optimized: `mythconverg`.`recordmatch`
Analyzed: `mythconverg`.`recordmatch`
Repaired/Optimized: `mythconverg`.`romdb`
Analyzed: `mythconverg`.`romdb`
Repaired/Optimized: `mythconverg`.`scannerfile`
Analyzed: `mythconverg`.`scannerfile`
Repaired/Optimized: `mythconverg`.`scannerpath`
Analyzed: `mythconverg`.`scannerpath`
Repaired/Optimized: `mythconverg`.`schemalock`
Analyzed: `mythconverg`.`schemalock`
Repaired/Optimized: `mythconverg`.`settings`
Analyzed: `mythconverg`.`settings`
Repaired/Optimized: `mythconverg`.`storagegroup`
Analyzed: `mythconverg`.`storagegroup`
Repaired/Optimized: `mythconverg`.`tvchain`
Analyzed: `mythconverg`.`tvchain`
Repaired/Optimized: `mythconverg`.`tvosdmenu`
Analyzed: `mythconverg`.`tvosdmenu`
Repaired/Optimized: `mythconverg`.`upnpmedia`
Analyzed: `mythconverg`.`upnpmedia`
Repaired/Optimized: `mythconverg`.`videocast`
Analyzed: `mythconverg`.`videocast`
Repaired/Optimized: `mythconverg`.`videocategory`
Analyzed: `mythconverg`.`videocategory`
Repaired/Optimized: `mythconverg`.`videocollection`
Analyzed: `mythconverg`.`videocollection`
Repaired/Optimized: `mythconverg`.`videocountry`
Analyzed: `mythconverg`.`videocountry`
Repaired/Optimized: `mythconverg`.`videogenre`
Analyzed: `mythconverg`.`videogenre`
Repaired/Optimized: `mythconverg`.`videometadata`
Analyzed: `mythconverg`.`videometadata`
Repaired/Optimized: `mythconverg`.`videometadatacast`
Analyzed: `mythconverg`.`videometadatacast`
Repaired/Optimized: `mythconverg`.`videometadatacountry`
Analyzed: `mythconverg`.`videometadatacountry`
Repaired/Optimized: `mythconverg`.`videometadatagenre`
Analyzed: `mythconverg`.`videometadatagenre`
Repaired/Optimized: `mythconverg`.`videopart`
Analyzed: `mythconverg`.`videopart`
Repaired/Optimized: `mythconverg`.`videopathinfo`
Analyzed: `mythconverg`.`videopathinfo`
Repaired/Optimized: `mythconverg`.`videosource`
Analyzed: `mythconverg`.`videosource`
Repaired/Optimized: `mythconverg`.`videotypes`
Analyzed: `mythconverg`.`videotypes`
Repaired/Optimized: `mythconverg`.`weatherdatalayout`
Analyzed: `mythconverg`.`weatherdatalayout`
Repaired/Optimized: `mythconverg`.`weatherscreens`
Analyzed: `mythconverg`.`weatherscreens`
Repaired/Optimized: `mythconverg`.`weathersourcesettings`
Analyzed: `mythconverg`.`weathersourcesettings`

Again, it may seem to stall on recordedrating while it is checking
recordedseek.

If there is something wrong with the database, you will likely get
some warning or error messages, but the repair & optimise often fixes
the usual sorts of errors.  After this, try to start mythbackend and
see if it works.  The normal command to do this is:

  sudo start mythtv-backend

and you can see if it is running using the command:

  sudo status mythtv-backend

This repair & optimise is done automatically daily if you have set the
"Enable daily MySQL DB Optimize/Repair cron job" option on the MySQL
tab of Mythbuntu Control Center.  If you do not have that option set,
I highly recommend setting it as it makes database problems much less
likely to happen in the future.  Setting that option copies the
optimize_mythdb.pl file to /etc/cron.daily/optimize_mythdb so that it
is run automatically every day.