Bug #89958 Bad export generated by Workbench: ALTER TABLE for UNIQUE KEY

• Submitted: 8 Mar 2018 10:02
• Modified: 13 Mar 2018 8:04
• Reporter: Terje Rosenlund
• Status: Can't repeat
• Category: MySQL Workbench
• Severity: S3 (Non-critical)
• Version: 5.7.21-0ubuntu0.16.04.1
• OS: Windows (Server 2012 R2 Datacenter)
• CPU Architecture: x86 (x64)
• Tags: ALTER TABLE, export, unique key

[8 Mar 2018 10:02] Terje Rosenlund

Description:
Exporting a Jira db using Workbench produces an export that can't be imported due to a bug in exported table with an UNIQUE KEY:

Exported: ALTER TABLE `ao_587b34_project_config` UNIQUE KEY `U_AO_587B34_PROJECT2070954277` (`NAME_UNIQUE_ADD CONSTRAINT`),;

Produced by phpmyadmin when adding same key manually (correct):

ALTER TABLE`ao_587b34_project_config` ADD UNIQUE`U_AO_587B34_PROJECT2070954277` (`NAME_UNIQUE_CONSTRAINT`);

The complete schema when fixed:

CREATE TABLE `ao_587b34_project_config` (
  `CONFIGURATION_GROUP_ID` varchar(255) COLLATE utf8_bin NOT NULL,
  `ID` int(11) NOT NULL,
  `NAME` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `NAME_UNIQUE_CONSTRAINT` varchar(255) COLLATE utf8_bin NOT NULL,
  `PROJECT_ID` bigint(20) NOT NULL DEFAULT '0',
  `ROOM_ID` bigint(20) NOT NULL DEFAULT '0',
  `VALUE` varchar(255) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `ao_587b34_project_config`
--
ALTER TABLE `ao_587b34_project_config`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `U_AO_587B34_PROJECT2070954277` (`NAME_UNIQUE_CONSTRAINT`),
  ADD KEY `index_ao_587b34_pro2115480362` (`NAME`),
  ADD KEY `index_ao_587b34_pro1732672724` (`ROOM_ID`),
  ADD KEY `index_ao_587b34_pro2093917684` (`PROJECT_ID`),
  ADD KEY `index_ao_587b34_pro193829489` (`CONFIGURATION_GROUP_ID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `ao_587b34_project_config`
--
ALTER TABLE `ao_587b34_project_config`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT;

How to repeat:
1. Create the table below 
2. Export using Workbench
3. Import using workbench

CREATE TABLE `ao_587b34_project_config` (
  `CONFIGURATION_GROUP_ID` varchar(255) COLLATE utf8_bin NOT NULL,
  `ID` int(11) NOT NULL,
  `NAME` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `NAME_UNIQUE_CONSTRAINT` varchar(255) COLLATE utf8_bin NOT NULL,
  `PROJECT_ID` bigint(20) NOT NULL DEFAULT '0',
  `ROOM_ID` bigint(20) NOT NULL DEFAULT '0',
  `VALUE` varchar(255) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `ao_587b34_project_config`
--
ALTER TABLE `ao_587b34_project_config`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `U_AO_587B34_PROJECT2070954277` (`NAME_UNIQUE_CONSTRAINT`),
  ADD KEY `index_ao_587b34_pro2115480362` (`NAME`),
  ADD KEY `index_ao_587b34_pro1732672724` (`ROOM_ID`),
  ADD KEY `index_ao_587b34_pro2093917684` (`PROJECT_ID`),
  ADD KEY `index_ao_587b34_pro193829489` (`CONFIGURATION_GROUP_ID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `ao_587b34_project_config`
--
ALTER TABLE `ao_587b34_project_config`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT;

[8 Mar 2018 11:02] Terje Rosenlund

The complete schema posted was from PhpMyadmin, also posting the original export from Workbench

--
-- Table structure for table `ao_587b34_project_config`
--

DROP TABLE IF EXISTS `ao_587b34_project_config`;
CREATE TABLE IF NOT EXISTS `ao_587b34_project_config` (
  `CONFIGURATION_GROUP_ID` varchar(255) COLLATE utf8_bin NOT NULL,
`ID` int(11) NOT NULL,
  `NAME` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `NAME_UNIQUE_CONSTRAINT` varchar(255) COLLATE utf8_bin NOT NULL,
  `PROJECT_ID` bigint(20) NOT NULL DEFAULT '0',
  `ROOM_ID` bigint(20) NOT NULL DEFAULT '0',
  `VALUE` varchar(255) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Indexes for table `ao_587b34_project_config`
--
ALTER TABLE `ao_587b34_project_config`
 ADD PRIMARY KEY (`ID`), ADD KEY `index_ao_587b34_pro2115480362` (`NAME`), ADD KEY `index_ao_587b34_pro1732672724` (`ROOM_ID`), ADD KEY `index_ao_587b34_pro2093917684` (`PROJECT_ID`), ADD KEY `index_ao_587b34_pro193829489` (`CONFIGURATION_GROUP_ID`);

-- AUTO_INCREMENT for table `ao_587b34_project_config`
--
ALTER TABLE `ao_587b34_project_config`
MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT;

-- Constraints for table `ao_587b34_project_config`
--
ALTER TABLE `ao_587b34_project_config`
UNIQUE KEY `U_AO_587B34_PROJECT2070954277` (`NAME_UNIQUE_ADD CONSTRAINT`),;

[13 Mar 2018 8:04] Chiranjeevi Battula

Hello Terje Rosenlund,

Thank you for the bug report.
I could not repeat the issue at our end using with MySQL workbench 6.3.10 version with MySQL 5.7.21 and 5.6.39 versions.
If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Thanks,
Chiranjeevi.