Duplicate entry '0' for key 'PRIMARY'

[fuge...@gmail.com]

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

[J.O. Aho]

On 04/08/17 04:41, fuge...@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.

--

//Aho

[Thomas 'PointedEars' Lahn]

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: @PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.

[Jerry Stuckle]

J.O. is correct and you are wrong (as usual).

The problem is the backup file does not have the id column in it, or the
id column does not have a unique key. Dropping the primary key allows
the data to be imported, but does not create the unique key (not this is
from a backup file - NOT another table!).

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
jstu...@attglobal.net
==================

[Thomas 'PointedEars' Lahn]

Jerry Stuckle wrote:

> J.O. is correct and you are wrong (as usual).

No, J.O. and you are wrong. (*You*, and you full-quoting without proper
reading, as much too often.)

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

and no AUTO_INCREMENT, but it is a possibility.

> or the id column does not have a unique key.

Very likely the dump file has multiple records with the value '0' or NULL in
the field value whose column has a PRIMARY KEY constraint; that is the
reason why the OP gets *this* error message (in the Subject).

> Dropping the primary key allows the data to be imported, but does not
> create the unique key

Of course it does not. Therefore the *copying* to *another* table *with the
same structure* that automatically fixes that later. Once the data has been
fixed, the original target table can be DROPped or RENAMEd, and the table
with the correct data be RENAMEd to carry the name of the target table.

> (not this is from a backup file - NOT another table!).

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

Only if (for some weird reason here) the index definitions are contained in
the dump file as well (which begs the question how the dumped data could be
stored in there in the first place: you would have to disable checks before
you add the constraint and then create the dump file), the dump file
absolutely needs to be edited before importing it. In all other cases one
should refrain from editing the dump file so as not to invalidate it.
Invalidation includes modifying the attributes of the file and accidentally
modifying the original data (e.g. because of encoding issues or whatever bug
the editor-du-jour may have).

> Additionally, even if it is from another table, your suggestion would

> potentially change every primary key in the table - […]

Notice the “e.g.”; it was an *example*. If you prefer, you can also use
MAX(`primary_key_column`) + 1 or whatever value you like for ON DUPLICATE
KEY UPDATE, so that the number of modified primary key values as compared to
the original is minimized.

In fact, if the primary key is not numeric, you have to use a different
approach, for example concatenating a character.

[J.O. Aho]

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.

Sure you can try to do it in the database, but can cause the result to
be missed constraints when fixing everything. But sure, you can mess up
the dump file by editing it.

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

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

Apparently it don't, as the table wouldn't have the either the
constraint or not two rows with primary key 0.

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.

Only if the original table didn't have the primary key column, for a
table with a primary key column would have the following dump data:

INSERT INTO `t2` VALUES (1,'zero'),(2,'one'),(3,'two');

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.

--

//Aho

[Thomas 'PointedEars' Lahn]

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.
>
> Sure you can try to do it in the database, but can cause the result to
> be missed constraints when fixing everything.

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

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.
>
> Apparently it don't, as the table wouldn't have the either the
> constraint or not two rows with primary key 0.

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

> Only if the original table didn't have the primary key column, […]

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.

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

[J.O. Aho]

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

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.

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.


--

//Aho

[Thomas 'PointedEars' Lahn]

J.O. Aho wrote:

> On 04/10/17 21:43, Thomas 'PointedEars' Lahn wrote:
>> J.O. Aho wrote:
>>> as the data dump would otherwise have the primary key.

>>> […]

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

> No, if you make a default dump (not option given) […]

*If*. That it is a default dump is *your* *assumption*. It does not need
to be correct.

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

JFTR: As I showed, you can set the AUTO_INCREMENT value of a table before
data import.

>> And yet it is a distinct possibility. For a dump of a single table
>> instead of the whole database, it might even be intentional.
>
> The issue still there and wouldn't happen with a default dump.

That is precisely my point. (Apparently I have not made myself clear
enough.) The evidence suggests that this file is not the result of a

default dump.

> As the OP not given any more information, so all we say are just
> speculations.

And there is no logic in excluding other possibilities, and dismiss
suggestions pertaining to those.

.
.
.
.
.
.

[Jerry Stuckle]

It's also quite possible (or even probable) that rows have been deleted,
leaving gaps in the autoincrement values.

