MySQL Bugs: #71458: Incorrect synchronization Boolean columns

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

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

Please provide a test case project file. Thanks.

model

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

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                  |
+------------------------+

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)

Thank you for the feedback.

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.