MySQL Bugs: #95747: Update records with a bit column

Bug #95747	Update records with a bit column
Submitted:	12 Jun 2019 8:46	Modified:	12 Jun 2019 9:28
Reporter:	Claude Renglet	Email Updates:
Status:	Won't Fix	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	8.0.16	OS:	Windows (Microsoft Windows 10 Pro)
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
Update a record from the Result Grid with a bit column generates a wrong SQL string and generate an error when applying query.

Operation failed: There was an error while applying the SQL script to the database.
Executing:
UPDATE `testAccess`.`MenusAccess` SET `Allowed` = '0' WHERE (`idMenusAccess` = '35');

ERROR 1406: 1406: Data too long for column 'Allowed' at row 1
SQL Statement:
UPDATE `testAccess`.`MenusAccess` SET `Allowed` = '0' WHERE (`idMenusAccess` = '35')

How to repeat:
Create a table with a column of type `bit` either with SQL string or with the Create Table GUI.
CREATE TABLE `MenusAccess` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idMenus` int(11) DEFAULT NULL,
  `Agent` varchar(8) DEFAULT NULL,
  `Allowed` bit(1) DEFAULT b'1',
  PRIMARY KEY (`idMenusAccess`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1 COMMENT='';

Insert a record :
INSERT INTO `MenusAccess` (`idMenus`, `Agent`, `Allowed`) VALUES ('1', 'xxx', b'1');

Select rows from new table :
Select * from `MenusAccess`; execute query with CTRL-ENTER

Try to update the bit column value and Apply.

Suggested fix:
either remove the quotes around the 0 in SET `Allowed` = '0' to have SET `Allowed` = 0
or
add the letter b before the '0'.

UPDATE `testAccess`.`MenusAccess` SET `Allowed` = 0 WHERE (`idMenusAccess` = '35');
UPDATE `testAccess`.`MenusAccess` SET `Allowed` = b'0' WHERE (`idMenusAccess` = '34');

Hello Claude Renglet,

Thank you for the report and test case.

regards,
Umesh

Posted by developer:

This issue applies to the legacy MySQL Workbench 8.x implementation that is no longer supported or maintained.
MySQL Workbench 26.7 is a complete rewrite using a new technology stack and does not use the affected legacy implementation.
Therefore, this issue is not applicable to the current MySQL Workbench product and as such is closed.