Remote MySQL Connection Via SSH Failure

[techotaku]

I'm trying to establish a remote MySQL connection through SSH in
Sequel Pro, but the connection fails with the message: "MySQL said:
Lost connection to MySQL server at 'reading initial communication
packet', system error: 0"

The FAQ suggests I edit the MySQL config file: /etc/my.conf but I
assume it's referring to the remote version to which I don't have
access.

Interestingly, I can establish a remote MySQL connection through SSH
in Terminal using the following comands:

ssh -L13306:127.0.0.1:3306 user@hostname

then

mysql -h database -u mysql_user -p

I'm also able to establish a standard remote MySQL connection in
Sequel Pro.

I'm sure I'm entering the wrong parameters in Sequel Pro, but am not
sure which are the correct ones. Any help would be gratefully
appreciated.

Regards.

[Jakob Egger]

If you type 'ssh -L13306:127.0.0.1:3306 user@hostname' and then immediately 'mysql -h database -u mysql_user -p', then you are doing the following
1) you are connecting via SSH to hostname
2) You are connecting from the host 'hostname' to the host 'database'
3) you are not actually using the SSH tunnel you opened with '-L13306:127.0.0.1:3306'


If this works, then you should probably type the following into Sequel pro:
MySQL Host: whatever you wrote after -h in the second command ('database' in the example you gave)
Username: mysql_user
Password: the mysql password
Database: leave blank
Port: 3306
SSH Host: hostname
SSH User: user
SSH Password: the ssh password
SSH Port: the ssh port


Please see http://www.sequelpro.com/docs/Connecting_to_a_MySQL_Server_on_a_Remote_Host for more details. Look at the diagrams to see what is going on.


Best regards,
Jakob

> --
> You received this message because you are subscribed to the Google Groups "Sequel Pro" group.
> To post to this group, send email to seque...@googlegroups.com.
> To unsubscribe from this group, send email to sequel-pro+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sequel-pro?hl=en.
>

[techotaku]

Hi Jakob,

Thanks for the info. I've dropped the SSH tunnel from my Terminal command as I can see now that's superfluous. It works as expected.

Your suggestion on how to configure the Sequel Pro connection is exactly what I've been using resulting in the connection error I detailed in my initial post.

Interestingly, I've used a couple of other clients: Navicat and MySQL Workbench and the same error occurs.

Any other ideas would be greatly appreciated.

Reagrds, Steve.

[Rowan Beentje]

Hi Steve,

When you run the command-line mysql, you're running it from the server you're SSHed into; all the clients you're seeing failures are having problems connecting via the tunnel. So to see if the command line test works, you'd want to do:
1) ssh -L 13306:database:3306 user@hostname
2) On your *local* machine, mysql -h 127.0.0.1 -P 13306 -u mysql_user -p

Of course, you may not have a mysql binary available locally, but I'd bet that would also fail.

Debugging this is tough; typically something is stopping the client from talking to the server at the network level, but if you can successfully connect from the machine you're SSHing into, it's hard to see what the difference could be. How does "database" resolve remotely? Does that map to an IP address, or is "database" something set up specially on the server you're SSHing into, do you know?

Rowan

> --
> You received this message because you are subscribed to the Google Groups "Sequel Pro" group.

> To view this discussion on the web visit https://groups.google.com/d/msg/sequel-pro/-/V1Hhg8bzqxcJ.

[techotaku]

Hi Rowan,

Thanks for your reply. I did as you suggested and you're right.  When entering mysql -h 127.0.0.1 -P 13306 -u mysql_user -p on my local machine under XAMPP I get the message: "Lost connection to MySQL server at 'reading initial communication packet', system error: 0". The same message reported by Sequel Pro.

Shortly after the Terminal window showing the remote connection displays the message "channel 3: open failed: administratively prohibited: open failed". This same message is displayed in the remote connection's Terminal window if I try connecting using the SSH config in Sequel Pro. Looks like my hosting provider is limiting access.

Regards, Steve.

On Tuesday, July 3, 2012 3:39:59 PM UTC+9, techotaku wrote:

[techotaku]

I found this article about the "channel 3: open failed: administratively prohibited: open failed" error. It's possible that the /etc/ssh/sshd_config file on the remote machine needs to include PermitTunnel yes. In mine, PermitTunnel no is commented-out, but it's not explicitly set to yes. I don't have write access to my remote /etc/ssh/sshd_config so I can't test it, but it may prove useful to someone.

