| Bug #1846 | mysqldump incorrectly records MERGE tables--can't restore (ID: 2076) | ||
|---|---|---|---|
| Submitted: | 15 Nov 2003 17:48 | Modified: | 29 Jan 2004 14:43 |
| Reporter: | Boyd Gerber | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: mysqldump Command-line Client | Severity: | S1 (Critical) |
| Version: | 4.0.16 | OS: | FreeBSD (FREEBSD) |
| Assigned to: | Sergei Glukhov | CPU Architecture: | Any |
[15 Nov 2003 17:50]
Boyd Gerber
On Thu, Nov 06, 2003 at 00:15:10AM +0100, Jeremy Zawodny wrote:
>
> Using the simple MERGE table example in the manual here (http://www.mysql.com/doc/en/MERGE.html), one can create a MERGE table in the "test" database and then dump it with mysqldump. The code below is what I used to create the tables. I then used mysqldump and got the attached file.
>
> mysql> use test;
> Database changed
> mysql> CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
> Query OK, 3 rows affected (0.00 sec)
> Records: 3 Duplicates: 0 Warnings: 0
>
> mysql> INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
> Query OK, 3 rows affected (0.00 sec)
> Records: 3 Duplicates: 0 Warnings: 0
>
> mysql> CREATE TABLE total (a INT NOT NULL AUTO_INCREMENT, message CHAR(20), KEY(a))
> -> TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> SELECT * FROM total;
> +---+---------+
> | a | message |
> +---+---------+
> | 1 | Testing |
> | 2 | table |
> | 3 | t1 |
> | 1 | Testing |
> | 2 | table |
> | 3 | t2 |
> +---+---------+
> 6 rows in set (0.00 sec)
>
> Then, trying to restore it, I get an error: "ERROR 1022 at line 57: Can't write, duplicate key in table 'total'" Looking at the dump file, the reason is obvious. MySQL is trying to insert into the MERGE table as well as the underlying "real" tables. The fix is probably to dump the only the CREATE TABLE for a MERGE table.
>
> The attached file shows the example.
>
> For what it's worth, this bug was noted in the annotated manual roughly 6 months ago, but I see no official response there at all.
[29 Jan 2004 14:18]
Sergei Golubchik
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at http://www.mysql.com/downloads/ Additional info: fixed in 4.0.17
Description: Using the simple MERGE table example in the manual here (http://www.mysql.com/doc/en/MERGE.html), one can create a MERGE table in the "test" database and then dump it with mysqldump. The code below is what I used to create the tables. I then used mysqldump and got the attached file. > > mysql> use test; > Database changed > mysql> CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)); > Query OK, 0 rows affected (0.00 sec) > > mysql> CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)); > Query OK, 0 rows affected (0.00 sec) > > mysql> INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1"); > Query OK, 3 rows affected (0.00 sec) > Records: 3 Duplicates: 0 Warnings: 0 > > mysql> INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2"); > Query OK, 3 rows affected (0.00 sec) > Records: 3 Duplicates: 0 Warnings: 0 > > mysql> CREATE TABLE total (a INT NOT NULL AUTO_INCREMENT, message CHAR(20), KEY(a)) > -> TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; > Query OK, 0 rows affected (0.00 sec) > > mysql> SELECT * FROM total; > +---+---------+ > | a | message | > +---+---------+ > | 1 | Testing | > | 2 | table | > | 3 | t1 | > | 1 | Testing | > | 2 | table | > | 3 | t2 | > +---+---------+ > 6 rows in set (0.00 sec) > > Then, trying to restore it, I get an error: "ERROR 1022 at line 57: Can't write, duplicate key in table 'total'" Looking at the dump file, the reason is obvious. MySQL is trying to insert into the MERGE table as well as the underlying "real" tables. The fix is probably to dump the only the CREATE TABLE for a MERGE table. > How to repeat: See above