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

[doveman]

I created a new Server Instance on localhost port 3307. It couldn't connect on localhost, 127.0.0.1 or 192.168.1.64 but it said it might be because the server wasn't running so I continued through to the end of the creation. It defaulted to use my.ini but that's set to use port 3306 so I copied it to my2.ini and changed the port in it to 3307. I also changed the data folder to data2 so that it didn't use the same one as the first instance.

So then I have two instances in the SQL Development column, the original one on port 3306 and the new one on port 3307. Comodo Firewall doesn't show anything listening on either port but netstat -an shows 3306 open, so probably Comodo just doesn't show local/internal connections. If I try to connect to the second instance on 3307 via "Open connection to start querying" or "Manage Connections" - Test Connection it says Can't Connect. Maybe I need to restart mysql before the second instance will work. However now I can connect to the original instance on 3306 via either method so I don't know why that's suddenly started working. It still just closes the window when I enter MediaPortal as the password in the Start Menu commandline shortcut though, which is weird.

Anyway, now I can connect I'll try doing what jameson_uk suggested.

 

[doveman]

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

OK, well I still can't connect to my second instance on port 3307 even after restarting MySQL. I did the above on the original instance on port 3306 OK though.

localhost	root	*49BEEF3FDF2CF9638F0301B447D4F5E300C9015F	Y	Y	Y	Y
127.0.0.1	root		Y	Y	Y	Y
localhost			N	N	N	N
%	root	*49BEEF3FDF2CF9638F0301B447D4F5E300C9015F	Y	Y	Y	Y
%	xbmc	*EC4F5173DE994BFEAB7040A4A32B4ED6DE26F6F0	N	N	N	N

It looks the same as I got running SELECT host,user from mysql.user; from the commandline when that was working, which I can't get into now using the password MediaPortal, even though that must be the password or else MP/TV Server wouldn't be working

 

[doveman]

I still can't connect using the commandline Start Menu shortcut but using the Workbench I can login with root/Mediaportal and see mptvdb, mymusic32 and myvideos75 and I can login with xbmc/xbmc and see just mymusic32 and myvideos75, so it seems the users are OK so I don't understand why the commandline shortcut doesn't work using MediaPortal.

Ah, I just tried clearing the stored password in Workbench and it still connected using root without prompting for a password. So I tried the commandline shortcut without entering a password, just pressing enter and it connected, so it seems the password has been wiped somehow. I'm surprised MP works still as I thought it would be using root/Mediaportal

EDIT: I still can't connect to the 2nd instance on port 3307 I created though. I've posted below the my2.ini associated with it and the my.ini used by the working instance on port 3306 if you could take a look and see if there's anything obvious. The second instance my2.ini is pointed to datadir="C:/ProgramData/MySQL/MySQL Server 5.1/Data2" which is currently empty. Is this wrong and am I meant to create some files in there manually?

[client]
port=3307
[mysql]
default-character-set=utf8
[mysqld]
port=3307
basedir="C:/Program Files/MySQL/MySQL Server 5.1/"
datadir="C:/ProgramData/MySQL/MySQL Server 5.1/Data2"
default-character-set=utf8
default-storage-engine=myisam
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=32M
table_cache=64
tmp_table_size=18M
thread_cache_size=4
thread_concurrency=4
myisam_max_sort_file_size=100M
myisam_max_extra_sort_file_size=100M
myisam_sort_buffer_size=64M
key_buffer_size=16M
read_buffer_size=2M
read_rnd_buffer_size=16M
sort_buffer_size=2M
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=96M
innodb_log_file_size=50M
innodb_thread_concurrency=8
bind-address = 0.0.0.0
skip-name-resolve

[client]
port=3306
[mysql]
default-character-set=utf8
[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.1/"
datadir="C:/ProgramData/MySQL/MySQL Server 5.1/Data"
default-character-set=utf8
default-storage-engine=myisam
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=32M
table_cache=64
tmp_table_size=18M
thread_cache_size=4
thread_concurrency=4
myisam_max_sort_file_size=100M
myisam_max_extra_sort_file_size=100M
myisam_sort_buffer_size=64M
key_buffer_size=16M
read_buffer_size=2M
read_rnd_buffer_size=16M
sort_buffer_size=2M
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=96M
innodb_log_file_size=50M
innodb_thread_concurrency=8
bind-address = 0.0.0.0
skip-name-resolve

 

[doveman]

So now I've established that the password is blank and I can connect with either Workbench or the CLI, how do I set the password back to MediaPortal? I don't know that it needs to be as it seems to working without any password but it's obviously not good practice to have a blank password and there may be something that doesn't work if it's expecting it to be MediaPortal.

Strange that the Workbench connects whether I clear the password or use Store in Vault to set it to MediaPortal, whereas the CLI definitely only works with no password but that's not really important.