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, 8.0.28, 8.0.31 OS: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] MySQL Verification Team 
Hello Claude Renglet,

Thank you for the report and test case.

regards,
Umesh
[12 Jun 2019 8:41] MySQL Verification Team 
- CLI

mysql> 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 (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1 COMMENT='';
Query OK, 0 rows affected (0.01 sec)

- WB
SELECT * FROM test.MenusAccess;

Tried to add below:

1	1	TEST	1
2	2	TEST2	1

Apply shows:

INSERT INTO `test`.`MenusAccess` (`id`, `idMenus`, `Agent`, `Allowed`) VALUES ('1', '1', 'TEST', b'1');
INSERT INTO `test`.`MenusAccess` (`id`, `idMenus`, `Agent`, `Allowed`) VALUES (b'2', b'2', b'TEST2', b'1');

-
Executing:
INSERT INTO `test`.`MenusAccess` (`id`, `idMenus`, `Agent`, `Allowed`) VALUES ('1', '1', 'TEST', b'1');
INSERT INTO `test`.`MenusAccess` (`id`, `idMenus`, `Agent`, `Allowed`) VALUES (b'2', b'2', b'TEST2', b'1');

Operation failed: There was an error while applying the SQL script to the database.
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'2', b'2', b'TEST2', b'1')' at line 1
SQL Statement:
INSERT INTO `test`.`MenusAccess` (`id`, `idMenus`, `Agent`, `Allowed`) VALUES (b'2', b'2', b'TEST2', b'1')
[5 Mar 2022 7:24] MySQL Verification Team 
Bug #106644 marked as duplicate of this one
[2 Dec 2022 6:29] MySQL Verification Team 
Bug #109266 marked as duplicate of this one