MySQL Bugs: #36170: Workbench creates unnessesarry SQL code on Forward-Engeneer Alter and Syncronize

Bug #36170	Workbench creates unnessesarry SQL code on Forward-Engeneer Alter and Syncronize
Submitted:	17 Apr 2008 7:25	Modified:	10 Jul 2008 14:19
Reporter:	Jan W. Hentsch	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.0.21 SE	OS:	Windows
Assigned to:	Vladimir Kolesnikov	CPU Architecture:	Any
Tags:	wrong SQL

Description:
this time I checked the OSS-Version 5.0.19. I have 2 very simple MyIsam-Tables and both contain a nullable date field. Whenever I change something to table1, say add another column, WB creates an SQL-Alter-Script that also ALTERS table2 and tries to change my date column to nullable, what it already is. But if my table contains a lot of data, the server accepts the statement, while it is useless, and copies the table, which may run for a long time.
The same happens to WB-SE till 5.0.17 (had no chance to test SE 5.0.19 so far).

Another bug, sometimes WB doesn't put in the field types in the SQL-Create-Script, as well as in the Alter-Script, which the server rejects as a syntax error, of course.

Finally, why does WB generate CONSTRAINT stuff on MyISAM-Tables

How to repeat:
Create two very simple MyISAM-Tables and experiment with adding and/or changing some columns within both tables, or just within one table.

Confirmed here. The same goes for foreign keys, they are also recreated when this is in fact completely unnecessary. Using 5.19 OSS on windows XP.

Steps to reproduce:
import database
add table with foreign key
forward engineer alter script, execute
forward engineer alter script -> observe that foreign key is deleted and recreated

checked it against 5.0.21, still the same. This time, it looks even more strange. Till now, I saw it generate unnessessary alter table code only when nullable columns where defined, now I have a colum "name" which is varchar(45) and not nullable. It still generates an alter table statement which tries to alter the table to exactly what it was before and the server doesn't recocnize it as unnessessary and runs this statement, which can run for hours on big tables.

I realized another thing. When defining the schemas collation say to latin1 - latin1_general_ci and later on defining new tables with a collation of schema default the tabled definition becoms latin1 - swedish.

So far, the tool is NOT usable for us in production area, hopefully it will be soon.

my little "father-child" test

Attachment: VaterKind.mwb (application/octet-stream, text), 4.94 KiB.

fixed. tested in bzr rev nr 3106

Hello again from Germany,

I just checked it against 5.0.22 SE with my little two MyISAM-table table test case.

First, it looks better but still:
- it still generates unnessessary code with nullable fields, tries to alter the field to what it already is.
- I had problems, when it had to synchronize a new field and a field movement in the same table at the same time. It produced an error but fullfilled half of the statement. So with the second sync try it completed.
- it still tries to sell the server real foreign key stuff with MyISAM-tables what the server ignores (I don't know wheather it would alter/copy a big table to what it already is because of that, but if so, than unnessessary and time consuming)
- I set my Schema to latin1 - latin_general_ci, and new tables to Collation of Schema default, but what happens is, the table and all its character fields get latin1 - swedish... (really I like Sweden and swedish people, but I don't like that automatism ;-)

that's all for today,

Do I need to open a new BugReport for that (because it has a status of documenting)

cu jwh

An entry has been added to the 5.0.21 changelog:

The scripts generated by the File, Export, Forward Engineer SQL ALTER Script and File, Export, Forward Engineer SQL CREATE Script include unnecessary SQL code.