In either case the database will be seriously borked with foreign keys
pointing at the wrong data.

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

And you're arguing with a well-known troll, J.O. He'll never admit he
is wrong.

[Peter H. Coffin]

On Tue, 11 Apr 2017 08:36:17 -0400, Jerry Stuckle wrote:
> In either case the database will be seriously borked with foreign keys
> pointing at the wrong data.

And that is, im my humble opinion, a point of such importance that it
may as well be the ONLY point. Data integrity is paramount. Build the
table without a primary key, FIND THE DUPLICATES, resolve them (by hand,
if necessary) so that the data is intact and correct, THEN add the
primary key back.

--
186,000 Miles per Second. It's not just a good idea. IT'S THE LAW.

[Thomas 'PointedEars' Lahn]

Peter H. Coffin wrote:

> On Tue, 11 Apr 2017 08:36:17 -0400, Jerry Stuckle wrote:
>> In either case the database will be seriously borked with foreign keys
>> pointing at the wrong data.
>
> And that is, im my humble opinion, a point of such importance that it
> may as well be the ONLY point.

AISB, as long as one is *aware* that any foreign keys referring to that
table are then invalid, too, then there is no problem. If there are (non-
NULL) foreign keys referring that table, then one should work with a *copy*
of either the target table or the target database.

But apparently I have to emphasize *again* that the target table *in this
case* was *empty*. Any non-NULL foreign keys referring to it would have
been *invalid in the first place*, and NULL foreign keys would not matter.

> Data integrity is paramount.

So much for that.

> Build the table without a primary key, FIND THE DUPLICATES, resolve them
> (by hand, if necessary) so that the data is intact and correct, THEN add
> the primary key back.

ACK, and the mere *attempt* to add the primary key (or any other key) (back)
with key checks *enabled* will *result* in *detecting* those inconsistencies
already. AISB.

[Jerry Stuckle]

On 4/15/2017 9:19 AM, Thomas 'PointedEars' Lahn wrote:
> Peter H. Coffin wrote:
>
>> On Tue, 11 Apr 2017 08:36:17 -0400, Jerry Stuckle wrote:
>>> In either case the database will be seriously borked with foreign keys
>>> pointing at the wrong data.
>>
>> And that is, im my humble opinion, a point of such importance that it
>> may as well be the ONLY point.
>
> AISB, as long as one is *aware* that any foreign keys referring to that
> table are then invalid, too, then there is no problem. If there are (non-
> NULL) foreign keys referring that table, then one should work with a *copy*
> of either the target table or the target database.
>

And that's where you are wrong. Just being aware that foreign keys are
invalid does NOT solve the problem.

> But apparently I have to emphasize *again* that the target table *in this
> case* was *empty*. Any non-NULL foreign keys referring to it would have
> been *invalid in the first place*, and NULL foreign keys would not matter.
>

So what? The data being inserted into the new database is NOT "empty".
And it is this data which will be corrupted with your bad advice.

>> Data integrity is paramount.
>
> So much for that.
>

Yes, you've already proven you have no idea how do ensure data integrity.

>> Build the table without a primary key, FIND THE DUPLICATES, resolve them
>> (by hand, if necessary) so that the data is intact and correct, THEN add
>> the primary key back.
>
> ACK, and the mere *attempt* to add the primary key (or any other key) (back)
> with key checks *enabled* will *result* in *detecting* those inconsistencies
> already. AISB.
>
>

It *MAY* detect inconsistencies. For instance, if there is a foreign
key pointing to a non-existent primary key, it will detect that.
However, if the foreign key is pointing to the *WRONG ROW* because *THE
PRIMARY KEY HAS CHANGED*, it will not detect the error.

The result is a seriously borked database. But you have already shown
you do not understand relational databases and how to maintain database
integrity.

[Thomas 'PointedEars' Lahn]

Jerry Stuckle wrote:

> On 4/15/2017 9:19 AM, Thomas 'PointedEars' Lahn wrote:
>> AISB, as long as one is *aware* that any foreign keys referring to that
>> table are then invalid, too, then there is no problem. If there are
>> (non- NULL) foreign keys referring that table, then one should work with
>> a *copy* of either the target table or the target database.
>
> And that's where you are wrong. Just being aware that foreign keys are
> invalid does NOT solve the problem.

