MySQL Bugs: #45802: Duplicate & Garbage indexes added to tables

Bug #45802	Duplicate & Garbage indexes added to tables
Submitted:	27 Jun 2009 17:19	Modified:	24 Jul 2009 9:11
Reporter:	Bhuvanesh Phadnis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S1 (Critical)
Version:	1.2.17	OS:	Windows (Vista)
Assigned to:	Sveta Smirnova	CPU Architecture:	Any

Description:
I created a table and added foreign keys to a table using the following script in MySQL Query Browser 1.2.17 on MySQL Server 5.1.34 

******** 

DROP TABLE IF EXISTS `app_one`.`application_account`; 
CREATE TABLE `app_one`.`application_account` ( 
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
`NAME` varchar(100) NOT NULL, 
`FK_PRIMARY_USER` bigint(20) unsigned DEFAULT NULL, 
`CREATED_BY` bigint(20) unsigned DEFAULT NULL, 
`CREATED_ON` datetime DEFAULT NULL, 
`MODIFIED_BY` bigint(20) unsigned DEFAULT NULL, 
`MODIFIED_ON` datetime DEFAULT NULL, 
`IS_DELETED` bit(1) DEFAULT NULL, 
PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

ALTER TABLE `app_one`.`application_account` 
ADD CONSTRAINT `FK_application_account_1` FOREIGN KEY (`FK_PRIMARY_USER`) REFERENCES `application_user` (`ID`), 
ADD CONSTRAINT `FK_application_account_2` FOREIGN KEY (`CREATED_BY`) REFERENCES `application_user` (`ID`), 
ADD CONSTRAINT `FK_application_account_3` FOREIGN KEY (`MODIFIED_BY`) REFERENCES `application_user` (`ID`); 

********** 

there are many more such tables ... 

I applied the changes and closed the browser and opened it again. after opening this is the state of the table 

******** 

DROP TABLE IF EXISTS `app_one`.`business_account`; 
CREATE TABLE `app_one`.`business_account` ( 
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
`NAME` varchar(45) NOT NULL, 
`FK_PARENT_ACCOUNT_ID` bigint(20) unsigned DEFAULT NULL, 
`FK_COMPANY_ID` bigint(20) unsigned DEFAULT NULL, 
`FK_ACCOUNT_OWNER_ID` bigint(20) unsigned DEFAULT NULL, 
`RATING` varchar(45) DEFAULT NULL, 
`FK_CONTACT_ID` bigint(20) unsigned DEFAULT NULL, 
`ACCOUNT_NUMBER` varchar(45) DEFAULT NULL, 
`ACCOUNT_TYPE` varchar(45) DEFAULT NULL, 
`ADDITIONAL_INFORMATION` varchar(1000) DEFAULT NULL, 
`FK_CREATED_BY` bigint(20) unsigned DEFAULT NULL, 
`CREATED_ON` datetime DEFAULT NULL, 
`FK_MODIFIED_BY` bigint(20) unsigned DEFAULT NULL, 
`MODIFIED_ON` datetime DEFAULT NULL, 
`IS_DELETED` bit(1) DEFAULT NULL, 
PRIMARY KEY (`ID`), 
KEY `FK602C30AEA0512AC0` (`FK_PARENT_ACCOUNT_ID`), 
KEY `FK602C30AE54989ABF` (`FK_CONTACT_ID`), 
KEY `FK602C30AEBA50CC99` (`FK_MODIFIED_BY`), 
KEY `FK602C30AE2A2A13B9` (`FK_ACCOUNT_OWNER_ID`), 
KEY `FK602C30AE5E43884E` (`FK_CREATED_BY`), 
KEY `FK602C30AE19ABE19F` (`FK_COMPANY_ID`), 
CONSTRAINT `FK602C30AE19ABE19F` FOREIGN KEY (`FK_COMPANY_ID`) REFERENCES `company` (`ID`), 
CONSTRAINT `FK602C30AE2A2A13B9` FOREIGN KEY (`FK_ACCOUNT_OWNER_ID`) REFERENCES `application_user` (`ID`), 
CONSTRAINT `FK602C30AE54989ABF` FOREIGN KEY (`FK_CONTACT_ID`) REFERENCES `contact` (`ID`), 
CONSTRAINT `FK602C30AE5E43884E` FOREIGN KEY (`FK_CREATED_BY`) REFERENCES `application_user` (`ID`), 
CONSTRAINT `FK602C30AEA0512AC0` FOREIGN KEY (`FK_PARENT_ACCOUNT_ID`) REFERENCES `business_account` (`ID`), 
CONSTRAINT `FK602C30AEBA50CC99` FOREIGN KEY (`FK_MODIFIED_BY`) REFERENCES `application_user` (`ID`), 
CONSTRAINT `FK_business_account_1` FOREIGN KEY (`FK_PARENT_ACCOUNT_ID`) REFERENCES `business_account` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, 
CONSTRAINT `FK_business_account_2` FOREIGN KEY (`FK_COMPANY_ID`) REFERENCES `company` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, 
CONSTRAINT `FK_business_account_3` FOREIGN KEY (`FK_ACCOUNT_OWNER_ID`) REFERENCES `application_user` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, 
CONSTRAINT `FK_business_account_4` FOREIGN KEY (`FK_CONTACT_ID`) REFERENCES `contact` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, 
CONSTRAINT `FK_business_account_5` FOREIGN KEY (`FK_CREATED_BY`) REFERENCES `application_user` (`ID`), 
CONSTRAINT `FK_business_account_6` FOREIGN KEY (`FK_MODIFIED_BY`) REFERENCES `application_user` (`ID`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

******** 

this is the state of all the tables ..... 

see the garbage indexes created ... 

KEY `FK602C30AEA0512AC0` (`FK_PARENT_ACCOUNT_ID`), 
KEY `FK602C30AE54989ABF` (`FK_CONTACT_ID`), 
KEY `FK602C30AEBA50CC99` (`FK_MODIFIED_BY`), 
KEY `FK602C30AE2A2A13B9` (`FK_ACCOUNT_OWNER_ID`), 
KEY `FK602C30AE5E43884E` (`FK_CREATED_BY`), 
KEY `FK602C30AE19ABE19F` (`FK_COMPANY_ID`), 
CONSTRAINT `FK602C30AE19ABE19F` FOREIGN KEY (`FK_COMPANY_ID`) REFERENCES `company` (`ID`), 
CONSTRAINT `FK602C30AE2A2A13B9` FOREIGN KEY (`FK_ACCOUNT_OWNER_ID`) REFERENCES `application_user` (`ID`), 
CONSTRAINT `FK602C30AE54989ABF` FOREIGN KEY (`FK_CONTACT_ID`) REFERENCES `contact` (`ID`), 
CONSTRAINT `FK602C30AE5E43884E` FOREIGN KEY (`FK_CREATED_BY`) REFERENCES `application_user` (`ID`), 
CONSTRAINT `FK602C30AEA0512AC0` FOREIGN KEY (`FK_PARENT_ACCOUNT_ID`) REFERENCES `business_account` (`ID`), 
CONSTRAINT `FK602C30AEBA50CC99` FOREIGN KEY (`FK_MODIFIED_BY`) REFERENCES `application_user` (`ID`), 

please help

How to repeat:
1) add tables with multiple rows
2) assign foreign keys
3) close browser
4) reopen the browser and the tables

