MySQL Bugs: #80483: Schema editor doesn't show any existing indexes, FKs, partitions

Bug #80483	Schema editor doesn't show any existing indexes, FKs, partitions
Submitted:	23 Feb 2016 16:48	Modified:	27 Mar 2018 0:16
Reporter:	Pil Soft	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S1 (Critical)
Version:	6.3.7	OS:	MacOS (10.11.3 El Capitan)
Assigned to:		CPU Architecture:	Any
Tags:	object editor, schema editor

Description:
I have pre-existing tables in mysql 5.6.22. I was previously using mysql Workbench 6.2.5. I upgraded to 6.3.6, even after flushing out previous state including Library, cache, and user preferences, and 6.3.6 will not show ANY existing Indexes, Partitions, or Foreign Keys in the table schema editor. The tabs are blank, as if no such objects exist, but you can clearly see they exist by viewing the schema outside the table schema editor.

The affected panes are blank. But when I use the "i" icon to view the schema, the respective objects (index, foreign keys, partitions) are clearly present.

This is crippling, entirely nullifies the product. No developers on our team can do any work with this version since table schema cannot be edited.

How to repeat:
Launch Mysql 6.3.6 on the latest El Capitan OSX 10.11.3 or closely comparable version. In the Schemas tree, click the "i" icon next to a table which is known to have at least one Index, Foreign Key, or has Partitions configured. Note it properly shows all existing indexes, foreign keys, and partitions when you click on the buttons at the top.

Now click the "wrench" icon next to the table to open the table in the Schema Editor. Click on the tabs at the bottom. The Columns tab does properly show columns, seems correct. Now click on Indexes, Foreign Keys, and Partitioning tabs. They are blank and do not show any existing objects which are actually present.

Note: Triggers DO properly show in the table schema editor.

The only solution I have found is to rollback to 6.2.5.

Suggested fix:
Fix the underlying code which is supposed to read the schema and present the facets in the user interface editor.

Showing indexes exist

Attachment: mysql_bug_1.png (image/png, text), 156.39 KiB.

Indexes tab showing no indexes

Attachment: mysql_bug_2a.png (image/png, text), 599.95 KiB.

BETTER: Indexes tab showing no indexes

Attachment: mysql_bug2b.jpg (image/jpeg, text), 229.88 KiB.

Partitions exist in schema

Attachment: mysql_bug3.png (image/png, text), 77.44 KiB.

Table schema editor shows NO partitions

Attachment: mysql_bug3b.png (image/png, text), 72.39 KiB.

Thank you for the bug report. I couldn't repeat with my own table please provide the create table statement of a table which presents for you this issue?. Thanks.

Hello, thanks for checking. Bear in mind this occurs on Mac OSX, versus Windows version of MySql Workbench.

Here is a create statement of one of the tables used in the screen shots I sent. 