I did NOT say that it solves the problem. Learn to read.

>> But apparently I have to emphasize *again* that the target table *in this
>> case* was *empty*. Any non-NULL foreign keys referring to it would have
>> been *invalid in the first place*, and NULL foreign keys would not
>> matter.
>
> So what?

Data integrity cannot be compromised by import because NOTHING RELEVANT is
referring to the table YET.

> The data being inserted into the new database is NOT "empty".

I have NOT said that the *data* is empty. Learn to read.

> And it is this data which will be corrupted with your bad advice.

Bullshit.

>> ACK, and the mere *attempt* to add the primary key (or any other key)
>> (back) with key checks *enabled* will *result* in *detecting* those
>> inconsistencies already. AISB.
>
> It *MAY* detect inconsistencies. For instance, if there is a foreign
> key pointing to a non-existent primary key, it will detect that.
> However, if the foreign key is pointing to the *WRONG ROW* because *THE
> PRIMARY KEY HAS CHANGED*, it will not detect the error.

But IN THIS CASE there should not be a foreign key referring to that
table in the first place. If it were, data integrity would already be
compromised.

[The Natural Philosopher]

On 15/04/17 15:05, Thomas 'PointedEars' Lahn wrote:
> But IN THIS CASE there should not be a foreign key referring to that
> table in the first place. If it were, data integrity would already be
> compromised.

In nutshell, this is the issue.

If there are duplicate primary keys in the data to be imported, the
problem exists already and must be corrected in any case.

One very faint possibility is that data is being imported into a table
that is not clean, even if its data has been deleted it may still have
an 'autofincrement' value implicit in it.

delete * from my_table

should always be followed by an

alter table my_table autoincrement=1;

etc. before importing data into it, if its been used before.

However I suspect this is irrelevant in this instance.


--
"Socialist governments traditionally do make a financial mess. They
always run out of other people's money. It's quite a characteristic of them"

Margaret Thatcher

[Thomas 'PointedEars' Lahn]

The Natural Philosopher wrote:

> One very faint possibility is that data is being imported into a table
> that is not clean, even if its data has been deleted it may still have
> an 'autofincrement' value implicit in it.
>
> delete * from my_table

The syntax of the (MySQL) DELETE statement is

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM `table`[.*] […] [WHERE
where_condition];

Therefore, the *proper* (MySQL) statement to delete all records in table
`my_table` is either

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM `my_table`;

which only requires the DELETE privilege (and the SELECT privilege for
columns that are only read) or

TRUNCATE [TABLE] `my_table`;

which requires the DROP privilege.

The parts in brackets are optional. (Note that “help TRUNCATE;” in the
mysql client will output the help screen for the TRUNCATE() *function* for
floating-point and fixed-point values, while “help TRUNCATE TABLE;” will
output that for the TRUNCATE [TABLE] *statement*.)

> should always be followed by an
>
> alter table my_table autoincrement=1;

AISB,

ALTER TABLE `my_table` AUTO_INCREMENT=1;

but that is only required if you use DELETE instead of TRUNCATE (among other
things, TRUNCATE resets the AUTO_INCREMENT value to its start value).

> etc. before importing data into it, if its been used before.

AISB.

But one of the problems likely to have caused the reported problem is that
the AUTO_INCREMENT attribute is _not_ set on the PRIMARY KEY column. I have
already explained how to set it and what needs to be considered when doing
so. [Does *anyone* here care to *read* *before* posting?]


While arguably standards-compliant per RFC 5536, the From header field value
of your posting still constitutes a disregard of Netiquette.

[The Natural Philosopher]

On 15/04/17 16:25, Thomas 'PointedEars' Lahn wrote:

>
> While arguably standards-compliant per RFC 5536, the From header field value
> of your posting still constitutes a disregard of Netiquette.
>

Better than being stalked IRL though


--
“Some people like to travel by train because it combines the slowness of
a car with the cramped public exposure of 
an airplane.”

Dennis Miller

[Jerry Stuckle]

