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

[Quoted] J.O. Aho wrote:

> On 04/08/17 04:41, fugee279_at_gmail.com wrote:

>> I get this error when I try to read data into an empty table from a
>> dumpfile.sql Anyone can help please? Thanks in advance

[Quoted] Do not do that. As the data is to be imported into an *empty* table, the recommended approach is to _not_ fiddle with (and potentially invalidate) the dump file, but remove the offending constraint from the target table, import the data, fix the *table*, and restore the constraint. This can be done automatically, for example:

  DROP INDEX `PRIMARY` ON `bad_table`;

• or “mysql -u "$user" -p "$database" < /foo/dumpfile.sql”;
• the SOURCE statement appears to be undocumented SET auto_commit=0; SOURCE /foo/dumpfile.sql; COMMIT;
• Clone table, structure only CREATE TABLE `good_table` LIKE `bad_table`;

  ALTER TABLE `good_table` ADD PRIMARY KEY … (…) …;

• Copy data, fix duplicate keys INSERT INTO `good_table` SELECT … FROM `bad_table` ON DUPLICATE KEY UPDATE `primary_key_column`=`primary_key_column` + 1

[Any foreign keys referring to that primary key would have to be updated accordingly, but that, too, can be automated to some extent. (Keep in mind that the original data is still available in `bad_table`.)]

If the table to be imported into is not empty, “ON DUPLICATE KEY UPDATE” can resolve key collisions in the same way.

<https://dev.mysql.com/doc/refman/5.7/en/drop-index.html>
<https://dev.mysql.com/doc/refman/5.7/en/create-table.html>
<https://dev.mysql.com/doc/refman/5.7/en/alter-table.html>
<https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html>
 

-- 
PointedEars

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