Bug with innodb multi-table delete and foreign keys.

am 12.02.2003 23:16:01 von Scott Wong

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=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
) TYPE=3DINNODB;=20

insert into parent set id=3D1;
insert into child set id=3D1, parent_id=3D1;
insert into grandchild set id=3D1, child_id=3D1;

delete parent,child from parent,child where parent.id=3Dchild.parent_id;
ERROR 1105: Unknown error=20

fix
?

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: Bug with innodb multi-table delete and foreign keys.

am 13.02.2003 20:30:43 von Sinisa Milivojevic

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
/ /|_/ / // /\ \/ /_/ / /__ 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: Bug with innodb multi-table delete and foreign keys.

am 13.02.2003 20:30:43 von Sinisa Milivojevic

RE: Bug with innodb multi-table delete and foreign keys.

am 13.02.2003 22:58:48 von Scott Wong

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.=20
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;
=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
) TYPE=3DINNODB;=20
=20
insert into parent set id=3D1;
insert into child set id=3D1, parent_id=3D1;
delete parent,child from parent,child where =
parent.id=3Dchild.parent_id;

now i get=20
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:sinisa@mysql.com]
Sent: Thursday, February 13, 2003 1:31 PM
To: Scott Wong
Cc: mysql@lists.mysql.com; bugs@lists.mysql.com
Subject: Re: Bug with innodb multi-table delete and foreign keys.

Scott Wong writes:
> Version: Mysql 4.0.10-gamma
>=20
>=20
> Description: ERROR 1105: Unknown error after issuing a multi-table =
delete on parent/child table.
> if there's no foreign keys .. works ok.
>=20
> How to Repeat :
>=20
> drop table parent;
> drop table 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
> ) TYPE=3DINNODB;=20
>=20
>=20
> insert into parent set id=3D1;
> insert into child set id=3D1, parent_id=3D1;
> insert into grandchild set id=3D1, child_id=3D1;
>=20
> delete parent,child from parent,child where =
parent.id=3Dchild.parent_id;
> ERROR 1105: Unknown error=20
>=20
>=20
> fix
> ?
>=20
>=20
> Thank you for your time
>=20
> 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:

=====3D sql/sql_delete.cc 1.91 vs edited =====3D
*** /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 =3D 0;
}
return local_error;
}
--- 429,441 ----
deleted++;
}
end_read_record(&info);
! if (local_error)
! {
! if (local_error == -1) // End of =
file
! local_error =3D 0;
! else if (table->db_type == DB_TYPE_INNODB && local_error < =
1000)
! local_error =3D ER_MULTI_TABLE_UPDATE_DELETE_WITH_INNODB;
! }
}
return local_error;
}
***************
*** 475,487 ****
/* Commit or rollback the current SQL statement */=20
if (transactional_tables)
if (ha_autocommit_or_rollback(thd,local_error > 0))
! local_error=3D1;
=20
if (deleted)
query_cache_invalidate3(thd, delete_tables, 1);
=20
! if (local_error)
! ::send_error(&thd->net);
else
::send_ok(&thd->net,deleted);
return 0;
--- 480,492 ----
/* Commit or rollback the current SQL statement */=20
if (transactional_tables)
if (ha_autocommit_or_rollback(thd,local_error > 0))
! error=3D(error) ? error : local_error;
=20
if (deleted)
query_cache_invalidate3(thd, delete_tables, 1);
=20
! if (error)
! ::send_error(&thd->net, error);
else
::send_ok(&thd->net,deleted);
return 0;

--=20
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / 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: Bug with innodb multi-table delete and foreign keys.

am 14.02.2003 12:49:41 von Sinisa Milivojevic

Scott Wong writes:
> 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

Yes, of course. I have sent just an excerpt.

Full patch is rather big and is not appropriate for mail.

Plus I am still improving it in order to work under all conditions.

Full patch will be in 4.0.11.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / 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: Bug with innodb multi-table delete and foreign keys.

am 14.02.2003 12:49:41 von Sinisa Milivojevic