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
