HeidiSQL cannot connect thru SSH if there is a local MySQL instance

[expired user #9442]'s profile image [expired user #9442] posted 8 years ago in Creating a connection Permalink

HeidiSQL cannot connect thru SSH if there is a running local MySQL instance while other tools like DBeaver or SQLYog can connect thru same SSH settings.

Here my test: On Win8.1 x64, my MySQL is running on local. When try to connect a remote server I see the below line in HeidiSQL log window:

plink.exe -ssh root@212.2.XXX.XXX -P 22 -i "myprivatekey.ppk" -N -L 3307:127.0.0.1:3306

But it fails with

Access denied for user 'root'@'localhost' (using password: YES)

Because somehow it tries to connect my local instance and its password is different.

Here what I tried so far:

  • If I stop local instance then it works.
  • If I execute the plink command on a DOS window first then HeidiSQL can connect without problem.
  • I changed the local MySQL port from 3306 to something else (3310) HS still tries to connect the local one first and fails.
  • I enabled named pipe and shared memory on local instance.
  • I disabled the TCP on local instance, still same (access denied, it still somehow connected to my local instance)
  • I can connect with other db tools like MySQL Workbench etc.

And one last thing, I remember that this was working a few months ago but after one update this problem occurred. (don't remember which update)

This makes almost impossible to use HeidiSQL for me because I need constantly stop/start my local MySQL. Any idea why this happens?

Thanks in advance!

[expired user #9861]'s profile image [expired user #9861] posted 8 years ago Permalink

I managed to access a remote MySQL instance via a SSH tunnel by launching plink via command line (I mean, in cmd.exe, not in HeidiSQL):

plink.exe -ssh -l MySshUsername MyRemoteHost -P 22 -i "MyPrivateKey.ppk" -N -L 127.0.0.1:3307:127.0.0.1:3306 -noagent

Then I just told HeidiSQL to connect to a normal MySQL instance ("MySQL (TCP/IP)" and not "MySQL (SSH Tunnel)" setting) on to the port 3307 (see attachment).

1 attachment(s):
  • heidisql-ssh-tunnel
[expired user #9442]'s profile image [expired user #9442] posted 8 years ago Permalink

Thank you mlocati,

I also do the same thing, but I have many servers to connect so it is still annoying. I also wonder how other applications handle it properly.

I also wrote a batch script to select which server I want to connect:

openlink.bat:

@ECHO OFF
ECHO Plink to (1) 212.xx.yy.1
ECHO Plink to (2) 212.xx.yy.2
ECHO Plink to (3) 212.xx.yy.3
ECHO Plink to (4) 212.xx.yy.4
ECHO Plink to (5) 212.xx.yy.5
ECHO Plink to (6) 212.xx.yy.6
CHOICE /M "SSH To" /C 123456
IF ERRORLEVEL 1 SET url=root@212.xx.yy.1
IF ERRORLEVEL 2 SET url=root@212.xx.yy.2
IF ERRORLEVEL 3 SET url=root@212.xx.yy.3
IF ERRORLEVEL 4 SET url=root@212.xx.yy.4
IF ERRORLEVEL 5 SET url=root@212.xx.yy.5
IF ERRORLEVEL 6 SET url=root@212.xx.yy.6
ECHO Connecting to %url%
@ECHO ON
plink.exe -ssh %url% -i "<path>\myprivatekey.ppk" -N -L 3307:127.0.0.1:3306
[expired user #9442]'s profile image [expired user #9442] posted 7 years ago Permalink

I should recall this issue, as this is definitely a bug.

Here is what I tested:

I have a local MySQL instance on TCP port 3306.

I have several remote MySQL servers.

When I try to connect remote servers with correct SSH settings, I can see that plink.exe runs in background and makes a tunnel from my local 3307 to remote's 3306, and I can telnet to localhost:3307 and see that it goes to the remote server.

But! Heidi SQL connects to my local instance on 3306, not to remote server thru 3307.

It can connect only if I stopped my local instance.

But other tools (SQLYog, DBeaver, Workbench etc.) can connect with the same settings.

[expired user #9442]'s profile image [expired user #9442] posted 7 years ago Permalink

I finally solved this issue,

I've commented below lines in [mysqld] section on my.ini file:

[mysqld]
# skip-networking=1
# enable-named-pipe=1
# shared-memory=1
# shared-memory-base-name=MYSQL
# socket=MYSQL

then HeidiSQL could connect to remote servers thru SSH while my local server is running.

So, probably there is an issue inside code, that tries first to connect thru shared memory (or pipe, or socket) before SSH. I can live with it.

Please login to leave a reply, or register at first.