MySQL Bugs: #78947: Cannot Connect to Database Server: Unsupported option provided to mysql

Bug #78947	Cannot Connect to Database Server: Unsupported option provided to mysql_options(
Submitted:	23 Oct 2015 15:16	Modified:	10 Dec 2015 17:32
Reporter:	Adam Wortmann	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	6.3.5	OS:	Windows (7 Pro)
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
Connecting to a database server using connection method "Standard TCP/IP over SSH" (private key authentication) with "Use old authentication protocol" enabled. Upon attempt to connect to database server, get error:

Cannot Connect to Database Server

Your connection attempt failed for user 'username' from your host to server at 'server name':3306:
Unsupported option provided to mysql_options()

Please:
1 Check that mysql is running on 'server name'
2 Check that mysql is running on port 3306...
3 Check that username has rights to connect to...
4 Make sure you are both providing a password if needed...

The issue occurs with connecting to MySQL server version 5.0.90 and 5.5.18. When deselecting the option "Use the old authentication protocol", I receive a Malformed packet error message.

I was able to connect properly with version 6.3.4 and was able to connect again properly after downgrading back to 6.3.4 from 6.3.5. I tried both repairing my 6.3.5 installation as well as uninstalling it completely and reinstalling. Also try creating a new connection in the MySQL Connections area.

How to repeat:
1 Upgrade to 6.3.5
2 Attempt to connect to MySQL server

This also fails in the same way using:

Workbench 6.3.5 on Centos 7.

connecting to:

mysql-5.5.46 on a Centos 5.11 server.

I note that I haven't updated Workbench since before the new Connector/C++ came out in June (since my gut says "Connector" -- I'd test, but I can't figure out what to click to get the source for 1.1.5 anymore).

http://bugs.mysql.com/bug.php?id=78948 marked as duplicate of this one.

Hello Adam Wortmann,

Thank you for the report.
While connecting MySQL 5.0.96 With WB 6.3.4(on Win7) + Use old authentication protocol, able to connect(just warns about some features not available).

While connecting MySQL 5.0.96 With WB 6.3.4(on Win7) + Use old authentication protocol, exception is popped up and in in WB log:

12:56:13 [INF][      Workbench]: UI is up
12:56:14 [INF][      Workbench]: Running the application
12:56:17 [INF][     SSH tunnel]: Starting tunnel
12:56:17 [INF][     SSH tunnel]: Existing SSH tunnel not found, opening new one
12:56:21 [INF][     SSH tunnel]: Opening SSH tunnel to x.x.x.x:22
12:56:24 [INF][     SSH tunnel]: TunnelManager.wait_connection returned OK
12:56:24 [INF][sshtunnel.py:set_keepalive:502]: SSH KeepAlive setting skipped.
12:56:24 [INF][     SSH tunnel]: SSH tunnel connect executed OK
12:56:24 [ERR][      SqlEditor]: SqlEditorForm: exception in do_connect method: Exception: Unsupported option provided to mysql_options()
12:56:24 [ERR][  GRTDispatcher]: exception in grt execute_task, continuing: Exception: Unsupported option provided to mysql_options()
12:56:24 [ERR][  GRTDispatcher]: worker: task 'execute sql queries' has failed with error:.Unsupported option provided to mysql_options()
12:56:24 [ERR][    WQE backend]: Got an exception during connection: Unsupported option provided to mysql_options()
12:56:24 [ERR][      SqlEditor]: SQL editor could not be connected: Unsupported option provided to mysql_options()
12:56:24 [ERR][      SqlEditor]: Your connection attempt failed for user 'root' from your host to server at localhost:15000:
  Unsupported option provided to mysql_options()

