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

PS I am probably clutching at straws, but I have found a post of mine 
from 2007 when I had what was apparently a similar issuse. Here's a cut 
and paste of what I had to do then. Maybe this is something that happens 
occasioanlly with Ubuntu upgrades???

Have a look at this post from 2007 and see if it points to what could be 
going on today. If not, please ignore this desperate attempt to find a 
solution and go back to my previous effort at getting to the bottom of 
it ....

Post from 2007:
I'm in I'm in I'm in!

Thanks to everyone who helped, especially Peter, Nick and RG Newbury
(from an earlier thread of mine)!

Here's what I had to do to get around mysql problems (writing this here
for my own reference as I feel sure I'll need it again someday!).

Uninstall (using synaptic package manager) mysql-server and myhtv-database

sudo rm -rf /var/lib/mysql

to remove mysql's settings.

Log out and back in (not sure if this is necessary)

re-instal mysql-server (sudo apt-get install mysql-server)

check that I can log in (mysql -u root -p   ... Enter as password when
requested)

sudo apt-get install mythtv-database

then ran the mc.sql script
  cd /usr/share/mythtv/sql
  mysql -u root -p < mc.sql

I was still having trouble, but though it was down to all of my settings
being 192.168.0.2 rather than 'localhost'. So I changed every mysql.txt
file back to 'localhost' and now all seems fine.

Thanks again everyone. I wasn't sure how I was going to get here!

Cheers
Damian


Yours clutching at any straws he can find laying around,
Damian