phpmyadmin Question posted in
The official documentation can be found here.

Warning #1366 on phpMyAdmin INSERT even with a BEFORE INSERT TRIGGER code to resolve it. Natural?

Summary

When I use the phpMyAdmin GUI to insert a new entry into my table (which has a BEFORE INSERT TRIGGER applied to it), it seems to insert the entry just fine… but it always displays this in response:

( ! ) 1 row inserted.
Warning: #1366 Incorrect integer value: ” for column ‘line_id’ at row 1

What am I doing wrong? Is there a better way to set up the trigger so I don’t get the error?

Background

Note: You can probably skip the code blocks as you read.

Using phpMyAdmin, I created my second table with this SQL statement. (This worked fine.)

CREATE TABLE IF NOT EXISTS `myDb`.`line_item` (
  `order_id` INT NOT NULL,
  `line_id` INT NOT NULL,
  `line_text` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`order_id`, `line_id`),
  CONSTRAINT `FK_order_line_item`
    FOREIGN KEY (`order_id`)
    REFERENCES `myDb`.`order` (`order_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

You’ll note there’s no AUTO_INCREMENT imposed on line_id. That is because we want it to reset it’s numbering with each new order_id. To accomplish this resetting number, we presumed a TRIGGER was most appropriate for the task.

When I tried to add a TRIGGER with this code, phpMyAdmin said it couldn’t make it. (Something to do about "permissions" or such, but I quickly resorted to a built-in workaround after this little adventure.)

DELIMITER $$
USE `myDb`$$
CREATE DEFINER = CURRENT_USER TRIGGER `myDb`.`line_id_incrementer` 
BEFORE INSERT ON `line_item` 
FOR EACH ROW
BEGIN
    DECLARE i INT;
    SELECT  COALESCE(MAX(line_id), 0) + 1
    INTO    i
    FROM    line_item
    WHERE   order_id = NEW.order_id;
    SET NEW.line_id = i;
END$$

DELIMITER ;

When the above SQL statement didn’t work, I simply used the GUI of phpMyAdmin to add the trigger to the table.

Server:localhost > Database:myDb > Table:line_item > "Triggers" Tab > "New" > "Add Trigger"

Trigger Name: line_id_incrementer
Table: line_item
Time: BEFORE
Event: INSERT
Definition:

BEGIN
    DECLARE i INT;
    SELECT  COALESCE(MAX(line_id), 0) + 1
    INTO    i
    FROM    line_item
    WHERE   order_id = NEW.order_id;
    SET NEW.line_id = i;
END

So far so fair.

Performing a practice run, I inserted a test entry into the ‘order’ table via the phpMyAdmin GUI (the ‘Insert’ Tab while viewing the ‘order’ table.): No problems there.

When I inserted a test entry for ‘line_id’ via the phpMyAdmin GUI, I left the NOT NULL ‘line_id’ field empty, to see if the trigger would fill it in correctly for me. And that’s when I got this:

( ! ) 1 row inserted.
Warning: #1366 Incorrect integer value: ” for column ‘line_id’ at row 1

With the generated code shown as:

INSERT INTO `line_item` 
    (`order_id`, `line_id`, `line_text`) 
    VALUES ('1', '', 'This is a test line for the incrementer trigger');

What is interesting is: It inserted the entry as expected (with a 1 as the line_id). When I inserted a second entry, the warning still showed, but the next entry was also entered as expected (with a 2 as the line_id).

So, the rows seem to be inserted just fine, but I keep getting that nagging Warning which makes me suspect I didn’t do something up to "Best Practice Standards".

Tags:

2

Answers


  1. 0

    The problem with your trigger is that theSELECT query will not return anything if there are no rows yet in the table for the current order. One solution is to move the COALESCE to the SET :

    BEGIN
DECLARE i INT;
    SELECT  MAX(line_id) + 1
    INTO    i
    FROM    line_item
    WHERE   order_id = NEW.order_id;
    SET NEW.line_id = COALESCE(i, 1);
END
    Login or Signup to reply.
  2. 0

    I don’t think your table definition is appropriate for what you wish to achieve. In particular the line_id and line_text are defined as not null. The problem with this is that the insert statement carries out the not null tests prior to the trigger being actioned and fails (I am assuming that your insert is only order_id and possibly line_text). You might want to look at setting default values. For example.

    drop table if exists line_item;
CREATE TABLE `line_item` (
  `order_id` INT NOT NULL,
  `line_id` INT not null default 0,
  `line_text` VARCHAR(100) not null default '',
  PRIMARY KEY (`order_id`, `line_id`) #,
  #CONSTRAINT `FK_order_line_item`
  #  FOREIGN KEY (`order_id`)
  #  REFERENCES `myDb`.`order` (`order_id`)
  #  ON DELETE NO ACTION
  #  ON UPDATE NO ACTION)
  );

 drop trigger if exists t;
 delimiter $$
 create trigger t before insert on line_item 
 for each row
 BEGIN
DECLARE i INT;
    SELECT  MAX(line_id) + 1
    INTO    i
    FROM    line_item
    WHERE   order_id = NEW.order_id;
    SET NEW.line_id = COALESCE(i, 1);
END $$
delimiter $$

insert into line_item (order_id) values (1),(1);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

+----------+---------+-----------+
| order_id | line_id | line_text |
+----------+---------+-----------+
|        1 |       1 |           |
|        1 |       2 |           |
+----------+---------+-----------+
2 rows in set (0.00 sec)

    I don’t use phpmyadmin and have no idea why it shows the warning it does but I suggest you run the insert from command line sql to see what error it throws.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search