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

Fri Nov 4 00:53:19 UTC 2011

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.