Bug #79604 MySQL Workbench won't properly generate a script if a BIT column is used
Submitted: 11 Dec 2015 15:16 Modified: 13 Dec 2015 14:11
Reporter: Ya T Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:6.3.5 (64-bit), 6.3.6 OS:Microsoft Windows (10 Pro)
Assigned to: CPU Architecture:Any
Tags: bit, workbench

[11 Dec 2015 15:16] Ya T
Description:
If you try to insert or amend the value of the BIT-type column the Workbench will not generate a proper script.

For example it will generate the following script:
INSERT INTO `schema`.`table` (`col1`, `col2`, `Bcol3`) VALUES ('Cheeseburger', 'CH', '1');
instead of the following script:
INSERT INTO `schema`.`table` (`col1`, `col2`, `Bcol3`) VALUES ('Cheeseburger', 'CH', b'1');

The wrong script in its turn will cause an "ERROR 1406: 1406: Data too long for column 'bCol3' at row 1"

How to repeat:
1. Create/open a table with one of the columns having a BIT type.
2. Try to insert a new row with binary value / update a cell with binary value using a Workbench.
3. Try applying the changes.
== On this step you will be given an error: "ERROR 1406: 1406: Data too long for column 'bCol3' at row 1"

Suggested fix:
The "b" prefix should be added in front of the value for the BIT columns
[13 Dec 2015 14:11] MySQL Verification Team
Hello Ya T,
 
Thank you for the report.
Verified as described with WB 6.3.5 on Win7.

Thanks,
Umesh
[19 Feb 2016 12:47] MySQL Verification Team
Bug #80443 marked as duplicate of this
[20 May 2016 22:47] MySQL Verification Team
http://bugs.mysql.com/bug.php?id=81525 marked as duplicate of this one.
[22 Sep 2016 16:49] MySQL Verification Team
http://bugs.mysql.com/bug.php?id=83099 marked as duplicate of this one.
[7 Oct 2016 0:35] MySQL Verification Team
http://bugs.mysql.com/bug.php?id=83294 marked as duplicate of this one.
[30 Nov 2016 16:01] Rui Afonso
Affecting 6.3.8, and it's not limited to Windows 10
This simple but disastrous bug has almost a year.
When will it be fixed?
[5 Feb 2018 21:07] Ryan Willis
Confirming this issue exists on Windows 10 Subsystem Linux with MySQL version 5.7.20
[24 Aug 2018 10:15] Martin Peter
Confirming this for Mac OS Workbench 6.3.10
This bug is a productivity killer for people maintaining mysql databases frequently. It makes them move to other tools.
[25 Oct 2018 7:20] Pavel Cibulka
Still a problem when editing values of BIT(1) columns in 8.0.13. Is it really that hard to fix?
[5 Nov 2018 16:15] Joe Mesot
This is just rediculous that this is now even more of a problem. All I want to do is copy a row and paste it in the same table... but I end up with ever coulmn's value being treated as a bit value!!! 

INSERT INTO `table`.`schema` (`id`, `name`, `dspName`, `safePrefix`, `priority`, `dsp`, `class`, `columnNumber`, `beforeForm`, `afterForm`, `custom`, `form_stepID`, `form_groupID`) VALUES (NULL, 'Correct string value', b'0', b'incorrect bit value', b'2', b'1', b'col-xs-12', b'0', b'0', b'0', b'0', b'1', b'2');

Why is every value after the first bit value treated as a bit value????? I can't even anymore with this thing.
[23 Jul 2019 18:26] Christopher Reese
Issue present in 8.0.16 as well, please fix
[22 Oct 2019 16:04] Shad Taylor
On 8.0.18 as well.  This needs to be fixed engineers.
[15 Jan 2020 15:59] Christopher Reese
Appears that Workbench 8.0.19 has corrected part of the issue, updates with bit changes along with other fields afterwards appears to work correctly

Scheme
id int(11), name varchar(255), active bit(1), sortOrder int(11)

This is correct
UPDATE `sample`.`sampleTable` SET `active` = b'0', `sortOrder` = '37' WHERE (`id` = '3');

Inserts do not

Insert - Incorrect
INSERT INTO `sample`.`sampleTable` (`name`, `active`, `sortOrder`) VALUES ('Testing', b'0', b'500');

MySQL Team, any information on when the rest of the issue will be fixed, been ongoing for 4+ years?
[1 Aug 2020 6:29] Thomas Campbell
8.0.21 and it's still there....

Is anyone from development team reading these?  This isn't something that happens every blue moon.  It's constant and easily reproducable.
[7 Sep 2020 6:36] Kevin Evans
Can confirm this occurs on 8.0 on both Windows and Ubuntu. 

If there's any BIT columns, all columns afterwards will have a 'b' prefixed. My workaround is changing BIT columns to TINYINT(1) columns.
[27 Oct 2020 16:00] Sean O'Connor
This has been on here since 2015!!  Will it ever be fixed?
It really causes a lot of issues when you are trying to insert or modify a lot of rows and you always have to copy and past the script elsewhere and go and fix every single row!
[26 Mar 8:29] Сергей Фам
Hello from 2021. Bug is stil here. I am on windows 10, worbench 8.0.23 x64