"ERROR 1406: 1406: Data too long for column" but it shouldn't be?

Question

I have the following table structure:

DROP TABLE IF EXISTS `tblusers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tblusers` (
  `UserID` int(5) NOT NULL AUTO_INCREMENT,
  `ContactPersonID` int(5) NOT NULL,
  `NameOfUser` varchar(70) NOT NULL,
  `LegalForm` varchar(70) DEFAULT NULL,
  `Address` varchar(70) DEFAULT NULL,
  `City` varchar(50) DEFAULT NULL,
  `Postal` int(8) DEFAULT NULL,
  `Country` varchar(50) DEFAULT NULL,
  `VatNum` int(10) DEFAULT NULL,
  `Username` varchar(30) NOT NULL,
  `Password` varchar(20) NOT NULL,
  `Email` varchar(40) NOT NULL,
  `Website` varchar(40) DEFAULT NULL,
  `IsSeller` bit(1) DEFAULT NULL,
  `IsBuyer` bit(1) DEFAULT NULL,
  `IsAdmin` bit(1) DEFAULT NULL,
  `Description` text,
  PRIMARY KEY (`UserID`),
  KEY `ContactPersonID` (`ContactPersonID`),
  CONSTRAINT `tblusers_tblpersons` FOREIGN KEY (`ContactPersonID`) REFERENCES `tblpersons` (`PersonID`)
) ENGINE=InnoDB AUTO_INCREMENT=87 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

Then once I create a user from the UI of my application, I have to manually set the very first admin, and this is the only time I am doing this directly from the DB, all the rest is envisioned to be done from the UI (granting admin privileges):

UPDATE `tblusers` SET `IsAdmin`='1' WHERE `UserID`='79';

but then I get:

Operation failed: There was an error while applying the SQL script to the database.
Executing:
UPDATE `trace`.`tblusers` SET `IsAdmin`='1' WHERE `UserID`='79';

ERROR 1406: 1406: Data too long for column 'IsAdmin' at row 1
SQL Statement:
UPDATE `trace`.`tblusers` SET `IsAdmin`='1' WHERE `UserID`='79'

Which doesn't make sense because I am doing the exact same thing on other machines and it works like a charm. The only difference is that in this scenario I have mysql 5.7 server whereas I have 5.6 versions on the machines that this does work.

I tried the following solution but it didn't work for me. Besides that, the my.ini file is unchanged in the 5.6 machine where it does work.

Downgrading to 5.6 is out of the question. I need a real solution here please.

Answer 1

isadmin is a column of type bit and you are storing a value of type varchar in it which is of larger size than bit. modify query as follows:-

UPDATE `tblusers` SET `IsAdmin`=b'1'  WHERE `UserID`='79';

Answer 2

IsAdmin has the datatype of bit(1), yet you are assigning the string '1' to it. Indicate that you are assigning a bit value to it by preceeding the '1' with b or use 0b format:

UPDATE `tblusers` SET `IsAdmin`=b'1'  WHERE `UserID`='79';

or

UPDATE `tblusers` SET `IsAdmin`=0b1  WHERE `UserID`='79';

The reason for this behaviour is probably that strict_all_tables or strict_trans_tables setting is enabled on the v5.7 mysql server:

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE.

Answer 3

The BIT data type is used to store bit values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64.

UPDATE tblusers SET IsAdmin=b'1' WHERE UserID='012';

UPDATE tblusers SET IsAdmin=b'0' WHERE UserID='012';

Answer 4

I had the same problem when I synchronized the Model's table from MySQL Workbench to the MySQL server which had old tables with data. the data of old column types is longer than the new column types. (for example: the old column type is char[43] but the new column type is binary[32] so the new column type can't contain all of the old data)

my solution: drop the old table and then synchronized new Model with the old database