On 4/15/2017 10:05 AM, Thomas 'PointedEars' Lahn wrote:
> Jerry Stuckle wrote:
>
>> On 4/15/2017 9:19 AM, Thomas 'PointedEars' Lahn wrote:
>>> AISB, as long as one is *aware* that any foreign keys referring to that
>>> table are then invalid, too, then there is no problem. If there are
>>> (non- NULL) foreign keys referring that table, then one should work with
>>> a *copy* of either the target table or the target database.
>>
>> And that's where you are wrong. Just being aware that foreign keys are
>> invalid does NOT solve the problem.
>
> I did NOT say that it solves the problem. Learn to read.
>

You said: "...as long as one is *aware* that any foreign keys referring

to that table are then invalid, too, then there is no problem."

If there is no problem, then the problem must be solved. You can't
backpedal out of it.

>>> But apparently I have to emphasize *again* that the target table *in this
>>> case* was *empty*. Any non-NULL foreign keys referring to it would have
>>> been *invalid in the first place*, and NULL foreign keys would not
>>> matter.
>>
>> So what?
>
> Data integrity cannot be compromised by import because NOTHING RELEVANT is
> referring to the table YET.
>

Once again you show you understand NOTHING about data integrity in a
relational database. Importing data into two tables which are related
is relevant.

>> The data being inserted into the new database is NOT "empty".
>
> I have NOT said that the *data* is empty. Learn to read.
>

Once again: "I have to emphasize *again* that the target table *in this
>>> case* was *empty*.".

There is no such thing as "empty data". It either exists or it does not.

>> And it is this data which will be corrupted with your bad advice.
>
> Bullshit.
>

Yup, that's what you are full of. For sure.

>>> ACK, and the mere *attempt* to add the primary key (or any other key)
>>> (back) with key checks *enabled* will *result* in *detecting* those
>>> inconsistencies already. AISB.
>>
>> It *MAY* detect inconsistencies. For instance, if there is a foreign
>> key pointing to a non-existent primary key, it will detect that.
>> However, if the foreign key is pointing to the *WRONG ROW* because *THE
>> PRIMARY KEY HAS CHANGED*, it will not detect the error.
>
> But IN THIS CASE there should not be a foreign key referring to that
> table in the first place. If it were, data integrity would already be
> compromised.
>
>

Bullshit. There very well could have been a foreign key involved. But
your renumbering of the primary key of the table has now broken that
relationship.

[Thomas 'PointedEars' Lahn]

Jerry Stuckle wrote:

> On 4/15/2017 10:05 AM, Thomas 'PointedEars' Lahn wrote:
>> I did NOT say that it solves the problem. Learn to read.
> You said: "...as long as one is *aware* that any foreign keys referring
> to that table are then invalid, too, then there is no problem."
>
> If there is no problem, then the problem must be solved. You can't
> backpedal out of it.

You should listen to your mindless babbling some time.

>> Data integrity cannot be compromised by import because NOTHING RELEVANT
>> is referring to the table YET.
>
> Once again you show you understand NOTHING about data integrity in a
> relational database.

Pot calling the kettle black.

> Importing data into two tables which are related is relevant.

But they SHOULD NOT be related in this case in any practical sense of the
word. There SHOULD NOT be any records in the first table referring to the
second one because the second one is EMPTY. (Foreign key constraints can
make sure of that in operation, but not with MyISAM; InnoDb is recommended.)

If it is BROKEN ALREADY, you CANNOT break it AGAIN.

And AISB, if it is BROKEN, then it needs to be FIXED FIRST. BEFORE
attempting to import ANYTHING else.

>>> The data being inserted into the new database is NOT "empty".
>> I have NOT said that the *data* is empty. Learn to read.
>
> Once again: "I have to emphasize *again* that the target table *in this
>>>> case* was *empty*.".
>
> There is no such thing as "empty data".

You should listen to your incoherent, inconsistent babbling some time.

If there is no such thing as ?empty data?, why have you claimed that there
could be?

And I did NOT say that there was empty DATA. A said that the target TABLE
is empty here. Got it now?

>>>> ACK, and the mere *attempt* to add the primary key (or any other key)
>>>> (back) with key checks *enabled* will *result* in *detecting* those
>>>> inconsistencies already. AISB.
>>> It *MAY* detect inconsistencies. For instance, if there is a foreign
>>> key pointing to a non-existent primary key, it will detect that.
>>> However, if the foreign key is pointing to the *WRONG ROW* because *THE
>>> PRIMARY KEY HAS CHANGED*, it will not detect the error.
>>
>> But IN THIS CASE there should not be a foreign key referring to that
>> table in the first place. If it were, data integrity would already be
>> compromised.
>
> Bullshit. There very well could have been a foreign key involved.

