CODEWITHSUNDEEP
mysqlsqlmysql-error-1136
Renier Swart
Renier Swart

Reputation: 85

Mysql Error 1136

I have read up on the error. It says that the sql passes more/less values than specified by table. But my table has 7 columns and i am sending 7 parameters, but still get the error. Please help

Books_out_on_loan

DROP TABLE IF EXISTS `books_out_on_loan`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `books_out_on_loan` (
  `book_borrow_id` int(11) NOT NULL,
  `member_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `isbn` varchar(13) NOT NULL,
  `date_issued` datetime NOT NULL,
  `date_due` datetime NOT NULL,
  `date_returned` datetime DEFAULT NULL,
  PRIMARY KEY (`book_borrow_id`),
  UNIQUE KEY `book_borrow_id_UNIQUE` (`book_borrow_id`),
  KEY `member_id_idx` (`member_id`),
  KEY `employee_id_idx` (`employee_id`),
  KEY `isbn_idx` (`isbn`),
  CONSTRAINT `employee_id` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_number`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `isbn-book-loan` FOREIGN KEY (`isbn`) REFERENCES `books` (`ISBN`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `member_id` FOREIGN KEY (`member_id`) REFERENCES `members` (`member_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SQL FILE:

INSERT INTO books_out_on_loan (book_borrow_id,member_id,employee_id,isbn,date_issued,date_due,date_returned)VALUES ('1','1','1','863758832-8','20160529','20160606',NULL);
INSERT INTO books_out_on_loan (book_borrow_id,member_id,employee_id,isbn,date_issued,date_due,date_returned)VALUES ('2','2','2','278860648-6','20160521','20160606',NULL);
INSERT INTO books_out_on_loan (book_borrow_id,member_id,employee_id,isbn,date_issued,date_due,date_returned)VALUES ('3','3','3','012454099-6','20160520','20160606',NULL);
INSERT INTO books_out_on_loan (book_borrow_id,member_id,employee_id,isbn,date_issued,date_due,date_returned)VALUES ('4','4','4','553185783-7','20160510','20160606',NULL);
INSERT INTO books_out_on_loan (book_borrow_id,member_id,employee_id,isbn,date_issued,date_due,date_returned)VALUES ('5','5','5','711473445-X','2016059','20160606',NULL);
INSERT INTO books_out_on_loan (book_borrow_id,member_id,employee_id,isbn,date_issued,date_due,date_returned)VALUES ('6','1','6','830664326-7','2016058','20160606',NULL);
INSERT INTO books_out_on_loan (book_borrow_id,member_id,employee_id,isbn,date_issued,date_due,date_returned)VALUES ('7','2','7','555264867-6','20160510','20160606',NULL);
INSERT INTO books_out_on_loan (book_borrow_id,member_id,employee_id,isbn,date_issued,date_due,date_returned)VALUES ('8','3','8','607119764-3','20160512','20160606',NULL);
INSERT INTO books_out_on_loan (book_borrow_id,member_id,employee_id,isbn,date_issued,date_due,date_returned)VALUES ('9','4','9','893613484-1','20160514','20160606',NULL);
INSERT INTO books_out_on_loan (book_borrow_id,member_id,employee_id,isbn,date_issued,date_due,date_returned)VALUES ('10','5','10','826004987-2','20160515','20160606',NULL);

SQL fiddle here, with working inserts : http://sqlfiddle.com/#!9/cbcc1

Upvotes: 1

Views: 328

Answers (3)

Mesut Uçar
Mesut Uçar

Reputation: 162

If you have any triggers like AFTER INSERT on the destination table and if there is a problem with this trigger, you will have this 1136 error which belongs actually to this trigger action. It is an old topic but checking these will be useful.

Upvotes: 2

Paul Stanley
Paul Stanley

Reputation: 4098

You have data truncates on these inserts, that might be causing an issue, I have corrected them here, 

INSERT INTO books_out_on_loan (book_borrow_id,member_id,employee_id,isbn,date_issued,date_due,date_returned)VALUES ('5','5','5','711473445-X','20160509','20160606',NULL);
INSERT INTO books_out_on_loan (book_borrow_id,member_id,employee_id,isbn,date_issued,date_due,date_returned)VALUES ('6','1','6','830664326-7','20160508','20160606',NULL);

Upvotes: 0

fstafai
fstafai

Reputation: 21

Try to put N before every varchar value and execute the query, like this: N'863758832-8' (this is one of your ISBN's). This N means converting your string to Unicode and I think this will help.

And Refer to this link There is an explanation of this. If this doesn't work you will also try to increase ISBN column from 13 to 20 maybe. Because hyphen will be taken as more than one character this mainly happens for UTF-8 encoding.

Upvotes: 0

Related Questions