CREATE TABLE "ticket_item" (
  "created_year" smallint(6) NOT NULL,
  "id" int(11) NOT NULL AUTO_INCREMENT COMMENT 'a display id for human readability in the ui',
  "uuid" varchar(36) NOT NULL,
  "created_datetime" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "state" tinyint(1) NOT NULL DEFAULT '1',
  "status" smallint(6) NOT NULL DEFAULT '1',
  "creator_uuid" varchar(36) NOT NULL,
  "client_uuid" varchar(36) NOT NULL,
  "com_uuid" varchar(36) NOT NULL,
  "type" int(11) NOT NULL,
  "priority" int(11) NOT NULL,
  "category" varchar(25) NOT NULL,
  "subcategory" varchar(25) NOT NULL,
  "subject" varchar(125) NOT NULL,
  "target_type" varchar(25) NOT NULL,
  "target_entity" varchar(36) DEFAULT NULL,
  "last_updated_datetime" timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  "last_updated_by" varchar(64) DEFAULT NULL,
  PRIMARY KEY ("created_year","id"),
  UNIQUE KEY "idx_ticket_uuid" ("created_year","uuid"),
  KEY "idx_ticket_id" ("id"),
  KEY "idx_ticket_client" ("client_uuid"),
  KEY "idx_ticket_clientComm" ("client_uuid","com_uuid"),
  KEY "idx_ticket_clientCommType" ("client_uuid","type"),
  KEY "idx_ticket_createdDate" ("client_uuid","created_datetime"),
  KEY "idx_ticket_status" ("client_uuid","status"),
  KEY "idx_ticket_state" ("client_uuid","state"),
  KEY "idx_ticket_target" ("client_uuid","target_type"),
  KEY "idx_ticket_category" ("client_uuid","category"),
  KEY "idx_ticket_subcategory" ("client_uuid","subcategory"),
  KEY "idx_ticket_lastModDateTime" ("client_uuid","last_updated_datetime")
) ENGINE=InnoDB AUTO_INCREMENT=20198 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (created_year)
(PARTITION part0 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION part1 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION part2 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION part3 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION part4 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION part5 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

Also, just wanted to add that this problem does not appear specific to any particular table. On the affected version, no tables show any of the following metadata in the Table Schema Editor: Index, Foreign Keys, partition. Though note that for whatever reason, Triggers do show.

indexes

Attachment: indexes.png (image/png, text), 228.72 KiB.

Thank you for the feedback. I couldn't repeat on the below environment:

MySQL Workbench Community (GPL) for Mac OS X version 6.3.6 CE build 511 (32 bit)
Configuration Directory: /Users/miguel/Library/Application Support/MySQL/Workbench
Data Directory: /Applications/MySQLWorkbench.app/Contents/Resources
Cairo Version: 1.10.2
OS: OS X 10.11.x El Capitan
CPU: 4x Intel(R) Core(TM) i5-3210M CPU @ 2.50GHz - 16.00GiB RAM

Maybe is there something I am missing that doesn't allow me to repeat.

Hmm, I am using the 64 bit version where I experienced the problem. It seems from your environment info that you are using the 32 bit version. Perhaps that is the source of the behavior difference.

Odd, I do not even see an option on the Oracle site to download a 32 bit version of Mysql Workbench 6.3.6 in 32 bit. Where did you find that? See attached screen shots. This is the version I have (64 bit) and the download page options where I obtained the download from the Oracle site. It does not present a 32 bit version unless you regress to previous versions (like 6.2.5 which is a 32 bit version). 

Have you tried to download and reproduce this problem on 64 bit? Or, can you direct me to the 32 bit version which may not exhibit the problem?

Downloage page at Oracle site

Attachment: mysql_download.png (image/png, text), 99.54 KiB.

My About Versio dialog

Attachment: mysql_version.png (image/png, text), 38.20 KiB.

Thank you for the feedback. The about dialog shows to me a 64 bit like you so my guess the Help->System Info prints wrong 32 bit please check the System Info. Thanks.

Ah, you are correct. It does seem the help --> system info is incorrect, we are in fact using a comparable version. Hmm. Let me try a fresh 6.3.6 install inside an osx VM against our schema to determine if the problem persists.

MySQL Workbench Community (GPL) for Mac OS X version 6.3.6 CE build 511 (32 bit)
Configuration Directory: /Users/user/Library/Application Support/MySQL/Workbench
Data Directory: /Users/user/Downloads/MySQLWorkbench.app/Contents/Resources
Cairo Version: 1.10.2
OS: OS X 10.11.x El Capitan
CPU: 8x Intel(R) Core(TM) i7-4960HQ CPU @ 2.60GHz - 16.00GiB RAM

Fresh OSX install in VMWare virtual machine: still no indexes

Attachment: mysql_636_no_indexes_freshVmInstall.jpg (image/jpeg, text), 353.34 KiB.

See new attachment I just added. I just created a brand new, fresh, clean virtual machine install of OSX. I downloaded MySQL Workbench 6.3.6 64 bit, ran it, added the connection info to our MySQL server, and right off the bat, it is broken: Exact same symptom. There are no indexes, no foreign keys showing, no partitions showing, HOWEVER, the indexes, foreign keys, and partitions DO show properly in Workbench 6.2.5 to the exact same Mysql server and schema.

So to me, this means there is nothing wrong with my machine, a bad install, corrupt data files. This VM got a brand new install of OSX with nothing else on it, and fresh (never before used) install of MySQL Workbench. And the symptom still exists. 

We really need to find out what is so broken about 6.3.6 that we cannot install and use it on our DB schema (however, using 6.2.5 works just perfectly fine).

What else can we investigate or compare? There must be something different. How about the server version? We are using 5.6.23 hosted on Amazon RDS.

Our MySql config params, difference from Amazon RDS defaults

Attachment: mysqlparams.jpg (image/jpeg, text), 154.26 KiB.

I just attached a screen shot showing our mysql config params compared to the Amazon mysql RDS defaults. This will show you how we have changed our mysql server from the defaults. 

Since you install mysql Workbench clean and it is fine, but I install mysql Workbench clean and it's broken, perhaps our mysql server is the difference.

Could one of the config settings shown be causing Workbench 6.3.6 to fail to show Indexes, Foreign Keys, and Partitions in the schema editor?

Sadly, I just downloaded and ran the new 6.3.7 and this DOA defect is still unchanged. I open any table in the schema editor, and the Indexes, Foreign Keys, Triggers, and Partitioning tabs are all blank (when objects do exist). I reopen right back in 6.2.5.0, and all is well again. 

It's just so disheartening that a defect this severe (being unable to manipulate DB objects such as tables and therefore making the product unusable) still exists. This is the type of occurrence which make products like mysql seem not viable for serious business.

If anyone is willing to work with me on this, I am still wanting to have this fixed. otherwise, I am stuck on 6.2.5 forever! To recap, I started brand new Mac OSX virtual machine in WMWare, downloaded 6.3.6, connected to our MySQl server at AWS (RDS), and immediately the table schema editor is already broken. There is virtually nothing required to reproduce this.

Someone tried OSX and did not reproduce it but I suspect the problem may be manifest when connecting to mysql at Amazon RDS. Anyone?
The problem may be related to t

I FOUND OUT WHAT IS BREAKING WORKBENCH 6.3!!! 

It's sql mode = ANSI_QUOTES. I have proven this. Reset mysql back original config. MySql Workbenche 6.3.7 now *DOES* properly show the indexes, FKs, and partitions in the Object Editor. 

However, as soon as I set sql_mode=ANSI_QUOTES, BAM! Dead! It no longer shows any content in the aforementioned object/schema editor tabs.

Can we PLEASE get this fixed? This used to work fine in Workbench 6.2.5. Even with ANSI_QUOTES enabled, 6.2.5 properly shows all contents in the schema editor.

Can we please fix 6.3 so that it works properly with ANSI_QUOTES?

Thank you for the feedback. Vefified when setting sql_mode=ANSI_QUOTES.

I have the same problem on: 
- Windows 7 professional SP1 [Workbench 6.3.7 build 1199 CE (32 bits)]
- Ubuntu 16.04 LTS [Workbench 6.3.7 build 1199 CE (64 bits)]

Schema editor doesn't show any existing indexes, FKs, partitions

Attachment: Capture.PNG (image/png, text), 100.29 KiB.

Hey,

Having same problem with version 6.3.9 on Ubuntu 16.04, however mine is linked to using TIMESTAMP with a precision specifier...

If I change a column data type from TINESTAMP to TIMESTAMP(6), the indexes and FKs disappear, change it back to TIMESTAMP and they're back! Seems to be easily repeatable on any table that has a TIMESTAMP column.

Hope this helps to find a fix.
Paul.

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

For a table with existing indexes (and SQL_MODE=ANSI_QUOTES), the Indexes
tab within the table editor (opened with the wrench icon) did not show the
indexes as expected. Column, Foreign Key, Triggers, and Partitioning tabs
were also missing objects defined in the table.

Thank you for the bug report.