[mythtv-users] Upgraded the Ubuntu 11.10 and not Myth can't connect to MySQL

[Damian]

On 05/11/2011 04:12, R. G. Newbury wrote:
>> Hi Geoff,
>>
>> Thanks so much for helping out. Hope we can get to the bottom of this.
>>
>> 192.168.0.2 is definately my correct ip address. It can't not be as my
>> router assigns the IP's, but I have also double checked this now with
>> ifconfig.
>>
>> The first thing I did was change my hosts file, so this now reads:
>>
>> 127.0.0.1 localhost.localdomain localhost
>> # ::1 MythBox localhost6.localdomain6 localhost6
>> # 127.0.1.1 MythBox
>> 192.168.0.2 MythBox
>
> This is good, especially if this is a backend. Make sure that the
> mysql.txt and config.xml files use the *NAME* and that all boxes have
> that line in their /etc/hosts file. Then those other boxes can find the
> mysql server.
>
> This is stuff is completely irrelevant at present. We don't use IPv6
>> # The following lines are desirable for IPv6 capable hosts
>> ::1 ip6-localhost ip6-loopback
>> fe00::0 ip6-localnet
>> ff00::0 ip6-mcastprefix
>> ff02::1 ip6-allnodes
>> ff02::2 ip6-allrouters
>> ff02::3 ip6-allhosts
>>
>> I then re-booted with fingers crossed in the hopes that this little
>> change was the solution to all. Alas, not. Did I change it correctly?
>>
>> I then went on with your suggestion. I typed
>>
>> mysql -u mythtv -pmythtv mythconverg
>>
>> which returned:
>>
>> ERROR 2002 (HY000): Can't connect to local MySQL server through socket
>> '/var/run/mysqld/mysqld.sock' (2)
>>
>  > This is not the same as the predicted: 'login not allowed for user
>> mythtv at whereever'. Does this tell you anything different?
>
> Yes: The *usual* reason for this is that the /var/run/mysqld folder is
> NOT owned by mysql. Check this. If not so owned, go to /var/run and do:
> chown -R mysql:mysql mysql
>
>> I wanted to check again that mysql was actually running (since the
>> re-boot), so typed:
>>
>> sudo service mysql status
>>
>> which returned:
>>
>> mysql start/post-start, process 4732
>> post-start process 4733
>>
>> What on earth does that mean?
>
> I don't see that on Fedora, but those numbers are most likely the PID
> numbers for the running processes. Check by running 'ps -ae | grep
> mysql' which should show something like:
> 1492 ? 00:00:00 mysqld_safe
> 1697 ? 00:00:12 mysqld
>
>
>> I'm now on the edge of family understanding. Two weekends of watching
>> X-factor over the internet rather than watching pre-recorded is enough
>> for them to start thinking about a Windows based system! Help! :-)
>>
>> I'm not sure If I should cary on with your next step as the returned
>> message from the last step didn't match up with what was expected. Let
>> me know what to do next if you can.
>
> It would be useless to do what I next suggested as the mysql server
> itself was not set up correctly and could not be contacted.
>
>> I should also say that this system has always been more than a single
>> FE/BE machine. I have other machines that log into MythTV as Frontends
>> to the system.
>
> Then getting mysql working is really important! There are a number of
> really good chapters on troubleshooting in the Mysql manual available
> online. The various error messages are listed and explained. That is how
> I learned about mysql setup!
>
> Of course google works too! Just google 'mysql error 2002' and you will
> get LOTS of links.
>
> ....find an error, read about it: free, find it again a couple of months
> later, curse yourself for *almost* being able to remember how to fix it,
> but NOT QUITE being able to do so...priceless!
>
> Geoff
>
>

:-) Thanks Geoff, Bill and John for helping out.

Here's the latest. I've done a lot, and think I've got enough to clarify 
the problem a bit to 'those of you who know', but I'm at the edge of my 
understanding again. Here's where it's at ....

Both mysql.txt and config.xml contain '192.168.0.2' rather than 
'MythBox', but I think I need to fix my bigger problems with mysql 
before I start messing with the MythTV setup. I can't currently log into 
mysql as root, so trying to fix anything further down the line seems futile.

