Bug #62549 MySQL Workbench can not work with MySQL server 5.0 any more
Submitted: 27 Sep 2011 11:28 Modified: 1 Dec 2011 19:03
Reporter: Valerii Kravchuk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: Administration Severity:S1 (Critical)
Version:5.2.35 OS:Microsoft Windows
Assigned to:
Tags: regression
Triage: Needs Triage: D1 (Critical)

[27 Sep 2011 11:28] Valerii Kravchuk
Description:
When I try to connect to the instance of MySQL server 5.0.91 in Administration part, connection (successfully created just before that) fails, as Workbench tires to execute:

show plugins

statement that is NOT supported by 5.0:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.91-community-nt MySQL Community Edition (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show plugins;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'plugi
ns' at line 1

This is a regression bug (probably related to recently added check for plugins in 5.1 and/or 5.5).

How to repeat:
Try to administer any version of MySQL server 5.0 with Workbench 5.2.35. You'll get error message upon connection.

Suggested fix:
Check server version before running "show plugins"?
[27 Sep 2011 14:50] Mike B
A similar error occurs when connected to MySQL Server 5.0.45-community-nt.  

"QueryError: Error executing 'You have an error in your SQL syntax;"..."near 'plugins' at line 1'
show plugins.
SQL Error: 1064"
[27 Sep 2011 17:52] Byron Palmer
I also suffer from this bug and can no longer administer my production system.
[27 Sep 2011 17:52] Byron Palmer
I also suffer from this bug and can no longer administer my production system.
[27 Sep 2011 18:17] Byron Palmer
I edited the file C:\Program Files\MySQL\MySQL Workbench 5.2 CE\modules\wb_admin_control.py and things started working.

and starting at line 570, I added a line to prevent the execution of the query to check plugins for MySQL versions starting with 5.0. Notice the indented lines that follow the if statement.

    #---------------------------------------------------------------------------
    def connect_sql(self): # from GUI thread only, throws MySQLError
        if self.sql is None or not self.sql.is_connected():
            connection = MySQLConnection(self.server_profile.db_connection_params, self.sql_status_callback)
            connection.connect()

            self.sql = SQLQueryExecutor(connection)

        if self.sql and self.sql.is_connected():
            # perform some server capabilities checking
            
            # check version
            result = self.exec_query("select version() as version")
            if result and result.nextRow():
                self.server_version= result.stringByName("version")
                self.raw_version = self.server_version
                dprint_ex(1, "Got MySQL version -", self.server_version)
            if not self.server_version.startswith("5.0."):
				result = self.exec_query("show plugins")
				# check whether Windows authentication plugin is available
				while result and result.nextRow():
					name = result.stringByName("Name")
					status = result.stringByName("Status")
					plugin_type = result.stringByName("Type")
					if status == "ACTIVE":
						self.server_active_plugins.add((name, plugin_type))

            dprint_ex(1,"Created SQL connection to MySQL server. Server version is", self.server_version, ", conn status = ", self.is_sql_connected(), ", active plugins = ", str(self.server_active_plugins))
        else:
            print "Failed to connect to MySQL server"
[27 Sep 2011 20:07] Mike B
I found it difficult to isolate the change visually, so I just want to make it a little more clear:

Line 590: "            if not self.server_version.startswith("5.0."):"
(Insert, without quotes)

Save the file, and restart workbench.
After doing this, I can use the admin tool again.
[29 Sep 2011 13:08] Sergio Andres De La Cruz Rodriguez
As stated by Byron Palmer and Mike B, there's a workaround to solve this isue. I would like to add that it would be better to also cover 4.x versions of MySQL Server.

So as they suggested:

- With your favorite text editor open the file wb_admin_control.py located in the modules directory inside the Workbench binary directory (the directory where Workbench got installed; the full path is C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\modules\wb_admin_control.py for Windows 7)

- Go to the definition of the connect_sql function in the file (line No. 571 in Workbench 5.2.35)

- Before the line that reads "result = self.exec_query("show plugins")" insert this new line:
if not (self.server_version.startswith("5.0.") or self.server_version.startswith("4.")):

- Indent to the right the 8 lines that follows the line you inserted in the previous step. The changed section should look like this:

                dprint_ex(1, "Got MySQL version -", self.server_version)
            
            if not (self.server_version.startswith("5.0.") or self.server_version.startswith("4.")):
                result = self.exec_query("show plugins")
                # check whether Windows authentication plugin is available
                while result and result.nextRow():
                    name = result.stringByName("Name")
                    status = result.stringByName("Status")
                    plugin_type = result.stringByName("Type")
                    if status == "ACTIVE":
                        self.server_active_plugins.add((name, plugin_type))
                    
            result = self.exec_query("SHOW VARIABLES LIKE 'general_log_file'")

- Save the file and now Workbench should behave as expected.
[29 Sep 2011 13:28] Shaun van Wyngaard
Unfortunately this didn't work for me.
Although it no longer gives the "Plugin" error, it briefly displays the follwing message before disappearing again, but nothing further happens:

Opening MySQL WorkBench Administrator

The MySQL Workbench Administrator for 193.1.1.52 is being opened and should be available in a moment.

Please stand by...
[29 Sep 2011 14:10] Mike B
I had the same problem. I had made a mistake while editing the file. If you can't figure out the typo, you can delete the file and rerun the installer in repair mode.  Then you can try to make the edit again.
[30 Sep 2011 5:20] Shaun van Wyngaard
Well, as you say, I can't figure out the typo. This is what I have in place now:

            if not (self.server_version.startswith("5.0"):
                result = self.exec_query("show plugins")

My server I'm trying to administrate is version 5.0.22
Using this it displayed a brief message, but then nothing else.
I am running XP 32-bit, if that helps.

I am also trying to administrate a server version 5.5.13, so I had to remove this line again, else it wouldn't allow me in either until this can be resolved.
[30 Sep 2011 5:45] Mike B
This is not my normal programming language, (Actually the first time editing) so I may be wrong, but There are 2 decimals in the version number.

if not (self.server_version.startswith("5.0."):
[30 Sep 2011 5:50] Shaun van Wyngaard
Thanks for that, but it didn't make a difference.
Still just a quick pop-up message, then nothing.
[30 Sep 2011 5:59] Mike B
If you indented the code after that line, then post the whole section.
[30 Sep 2011 6:04] Shaun van Wyngaard
This is a copy of what I have now. The line added is the 6th line from the end.
Not sure if the iundentation would affect it though, more just to neaten it.

            if result and result.nextRow():
                self.server_version= result.stringByName("version")
                self.raw_version = self.server_version
                dprint_ex(1, "Got MySQL version -", self.server_version)
            
            if not (self.server_version.startswith("5.0."):
                result = self.exec_query("show plugins")
            # check whether Windows authentication plugin is available
            while result and result.nextRow():
                name = result.stringByName("Name")
                status = result.stringByName("Status")
[30 Sep 2011 6:09] Mike B
Like I said, I am new to this, but I believe indentation is important.  Using Google...."the exact amount of indentation doesn't matter at all, but only the relative indentation of nested blocks (relative to each other). "
[30 Sep 2011 6:17] Shaun van Wyngaard
Thanks Mike for your input, I have figured it out.
Sergio de la Cruz suggested adding support for vers. 4 as well, and introduced an extra set of brackets to cover this. I had mistakingly adding an opening bracket, but no closing bracket. (Since I didn't need it to cover vers. 4)
By removing the first one now, it runs fine.
[3 Oct 2011 15:11] José Mira
When editing *.py files (Python) you have to make sure that the spacing is done with spaces (!?) and not with tabs.

If you insert tabs you will get the opening and closing dialog.
[7 Oct 2011 7:16] Valerii Kravchuk
Bug #62408 was marked as a duplicate of this one.
[7 Oct 2011 16:41] Touhid Ghasemi
It's not only limited to Windows clients, I get the same error on Mac OSX
[13 Oct 2011 15:40] billy s
To fix the issue (at least in mac osx), I just indented the 'result = self.exec_query' line in wb_admin_control.py by 2 spaces, and added 2 lines above it so it appears as follows:

            result = []
            if not (self.server_version.startswith("5.0.") or self.server_version.startswith("4.")):
              result = self.exec_query("show plugins")
[13 Oct 2011 19:37] Sergio Donoso
Gracias. Corregí las líneas en el archivo y me pude conectar. En ubuntu 11.04.
[21 Oct 2011 13:44] Ian Bussieres
Works like a charm in Ubuntu 11.10 as well.
[22 Oct 2011 13:44] Valerii Kravchuk
Bug #62876 was marked as a duplicate of this one.
[24 Oct 2011 8:31] gunter reinitzer
These are the steps to solve it in Ubuntu:

sudo nano /usr/lib/mysql-workbench/modules/wb_admin_control.py

go to line 588:
add 2 lines starting with result = []:

            if result and result.nextRow():
                self.server_version= result.stringByName("version")
                self.raw_version = self.server_version
                dprint_ex(1, "Got MySQL version -", self.server_version)

            result = []
            if not (self.server_version.startswith("5.0.") or self.server_version.startswith("4.")):
                result = self.exec_query("show plugins")

            # check whether Windows authentication plugin is available
            while result and result.nextRow():
[1 Dec 2011 19:03] Philip Olson
Fixed as of 5.2.36:
+        The <literal>Server Administrator</literal> did not function
+        with MySQL Server 5.0. And although &workbench; does not
+        officially support MySQL Server 5.0, this functionality now
+        works.
[23 Jan 2012 14:40] James Irving-Swift
Thank you Byron Palmer,
Your fix worked for me. Though the path using ubuntu/debian is:
'/usr/lib/mysql-workbench/modules/wb_admin_control.py'