Error: Out of range value for column 'id' when using an after insert trigger in MySQL

Question

I have a MySQL table named products with the following structure:

domain_id varchar(10),
data_source_id int(12),
id bigint(64),
title varchar(255)`

This table has an after insert trigger that inserts the values of domain_id, data_source_id, and id into a new table.

I'm attempting to insert a new record into the products table using the following query:

INSERT INTO products (domain_id, data_source_id, id, title) VALUES ("12345678", 123, 8083207880923, "test")

However, I encountered the error

Out of range value for column 'id' at row 1.

As you can see this is the BIGINT column and the number "8083207880923"

is well within its range. Also, the error disappears when the trigger is removed.

I'm clueless and confused as to why this is happening. Any help or directions on this would be greatly helpful. Thanks in advance!!

DDL query for table products:

CREATE TABLE products (
    domain_id varchar(16) NOT NULL,
    data_sources_id int(12) UNSIGNED NOT NULL,
    id bigint(64) NOT NULL,
    title varchar(255) NOT NULL,
    PRIMARY KEY (domain_id, data_sources_id, id)
  );`
  

DDL query of the new table where the trigger is updating data:

CREATE TABLE products_process (
    id int(10) UNSIGNED NOT NULL AUTO_INCREMENT primary key,
    domain_id varchar(10) NOT NULL,
    data_sources_id int(10) NOT NULL,
    product_id varchar(20) NOT NULL,
    processed tinyint DEFAULT 0,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
  );
  

DDL query for trigger:

CREATE TRIGGER product_insert_trigger
  AFTER INSERT ON products
  FOR EACH ROW
  BEGIN
  INSERT INTO products_process (domain_id, data_sources_id, id)
  VALUES (NEW.domain_id, NEW.data_sources_id, NEW.id);
END;`

Answer 1

The id in the target table (products_process) of the trigger defined as int(10) and you try to insert the bigint to that.. That can't work.

Also this column is an auto_increment, you shouldn't set it by the trigger