But then the DATA INTEGRITY WOULD BE COMPROMISED ALREADY.

> But your renumbering of the primary key of the table has now broken that
> relationship.

Either there ARE records in any table that refer to the EMPTY target table
by foreign key, then the relationship is BROKEN ALREADY. Or there are NO
such records, then NOTHING will be broken by adding data to an EMPTY TABLE.

[Thomas 'PointedEars' Lahn]

The Natural Philosopher wrote:

> On 15/04/17 16:25, Thomas 'PointedEars' Lahn wrote:
>> While arguably standards-compliant per RFC 5536, the From header field
>> value of your posting still constitutes a disregard of Netiquette.
>
> Better than being stalked IRL though

<https://en.wikipedia.org/wiki/Paranoid_personality_disorder#Treatment>

Good luck.

[The Natural Philosopher]

On 15/04/17 18:33, Thomas 'PointedEars' Lahn wrote:
> Either there ARE records in any table that refer to the EMPTY target table
> by foreign key, then the relationship is BROKEN ALREADY. Or there are NO
> such records, then NOTHING will be broken by adding data to an EMPTY TABLE.

Sigh. Everyone else killfiled the stucklehead years ago.

He is patently suffering from NPD.


--
“But what a weak barrier is truth when it stands in the way of an
hypothesis!”

Mary Wollstonecraft

[The Natural Philosopher]

On 15/04/17 18:43, Thomas 'PointedEars' Lahn wrote:
> The Natural Philosopher wrote:
>
>> On 15/04/17 16:25, Thomas 'PointedEars' Lahn wrote:
>>> While arguably standards-compliant per RFC 5536, the From header field
>>> value of your posting still constitutes a disregard of Netiquette.
>>
>> Better than being stalked IRL though
>
> <https://en.wikipedia.org/wiki/Paranoid_personality_disorder#Treatment>
>
> Good luck.
>

ROFLMAO!

Those are the people who stalk, yeah.


--
There’s a mighty big difference between good, sound reasons and reasons
that sound good.

Burton Hillis (William Vaughn, American columnist)

[Jerry Stuckle]

On 4/15/2017 1:33 PM, the famous troll Thomas 'Pointed Head' Lahn wrote:
> Jerry Stuckle wrote:
>
>> On 4/15/2017 10:05 AM, Thomas 'PointedEars' Lahn wrote:
>>> I did NOT say that it solves the problem. Learn to read.
>> You said: "...as long as one is *aware* that any foreign keys referring
>> to that table are then invalid, too, then there is no problem."
>>
>> If there is no problem, then the problem must be solved. You can't
>> backpedal out of it.
>
> You should listen to your mindless babbling some time.
>

ROFLMAO. I'm just quoting YOU.

>>> Data integrity cannot be compromised by import because NOTHING RELEVANT
>>> is referring to the table YET.
>>
>> Once again you show you understand NOTHING about data integrity in a
>> relational database.
>
> Pot calling the kettle black.
>

ROFLMAO! Another ad hominem attack.

>> Importing data into two tables which are related is relevant.
>
> But they SHOULD NOT be related in this case in any practical sense of the
> word. There SHOULD NOT be any records in the first table referring to the
> second one because the second one is EMPTY. (Foreign key constraints can
> make sure of that in operation, but not with MyISAM; InnoDb is recommended.)
>

Then how do you import data from one RDB to another? Oh, I guess
according to you, you can't.

> If it is BROKEN ALREADY, you CANNOT break it AGAIN.
>

It wasn't broken before you got involved.

> And AISB, if it is BROKEN, then it needs to be FIXED FIRST. BEFORE
> attempting to import ANYTHING else.
>

It wasn't broken before you got involved.

>>>> The data being inserted into the new database is NOT "empty".
>>> I have NOT said that the *data* is empty. Learn to read.
>>
>> Once again: "I have to emphasize *again* that the target table *in this
>>>>> case* was *empty*.".
>>
>> There is no such thing as "empty data".
>
> You should listen to your incoherent, inconsistent babbling some time.
>

