MySQL Bugs: #70128: MySQL EER Synchronize To Database [fails] on BIT(1) columns

Bug #70128	MySQL EER Synchronize To Database [fails] on BIT(1) columns
Submitted:	23 Aug 2013 2:25	Modified:	23 Aug 2013 11:57
Reporter:	James Barwick	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	6.0.6.11184	OS:	MacOS (10.8.4)
Assigned to:		CPU Architecture:	Any
Tags:	bit, EER, modeling, synchronize, workbench

Description:
Attempting to synchronize the model with MySQL Version 5.6.13

If a column is defined as BIT(1), the synchronization will not 'synchronize' and the model will always attempt to update/alter these columns.

The mysql server on "SHOW CREATE TABLE" returns the following:

`ACTIVE` bit(1) NOT NULL DEFAULT b'1',

When the synchronize model to database option is selected, it analyzes the schema and produces the following alter statement:

CHANGE COLUMN `ACTIVE` `ACTIVE` BIT(1) NOT NULL DEFAULT 1 ;

again, this always occurs no matter how many times the synchronization is attempted.  This also occurred in MySQL Workbench 5 (And I upgraded to Workbench 6 this morning to see if the issue was resolved...it was not)

How to repeat:

1) Create a table in the Model EER
2) Add a Bit(1) NOT NULL DEFAUL 1 column to the table.
3) Forward Engineer the model to a schema
4) "synchronize" the model

On step 4 you would expect no alter statements.  However, the synchronization [fails] and the model always produces an ALTER statement.

Suggested fix:

Make sure the model synchronization recognizes the BIT(1) appropriately.  Is it the lowercase Bit(1) from the show create table vs the BIT(1) in the model?  Or is it the DEFAULT b'1' from the database vs DEFAULT 1 in the model...dunno.

I verified that it is the comparison of the default b'1' from the database and the default 1 from the model.

Setting the model default to b'1' solved the issue.  (Don't know why I didn't think of that earlier)

Perhaps you might want to add validation rules to the DEFAULT values column in the Model tool just to make sure we don't make a type-o?

I verified that the issue was 'my mistake' by setting the default value in the model incorrectly.

Changing the default in the model from 1 to b'1' solved the problem.  (I don't know why I didn't think of this earlier).

Perhaps we want to make some validation rules on the default column settings in the model?

Just a suggestion..

Thank you for the feedback.