Matthias Hoste Matthias Hoste - 2 months ago 13
MySQL Question

MySql Column count doesn't match value count at row 1 but all values are filled

I have my small database for the user records, when I insert a new user I want the id to be auto-generated, so I made my database using the following sql script:

CREATE TABLE `members` (
`idmembers` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(145) DEFAULT NULL,
`password` varchar(545) DEFAULT NULL,
`clearance` int(5) DEFAULT NULL,
`profilepic` text,
`createdby` varchar(45) DEFAULT NULL,
`friends` varchar(545) DEFAULT NULL,
PRIMARY KEY (`idmembers`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


and the following trigger:

CREATE DEFINER=`root`@`localhost` TRIGGER `fuhrergroup`.`members_BEFORE_INSERT` BEFORE INSERT ON `members` FOR EACH ROW
BEGIN
INSERT INTO members VALUES (NULL);
SET NEW.idmembers = CONCAT('F', LPAD(LAST_INSERT_ID(), 3, '0'));
END


However when I want to insert:

INSERT INTO fuhrergroup.members (idmembers, username, password, clearance, profilepic, createdby, friends) VALUES (NULL, 'admin', 'secret', '5', 'aa', 'System', '')


I always get:
Error Code: 1136. Column count doesn't match value count at row 1

Googling didn't really help

Answer

Your trigger has an incorrect query:

INSERT INTO members VALUES (NULL);

You should tell MySQL which column has to become NULL:

INSERT INTO members (columnname) VALUES (NULL);