Re: Duplicate entry '0' for key 'PRIMARY'
From: J.O. Aho <user_at_example.net>
Date: Tue, 11 Apr 2017 07:50:07 +0200
Message-ID: <el394gFs3haU1_at_mid.individual.net>
>
> No, I have tested it. Jerry is correct about the possibility.
>
> In theory, if the target table has
>
> | mysql> SHOW CREATE TABLE `t1`\G
> | […] CREATE TABLE `t1` (
> | `id` int(…) NOT NULL DEFAULT '0',
> | `foo` VARCHAR(50),
> | […]
> | PRIMARY KEY (`id`)
> | ) […]
>
> and the offending statement(s) is/are of the form
>
> INSERT INTO `t1` (`foo`) VALUES ('zero'),('one'),('two');
>
> And yet it is a distinct possibility. For a dump of a single table instead
> of the whole database, it might even be intentional.
Date: Tue, 11 Apr 2017 07:50:07 +0200
Message-ID: <el394gFs3haU1_at_mid.individual.net>
On 04/10/17 21:43, Thomas 'PointedEars' Lahn wrote:
> J.O. Aho wrote:
>>>> The problem is the backup file does not have the id column in it, >>> >>> It would be a very ill-conceived target table that has >>> >>> `id` INT(…) PRIMARY KEY DEFAULT '0' >> >> This would also require that original table didn't have the id (in your >> example) column. >> >> as the data dump would otherwise have the primary key. >> >> INSERT INTO `t1` VALUES (0,'zero'),(1,'one'),(2,'two');
>
> No, I have tested it. Jerry is correct about the possibility.
>
> In theory, if the target table has
>
> | mysql> SHOW CREATE TABLE `t1`\G
> | […] CREATE TABLE `t1` (
> | `id` int(…) NOT NULL DEFAULT '0',
> | `foo` VARCHAR(50),
> | […]
> | PRIMARY KEY (`id`)
> | ) […]
>
> and the offending statement(s) is/are of the form
>
> INSERT INTO `t1` (`foo`) VALUES ('zero'),('one'),('two');
[Quoted] No, if you make a default dump (not option given) you will get the id column too when you use primary key not null default 0.
If you have had auto_increment from the start, the id will start from 1 and you still have the id.
The simple reason is that the dump do not guarantee that data will be in the same order as the primary key value and loading the dump could make your database inconsistent.
>> Just think of the idea where you wouldn't have the primary key and you >> insert data into another database, which may been used and tables just >> truncated, so then the auto_increment wouldn't necessarily start from 1 >> and you would suddenly have an inconsistent database and a lot of errors >> when key constraints wouldn't work.
>
> And yet it is a distinct possibility. For a dump of a single table instead
> of the whole database, it might even be intentional.
[Quoted] The issue still there and wouldn't happen with a default dump.
As the OP not given any more information, so all we say are just speculations.
-- //AhoReceived on Tue Apr 11 2017 - 07:50:07 CEST