1

This drives me nuts... I have tried many different solutions, googled, and tried to find answers that really explains why I get the error "1062 Duplicate Error" in MySQL when I try to copy a table to another.

My table that I try to copy has the following contents:

Table: "mywines"
+----+--------------------------------+------------------+------------+
| id | name                           | winery           | grapes     |
+----+--------------------------------+------------------+------------+
| 1  | Champagne Tentation            | A. Bergère       |            |
| 2  | Champagne Les Clos             | A. Bergère       | Menunier   |
| 4  | Champagne Cuvée Selection      | Collard-Picard   |            |
| 8  | Les Vaupulans Chablis 1er Cru  | Vrignaud         | Chardonnay |
| 9  | Les Cotes de Fontenay 1er Cru  | Vrignaud         | Chardonnay |
| 10 | Domaine Sainte Claire          | Jean-Marc Brocard| Chardonnay |
+----+--------------------------------+------------------+------------+

The table is constructed and designed according to the following SQL script:

CREATE TABLE `mywines` (
  `id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL DEFAULT '',
  `winery` varchar(100) DEFAULT '',
  `grapes` varchar(200) DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `mywines` (`id`, `name`, `winery`, `grapes`) VALUES
(1, 'Champagne Tentation', 'A. Bergère', ''),
(2, 'Champagne Les Clos', 'A. Bergère', 'Menunier'),
(4, 'Champagne Cuvée Selection', 'Collard-Picard', ''),
(8, 'Les Vaupulans Chablis 1er Cru', 'Vrignaud', 'Chardonnay'),
(9, 'Les Cotes de Fontenay 1er Cru', 'Vrignaud', 'Chardonnay'),
(10, 'Domaine Sainte Claire', 'Jean-Marc Brocard', 'Chardonnay');

ALTER TABLE `mywines`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `mywines`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
COMMIT;

Now... I try to create a copy of this table. I run three SQL-commands:

DROP TABLE IF EXISTS `mywines_copy`;
CREATE TABLE `mywines_copy`  LIKE `mywines`; 
INSERT `mywines_copy` SELECT * FROM `mywines`;

First, drop any existing table copies. Second, create the mywines_copy with same structure as mywines. Finally, copy data to the new table. Here the following error occurs:

Error Code: 1062. Duplicate entry '1' for key 'PRIMARY'

I don't understand this error. No data is copied at all to the new table. How can there be a duplicate??? I have really tried to understand similar questions. I have experimented with different tables, columns, settings... but I still don't get it...

Gowire
  • 1,046
  • 6
  • 27
  • 2
    Not able to reproduce this behavior: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1856e235987d95d3939a874a043783e8. Are you sure this is the exact code you are running? – GMB Dec 20 '20 at 11:18
  • @GMB ... hmm... Actually I didn't try the generating scripts before posting. I simply exported the "mywines" table and thought that the error behaviour would continue to appear. In that case there's something else wrong that's not included when I export the table – Gowire Dec 20 '20 at 11:35

0 Answers0