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

[Damian]

On 04/11/2011 00:53, R. G. Newbury wrote:
> On 11/03/2011 05:58 PM, Damian wrote:
>> On 01/11/2011 23:47, R. G. Newbury wrote:
>>> On 11/01/2011 07:37 PM, Newbury wrote:
>>>>
>>>> On 2011-11-01, at 19:26, Damian<myth at surr.co.uk> wrote:
>>>>
>>>>> Hi all,
>>>>>
>>>>> Any more ideas on this?
>>>>>
>>>>> Being unable to log into phpmyadmin makes me think that passwords have
>>>>> changed during the upgrade somehow!?!
>>>>>
>>>>> I've just done the following to confirm the status of things:
>>>>>
>>>>> $ sudo service mysql status
>>>>> mysql start/running
>>>>>
>>>>> $ sudo service mythtv-backend status
>>>>> mythtv-backend stop/waiting
>>>>>
>>>>>
>>>>> It's a long time since I've had scary mysql problems and needed my
>>>>> hand
>>>>> holding though it, but I seem to be there again for some reason.
>>>>>
>>>>
>>>> This came up about a week ago and I set out a checklist of steps in
>>>> response to a similar problem. Go to gossamer-threads and search using
>>>> 'Newbury' and 'mysql' to find the thread.
>>>>
>>>> On my iPhone ATM.
>>>>
>>>> Geoff
>>>
>>> OK, This thread:
>>> http://www.gossamer-threads.com/lists/mythtv/users/494571
>>>
>>> Geoff
>>>
>>>
>>
>> Hi Geoff,
>>
>> Thanks for replying.
>>
>> The permission of my /etc/mythtv/mysql.txt file is still set to 'mythtv'.
>>
>> Would posting the contents of it be useful? The top line says:
>> DBHostName=192.168.0.2
>
> I presume that 192.168.0.2 is the correct IP address of your mythbox.
>
>
>> Here's the content of my 'hosts' file:
>>
>> 127.0.0.1 localhost.localdomain localhost
>> ::1 MythBox localhost6.localdomain6 localhost6
>> 127.0.1.1 MythBox
>
> If you use a local name for your box, it should be listed in hosts *by
> the IP address*.
> 192.168.0.2 MythBox
>
>
>> # 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
>>
>>
>> There's no mention of '192.168.0.2' in there, but the properties say
>> that the file hasn't been modified since May, so I assume that's not the
>> problem.
>>
>> /home/.mythtv/config.xml has this in there:
>> <DBHostName>192.168.0.2</DBHostName>
>>
>>
>> I still can't log into phpmyadmin, so either mysql isn't running ("sudo
>> service mysql status" seems to say that it is), or my passwords have got
>> messedup/changed/deleted somehow. Why on earth should that happen with
>> an upgrade??
>>
>> I'm hoping that the database is all still there and fine and I just need
>> to crack logging into the thing.
>>
>> What steps should I take from here?
>
> Start with 'mysql -u mythtv -pmythtv mythconverg'
>
> If you end up with the "mysql >" prompt then it is NOT mysql's fault, it
> is your myth setup. Go to A
>
> If you don't then your mysql setup is munged. You will get a message
> like 'login not allowed for user mythtv at whereever'. Go to B
>
>
> A
> First, run ifconfig and confirm that 192.168.0.2 *IS* your correct IP
> address.
> Then I suggest that you *very* carefully check your
> /home/mythtv/.mythtv/mysql.txt and config.xml files, so that the correct
> IP, user and password are listed.
>
> I strongly suggest that you then search for each and every other copy of
> those files and replace them with symlinks. Having different versions of
> these is *really* messy.
> (Use 'find -L . -type f -iname mysql.txt' from /etc/ and /home and /root)
> service restart network
> service restart mysqld
> mythtv-setup
> Check that the IP address and computer name are properly set on the
> first two pages, and in the General pages.
>
> This could happen if your box gets a new IP address. You really do want
> to have your router serve up a static address. Most routers allow you to
> tie the MAC address to an IP address. From your symptoms I suspect that
> this could have been what happened.
>
> B
> Mysql does not know who your user is. Create a file, mc.sql containing:
>
> CREATE DATABASE IF NOT EXISTS mythconverg;
> GRANT ALL ON mythconverg.* TO mythtv at localhost IDENTIFIED BY "mythtv";
> FLUSH PRIVILEGES;
> GRANT CREATE TEMPORARY TABLES ON mythconverg.* TO mythtv at localhost
> IDENTIFIED BY "mythtv";
> FLUSH PRIVILEGES;
>
> If your box is EVER going to be more than a single BE/FE setup you MUST
> substitute your mythboxname for localhost in the above.
>
> You need to know your root *mysql* password, if one was set.
> Run 'mysql -u root -p < mc.sql'
> and enter the password. The root mysql user has the privilege levels to
> create the mythtv user.
>
> Since you DID have a working setup, I doubt that this is the cause.
> As set out in the prior thread, you can see which users mysql 'knows'
> about by signing into mysql as root user, then:
> mysql > use mysql;
> mysql > select host,user,password from user;
>
> This lists all the known users on which hosts, and whether there is a
> password.
>
> Geoff
>
> Restart the network and mysqld.
>

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

# 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?

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'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.

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.

Thanks again
Damian