MySQL (Percona) Error 1452: Cannot add or update a child row for no reason

I have 2 Database Tables “users” and “tasks”. The tasks table contains two foreign keys for the createdBy_user_id and updatedBy_user_id columns both referencing users.id. If I try to insert an entry to tasks (after making sure the user referenced by the foreign keys exists) like this:

INSERT INTO tasks (createdBy_user_id,updatedBy_user_id,noOfYear,createdAt,updatedAt,status,customer_id)
VALUES (1,1,1,NOW(),NOW(),"open",1)

The query fails with Error 1452:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`tasks`, CONSTRAINT `user_id_fk_constr` FOREIGN KEY (`createdBy_user_id`) REFERENCES `users` (`id`))

I don’t know why this happens, because everything works fine if I remove the constraint. The same error does not happen for the “updatedBy_user_id” column making this such confusing.

The Tables have the following DDL:

Users table:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `email_confirmed_at` datetime DEFAULT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `job` varchar(64) DEFAULT NULL,
  `position` varchar(64) DEFAULT NULL,
  `specialKnowledge` text,
  `tasks` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Tasks table:

CREATE TABLE `tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `noOfYear` int(11) NOT NULL,
  `year` int(11) GENERATED ALWAYS AS (right(year(`createdAt`),2)) VIRTUAL NOT NULL,
  `createdBy_user_id` int(11) NOT NULL,
  `updatedBy_user_id` int(11) NOT NULL,
  `status` enum('open','closed') NOT NULL,
  `customer_id` int(11) NOT NULL,
  `projectDescription` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tasks_year_unique_constr` (`year`,`noOfYear`),
  KEY `user_id_fk_constr` (`createdBy_user_id`),
  KEY `customer_id_fk_constr` (`customer_id`),
  KEY `user_up_id_fk_constr` (`updatedBy_user_id`),
  CONSTRAINT `customer_id_fk_constr` FOREIGN KEY (`customer_id`) REFERENCES `Customer` (`id`),
  CONSTRAINT `user_id_fk_constr` FOREIGN KEY (`createdBy_user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `user_up_id_fk_constr` FOREIGN KEY (`updatedBy_user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 

As you can see the datatypes match and both tables use the InnoDB Engine. The users table contains one entry:

select id,username from users;

+----+----------+
| id | username |
+----+----------+
|  1 | admin    |
+----+----------+
1 row in set (0.00 sec)

So there is no obvious reason, why the insertion should fail. Have you an idea what’s wrong with my tables? Looks like a software bug.

Answer

This is a bug. MySQL 5.7 has had some troubles with generated columns and foreign keys, and I assume this is an unfixed variant of Bug #79772 Foreign key not allowed when a virtual index exists.

In your particular case, and probably depending on your exact version, any of the following modifications seems to prevent that bug from occurring:

• do not use a virtual column, but make it stored
• do not create a foreign key for a column directly following the virtual column, so e.g. change the column order to year, status, createdBy_user_id, updatedBy_user_id.
• do not use a unique index on the virtual column, a normal index should be fine (at least in a version where the linked bug is fixed). You want a unique constraint, so this is not an option, but the fact that this fixes your problem emphasized the “bug” nature of the problem.

The second bullet point seems to be the underlying bug: I assume that some iterator doesn’t count the virtual column properly, so the foreign key that shall check createdBy_user_id seems to mix up the columns and actually checks the value of year (in this case “20”) against the users table. So if you have a user with id “20” in your users table, the foreign key will actually accept this, no matter what value for createdBy_user_id you are trying to insert, see the MySQL 5.7.29 fiddle.

Unless you have a specific reason to use a virtual column, using stored is probably the sane thing to do.