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: Verified Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:8.0.16 OS:Microsoft Windows (Microsoft Windows 10 Pro)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[12 Jun 2019 8:46] Claude Renglet
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');
[12 Jun 2019 9:28] MySQL Verification Team
Hello Claude Renglet,

Thank you for the report and test case.

regards,
Umesh