“ERROR 1406: 1406: Data too long for column” but it shouldn’t be?

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *