Re: Duplicate entry '0' for key 'PRIMARY'

[Quoted] J.O. Aho wrote:

> On 04/10/17 06:39, Thomas 'PointedEars' Lahn wrote:

>> Jerry Stuckle wrote:
>>> J.O. is correct and you are wrong (as usual).
>> No, J.O. and you are wrong.

When the previously removed/disabled constraints are restored/re-enabled, and in general if constraints are attempted to be defined on or relating to a table that already contains records, error messages will be issued if the data does not conform to them; the offending records can be detected and fixed by means of the DBMS, as I indicated near the bottom of my previous follow-up.

It has been my experience that it is easier and less error-prone to modify the data in the database than in plain text. (You need not and maybe should not perform a “hot replace”; you can and maybe should import the data into a temporary database, and update the production database when the temporary database is consistent.)

With InnoDB, foreign key checks and/or UNIQUE constraint checks on secondary keys can be disabled temporarily:

  SET unique_checks=0;
  SET foreign_key_checks=0;

• import

  SET foreign_key_checks=1;
  SET unique_checks=1;

(In fact, that is included in the recommended approach for bulk data loading, to improve performance:

<https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-bulk-data-loading.html>)

With MyISAM only (at least it does not appear to work with InnoDB in MySQL 5.6.30 on Debian GNU/Linux), all key constraints can be disabled temporarily:

  ALTER TABLE … DISABLE KEYS;

• import

  ALTER TABLE … ENABLE KEYS;

So it is not necessary to remove and restore constraints in every case.

>>> 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'

[Quoted] [Quoted] 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');

instead, one would get the error message that the OP got:

| Duplicate entry '0' for key 'PRIMARY'

(If you add a PRIMARY KEY constraint on a column with a numeric type, the column is automatically defined NOT NULL DEFAULT '0', at least with InnoDB in the mentioned MySQL version.)

However, as I indicated, if I were to design the target table, I would certainly define

  CREATE TABLE `t1` (
    `id` INT(…) NOT NULL AUTO_INCREMENT,     `foo` VARCHAR(50),
    PRIMARY KEY (`id`)
  ) …

That is why I said it might already be sufficient to add AUTO_INCREMENT to the PRIMARY KEY column of the target table to solve the problem at hand:

  ALTER TABLE `t1` AUTO_INCREMENT=1;
  ALTER TABLE `t1` CHANGE COLUMN `id` `id` INT(…) NOT NULL AUTO_INCREMENT;

[It is important to note that you cannot drop a PRIMARY KEY of a column with AUTO_INCREMENT –

| mysql> SHOW CREATE TABLE `tmp2`\G
| *************************** 1. row ***************************
|        Table: tmp2
| Create Table: CREATE TEMPORARY TABLE `tmp2` (
|   `id` int(11) NOT NULL AUTO_INCREMENT,
|   `foo` varchar(50) DEFAULT NULL,
|   PRIMARY KEY (`id`)
| ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
| 1 row in set (0.00 sec)
| 
| mysql> DROP INDEX `PRIMARY` ON `tmp2`;
| ERROR 1075 (42000): Incorrect table definition; there can be only one auto 
| column and it must be defined as a key

– you must first remove the AUTO_INCREMENT from the column:

| mysql> ALTER TABLE `tmp2` CHANGE COLUMN `id` `id` INT;
| Query OK, 2 rows affected (0.02 sec)
| Records: 2  Duplicates: 0  Warnings: 0
| 
| mysql> SHOW CREATE TABLE `tmp2`\G
| *************************** 1. row ***************************
|        Table: tmp2
| Create Table: CREATE TEMPORARY TABLE `tmp2` (
|   `id` int(11) NOT NULL DEFAULT '0',
|   `foo` varchar(50) DEFAULT NULL,
|   PRIMARY KEY (`id`)
| ) ENGINE=InnoDB DEFAULT CHARSET=latin1
| 1 row in set (0.00 sec)
| 
| mysql> DROP INDEX `PRIMARY` ON `tmp2`;
| Query OK, 2 rows affected (0.02 sec)
| Records: 2  Duplicates: 0  Warnings: 0

If the PRIMARY KEY is to be modified, this can be achieved without removing AUTO_INCREMENT:   ALTER TABLE `mytable` DROP PRIMARY KEY, ADD PRIMARY KEY (`col1`, `col2`);

(Courtesy of Duane Hitz, <https://dev.mysql.com/doc/refman/5.7/en/alter-table.html#c7412>)

See also caveats of AUTO_INCREMENT expressed by Bill Vogel in <https://dev.mysql.com/doc/refman/5.7/en/alter-table.html#c10750>.]

>> A dump file with name suffix “.sql”, as the OP reports, contains a
>> sequence of *(My)SQL statements* for reconstruction of the original data. 
>> In the case of a dump file of a table, it contains INSERT statements for
>> the original data, maybe preceded by a CREATE TABLE statement and if so,
>> wisely preceded only by a CREATE TABLE IF NOT EXIST statement.

There are several ways how a dump file can be created. It does not have to have complete INSERTs.

>> It is at least one of those INSERT statements that fails here.  Removing
>> the offending constraint from the *empty* target table allows those
>> INSERT statements to succeed so that the data can be corrected *in the
>> database* *by means of the DBMS*.  In fact, it might only be necessary to
>> add AUTO_INCREMENT to the PRIMARY KEY column here.

No, …

> for a table with a primary key column would have the following dump data:
>
> INSERT INTO `t2` VALUES (1,'zero'),(2,'one'),(3,'two');

… see above.  

> 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.

[Quoted] [Quoted] And yet it is a distinct possibility. For a dump of a single table instead of the whole database, it might even be intentional.  

-- 
PointedEars

Twitter: _at_PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.