Bug #71458 Incorrect synchronization Boolean columns
Submitted: 23 Jan 2014 14:33 Modified: 13 Mar 2014 21:22
Reporter: Андрей Ганьков Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S3 (Non-critical)
Version:6.0.8 OS:Linux (openSUSE)
Assigned to: CPU Architecture:Any
Tags: synchronize
Triage: Needs Triage: D4 (Minor)

[23 Jan 2014 14:33] Андрей Ганьков
Description:
If i created model columns with bool or boolean type and create database from model. Always when i synchronize model with database, workbench try change boolean column like this:
ALTER TABLE `bazar`.`services` 
CHANGE COLUMN `by_area` `by_area` TINYINT(1) NOT NULL DEFAULT FALSE

But database already has 'by_area' column with tinyint(1) type, see:
Table: services
Columns:
id	int(10) UN AI PK
name	varchar(45)
units_id	int(10) UN
income_id	int(10) UN
by_area	tinyint(1)

I think it happens because TINYINT(1) type incorrect compare with boolean type.

How to repeat:

When comparing the correct comparing bool types
[23 Jan 2014 15:29] Miguel Solorzano
Please provide a test case project file. Thanks.
[23 Jan 2014 15:34] Андрей Ганьков
model

Attachment: Factory.mwb (application/vnd.mysql-workbench-model, text), 54.02 KiB.

[23 Jan 2014 16:12] Miguel Solorzano
Thank you for the feedback. That's is done by today design not WorkBench, see using the mysql client:

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> create table yu (id bool);
Query OK, 0 rows affected (0.62 sec)

mysql> show create table yu;
+-------+-----------------------------------------------------------------------
------------------+
| Table | Create Table
                  |
+-------+-----------------------------------------------------------------------
------------------+
| yu    | CREATE TABLE `yu` (
  `id` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
------------------+
1 row in set (0.00 sec)

http://dev.mysql.com/doc/refman/5.6/en/numeric-type-overview.html

BOOL, BOOLEAN

These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:

mysql> SELECT IF(0, 'true', 'false');
+------------------------+
| IF(0, 'true', 'false') |
+------------------------+
| false                  |
+------------------------+
[23 Jan 2014 16:32] Peter Laursen
I think it is a (small) bug.  The bug is IMO that there should not be executed an ALTER TABLE statement at all.  It seems that WB just does a 'string compare' and decides for an ALTER TABLE that is completely useless (changing a column type to what it already is).

Peter
(not a MySQL/Oracle person)
[23 Jan 2014 17:17] Miguel Solorzano
Thank you for the feedback.
[13 Mar 2014 21:22] Philip Olson
Fixed as of the upcoming MySQL Workbench 6.1.3 release, and here's the changelog entry:

The values "true" and "false" are now converted to "1" and "0" if the
column is a BOOLEAN or TINYINT. Also, synchronizing a BOOLEAN against a
TINYINT no longer generates any differences, when before it might generate
SQL that "changed" TINYINT columns to TINYINT.

Thank you for the bug report.