Bug #41689 Foreign keys: false error message
Submitted: 23 Dec 2008 0:15 Modified: 20 Dec 2013 8:24
Reporter: Peter Gulutzan Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:6.1.0-alpha-debug OS:Linux (SUSE 10.0 / 32-bit)
Assigned to: CPU Architecture:Any

[23 Dec 2008 0:15] Peter Gulutzan
Description:
I'm using mysql-6.1-fk.
I start the server with mysqld --foreign-key-all-engines=1.

I have a self-referencing table.
I insert into it while @@foreign_key_checks = 0.
I update it while @@foreign_key_checks = 1;
I get an error about "table doesn't exist".
It's fine to get an error, but the table certainly exists.

How to repeat:
drop table if exists a1;
drop table if exists a2;
create table a1 (s1 int unique not null, s2 int unique not null,
                 foreign key (s1) references a1(s2) on update cascade,
                 foreign key (s2) references a1(s1) on update cascade) engine=falcon;
set @@foreign_key_checks = 0;
insert into a1 values (0,0);
set @@foreign_key_checks = 1;
update a1 set s1 = s1;
/* result is a "table doesn't exist" error message 8/
[23 Dec 2008 14:55] MySQL Verification Team
Thank you for the bug report. Verified as described.
[30 Mar 2009 17:42] Peter Gulutzan
Here is another way to get a "not exists" message.

I started the server with:
libexec/mysqld --user=root --foreign-key-all-engines=1 --log-bin=/tmp/qqj

I did this on the mysql client:
USE test
SET @@storage_engine=falcon;
SET @@autocommit = 0;
DROP TABLE IF EXISTS t3,t2,t1;
CREATE TABLE t1 (s1 INT NOT NULL UNIQUE, s2 INT NOT NULL UNIQUE);
CREATE TABLE t2 (s1 INT NOT NULL UNIQUE, s2 INT NOT NULL UNIQUE,
    FOREIGN KEY (s2) REFERENCES t1 (s2) ON UPDATE CASCADE);
CREATE TABLE t3 (s1 INT DEFAULT 2, s2 INT DEFAULT 2,
    FOREIGN KEY (s1) REFERENCES t1 (s1),
    FOREIGN KEY (s2) REFERENCES t2 (s2) ON UPDATE SET DEFAULT);
UPDATE t1 SET s1 = s1 + 1, s2 = s2 = 1;
[20 Dec 2013 8:24] Erlend Dahl
The 6.x project has been abandoned.