MySQL Bugs: #87740: cannot connect to server via standard tcp/ip over ssh

Bug #87740	cannot connect to server via standard tcp/ip over ssh
Submitted:	12 Sep 2017 13:45	Modified:	28 Sep 2017 2:50
Reporter:	Aihua Wang	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Workbench	Severity:	S1 (Critical)
Version:	6.3.9	OS:	MacOS (macOS 10.12.x Sierra x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
When connecting to mysql server via standard tcp/ip over ssh, the server is not accessed.
However, we connect it using Navicat in the same way.

The log is following, ssh.foo.com:6022 is ssh server and its port, 'dba' and 'db.foo.com' is the database user and the database server. You can replace them with real one.

20:53:15 [INF][     SSH tunnel]: Starting tunnel
20:53:15 [INF][     SSH tunnel]: Existing SSH tunnel not found, opening new one
20:53:28 [INF][     SSH tunnel]: Opening SSH tunnel to ssh.foo.com:6022
20:53:29 [INF][     SSH tunnel]: TunnelManager.wait_connection returned OK
20:53:29 [INF][     SSH tunnel]: SSH tunnel connect executed OK
20:53:29 [ERR][  GRTDispatcher]: exception in grt execute_task, continuing: Exception: Access denied for user 'dba'@'db.foo.com' (using password: NO)
20:53:29 [ERR][  GRTDispatcher]: worker: task 'execute sql queries' has failed with error:.Access denied for user 'dba'@'db.foo.com' (using password: NO)
20:53:46 [ERR][SQL Editor Form]: SqlEditorForm: exception in do_connect method: Exception: Can't connect to MySQL server on '127.0.0.1' (61)
20:53:46 [WRN][SQL Editor Form]: Can't connect to MySQL server on '127.0.0.1' (61)
20:53:46 [ERR][SQL Editor Form]: Connection failed but remote admin does not seem to be available, rethrowing exception...
20:53:46 [ERR][  GRTDispatcher]: exception in grt execute_task, continuing: Exception: Can't connect to MySQL server on '127.0.0.1' (61)
20:53:46 [ERR][  GRTDispatcher]: worker: task 'execute sql queries' has failed with error:.Can't connect to MySQL server on '127.0.0.1' (61)
20:53:46 [ERR][    WQE backend]: Got an exception during connection: Can't connect to MySQL server on '127.0.0.1' (61)
20:53:46 [ERR][SQL Editor Form]: SQL editor could not be connected: Can't connect to MySQL server on '127.0.0.1' (61)
20:53:46 [ERR][SQL Editor Form]: Your connection attempt failed for user 'dba' from your host to server at db.foo.com:3306:
  Can't connect to MySQL server on '127.0.0.1' (61)

How to repeat:
1) From mysql workbench, setup new connection;
2) Input 'Connection Name';
3) Select 'Standard TCP/IP over SSH from 'Connection Method';
4) Input 'ssh.foo.com:6022' in 'SSH Hostname';
5) Input 'developer' in 'SSH Username';
6) Input 'db.foo.com' in 'MySQL Hostname';
7) Input '3306' in 'MySQL Server Port';
8) Input 'dba' in 'Username';
9) Test Connection.

connection settings and errors

Attachment: Screen Shot 2017-09-12 at 20.54.01.png (image/png, text), 187.79 KiB.

connection settings

Attachment: settings.png (image/png, text), 310.70 KiB.

connection errors

Attachment: errors.png (image/png, text), 187.79 KiB.

Hello !

Thank you for the report.
This issue is seen

-- MySQL User 'ushastry2' doesn't exists, create a new connection using "Standard TCP/IP over SSH Connection" Method, you can either opt for ssl keypair or provide credentials to used SSH user(tried both ways, and no issue found). With invalid MySQL user 'ushastry2' I'm seeing the errors which you observed.