Regards, Steve.

[WyattBiker]

Did you enable remote access? in my.cnf put this line

bind-address=127.0.0.1

[_itsjohn]

Has there been any issues yet. I've attached a ss just to make sure if you guys are getting this as well.

john

[aristidesfl]

I have this problem as well.

I can connect if I establish the ssh tunnel manually (ssh -L 3306:127.0.0.1:3306 miner.home), but I can't make Sequel Pro establish the tunnel from for me.

These are the setting I'm using:

MySQL Host: 127.0.0.1

Username: root

Password: *****

SSH

[aristidesfl]

I have the same problem.

I can connect if I create the tunnel manually (ssh -L 3306:127.0.0.1:3306 us...@miner.home), but I can't make Sequel Pro establish the tunnel for me.

Actually I think Sequel Pro is connecting to ssh successfully, but it doesn't connect to the database.

Here are the settings I'm using:

MySQL Host: 127.0.0.1

Username: root

Password: **********

SSH Host: miner.home

SSH User: user

SSH Password: ****** 

The MySQL install is fresh on Ubuntu 12.04 with no configurations modified.

Sequel Pro is 0.9.9.1

Any help on this would be very appreciated!

On Saturday, July 21, 2012 11:26:43 PM UTC+1, _itsjohn wrote:

[Xavier PEYRONNET]

Hello, same issue here.

Everything is OK through the terminal, same parameters used under Sequel Pro unfortunately no connexion available ....

I will be crazy sooner that expected.

[Jhon Bauwens]

Same problem here...

Unable to connect to host 127.0.0.1, or the request timed out.

Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).

MySQL said: Lost connection to MySQL server at 'reading initial communication packet', system error: 0

[Tyler Freeman]

I have had to deal with a weird issue a couple of times where all of the sudden, my SequelPro access to a remote server would no longer be able to connect. And would give this error:

>Unable to connect to host 127.0.0.1, or the request timed out.

>Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).

>MySQL said: Lost connection to MySQL server at 'reading initial communication packet', system error: 0

It turns out that the tunnel was failing because the remote server still had an ssh connection open. I was able to diagnose with this command on my local machine:

ssh -L13306:127.0.0.1:3306 user@hostname

which gave me this error:


>Warning: remote port forwarding failed for listen port 52698


I then ran this command on the remote server to find the process id:


sudo netstat -antpl | grep 52698

once I had killed the process on the remote server (XXX is the process id):

sudo kill -9 XXXX

I was able to connect once more. Perhaps the above error message could be amended to reflect this possible cause?

[Ben M]

Tyler - were you using rmate in a setup similar to this: https://stackoverflow.com/questions/15958056/how-to-use-sublime-over-ssh/18538531#18538531 ?

When I commented out the RemoteForward line in my local ssh config, Sequel Pro was able to properly form the tunnel and connect to mysqld.

If I run 'ssh -L 3306:localhost:3306 myserver', even with the RemoteForward line in place in ~/.ssh/config, I am able to connect to mysqld with a standard connection in Sequel Pro, and I get the '>Warning: remote port forwarding failed for listen port 52698' warning also. I think you get the warning when you already have another session open and tunneling for that port. Since my config tries to tunnel port 52698 for all connections, I get that warning for any ssh sessions started while another session is already up.

So the fix in this case for me was to comment out the RemoteForward in the config file, or to make the tunnel manually and then use a standard connection in Sequel Pro. If I come across a way to get Sequel Pro to play nicely with the RemoteForward line in my ~/.ssh/config file, I'll post an update here.

-Ben

[Ben M]

Followup to my post that is now pending review: there is an issue on github for this at https://github.com/sequelpro/sequelpro/issues/1869

Looks like they're aware. The cause is that the warning about the failed port forwarding for the port Sequel Pro doesn't care about causes Sequel Pro to give up on the connection.

-Ben

On Wednesday, May 21, 2014 9:22:34 PM UTC-5, Tyler Freeman wrote:

[Tyler Freeman]

Ben - 

I don't have that setup. It usually seems to happen when my internet gets disconnected suddenly. It seem like the server doesn't kill the process listening to the port for a while and so I have to go in manually and kill it.

Tyler