I did run:
/var/run$ chown -R mysql:mysql mysql
but this just responded:
chown: cannot access `mysql': No such file or directory

Should one of those 'mysql's have been 'mysqld'? I assume so, but I 
don't know which one.

I've just had a look at the /var/run/mysqld folder, and it looks 
completely empty. That doesn't seem like a good thing!???

Have I not even got mysql installed properly anymore?? This is just 
weird. How can an 'upgrade' break things so badly??

Found this thread:

http://ubuntuforums.org/showthread.php?t=1601420

$ sudo chown -R mysql:mysql /var/lib/mysql
Then rebooted


$ sudo service mysql status
mysql respawn/post-start, (post-start) process 3234


$ mysql -u mythtv -pmythtv mythconverg
ERROR 2002 (HY000): Can't connect to local MySQL server through socket 
'/var/run/mysqld/mysqld.sock' (2)

$ ps -A | grep mysql
Returns nothing

Tried running mysqld out of bin:

/usr/bin$ mysqld
111105 11:19:20 [Warning] option 'net_buffer_length': unsigned value 
8388608 adjusted to 1048576
111105 11:19:20 [Warning] Can't create test file 
/var/lib/mysql/MythBox.lower-test
111105 11:19:20 [Warning] Can't create test file 
/var/lib/mysql/MythBox.lower-test
mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 13)
111105 11:19:20 [ERROR] Aborting

111105 11:19:20 [Note] mysqld: Shutdown complete

Tried running mysqld_safe out of bin:

/usr/bin$ mysqld_safe
111105 11:21:25 mysqld_safe Logging to syslog.
111105 11:21:25 mysqld_safe Starting mysqld daemon with databases from 
/var/lib/mysql
rm: cannot remove `/var/lib/mysql/MythBox.pid': Permission denied
111105 11:21:25 mysqld_safe mysqld from pid file 
/var/lib/mysql/MythBox.pid ended

Im I getting somewhere here? Maybe the hole problem is just down to 
permissions? Or maybe it's permissions and paswords have been changed? 
One thing at a time. What should the permissions on these folders be and 
how do I change them?

I'm learning, but not enough :-)

There's this sequence suggested by the guy on the forum thread from last 
year...
     purge and reinstall mysql server
     chgrp -R mysql /var/lib/mysql/
     Check if mysql can run in savemode: /usr/bin: mysqld_safe
     Somehow I lost my mysql.conf for upstart. So recreate it and paste 
exec sudo -u mysql /usr/sbin/mysqld # <- important execute as use mysql

but I don't even understand all of that, let alone have confidence in 
accieving it. Nor do I know if it will kill any database I once had 
(which I'm hoping I can keep if possible, although I am losing hope that 
it even exists any more).

The last thing I did was follow bills advice and run the 'post-start 
script' from /etc/init/mysql.conf maually on the command line. Here's 
the results:

$ for i in `seq 1 30` ; do
 > /usr/bin/mysqladmin --defaults-file="${HOME}"/debian.cnf ping && {
 > exec "${HOME}"/debian-start
 > # should not reach this line
 >             exit 2
 >         }
 >         sleep 1
 >     done
Could not open required defaults file: /home/damian/debian.cnf
Fatal error in defaults handling. Program aborted
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket 
'/var/run/mysqld/mysqld.sock' (2)'
Check that mysqld is running and that the socket: 
'/var/run/mysqld/mysqld.sock' exists!
Could not open required defaults file: /home/damian/debian.cnf
Fatal error in defaults handling. Program aborted
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket 
'/var/run/mysqld/mysqld.sock' (2)'
Check that mysqld is running and that the socket: 
'/var/run/mysqld/mysqld.sock' exists!
etc etc repeat to infinity

I then tried Bill's test to see if my mythconverg database was still 
there .. and I think it is! :-) Here's the results:

$ sudo -i
root at MythBox:~#    cd /var/lib/mysql/mythconverg
root at MythBox:/var/lib/mysql/mythconverg#    ls -l
total 19748
-rw-rw---- 1 mysql mysql    9255 2011-05-09 07:53 archiveitems.frm
-rw-rw---- 1 mysql mysql       0 2011-10-27 08:11 archiveitems.MYD
-rw-rw---- 1 mysql mysql    2048 2011-10-27 08:11 archiveitems.MYI
-rw-rw---- 1 mysql mysql    8630 2011-05-09 07:53 callsignnetworkmap.frm
-rw-rw---- 1 mysql mysql       0 2011-10-27 08:11 callsignnetworkmap.MYD
-rw-rw---- 1 mysql mysql    1024 2011-10-27 08:11 callsignnetworkmap.MYI
-rw-rw---- 1 mysql mysql    9752 2011-05-09 07:53 capturecard.frm
-rw-rw---- 1 mysql mysql     328 2011-10-27 08:11 capturecard.MYD
and lots more of this .. as expected.

Ok, my head hurts now. Where next?

Thanks again to everyone for spending their time helping me out. It is 
very much appreciated, and I'm promising my wife and kids that the end 
is in sight (but I'm not guaranteeing X-Factor recoding tonight!).

Damian