Bug #95746 Insert records with a `bit`column from the Result Grid
Submitted: 12 Jun 2019 8:34 Modified: 12 Jun 2019 8:40
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:34] Claude Renglet
Description:

When inserting multiple records in a table containing a bit column, only the first INSERT is correct, the other INSERT include a 'b' in front of all values, see execution steps and errors here under :

Operation failed: There was an error while applying the SQL script to the database.
Executing:
INSERT INTO `intranet`.`MenusAccess` (`idMenus`, `Agent`, `Allowed`) VALUES ('184', 'MDU', b'1');
INSERT INTO `intranet`.`MenusAccess` (`idMenus`, `Agent`, `Allowed`) VALUES (b'184', b'JECH', b'1');
INSERT INTO `intranet`.`MenusAccess` (`idMenus`, `Agent`, `Allowed`) VALUES (b'184', b'PVA', b'1');

ERROR 1064: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b'184', b'JECH', b'1')' at line 1
SQL Statement:
INSERT INTO `intranet`.`MenusAccess` (`idMenus`, `Agent`, `Allowed`) VALUES (b'184', b'JECH', b'1')

ERROR 1064: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b'184', b'PVA', b'1')' at line 1
SQL Statement:
INSERT INTO `intranet`.`MenusAccess` (`idMenus`, `Agent`, `Allowed`) VALUES (b'184', b'PVA', b'1')

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='';

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

In the result grid, try to add some records, at least 2.
click on Apply.

Suggested fix:
Don't insert the 'b', qualifying a bit column, after the first bit detected column where it is not needed.
[12 Jun 2019 8:40] Umesh Shastry
Hello Claude Renglet,

Thank you for the report and test case.

regards,
Umesh