Re: Duplicate entry '0' for key 'PRIMARY'
Date: Sun, 9 Apr 2017 22:55:52 -0400
Message-ID: <oces23$6m0$1_at_jstuckle.eternal-september.org>
On 4/8/2017 3:48 PM, The infamous troll Thomas 'Pointed Head' Lahn wrote:
> 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 >> >> The original table from where your dump file is from didn't have the >> column as a primary key column, so now when your column is a primary key >> column it can't have two values with the same primary key. >> >> You would need to manually edit your dump file, so that the column will >> only have unique rows (either removing one of the rows with primary key >> 0 or give one of the rows a new unique id). This will just fix so you >> can store the data back into the new table, not other inconsistencies >> with data.
>
> 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>
>
>
[Quoted] J.O. is correct and you are wrong (as usual).
[Quoted] The problem is the backup file does not have the id column in it, or the [Quoted] [Quoted] id column does not have a unique key. Dropping the primary key allows [Quoted] the data to be imported, but does not create the unique key (not this is from a backup file - NOT another table!).
[Quoted] Additionally, even if it is from another table, your suggestion would potentially change every primary key in the table - and screw up every foreign key referencing it. The result would be a seriously broken database.
J.O.'s suggestion -although it takes more time - is the correct way to fix the problem.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Mon Apr 10 2017 - 04:55:52 CEST