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, 8.0 OS: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 2021 8:29] Сергей Фам
Hello from 2021. Bug is stil here. I am on windows 10, worbench 8.0.23 x64
[7 Mar 2022 10:11] Gawein Le Goff
I also have this issue on Workbench 8.0.28 on Windows 10 Entreprise 64 bits
[8 Apr 2022 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 2022 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 2022 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 2022 8:24] Jesse Wei
Good news. The bit column will be updated successfully if you enter the value as format below:
b'1'

I tested this workaround in result grid of Workbench 8.0 on Windows 11.
[12 Apr 2023 18:24] Sean O'Connor
This has been in play for 7 years now!!! I check in every year and still it's not fixed.  I've commented many times over the years.  I just downloaded the latest version and STILL it's broke.  This causes our developers issues on a daily basis.  Can you PLEASE PLEASE look into this?

All you have to do is go into the editor, add a new line that has some booleans on it, change a varchar value that is past a boolean column, and then all the columns get treated as booleans!!  I have to manually go into the script line by line and fix every single column in order to get a good script, times the number of lines!

Can you PLEASE fix this one.  It's been 7 years now.
[12 Apr 2023 18:26] Sean O'Connor
What do we need to do in order to get this fixed?  Is there a paid version we can buy that is minus this bug?  Just let me know the solution here.
[8 May 2023 17:56] Sean O'Connor
I've had my company pass over your guys so many times for purchases just due to the fact that you never respond to bugs in here.  I can't imagine spending all that money and then not getting help for something that's been in play for seven years.
[20 Oct 2023 11:58] MySQL Verification Team
Bug #112748 marked as duplicate of this one
[10 Jan 11:52] snu Tranum
Can verify that this is still an issue in the latest version (8.0.34) 8 years on.

Any copying of values in tables requires the use of a text editor to replace the incorrect values with any efficiency. We have long since created a script in-house that replaces b'0' with 0, b'1' with 1 and the remaining b' with ' JUST for this issue whenever we end up having to use MySQL Workbench.

The fact that such a simple, yet incessently frustrating bug, has not been fixed in nearly a decade tells us a lot about how little oracle cares about their customers and their feedback. The fact that most bugs even of S1 Critical level take on average years to correct just underscores the point.

On a general note this sort of behavior has and will ensure that my department will always get a recommendation to avoid using oracle products whenever possible, and gossip about bad experiences spreads.