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