MySQL Bugs: #53513: Workbench unable to fetch schema information in object browser and overview

Bug #53513	Workbench unable to fetch schema information in object browser and overview
Submitted:	8 May 2010 19:30	Modified:	8 Jun 2010 18:15
Reporter:	Manuel Bleichenbacher	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	5.2.21	OS:	Windows (Windows 7)
Assigned to:		CPU Architecture:	Any
Tags:	Fetching, table

Description:
After connecting to a database, the SQL editor opens and displays the object browser view and several tabs, one called "Overview". Both the object browser view and the overview view are unable or partially unable to fetch the schema information, i.e. the available tables, views and routines.

The server contains two databases: cms and iaeth (in addtion to information_schema and mysql), cms and iaeth are shown when I connect as root. But for none of the databases the "Overview" shows any tables(even though there are many in the two databases). Instead it says "0 items" for tables, views and routines.

The object browsers shows the "Tables", "Views" and "Routines" nodes for the first database (cms), but only "fetching..." when the second database (iaeth) and its subnodes.

If I select the second database (iaeth) in the "Overview" view and then expand the database node in the object browser, it works.

The problem seems to appear only in combination with certain MySQL servers. I can connect to another MySQL instance on a different server. With that instance, MySQL Workbench doesn't show this problem.

In the MySQL forum I've seen that other users encountered the same problem.

How to repeat:
- Start MySQL Workbench
- Open a connection from the "Home" screen
("Overview" view shows "0 items" for tables, views and routines.)

- Expand the the node for the second database in the Object Browser
(The node is expanded, but the database node and it's three subnodes just display "fetching...")

Screenshot 1: 0 items in "Overview" view

Attachment: screen01.png (image/png, text), 116.35 KiB.

Screenshot 2: Object browser only shows "fetching..."

Attachment: screen02.png (image/png, text), 107.87 KiB.

What exact version of MySQL server, x.y.z, local or remote, are you working with?

Client:
MySQL Workbench 5.2.20 for Windows on Windows 7
(no other MySQL software or installation)

Server 1 (with fetching problem):
MySQL server 5.1.46 on CentOS 5.4 (in a VM)

Server 2 (without fetching problem):
MySQL server 5.0.51a-24 on Debian Lenny (in a VM)

BTW: The problem occurred as well with MySQL Workbench 5.2.11 for Windows

Thank you for the feedback. The user which connect with server with problems has the same privileges rights than the server without problems to fetch?. Thanks in advance.

In both cases, I've connected with MySQL root user, which should have maximum privileges and access rights.

I have the same issue on Mac - some of the databases expand into tables, views etc, some continue to show "fetching..."

MySQL Workbench OSS for Mac OS X version 5.2.20
Cairo Version: 1.9.1
Rendering Mode: OpenGL is available on this system, so OpenGL is used for rendering.
OpenGL Driver Version: 2.0
OS: Darwin 10.3.0
CPU: 2x Intel(R) Core(TM)2 Duo CPU     T9600  @ 2.80GHz, 4.0 GB RAM

Please, check with a newer version, 5.2.21, and inform about the results.

The problem stays the same with 5.2.21.

Thank you for the feedback.

There is delay between WB opens view and really fetches tables. In case of slow network this can take time. What happens if you wait long enough? Can you see all tables?

Believe me, there is a bug. I can wait for hours and it doesn't change.

And it depends on the sequence of clicking in the UI. If I expand the second database in the object browser without clicking anything before, I get the "fetching..." nodes and they stay forever. If I select the second database in the overview view first and then expand the same database in the object browser, it more or less immediately shows all the tables and views.

And the overview view always shows wrong numbers ("0 item") and no "fetching...", except when you create a new table. Then it's refreshed with the correct numbers.

Thank you for the feedback.

I can not repeat described behavior with version 5.2.22 and 2 databases with 10000 tables in each. Although there is delay between time when I click on database name and I get list of tables.

I've upgraded the MySQL server that has been causing problems and now the problem is gone.

After the installation I've investigated what could have caused the problem. At first I thought it was related to the specific version of MySQL. But then I've seen a few error message in the mysqld.log that could point to the real cause of the problem:

100522 23:27:45 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
100522 23:27:45  InnoDB: Started; log sequence number 0 43655
100522 23:27:46 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
100522 23:27:46 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50077, now running 50146.
Please use mysql_upgrade to fix this error.
100522 23:27:46 [ERROR] mysql.user has no `Event_priv` column at position 29
100522 23:27:46 [ERROR] Cannot open mysql.event
100522 23:27:46 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.

So the problem might have gone away with running "mysql_upgrade".

I can reproduce this problem in PLESK by creating a user in PHPMYADMIN instead of PLESK itself, after running a php upgrade, all users and db's created outside PLESK have problems

Workaround for this bug:

1. Set desired databases to the default in the Object Browser
2. Right click on the desired database, which has been stuck at "fetching"
3. Select "Refresh All"

The tables, views, etc will load as expected.

I had the same problem and it was due to upgrading.  i went from 5.0 to 5.5. I forgot to run mysql_upgrade.  after I ran mysql_upgrade everything worked as expected.

As mentioned by another, I also ran the mysql_upgrade and that fixed my issue as well. thank you!

mysql_upgrade will solve the fetching... problem if your server and mysql workbench both are local and you run mysql_upgrade on your local server. 

However I have mysql workbench which is latest version but server is on another PC and is older and I do not want to run mysql_upgrade on server. 

I found a workaround - run the sql bellow 'for each table' from your workbench after connecting to your server (pick a column1 that will never have null values in your table, and it will return zero rows. Make n such query for all your n number of tables & run - imp: DB_NAME is must else it does not work!!)

select <column1> from <DB_NAME>.<TABLE_NAME> where <column1>=null;

all tables will be visible.