Scott Wong writes:
>
> Version: Mysql 4.0.10-gamma
>
> Description: Mysql hangs and possibly can not recover from a query on an innodb
> table.
>
> How to Repeat :
>
> drop table if exists parent;
> drop table if exists 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
> ON UPDATE CASCADE
> ) TYPE=INNODB;
>
> insert into parent set id=1;
> insert into child set id=1, parent_id=1;
> update parent,child set parent.id=parent.id+1, child.parent_id=parent.id+1;
>
>
> Thank you for your time.
> Scott Wong,
> Meiko America, INC
>
Hi!
Thank you for your bug report.
It helped us fix a bug in multi-table updates with InnoDB. A fix will
come in 4.0.12, which should come out in a week.
This is a patch:
===== sql/sql_class.h 1.141 vs edited =====
*** /tmp/sql_class.h-1.141-18011 Wed Mar 5 19:43:53 2003
--- edited/sql/sql_class.h Tue Mar 11 18:34:13 2003
***************
*** 831,837 ****
uint table_count;
Copy_field *copy_field;
enum enum_duplicates handle_duplicates;
! bool do_update, trans_safe, transactional_tables, log_delayed;
public:
multi_update(THD *thd_arg, TABLE_LIST *ut, List<Item> *fields,
--- 831,837 ----
uint table_count;
Copy_field *copy_field;
enum enum_duplicates handle_duplicates;
! bool do_update, trans_safe, transactional_tables, log_delayed, on_the_fly;
public:
multi_update(THD *thd_arg, TABLE_LIST *ut, List<Item> *fields,
===== sql/sql_update.cc 1.75 vs edited =====
*** /tmp/sql_update.cc-1.75-18011 Wed Feb 19 16:08:27 2003
--- edited/sql/sql_update.cc Tue Mar 11 18:59:11 2003
***************
*** 413,419 ****
:all_tables(table_list), update_tables(0), thd(thd_arg), tmp_tables(0),
updated(0), found(0), fields(field_list), values(value_list),
table_count(0), copy_field(0), handle_duplicates(handle_duplicates_arg),
! do_update(1), trans_safe(0)
{}
--- 413,419 ----
:all_tables(table_list), update_tables(0), thd(thd_arg), tmp_tables(0),
updated(0), found(0), fields(field_list), values(value_list),
table_count(0), copy_field(0), handle_duplicates(handle_duplicates_arg),
! do_update(1), trans_safe(0), on_the_fly(1)
{}
***************
*** 538,549 ****
main_table=join->join_tab->table;
trans_safe= transactional_tables= main_table->file->has_transactions();
log_delayed= trans_safe || main_table->tmp_table != NO_TMP_TABLE;
!
/* Create a temporary table for all tables after except main table */
for (table_ref= update_tables; table_ref; table_ref=table_ref->next)
{
TABLE *table=table_ref->table;
! if (table != main_table)
{
uint cnt= table_ref->shared;
ORDER group;
--- 538,552 ----
main_table=join->join_tab->table;
trans_safe= transactional_tables= main_table->file->has_transactions();
log_delayed= trans_safe || main_table->tmp_table != NO_TMP_TABLE;
! #ifdef HAVE_INNOBASE_DB
! if (main_table->db_type == DB_TYPE_INNODB)
! on_the_fly=0;
! #endif
/* Create a temporary table for all tables after except main table */
for (table_ref= update_tables; table_ref; table_ref=table_ref->next)
{
TABLE *table=table_ref->table;
! if (!on_the_fly || table != main_table)
{
uint cnt= table_ref->shared;
ORDER group;
***************
*** 623,629 ****
uint offset= cur_table->shared;
table->file->position(table->record[0]);
! if (table == main_table)
{
table->status|= STATUS_UPDATED;
store_record(table,1);
--- 626,632 ----
uint offset= cur_table->shared;
table->file->position(table->record[0]);
! if (on_the_fly && table == main_table)
{
table->status|= STATUS_UPDATED;
store_record(table,1);
***************
*** 716,722 ****
for (cur_table= update_tables; cur_table ; cur_table= cur_table->next)
{
table = cur_table->table;
! if (table == main_table)
continue; // Already updated
org_updated= updated;
--- 719,725 ----
for (cur_table= update_tables; cur_table ; cur_table= cur_table->next)
{
table = cur_table->table;
! if (on_the_fly && table == main_table)
continue; // Already updated
org_updated= updated;
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / 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
