2

This seems like a very specific issue but we are running into a problem connecting to a remote database when using MYSQL workbench. Workbench can't make the connection and gives me the error

Lost connection to MySQL server during query

I have tested the same credentials though a command line and it makes the connection without issue. In addition I tried using another Program (DBeaver) and same thing, I was able to connect without issue. So It seems like it is an issue with MYSQL workbench.

Things get a little complicated because I am connecting to ProxySql server (Which is why the port is 6033 instead of 3306) which is directing to the Server with the database on it. I've checked the install and it is working the way it's expected. But I don't understand why this would have any impact on my ability to connect from Workbench. If I can connect from the command line shouldn't I be able to connect from Workbench?

Edit: I set the logs to debug for more detailed output

This is what gets logged when i try to make a connection.

11:41:57 [DB3][SQL Editor Form]: Creating KeepAliveInterval timer...
11:41:57 [DB1][ mforms managed]: Looking up password for service: Mysql@IPADDRESS:6033, account: fuser
11:41:57 [DB1][ mforms managed]: Loading password cache
11:41:57 [DB1][ mforms managed]: Get special folder
11:41:57 [DB2][ mforms managed]: Decrypting password data
11:41:57 [DB2][ mforms managed]: Filling password cache
11:41:57 [DB1][ mforms managed]: Unloading password cache
11:41:57 [DB1][ mforms backend]: Looking up password for 'fuser'@'Mysql@IPADDRESS:6033' has succeeded
11:41:57 [DB1][ mforms managed]: Running a cancelable wait message
11:41:57 [DB3][    WQE backend]: Connecting SQL editor...
11:41:57 [DB3][       GRT task]: Sending task "execute sql queries" to dispatcher (wait)...
11:41:57 [DB1][  GRTDispatcher]: starting worker thread
11:41:57 [DB1][  GRTDispatcher]: worker thread running
11:41:57 [DB3][  GRTDispatcher]: Running task "execute sql queries"
11:41:57 [DB2][SQL Editor Form]: Notifying server state change of Mysql@IPADDRESS:6033 to not running
11:41:57 [ERR][SQL Editor Form]: SqlEditorForm: exception in do_connect method: Exception: Lost connection to MySQL server during query
11:41:57 [WRN][SQL Editor Form]: Lost connection to MySQL server during query
11:41:57 [ERR][SQL Editor Form]: Connection failed but remote admin does not seem to be available, rethrowing exception...
11:41:57 [ERR][  GRTDispatcher]: exception in grt execute_task, continuing: Exception: Lost connection to MySQL server during query
11:41:57 [DB3][  GRTDispatcher]: Task "execute sql queries" finished
11:41:57 [ERR][  GRTDispatcher]: worker: task 'execute sql queries' has failed with error:.Lost connection to MySQL server during query
11:41:57 [DB2][ mforms managed]: HUD window returned with code: 3
11:41:57 [DB2][ mforms backend]: forgetting cached password for 'fuser'@'Mysql@IPADDRESS:6033'
11:41:57 [DB2][  GRTDispatcher]: Main thread waiting for background thread to finish
11:41:57 [DB2][  GRTDispatcher]: Background thread finished
11:41:57 [ERR][SQL Editor Form]: SQL editor could not be connected: Lost connection to MySQL server during query
11:41:57 [ERR][SQL Editor Form]: Your connection attempt failed for user 'fuser' to the MySQL server at IPADDRESS:6033:
  Lost connection to MySQL server during query

Please:
1 Check that MySQL is running on address IPADDRESS
2 Check that MySQL is reachable on port 6033 (note: 3306 is the default, but this can be changed)
3 Check the user fuser has rights to connect to IPADDRESS 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 IPADDRESS connecting from the host address you're connecting from
11:41:57 [DB1][ mforms managed]: Showing an error to the user
11:41:57 [DB1][ mforms managed]: Hiding the wait message
11:41:57 [DB2][ mforms managed]: Wait message was not visible, nothing to do
11:41:57 [DB1][ mforms managed]: About to show a custom message box
11:41:57 [DB1][ mforms managed]: Creating and showing custom message box
11:41:57 [DB1][ mforms managed]: Running custom message box
11:41:58 [DB1][ mforms managed]: Custom message box closed
11:41:58 [DB2][            grt]: wb.form.showQueryConnectDialog finished in 3.96s

Any help would be greatly appreciated.

nickc
  • 1,193
  • 1
  • 6
  • 15
  • check out these links [link1](https://stackoverflow.com/a/12790569/10342514), [link2](https://stackoverflow.com/a/15712616/10342514) – James Jan 10 '20 at 05:48
  • No luck. I set all these numbers high and restarted Workbench but nothing changes. I also get the error immediately, It doesn't hang for a couple seconds then error. It just spits back the error. – nickc Jan 10 '20 at 18:25
  • Have you tried reading the link on ssh keep alive? Since you have mentioned about proxy. just saying though – James Jan 13 '20 at 04:19
  • I tried upping the migration time and all of the ssh connection timeout fields. Still can't get a connection. I have updated the log output with the more detailed setting if anything in there can be of any use. – nickc Jan 13 '20 at 16:51
  • I am also facing a similar issue. I can connect to the DB through putty terminal but cannot connect via MySQL workbench. More surprising is that I am able to connect through admin user via workbench but not through any other user. But all users working fine in putty – Nitin G Jul 20 '20 at 14:36

2 Answers2

0

In my case, my mysql database is running on docker, and when I create user using following statement: CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

this give me error when I try to connect to the database from host client using DBeaver.

I have to create a user with follow to make it work. CREATE USER 'username'@'%' IDENTIFIED BY 'password';

% is the wildcard which allow user to access database remotely from any host.

Dharman
  • 30,962
  • 25
  • 85
  • 135
JS84
  • 221
  • 3
  • 4
  • 1
    As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Sep 29 '21 at 20:34
-1

i am here to help u. So, i have been facing these problems a year ago when i was setting up MySQL server in my development computer and faced an endless number of issues on connecting to MySQL, especially using the workbench. So, i think i am capable of answering this. So, if u are able to connect to MySQL server using Command-Line then the expected problems that might be are :

  1. Unmatched version compatibility : (if your MySQL server version is 8.0., your MySQL Shell is 8.0 but MySQL WorkBench version is not 8.0). In that case, you might need to check your versions and upgrade them to the same version.

  2. Check if MySQL workbench has closed an existing connecting and is trying a new connection : (try to close your existing connection and try to open a new connection with login credentials)

  3. Check if password is correct: Sometimes, password gets auto changed in workbench due to its stored cache

  4. Lastly, check the connection type(TCP/ssh) : Choose suitable connection type

  5. Sorry for you but you might need to take a look at your loadbalancer configuration and permissions for your port accessibility

codechamp2006
  • 1
  • 1