Bug #85990 Workbench doesn't show Indexes or Foreign Keys on Alter Table view
Submitted: 18 Apr 2017 20:34 Modified: 19 Apr 2017 7:25
Reporter: Cristian Hidalgo Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:6.3.9 OS:Microsoft Windows (Microsoft Windows 7 Enterprise Service Pack 1)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[18 Apr 2017 20:34] Cristian Hidalgo
Description:
Workbench doesn't show Indexes or Foreign Keys on Alter Table view, even when the tables are created using the same Workbench.

How to repeat:
* Create a New Schema: Test, Collation: Server Default, all options default: Success.

* Create new table:
CREATE TABLE `Import_Files_Definition` (
  `Network` varchar(30) NOT NULL,
  `Formato` varchar(25) NOT NULL,
  `TimeStamp` timestamp(1) NOT NULL DEFAULT CURRENT_TIMESTAMP(1) ON UPDATE CURRENT_TIMESTAMP(1),
  PRIMARY KEY (`Formato`),
  UNIQUE KEY `Formato_UNIQUE` (`Formato`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

* Open the new table with Right-Click - Alter Table..., the Primary Key is not shown, although it shows OK in the Table Inspector

* The same happens with Foreign Keys.

* I am using Workbench on Windows 7, against MySQL Server 5.7.17-0ubuntu0.16.04.1 (Ubuntu)
[18 Apr 2017 21:16] Cristian Hidalgo
* Adding a second table with a foreign key. The Primary index in this table is not shown in Alter Table.
CREATE TABLE `Import_Files_Formats` (
  `Formato` varchar(25) NOT NULL,
  `Parameter` varchar(10) DEFAULT NULL,
  `TimeStamp` timestamp(1) NOT NULL DEFAULT CURRENT_TIMESTAMP(1) ON UPDATE CURRENT_TIMESTAMP(1),
  `IFF_Idx` int(1) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`IFF_Idx`),
  UNIQUE KEY `IFF_Idx_UNIQUE` (`IFF_Idx`),
  KEY `Formato_idx` (`Formato`),
  CONSTRAINT `Formato` FOREIGN KEY (`Formato`) REFERENCES `Import_Files_Definition` (`Formato`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
[18 Apr 2017 21:36] Cristian Hidalgo
I found the exact moment of failure: The system doesn't like the default expression "CURRENT_TIMESTAMP(1) ON UPDATE CURRENT_TIMESTAMP()". As soon as it appears, the indexes are gone. The datatype can be DateTime() or TimeStamp, it fails with both.
[19 Apr 2017 7:25] MySQL Verification Team
Hello Cristian Hidalgo,

Thank you for the report and test case.
Verified as described with WB 6.3.9 on Win7.

Thanks,
Umesh