---
14:35:19 [INF][     SSH tunnel]: Existing SSH tunnel not found, opening new one
14:35:23 [INF][     SSH tunnel]: Opening SSH tunnel to x.x.xx.com:22
14:35:25 [INF][     SSH tunnel]: TunnelManager.wait_connection returned OK
14:35:25 [INF][     SSH tunnel]: SSH tunnel connect executed OK
14:35:27 [ERR][  GRTDispatcher]: exception in grt execute_task, continuing: Exception: Access denied for user 'ushastry2'@'localhost' (using password: NO)
14:35:27 [ERR][  GRTDispatcher]: worker: task 'execute sql queries' has failed with error:.Access denied for user 'ushastry2'@'localhost' (using password: NO)
14:35:31 [ERR][SQL Editor Form]: SqlEditorForm: exception in do_connect method: Exception: Can't connect to MySQL server on '127.0.0.1' (61)
14:35:31 [WRN][SQL Editor Form]: Can't connect to MySQL server on '127.0.0.1' (61)
14:35:31 [ERR][SQL Editor Form]: Connection failed but remote admin does not seem to be available, rethrowing exception...
14:35:31 [ERR][  GRTDispatcher]: exception in grt execute_task, continuing: Exception: Can't connect to MySQL server on '127.0.0.1' (61)
14:35:31 [ERR][  GRTDispatcher]: worker: task 'execute sql queries' has failed with error:.Can't connect to MySQL server on '127.0.0.1' (61)
14:35:31 [ERR][    WQE backend]: Got an exception during connection: Can't connect to MySQL server on '127.0.0.1' (61)
14:35:31 [ERR][SQL Editor Form]: SQL editor could not be connected: Can't connect to MySQL server on '127.0.0.1' (61)
14:35:31 [ERR][SQL Editor Form]: Your connection attempt failed for user 'ushastry2' from your host to server at 127.0.0.1:3306:
  Can't connect to MySQL server on '127.0.0.1' (61)

Please:
1 Check that mysql is running on server 127.0.0.1
2 Check that mysql is running on port 3306 (note: 3306 is the default, but this can be changed)
3 Check the ushastry2 has rights to connect to 127.0.0.1 from your address (mysql rights define what clients can connect to the server and from which machines) 
4 Make sure you are both providing a password if needed and using the correct password for 127.0.0.1 connecting from the host address you're connecting from

In my case, I attempted to connect with the non-existing user 'ushastry2'.
But, I'm able to connect the instance using valid MySQL user (when mysql user password is stored in keychain then no access denied entry logged, but if you are entering then it can be seen):

-- MySQL User 'ushastry' exists

14:40:46 [INF][SQL Editor Form]: Opened connection 'x.x.xx.com' to MySQL Community Server (GPL) version 5.7.19
14:42:30 [INF][     SSH tunnel]: Existing SSH tunnel not found, opening new one
14:42:34 [INF][     SSH tunnel]: Opening SSH tunnel to x.x.xx.com:22
14:42:36 [INF][     SSH tunnel]: TunnelManager.wait_connection returned OK
14:42:36 [INF][     SSH tunnel]: SSH tunnel connect executed OK
14:42:46 [INF][SQL Editor Form]: Opened connection 'x.x.xx.com' to MySQL Community Server (GPL) version 5.7.19

## System info

MySQL Workbench Community (GPL) for Mac OS X version 6.3.9 CE build 10690321 (64 bit)
Configuration Directory: /Users/umshastr/Library/Application Support/MySQL/Workbench
Data Directory: /Applications/MySQLWorkbench.app/Contents/Resources
Cairo Version: 1.10.2
OS: macOS 10.12.x Sierra x86_64
CPU: 8x Intel(R) Core(TM) i7-3615QM CPU @ 2.30GHz - 4.00GiB RAM
No video adapter info available

Thanks,
Umesh

Hi,

Our mysql is not running on server 127.0.0.1 for some reason. We think it should connect to the ssh host address not 127.0.0.1 when connect to mysql over ssh tunnel. Meanwhile using navicat, we can connect to the same mysql server over ssh in the same way.