Mysql hangs on multi table update on innodb table.

am 21.02.2003 16:52:26 von Scott Wong

Version: Mysql 4.0.10-gamma

Description: Mysql hangs and possibly can not recover from a query on =
an innodb table. =20

How to Repeat :

drop table if exists parent;
drop table if exists child;

CREATE TABLE parent(id INT NOT NULL,
PRIMARY KEY (id)) TYPE=3DINNODB;
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=3DINNODB;

insert into parent set id=3D1;
insert into child set id=3D1, parent_id=3D1;
update parent,child set parent.id=3Dparent.id+1, =
child.parent_id=3Dparent.id+1;

Thank you for your time.
Scott Wong,
Meiko America, INC

------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Mysql hangs on multi table update on innodb table.

am 05.03.2003 18:45:56 von Scott Wong

Hi, I havent received any response on this so i'll send this again.
I did check again with 4.0.11-gamma and the problem is still there.

Regards,
Scott

> Version: Mysql 4.0.10-gamma
>=20
> Description: Mysql hangs and possibly can not recover from a query on =
an innodb table. =20
>=20
> How to Repeat :
>=20
> drop table if exists parent;
> drop table if exists child;
>=20
> CREATE TABLE parent(id INT NOT NULL,
> PRIMARY KEY (id)) TYPE=3DINNODB;
> 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=3DINNODB;
>=20
> insert into parent set id=3D1;
> insert into child set id=3D1, parent_id=3D1;
> update parent,child set parent.id=3Dparent.id+1, =
child.parent_id=3Dparent.id+1;
>=20
>=20
> Thank you for your time.
> Scott Wong,
> Meiko America, INC
>=20

------------------------------------------------------------ ---------
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 bugs-thread13909@lists.mysql.com
To unsubscribe, e-mail

RE: Mysql hangs on multi table update on innodb table.

am 05.03.2003 19:05:14 von Sinisa Milivojevic

Scott Wong writes:
>
> Hi, I havent received any response on this so i'll send this again.
> I did check again with 4.0.11-gamma and the problem is still there.
>
>
> Regards,
> Scott
>
Hi!

This will require a major re-write in multi table update's and will
not come up very soon. But should come up with the end of this month.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ 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 bugs-thread13910@lists.mysql.com
To unsubscribe, e-mail

RE: Mysql hangs on multi table update on innodb table.

am 05.03.2003 19:05:14 von Sinisa Milivojevic

Re: Mysql hangs on multi table update on innodb table.

am 11.03.2003 18:31:52 von Sinisa Milivojevic

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 *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 *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
/ /|_/ / // /\ \/ /_/ / /__ 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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Mysql hangs on multi table update on innodb table.

am 11.03.2003 18:31:52 von Sinisa Milivojevic