Can't connect MySQL Database over network (1 Viewer)

[Lay]

TV-Server Version: SVN
MediaPortal Version:
MediaPortal Skin: ReVision
Windows Version: Vista with all Updates
CPU Type: AMD64 X2 2x2.6GHz
HDD: 3x500GB Samsung (RAID5) & 1x80GB Maxtor
Memory: 3GB (2x1GB & 2x512MB)
Motherboard: ASRock
Motherboard Chipset:
Motherboard Bios:
Video Card: ASUS X1600 passive
Video Card Driver:
Sound Card: TerraTec Aurion Fun 5.1
Sound Card AC3: optical out
Sound Card Driver:
1. TV Card: Hauppauge WinTV-PVR 500
1. TV Card Type: 2x analog
1. TV Card Driver:
2. TV Card: Pinnacle PCTV 200e
2. TV Card Type: DVB-T
2. TV Card Driver:
3. TV Card:
3. TV Card Type:
3. TV Card Driver:
4. TV Card:
4. TV Card Type:
4. TV Card Driver:
MPEG2 Video Codec:
MPEG2 Audio Codec:
Satelite/CableTV Provider:
HTPC Case: Thermaltake Soprano black
Cooling:
Power Supply:
Remote: MS MCE remote control
TV: hp w2207
TV - HTPC Connection:


Hi!

I want to set up the TV Server Software and already have a MySQL Server on the same PC.
In the installation process I got asked for the database connection. I choose MySQL and filled my values and noticed that the test button only works if i fill "localhost" as host. This configuration can't be saved because an error message shows that no localhost is allowed.

It seems that my MySQL Server only works for localhost. The MySQL Administrator (GUI Tools) is also only connectable over localhost. I have configured to hear on all network interfaces and checked also the settings in my my.cfg file.
The user root has permission to connect over localhost, lassp(pc name) and 192.168.0.100. I set the last two also over the MySQL Administrator tool.

Did I miss something? What could I've done wrong?

I really need help because I don't want to run mysql and ms sql parallel.

Big thanks for all the work on this project!!!!!

 

[Wiedmann]

I have configured to hear on all network interfaces and checked also the settings in my my.cfg file.

In other words:
- you have not set "bind-address"
- you have not set "skip-networking"
in your "my.cfg" (btw. in normal case this should be "my.cnf"?)

You can verify that MySQL is listening on all interfaces with "netstat -a -n".

The user root has permission to connect over localhost, lassp(pc name) and 192.168.0.100.

You need only one user: "root@%" (Name: "root"; Host: "%")
If you have this user, you can/should delete all other users. (Don't edit the user table directly!. Use GRANT for adding users and DROP for deleting, or use the the MySQL Administrator tool)

If you don't have this user, you can add this user with:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'secret' WITH GRANT OPTION;

(change 'secret' to your password)

Did I miss something?

Make sure a local firewall allows access to port 3306.

 

[Lay]

Thanks for the fast answer!

First I don't used the bind-address command in the my.cfg file and it doesn't worked. Then I tried to add bind-address for localhost, lassp and 192.168.0.100 and it also doesn't worked.

Maybe it's really because I added the user rights with the MySQL Administrator. When I go home, I'll try to change the host to % (I expect that % stands for all possible hosts) and delete the localhost, lassp and 192.168.0.100 from the user rights.

 

[Lay]

Hi!
Thanks again for the help, but I don't managed it to get it running.

I've made 4 screenshots with my settings and the new error message I got.

You can find them in the attatchment.

Its not clear for me what the "Service" field means in the configuration. I think thats the name of the mysql service , which can be found in the service configuration of windows. Am I right?

 

[Wiedmann]

OK. As I can see you are using XAMPP... Now the things are easier ;-)
- You must not change anything in "c:\xampp\mysql\bin\my.cnf", and the settings from picture 1 looks good.

Let us work with phpMyAdmin:
- open http://localhost/phpmyadmin/
- click on "Rechte"
- mark the Users "root@LASSP" and "root@lassp", and now at "Die ausgewählten Benutzer löschen" hit on "OK".

- now only two users are left: "pma@localhost" and "root@localhost".
- With the user "root@localhost" click on the symbol in the last column ("Rechte ändern")
- Go down to "Logininformation ändern"
- in the row "Host:" change "Lokal" to "Jeder Host.
- mark "entziehe dem alten alle Rechte und lösche ihn anschließend. "
- hit "OK"

- click on "Rechte"
- at the bottom of the page click on "die Benutzerprofile neu laden"

Now we can test this:
- open a command prompt (Eingabeaufforderung)
- go to: "cd \xampp\mysql\bin"
- login to mysql: "mysql -h localhost -u root -pyourpassword
- if this work, enter "quit".

Now a remote test:
- login to mysql: "mysql -h lassp -u root -pyourpassword" (if "lassp" is your hostname", or use the IP-address of your pc)
- if this work, enter "quit".

 

[Lay]

thanks!

now it works!