I created a table and added foreign keys to a table using the following script in MySQL Query Browser 1.2.17 on MySQL Server 5.1.34 

******** 

DROP TABLE IF EXISTS `app_one`.`application_account`; 
CREATE TABLE `app_one`.`application_account` ( 
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
`NAME` varchar(100) NOT NULL, 
`FK_PRIMARY_USER` bigint(20) unsigned DEFAULT NULL, 
`CREATED_BY` bigint(20) unsigned DEFAULT NULL, 
`CREATED_ON` datetime DEFAULT NULL, 
`MODIFIED_BY` bigint(20) unsigned DEFAULT NULL, 
`MODIFIED_ON` datetime DEFAULT NULL, 
`IS_DELETED` bit(1) DEFAULT NULL, 
PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

ALTER TABLE `app_one`.`application_account` 
ADD CONSTRAINT `FK_application_account_1` FOREIGN KEY (`FK_PRIMARY_USER`) REFERENCES `application_user` (`ID`), 
ADD CONSTRAINT `FK_application_account_2` FOREIGN KEY (`CREATED_BY`) REFERENCES `application_user` (`ID`), 
ADD CONSTRAINT `FK_application_account_3` FOREIGN KEY (`MODIFIED_BY`) REFERENCES `application_user` (`ID`); 

********** 

there are many more such tables ... 

I applied the changes and closed the browser and opened it again. after opening this is the state of the table 

******** 

DROP TABLE IF EXISTS `application_account`; 
CREATE TABLE `application_account` ( 
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
`NAME` varchar(100) NOT NULL, 
`FK_PRIMARY_USER` bigint(20) unsigned DEFAULT NULL, 
`CREATED_BY` bigint(20) unsigned DEFAULT NULL, 
`CREATED_ON` datetime DEFAULT NULL, 
`MODIFIED_BY` bigint(20) unsigned DEFAULT NULL, 
`MODIFIED_ON` datetime DEFAULT NULL, 
`IS_DELETED` bit(1) DEFAULT NULL, 
PRIMARY KEY (`ID`), 
KEY `FK6A94E63EDDF07C94` (`CREATED_BY`), 
KEY `FK6A94E63E30426113` (`MODIFIED_BY`), 
KEY `FK6A94E63E1A2E0A8` (`FK_PRIMARY_USER`), 
CONSTRAINT `FK6A94E63E1A2E0A8` FOREIGN KEY (`FK_PRIMARY_USER`) REFERENCES `application_user` (`ID`), 
CONSTRAINT `FK6A94E63E30426113` FOREIGN KEY (`MODIFIED_BY`) REFERENCES `application_user` (`ID`), 
CONSTRAINT `FK6A94E63EDDF07C94` FOREIGN KEY (`CREATED_BY`) REFERENCES `application_user` (`ID`), 
CONSTRAINT `FK_application_account_1` FOREIGN KEY (`FK_PRIMARY_USER`) REFERENCES `application_user` (`ID`), 
CONSTRAINT `FK_application_account_2` FOREIGN KEY (`CREATED_BY`) REFERENCES `application_user` (`ID`), 
CONSTRAINT `FK_application_account_3` FOREIGN KEY (`MODIFIED_BY`) REFERENCES `application_user` (`ID`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

******** 

this is the state of all the tables ..... 

see the garbage indexes created ... 

KEY `FK6A94E63EDDF07C94` (`CREATED_BY`), 
KEY `FK6A94E63E30426113` (`MODIFIED_BY`), 
KEY `FK6A94E63E1A2E0A8` (`FK_PRIMARY_USER`), 
CONSTRAINT `FK6A94E63E1A2E0A8` FOREIGN KEY (`FK_PRIMARY_USER`) REFERENCES `application_user` (`ID`), 
CONSTRAINT `FK6A94E63E30426113` FOREIGN KEY (`MODIFIED_BY`) REFERENCES `application_user` (`ID`), 
CONSTRAINT `FK6A94E63EDDF07C94` FOREIGN KEY (`CREATED_BY`) REFERENCES `application_user` (`ID`), 

please help

i put up different tables in my first posting ... nevertheless ... this shows that the error occurs across all the tables

I have tried the same with MySQL 5.0.83 & MySQL 5.1.34 simultaneously ... works fine with the 5.0.83 ... something is definitely wrong with 5.1.34

nopes ...same problem happened with 5.0.83 as well

This should be QB bug indeed. Everything works as expected in command line client:

mysql> create table tpk1 (c1 int primary key, c2 int);
Query OK, 0 rows affected (0.05 sec)

mysql> alter table tpk1 engine=InnoDB;
Query OK, 0 rows affected (0.49 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table tfk(c1 int primary key, c2 int, c3 int, c4 int) engine=InnoDB;
Query OK, 0 rows affected (0.40 sec)

mysql> alter table tfk add constraint fk1 foreign key(c2) references tpk1(c1);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tfk add constraint fk2 foreign key(c3) references tpk1(c1);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tfk add constraint fk3 foreign key(c4) references tpk1(c1);
Query OK, 0 rows affected (0.83 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tfk\G
*************************** 1. row ***************************
       Table: tfk
Create Table: CREATE TABLE `tfk` (
  `c1` int(11) NOT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` int(11) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `fk1` (`c2`),
  KEY `fk2` (`c3`),
  KEY `fk3` (`c4`),
  CONSTRAINT `fk3` FOREIGN KEY (`c4`) REFERENCES `tpk1` (`c1`),
  CONSTRAINT `fk1` FOREIGN KEY (`c2`) REFERENCES `tpk1` (`c1`),
  CONSTRAINT `fk2` FOREIGN KEY (`c3`) REFERENCES `tpk1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

I can not repeat this with QB 1.2.17 and MySQL server 5.1.34 on Windows XP.

Which OS do you use?

Windows Vista Home Basic Service Pack 1

Many thanks for writing a bug report.

Unfortunately, we are not able to repeat this behaviour with Query Browser.

Anyway, we are on the way to implement full functionality of Query Browser into Workbench. Foreign Keys are already working with Workbench and I am sure that you won't get your problem by using Workbench here instead of Query Browser.

For more informations about Workbench look here:

http://mysql.com/products/workbench/