Remote access fails

There's quite a few similar posts on this subject but I've found none that matches mine so here goes:

I have two instances of mysql running, one on my local machine and one on my freenas server. Heidi connects to the local instance just fine but connecting to freenas returns the following:

SQL Error (2013):Lost connection to MySQL server at 'reading initial communication packet', system error: 0

I can connect to the freenas instance via ssh with several clients such as kitty or putty and even running plink in a command window with the following parameters:

plink -P 22 -l <freenas user> -pw <freenas user password> <freenas ip>

Once the freenas prompt appears I can connect to mysql just fine with no restrictions.

My Heidi connection parameters are as follows:

In the Settings tab:
Network type: SSH tunnel
Hostname/IP: <freenas ip>
User: <mysql user>
Password: <mysql user password>
Port: 3306
Compressed client/server protocol is left unchecked

In the SSH tunnel tab:
plik location is correct
SSH host + port: <freenas IP address> / 22
Username: <freenas username>
Password: <freenas username password>
Private key file is left empty
Local port: 3306

I've tried putting the mysql user name and password in the SSH tab and the freenas user name and password in the Settings tab but then I get the following error:

SQL Error (1045): Access denied for user '<mysql user>'@'localhost'(using password: YES)

And yet all parameters are correct, at least it would appear so since I can connect to mysql with those same parameters outside Heidi.

My Heidi version is 5.1.0.3531. Any ideas?

Just some random guess found here: Do you have MySQL set up to use some socket? Must be in your my.cnf/ini and looks like this:
[mysqld]
socket=mysql.sock

Another one: Prefer the hostname over the IP address of your freenas server.

Examining your SSH tunnel settings I found you have set the local port to 3306, which may conflict with your local MySQL installation. Try using 3307 or something else.

Thanks for the quick reply Anse. Re your first post I'm not sure where the mysql configuration is kept but it's not in my.cnf/ini. There's no such file anywhere in the system partition. As for the hostname I've tried that too and it doesn't work any better either, neither does port 3307.

I have the feeling there's some initial config value that's interfering since logging on to freenas via ssh allows me to access mysql. I'll be researching the location of this elusive config file but in the meantime any other ideas will be very welcome.

Cheers and keep up the good work.

Ok, new day, new ideas...

Re my.cnf: I created one out the my-large.cnf I found in the installation directory and specified port 3307. I also changed the port in the Heidi connection parameters to 3307. The mysql.sock file is where it's supposed to be. Unfortunately error 2013 is still the result.

I meant the mysql.sock file could have caused your problem - but now I'm unsure if it was there all the time? Try removing it, or remove the line out your my.cnf .

Further, if you have your local MySQL now running on port 3307, try reverting that and only use 3307 as the local port in Heidi's SSH settings. But I guess you already tried this combination.

Ok, if you after all are sure your SSH settings are right, you should now get again

Lost connection to MySQL server at 'reading initial communication packet'

No real new ideas here currently, just some URLs for you:
http://bugs.mysql.com/bug.php?id=9752
http://forum.qnap.com/viewtopic.php?t=11379

Ah, and I read somewhere about error 2013 that this can be due to being low on disk size - MySQL needs some space for its key buffer and other things, can even be some gigabytes, so make sure your harddrive has a minimum of let's say 10gb.

Hello Anse,

Still working on this and have some new info. I'm now using an Ubuntu 10.10 server instead of the old FreeNas server. I can now access the server database with Heidi but only if I open an SSH session from the client computer and then start Heidi. Otherwise I get the same error as before. Go figure. Any ideas?

Sam.

I've now solved this issue and it was pretty simple. I don't know why I didn't think of this earlier but sometimes you just start going down one path and ignore the alternatives. My mistake was in trying to connect via SSH. But if I use just straight TCP/IP all I had to do was create a user in mysql like this:
create user '<username>'@'%' identified by '<password>';
grant all on *.* to '<username>'@'%' identified by '<password>';

Then in Heidi's session manager my server session is as follows:
Network type: TCP/IP
Hostname/IP:<server IP address>
User:<username>
Password:<password>
Port:3306

And that's it. Now when I start Heidi both server instances are started and I can jump between them seamlessly. Fantastic!

in that way you expose the password to the internet :P some one with a sniffer on your lan or while in a man-in-the-middle could find the password

:P

I like better ssh tunneling when login to a remote server

You're absolutely right. However as I've already posted the SSH route using plink hasn't worked for me. Besides my network is private - no outside access. Still, if you have suggestions re: my ssh problems I'd love to read them.