"Unknown error" while executing multitable UPDATE

Hey,

This is done on MySQL 4.0.5a database. Tested on both MyISAM and InnoDB
tables.

How-To-Repeat:

mysql> CREATE TABLE t1 ( id int not null, str varchar(20) not null,
primary key(str), unique(id) );
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE t2 ( id int not null, str varchar(20) not null,
primary key(str), unique(id) );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES ('1', 'test1'), ('2', 'test2'), ('3',
'test3');
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 3

mysql> INSERT INTO t2 VALUES ('5', 'test1'), ('1', 'test2'), ('55',
'test3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> UPDATE t1,t2 SET t2.id=t1.id WHERE t1.str=t2.str;
ERROR 1105: Unknown error

Obviously, the error message should say "Duplicate entry '1' for key 2".



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

Re: "Unknown error" while executing multitable UPDATE

Maciek Dobrzanski writes:
> Hey,
>
> This is done on MySQL 4.0.5a database. Tested on both MyISAM and InnoDB
> tables.
>
> How-To-Repeat:
>
> mysql> CREATE TABLE t1 ( id int not null, str varchar(20) not null,
> primary key(str), unique(id) );
> Query OK, 0 rows affected (0.05 sec)
>
> mysql> CREATE TABLE t2 ( id int not null, str varchar(20) not null,
> primary key(str), unique(id) );
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> INSERT INTO t1 VALUES ('1', 'test1'), ('2', 'test2'), ('3',
> 'test3');
> Query OK, 3 rows affected (0.04 sec)
> Records: 3 Duplicates: 0 Warnings: 3
>
> mysql> INSERT INTO t2 VALUES ('5', 'test1'), ('1', 'test2'), ('55',
> 'test3');
> Query OK, 3 rows affected (0.00 sec)
> Records: 3 Duplicates: 0 Warnings: 0
>
> mysql> UPDATE t1,t2 SET t2.id=t1.id WHERE t1.str=t2.str;
> ERROR 1105: Unknown error
>
> Obviously, the error message should say "Duplicate entry '1' for key 2".

Hi!

Thank you for your bug report.

There have been some recent changes in the code for multi-table
updates, which fixed the above bug.

Those changes will be implemented in 4.0.6, for which we do not yet
have a release date.

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


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