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:
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:6.3.5 (64-bit), 6.3.6 OS:Windows (10 Pro)
Assigned to: CPU Architecture:Any
Tags: bit, workbench

[11 Dec 2015 15:16] Ya T
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.

[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

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 2021 8:29] Сергей Фам
Hello from 2021. Bug is stil here. I am on windows 10, worbench 8.0.23 x64
[7 Mar 10:11] Gawein Le Goff
I also have this issue on Workbench 8.0.28 on Windows 10 Entreprise 64 bits
[8 Apr 19:01] Sean O'Connor
This has been a problem for SEVEN years now.  Can you PLEASE fix it.  I have submitted this bug so many times.  Everytime I modify data I have to take the script out to notepad and edit every single column every single row that I'm changing.  

The editor is treating every single column as a bit after the first bit and putting a b'' around it.  Please see Joe Mesot's note.  I have been upgraded at each new release since 2015 hoping for a fix.  Hoping version 8 would fix it.  Can we PLEASE fix it?????
[8 Apr 19:03] Sean O'Connor
This should be considered critical as the editing feature is broken if your table contains any bit columns.
[29 Aug 19:02] Sean O'Connor
I have been asking for this to be fixed for SEVEN YEARS!!
Can you PLEASE PLEASE PLEASE fix this.  It used to work fine prior to 2015.
Everytime I make a change in the editor I have to go back and manually fix every single row of the SQL statement because of this bug.
Can you please check into it.  What else can we do to get this looked at?  SEVEN YEARS!!!  Is there a number I can call to talk with your devs?  Is there a paid version I can purchase which has this fixed?  Anything.
[18 Sep 8:24] Jesse Wei
Good news. The bit column will be updated successfully if you enter the value as format below:

I tested this workaround in result grid of Workbench 8.0 on Windows 11.