Please:
1 Check that mysql is running on server localhost
2 Check that mysql is running on port 15000 (note: 3306 is the default, but this can be changed)
3 Check the root has rights to connect to localhost 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 localhost connecting from the host address you're connecting from
12:58:14 [INF][     SSH tunnel]: Existing SSH tunnel found, connecting
12:58:24 [INF][      SqlEditor]: Opened connection 'x.x.x.x' to MySQL Community Server (GPL) version 5.0.96
12:58:25 [INF][     AutoCCache]: Initializing autocompletion cache for x.x.x.x
12:58:27 [ERR][      WBContext]: Unsupported server version: MySQL Community Server (GPL) 5.0.96
12:58:30 [INF][        WQE.net]: Launching SQL IDE
12:58:31 [INF][        WQE.net]: SQL IDE UI is ready
13:01:16 [INF][     SSH tunnel]: Existing SSH tunnel found, connecting
13:01:17 [ERR][      SqlEditor]: SqlEditorForm: exception in do_connect method: Exception: Unsupported option provided to mysql_options()
13:01:17 [ERR][  GRTDispatcher]: exception in grt execute_task, continuing: Exception: Unsupported option provided to mysql_options()
13:01:17 [ERR][  GRTDispatcher]: worker: task 'execute sql queries' has failed with error:.Unsupported option provided to mysql_options()
13:01:17 [ERR][    WQE backend]: Got an exception during connection: Unsupported option provided to mysql_options()
13:01:17 [ERR][      SqlEditor]: SQL editor could not be connected: Unsupported option provided to mysql_options()
13:01:17 [ERR][      SqlEditor]: Your connection attempt failed for user 'root' from your host to server at localhost:15000:
  Unsupported option provided to mysql_options()

Thanks,
Umesh

Screenshot..

Attachment: 78947.png (image/png, text), 31.39 KiB.

Also happens in Ubuntu 14.04.
Should I revert to 6.3.4?

Same issue on Windows 10

Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 6.3.6 release, and here's the changelog entry:

The "mysql_old_password" protocol is no longer supported by Workbench, as
its support was removed in MySQL Server 5.7. This affects the former "Use
old authentication protocol" SSH connection option.

Thank you for the bug report.

Hi,

Can you clarify the change log please.  Is the problem of logging in to certain versions of MySQL being fixed or are you simply removing the checkbox and leaving the missing feature out and therefore making it not compatible with those versions of MySQL?

I also see this problem on MySQL v5.0.95

For me it worked when I downgraded to 6.3.4

6.3.4 works for me too. click 'use the old authentication protocol' under advance tab

Unfortunately the older versions of WB are not readily available for the latest linux distributions such as Ubuntu 16.04 and aren't likely to be anytime soon.  There is no 6.3.4 build and the generic doesn't compile.

This is problematic given that very recent server distros like CentOS 6.6 don't natively support anything later than MySQL 5.1.  Granted these are not the latest versions but one of the benefits of using Linux is its stability over time.  These legacy distributions abound and upgrading production servers is not always an option or under the control of the WB user.

It is likely that the number of legacy MySQL servers will diminish slowly and plateau over the next few years.  Given all of this it seems that removing a legacy login at this point is premature.  Many of us work with a multitude of different distributions both on our local and server environments and rely on such tools when doing heavy workloads.  Intentionally eliminating backward compatibility before the number of legacy systems has been significantly reduced because they are "not supported" seems like a bit of the tail trying to wag the dog.

I solved this problem (both needing the latest MySQL Workbench - and needing a legacy version) by "hacking" together a Docker image with the sole purpose of running a legacy MySQL Workbench install. See this blog post for some pointers on how to do this:
https://blog.jessfraz.com/post/docker-containers-on-the-desktop/

I am short on time so my Dockerfile just "barely works" for me - thus I find it prudent not to share it here - but I am sure someone can make a good Dockerfile to be shared for other people with the same problem.

You can download previous MySQL Workbench versions from here: http://downloads.mysql.com/archives/workbench/

The procedure to update old passwords to the new (and current) format is now
documented here:

  http://dev.mysql.com/doc/workbench/en/wb-mysql-connections-secure-auth.html

The change took place because the bundled MySQL client was upgraded to 5.7
awhile back, and MySQL 5.7 does not support the old password format. The old
format has been deprecated for a long time, since around MySQL 4.1, and
support was finally removed in 5.7. This is because the secure_auth option
can no longer be disabled. It's always on.

In other words, MySQL 4.1 and later can be upgraded to use a supported method
of password storage. Unfortunately, the old form of password storage can
still be used in newer versions, including MySQL 5.6. Upgrading MySQL itself
does not update password formats as it must be done manually, because MySQL
stores passwords as hashes instead of plain text. This is why the old format
has been around so long.

This change was an unfortunate side effect of the MySQL client upgrade.
Developers typically update it to the latest version for security reasons.
Hopefully the new documentation will help, we're sorry for the inconvenience.

So, to clarify this bug report:
 * Workbench 6.3.4 supports the old style
 * Workbench 6.3.5 does not support it, although the "Use the old
authentication protocol" option was still present. It, however, did not work.
 * Workbench 6.3.6 removed the "Use the old authentication protocol" option
because it did not work.

Again, sorry for the confusion :(