Hi sinisa,
I tried this patch.
It came up with undefined symbol for ER_MULTI_TABLE_UPDATE_DELETE_WITH_INNODB on
compile
so i went ahead and added to include/mysqld_error.h for testing.
i'm not sure if it's the right place or the right error code but it made mysql compile
***************
256a257
> #define ER_MULTI_TABLE_UPDATE_DELETE_WITH_INNODB 1238
***************
then ran the query again.
drop table parent;
drop table child;
CREATE TABLE parent(id INT NOT NULL,
PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT PRIMARY KEY, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) TYPE=INNODB;
insert into parent set id=1;
insert into child set id=1, parent_id=1;
delete parent,child from parent,child where parent.id=child.parent_id;
now i get
ERROR 2013: Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
030213 15:54:20 mysqld restarted
regards,
Scott Wong
-----Original Message-----
From: Sinisa Milivojevic [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 13, 2003 1:31 PM
To: Scott Wong
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Bug with innodb multi-table delete and foreign keys.
Scott Wong writes:
> Version: Mysql 4.0.10-gamma
>
>
> Description: ERROR 1105: Unknown error after issuing a multi-table delete on
>parent/child table.
> if there's no foreign keys .. works ok.
>
> How to Repeat :
>
> drop table parent;
> drop table child;
>
> CREATE TABLE parent(id INT NOT NULL,
> PRIMARY KEY (id)) TYPE=INNODB;
> CREATE TABLE child(id INT PRIMARY KEY, parent_id INT,
> INDEX par_ind (parent_id),
> FOREIGN KEY (parent_id) REFERENCES parent(id)
> ON DELETE CASCADE
> ) TYPE=INNODB;
>
>
> insert into parent set id=1;
> insert into child set id=1, parent_id=1;
> insert into grandchild set id=1, child_id=1;
>
> delete parent,child from parent,child where parent.id=child.parent_id;
> ERROR 1105: Unknown error
>
>
> fix
> ?
>
>
> Thank you for your time
>
> Scott Wong
> Meiko America, INC
You have got an error due to the fact that cascading deletes deleted
matching rows already.
I have fixed this by introducing a correct error message:
===== sql/sql_delete.cc 1.91 vs edited =====
*** /tmp/sql_delete.cc-1.91-1826 Fri Nov 29 16:40:15 2002
--- edited/sql/sql_delete.cc Thu Feb 13 21:28:24 2003
***************
*** 429,436 ****
deleted++;
}
end_read_record(&info);
! if (local_error == -1) // End of file
! local_error = 0;
}
return local_error;
}
--- 429,441 ----
deleted++;
}
end_read_record(&info);
! if (local_error)
! {
! if (local_error == -1) // End of file
! local_error = 0;
! else if (table->db_type == DB_TYPE_INNODB && local_error < 1000)
! local_error = ER_MULTI_TABLE_UPDATE_DELETE_WITH_INNODB;
! }
}
return local_error;
}
***************
*** 475,487 ****
/* Commit or rollback the current SQL statement */
if (transactional_tables)
if (ha_autocommit_or_rollback(thd,local_error > 0))
! local_error=1;
if (deleted)
query_cache_invalidate3(thd, delete_tables, 1);
! if (local_error)
! ::send_error(&thd->net);
else
::send_ok(&thd->net,deleted);
return 0;
--- 480,492 ----
/* Commit or rollback the current SQL statement */
if (transactional_tables)
if (ha_autocommit_or_rollback(thd,local_error > 0))
! error=(error) ? error : local_error;
if (deleted)
query_cache_invalidate3(thd, delete_tables, 1);
! if (error)
! ::send_error(&thd->net, error);
else
::send_ok(&thd->net,deleted);
return 0;
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com
Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
