Can't use TV or repair database (1 Viewer)

[doveman]

Over the last couple of days I've been trying (and failing) to setup a separate database for XBMC so that's probably got something to do with this. I also moved my PC so everything had to be unplugged but I don't think that's relevant and the tuner appears to be working when tested with DVBGuide.

When I first booted my PC after moving it, MP had trouble connecting to the server on 192.168.2.1 and the MySQL box appeared when I started TV Server config, set to localhost but once I changed that to 192.168.2.1 it connected OK. After that, I had no EPG so did a manual refresh of that which worked.

Later I tried to watch TV and just got "Channel not found" when trying. So I thought I'd do a channel scan but that doesn't detect anything. As I say DVBGuide locks on fine, so I figured the database might need repairing, so tried the command I've used in the past:

mysqlcheck mptvdb --auto-repair -e -u root -p --password=MediaPortal

but that just gives:

mysqlcheck: Got error: 1045: Access denied for user 'root'@'127.0.0.1' (using password: YES) when trying to connect

Trying to open the MySQL Commandline prompts me for the password but then closes when I enter MediaPortal (was working yesterday)

So I'd be grateful if someone could advise me how to fix this. I'm sure I'll probably have to start with a fresh database but that's OK.

 

[doveman]

I figured it was worth trying to reinstall the server to see if that would sort it out. After doing that, I can manually start timeshifting on BBC One and scanning for channels works, so that's good but I still can't access the commandline and it just closes when I enter MediaPortal for the password and running the repair command gives the same error.

 

[jameson_uk]

Try root@localhost or root@hostname as user name

 

[doveman]

It doesn't ask me for the username, just the password, when I run the Command Line Client shortcut.

If I try adding the address to the repair command I get:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqlcheck mptvdb --auto-repair -e -u root@localhost -p --password=MediaPortal
mysqlcheck: Got error: 1045: Access denied for user 'root@localhost'@'127.0.0.1'(using password: YES) when trying to connect

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqlcheck mptvdb --auto-repair -e -u root@192.168.2.1 -p --password=MediaPortal
mysqlcheck: Got error: 1045: Access denied for user 'root@192.168.2.1'@'127.0.0.1' (using password: YES) when trying to connect

 

[jameson_uk]

It doesn't ask me for the username, just the password, when I run the Command Line Client shortcut.

You are specifying a user:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqlcheck mptvdb --auto-repair -e -u root@localhost -p --password=MediaPortal

I am assuming you are trying to connect from the same machine that hosts the database?
How did you setup this database?

Do you have MySQL workbench installed? (http://dev.mysql.com/downloads/workbench/)
Stop TV Service and MySQL service in windows then go into workbench.
If necessary add a new connection (just accept all the defaults)

Go into manage security and check the connection you probably just added (mine is mysqld@localhost)

Go to the options file section and on security tab tick the "skip-grant-tables" option

Now go to the startup / showdown section and restart the server.

Finally go to the home page and connect to the connection for the SQL Development tool.

In there run

select * from mysql.user;

Mainly interested in user and host columns. What values do you have there?

Afterwards go back to the admin stuff. Stop the server, untick the "skip-grant-tables" option, apply and start the server again

 

[doveman]

It doesn't ask me for the username, just the password, when I run the Command Line Client shortcut.

You are specifying a user:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqlcheck mptvdb --auto-repair -e -u root@localhost -p --password=MediaPortal

I'm referring to the Start Menu shortcut MySQL/My SQL Server 5.1/My SQL Command Line Client.

I am assuming you are trying to connect from the same machine that hosts the database?
How did you setup this database?

Yep, same PC. Database was setup by MediaPortal installer.

Do you have MySQL workbench installed? (http://dev.mysql.com/downloads/workbench/)
Stop TV Service and MySQL service in windows then go into workbench.
If necessary add a new connection (just accept all the defaults)

It opens with a connection already listed on localhost:3306. When I click Open Connection, it shows the Hostname as 127.0.0.1 and the username as root. When I try and open it, it fails. If I click on New Connection, it shows the same details and clicking on Test Connection it fails with the same error (10061) as it does if I change the hostname to 192.168.2.1 or 192.168.1.64. It's very strange as Mediaportal is working OK, including TV and that must be using root/Mediaportal to login

 

[doveman]

Whilst I'd obviously prefer to fix the database, if that's too tricky could you tell me how to just wipe it and start again?

 

[Paranoid Delusion]

Has your setting for MySQL service changed to "Network" service, this will stop localhost access.

See picture for what it should be, this was one thing I found when upgrading to MySQL5.6, not saying this is your problem, just worth taking a look.

 

[doveman]

Has your setting for MySQL service changed to "Network" service, this will stop localhost access.

See picture for what it should be, this was one thing I found when upgrading to MySQL5.6, not saying this is your problem, just worth taking a look.

Thanks for the suggestion. Mine had "Allow service to interact with the desktop" unticked. Is that what you mean by changed to "Network" service?

So I ticked it and still can't login using the start menu shortcut and MediaPortal for the password. I'll try restarting MySQL later when the TV isn't being used in case that's needed for the change to take effect.

 

[Paranoid Delusion]

Sounds like yours is set to local system run already, I would use workbench to create a new server instance and see if you can connect to that, deleting either user\server will not delete your db, so you can change these to your hearts content.