ROFLMAO! I'm quoting YOU.

> If there is no such thing as ?empty data?, why have you claimed that there
> could be?
>

I never claimed such.

> And I did NOT say that there was empty DATA. A said that the target TABLE
> is empty here. Got it now?
>

Nope. That's not what you said, no matter how much you try to backpedal.

>>>>> ACK, and the mere *attempt* to add the primary key (or any other key)
>>>>> (back) with key checks *enabled* will *result* in *detecting* those
>>>>> inconsistencies already. AISB.
>>>> It *MAY* detect inconsistencies. For instance, if there is a foreign
>>>> key pointing to a non-existent primary key, it will detect that.
>>>> However, if the foreign key is pointing to the *WRONG ROW* because *THE
>>>> PRIMARY KEY HAS CHANGED*, it will not detect the error.
>>>
>>> But IN THIS CASE there should not be a foreign key referring to that
>>> table in the first place. If it were, data integrity would already be
>>> compromised.
>>
>> Bullshit. There very well could have been a foreign key involved.
>
> But then the DATA INTEGRITY WOULD BE COMPROMISED ALREADY.
>

Nope. The data were correct before you got involved.

>> But your renumbering of the primary key of the table has now broken that
>> relationship.
>
> Either there ARE records in any table that refer to the EMPTY target table
> by foreign key, then the relationship is BROKEN ALREADY. Or there are NO
> such records, then NOTHING will be broken by adding data to an EMPTY TABLE.
>

Nope. Once again you show a complete lack of understanding of
relational databases. But that's pretty normal for you. No wonder
you're such a well-known troll in so many newsgroups. You insist on
displaying your ignorance time and time again.

[Jerry Stuckle]

On 4/15/2017 1:44 PM, The Natural Philosopher wrote:
> On 15/04/17 18:33, Thomas 'PointedEars' Lahn wrote:
>> Either there ARE records in any table that refer to the EMPTY target
>> table
>> by foreign key, then the relationship is BROKEN ALREADY. Or there are NO
>> such records, then NOTHING will be broken by adding data to an EMPTY
>> TABLE.
>
> Sigh. Everyone else killfiled the stucklehead years ago.
>
> He is patently suffering from NPD.
>
>

As if anyone cares what you think? You're afraid to use your real name
because you don't want people to know you're not a programmer - just a
ditch digger who got fired for now knowing which end of the shovel to use.

Your paranoia shows your true colors.

[Peter H. Coffin]

On Sat, 15 Apr 2017 19:33:01 +0200, Thomas 'PointedEars' Lahn wrote:

> But they SHOULD NOT be related in this case in any practical sense of
> the word. There SHOULD NOT be any records in the first table referring
> to the second one because the second one is EMPTY. (Foreign key
> constraints can make sure of that in operation, but not with MyISAM;
> InnoDb is recommended.)

Remember the original post. This is not a everyday load. This is a
restoration from a dump. Even though the rows are not in the table yet,
they represent a state of a database, very likely with proper
interrelationships set up. Though clearly Something Happened that this
restore is not going smoothly, but my answer actually addresses that.

--
96. My door mechanisms will be designed so that blasting the control
panel on the outside seals the door and blasting the control panel
on the inside opens the door, not vice versa.
--Peter Anspach's list of things to do as an Evil Overlord

[J.O. Aho]

On 04/16/17 03:19, Peter H. Coffin wrote:
> On Sat, 15 Apr 2017 19:33:01 +0200, Thomas 'PointedEars' Lahn wrote:
>
>> But they SHOULD NOT be related in this case in any practical sense of
>> the word. There SHOULD NOT be any records in the first table referring
>> to the second one because the second one is EMPTY. (Foreign key
>> constraints can make sure of that in operation, but not with MyISAM;
>> InnoDb is recommended.)
>
> Remember the original post. This is not a everyday load. This is a
> restoration from a dump. Even though the rows are not in the table yet,
> they represent a state of a database, very likely with proper
> interrelationships set up. Though clearly Something Happened that this
> restore is not going smoothly, but my answer actually addresses that.
>

From his other thread which he created afterwards:

Subject: data migrations after changing table structure

