MySQL Bugs: #55698: Missing INDEXes in CREATE Script

Bug #55698	Missing INDEXes in CREATE Script
Submitted:	2 Aug 2010 23:24	Modified:	30 Jun 2011 9:51
Reporter:	Kelsey Prantis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.2.25, 5.2.33	OS:	MacOS (10.5)
Assigned to:		CPU Architecture:	Any
Tags:	INDEX

Description:
I just upgraded to 5.2.25, and when I generated a new CREATE script without changing the model (and skipping creation of foreign keys), my CREATE script is missing many of the indexes that were previously being created. I do know that according to http://bugs.mysql.com/bug.php?id=49987, "indexes, specified as FOREIGN (thats what WB sets automatically created indexes for FK columns) are now skipped along with the keys when "Skip creation of FOREIGN KEYS" is checked." as of 5.2.17, but I have confirmed at lease one index that was of type INDEX is also missing in the new create script. It does happen to index a foreign key, but it was of type INDEX not type FOREIGN. It does allow me to create a new index with the same name and columns, and then that appears in the script, but I am unable to remove the old one and it's confusing to developers to have the index showing twice in MySQL Workbench. I noticed there is no longer a type FOREIGN for indices...so perhaps all indices that are for FKs only are being removed from the create script, regardless of if they used to be of type FOREIGN or of type INDEX? This seems like it'd be undesirable behavior as just because I don't want to export foreign keys doesn't mean I don't want to create an index on that column, and having two indices for the same column in MySQL workbench is confusing and likely to lead to mistakes on the part of developers in our team.

I also noticed that several of my tables were missing their PRIMARY KEY declarations. Going to the columns tab and unchecking and rechecking the PK checkbox seems to fix it so then it gets added, but that still seems like a bug that it wasn't generating the Primary Key declarations when there was a Primary Key checked and a PRIMARY index showing in Indices.

How to repeat:
1. Start with a model with some indexes that contain only foreign keys.
2. Upgrade to 5.2.25.
3. Use 'Forward Engineer SQL CREATE Script...' to generate a CREATE script with 'Skip generation of Foreign Keys' checked.
4. Examination of the resulting SQL shows missing indexes and PRIMARY KEY declarations.

Suggested fix:
Make it so that with Skip Foreign Key generation checked, indices I've made that were type INDEX in the old version still get generated, but those of type FOREIGN do not.

Hi Kelsey,
could you attach a demo-mwb file that shows that behavior? (you can mark it "private" if you want to restrict access to internal developers only)
thanks in advance

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Attached a demo mwb and sql file that demonstrate the problem. (For developers only.)

In 5.2.33 I see option "Skip crreation of FK indexes as well", and in it checked in your .mwb file. As soon as I uncheck it, missing index is created. So, what is the problem?

The problem is when I uncheck it, then it also creates a bunch of extra indices we *don't* want throughout our schema for all the foreign keys. We don't want it automatically making indices, but we also don't want it to be removing indices we've made ourself manually in the past. I've even confirmed with the older version that this index was type INDEX, not type FOREIGN, so it shouldn't be removing it even with that box checked.

(Note the one I submitted is a demo one only, our real schema is of course much larger, and contains the other tables where the extra indices are being created. I didn't think that was necessary to demonstrate as that is intended behavior when that box is not checked.)

This bug is so show-stopping for us that our company is still stuck using 5.1 until this is fixed.

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/