MySQL Bugs: #35725: intermittent invalid "can't update table in stored function/trigger"

Bug #35725	intermittent invalid "can't update table in stored function/trigger"
Submitted:	31 Mar 2008 22:31	Modified:	13 Jun 2008 15:23
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.51a	OS:	Linux (RHEL5)
Assigned to:		CPU Architecture:	Any

Description:
Occasionally, MySQL would incorrectly reject an 'insert' operation with "after insert" trigger with this error:

Can't update table 'xxx' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

How to repeat:
CREATE TABLE users (
  user_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
  password varchar(255)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE password_history (
  user_id int unsigned NOT NULL,
  password varchar(255),
  PRIMARY KEY (user_id, password),
  FOREIGN KEY (user_id) REFERENCES users (user_id) on delete cascade on update cascade
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TRIGGER all_user_autoregister AFTER insert ON users
  FOR EACH ROW BEGIN
    insert ignore into password_history (user_id, password) values (new.user_id, new.password);
  END;

lock tables users write;

insert into users (user_id, password) values (1, "12345");

At this point sometimes "Can't update table 'password_history' in stored function/trigger because it is already used by statement which invoked this stored function/trigger." error message appears. We are unable to repeat reliably, when it starts happening more consistently, restarting the mysql service solves the problem.

This is a MySQL feature/bug. InnoDB does not give the error message quoted here.

I could not repeat this issue on server built from source. Could you please try with newer released version. Thanks in advance.

I have the same problem, what I can not really reproduce. It happened yesterday evening, than till 10:00 AM today not, than again.

"withheld", what are your experiences?
- Restarting mysqld solves the problem for a while?
- Do you have experiences with newer mysql.

The affected system: CentOS 5.5, mysql 5.0.77