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

Question

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

MySQL has an `AUTO_INCREMENT` field that can do what you want ... http://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html — Bart Friederichs, Sep 24 '16 at 12:33

score 1 · Accepted Answer · answered Sep 24 '16 at 12:37

1

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

answered Sep 24 '16 at 12:37

Bart Friederichs

33,050
15
95
195

EDIT: nvm, I fixed it using this answer: http://stackoverflow.com/questions/15300673/mysql-error-cant-update-table-in-stored-function-trigger-because-it-is-already – Matthias Hoste Sep 24 '16 at 12:40

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

1 Answers1