I'm using rails schema:load to create mysql databases
After they're created I get duplicate entry 0 for primary key error when
i import data
How do I set default engine in my.cnf so when i create tables the engine
is set to InnoDB, the primary key is set to id and autoincrement is on
for primary key
I was told I needed to alter the tables in this way but I wanna set it
in my.cnf and rebuild the database again after

---

So he has made a change to the object in Rails, rails has created the
new database and he tries to restore the old data to this new database.
I would say that a column which didn't use to be a primary key has been
altered to be a primary key.

He has two rows in the table which has id 0, which he has to fix before
he can import the data, sure he can remove the primary key restraint,
which will leads to his rail application may not work.

--

//Aho

[Thomas 'PointedEars' Lahn]

Peter H. Coffin wrote:

> On Sat, 15 Apr 2017 19:33:01 +0200, Thomas 'PointedEars' Lahn wrote:
>> But they SHOULD NOT be related in this case in any practical sense of
>> the word. There SHOULD NOT be any records in the first table referring
>> to the second one because the second one is EMPTY. (Foreign key
>> constraints can make sure of that in operation, but not with MyISAM;
>> InnoDb is recommended.)
>
> Remember the original post.

I do (and it appears that I am one of the few people who does). It was:

| 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

(sic)

> This is not a everyday load.

Did I say or imply that it were? And why would that be relevant to what I
said?

> This is a restoration from a dump.

Of a table.

> Even though the rows are not in the table yet, they represent a state of a
> database,

You must be referring to another OP, then. From the information given in
the OP that I quoted above, I can only say with certainty that they
represent the state of a _table_.

> very likely with proper interrelationships set up.

Fallacy: Appeal to probability.

There is no good reason to assume that there are any “interrelationships” in
the first place. Because it is possible that a primary key constraint was
defined for the target table merely out of habit or recommendation (for
*future* use), or to establish uniqueness of field values.

> Though clearly Something Happened that this restore is not going smoothly,
> but my answer actually addresses that.

Fallacy: Slippery slope.

You are jumping to a lot of conclusions here, allowing the experience, that
you might have, to cloud your judgement.

What is your point anyway?

[Thomas 'PointedEars' Lahn]

J.O. Aho wrote:

> From his other thread which he created afterwards:

Indeed, distributing the problem description across several threads is one
of their problems.

> […]

> He has two rows in the table which has id 0, which he has to fix before
> he can import the data, sure he can remove the primary key restraint,
> which will leads to his rail application may not work.

More wishful thinking. One final time: He does _not_ have to fix the data
*before* importing them. He can fix them *after* importing them, and can
fix them *easier* this way, but not necessarily to/in the *production
database*.

I am not going to repeat myself any further. *Read* what the OP and I
*wrote*, not what *you wish them to have written*, and the nonsense
*you wish me to have written*. EOD.

--
PointedEars, head shaking

[Jerry Stuckle]

On 4/16/2017 8:08 PM, the famous troll Thomas 'Pointed Head' Lahn wrote:
> J.O. Aho wrote:
>
>> From his other thread which he created afterwards:
>
> Indeed, distributing the problem description across several threads is one
> of their problems.
>
>> […]
>> He has two rows in the table which has id 0, which he has to fix before
>> he can import the data, sure he can remove the primary key restraint,
>> which will leads to his rail application may not work.
>
> More wishful thinking. One final time: He does _not_ have to fix the data
> *before* importing them. He can fix them *after* importing them, and can
> fix them *easier* this way, but not necessarily to/in the *production
> database*.
>

Once again you are wrong. It is much easier to fix a problem like this
in a text file than to try to fix it in the database.

> I am not going to repeat myself any further. *Read* what the OP and I
> *wrote*, not what *you wish them to have written*, and the nonsense
> *you wish me to have written*. EOD.
>
>

Good. The op doesn't need any more of your advice on how to completely
screw up his database.

[kbgsach...@gmail.com]

On Saturday, April 8, 2017 at 8:11:50 AM UTC+5:30, fuge...@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

[bill]

What is your primary key?
--bill

[Kees Nuyt]

On Mon, 30 Jul 2018 06:58:08 -0400, bill <wil...@TechServSys.com>
wrote:

The problem is over a year old, I think the original poster has solved
it by now.
--
Regards,
Kees Nuyt

[bill]

LOL
Thanks Kees