autonumber troubles

I have a table, this table has an autonumber as a primary key.

I export the table in phpMyAdmin.

Cool so far.

I create a new DB, I import the backup of the DB in that new DB and it
throws an error that the table with the automnumber already has a value
of 1 (it isn't incrementing the new table).

Ok, go back and hack through, remove the autonumber. Now export and it
imports fine.

Except how do I get the autonumber back? There are hundreds of rows of
data and I want that autonumber but when I try and add it it says there
is already an ID field with a "1" (same as before, not saying "find the
highest and add one more" just saying "start at 1, oops there is one,
then die"

Anyone know a way around this? I want to add my autonumber to my table
with the data that is already there.

[email]JMosey[/email] wrote:

Quote:

---

> I have a table, this table has an autonumber as a primary key.
>
> I export the table in phpMyAdmin.
>
> Cool so far.
>
> I create a new DB, I import the backup of the DB in that new DB and it
> throws an error that the table with the automnumber already has a
> value of 1 (it isn't incrementing the new table).
>
> Ok, go back and hack through, remove the autonumber. Now export and it
> imports fine.
>
> Except how do I get the autonumber back? There are hundreds of rows of
> data and I want that autonumber but when I try and add it it says
> there is already an ID field with a "1" (same as before, not saying
> "find the highest and add one more" just saying "start at 1, oops
> there is one, then die"
>
> Anyone know a way around this? I want to add my autonumber to my table
> with the data that is already there.

---

Before (multi-)posting, try to read some of the answers already given in
different groups.

Export options:
- structure
- Add AUTO_INCREMENT value
- data
- Complete inserts

Should take car of the problem if you start with a clean table.

If for some reason, you can't gat this to work (and you should), you could
manually:
ALTER TABLE tbl AUTO_INCREMENT = next_integer_you_want;

Grtz,
--
Rik Wasmus

"If for some reason, you can't gat this to work (and you should), you
could
manually:
ALTER TABLE tbl AUTO_INCREMENT = next_integer_you_want; "

I did this and while the SQL didn't throw an error, none of the fields
are showing a autonumber in the structure table of phpMyAdmin

[email]JMosey[/email] wrote:

Quote:

---

> "If for some reason, you can't gat this to work (and you should), you
> could
> manually:
> ALTER TABLE tbl AUTO_INCREMENT = next_integer_you_want; "
>
> I did this and while the SQL didn't throw an error, none of the fields
> are showing a autonumber in the structure table of phpMyAdmin

---

No, it won't magically all already existing fields to an autonumber field,
it will just set the next autonumber field. If already existing fields do
not have a number, it can't be easily set.

Have you tried the first option, while exporting selecting "add
autoincrement value" in structure, and COMPLETE inserts in data? That should
work, I use in all the time.

If this doesn't work, please post a complete list of settings you choose
while exporting in phpmyadmin

Grtz,
--
Rik Wasmus

[email]JMosey[/email] wrote:

Quote:

---

> I have a table, this table has an autonumber as a primary key.
> I export the table in phpMyAdmin.
> Cool so far.

---

No. phpMyAdmin is kind of "last resort" tool. If you have the
possibility, I recommend using the MySQL command line utilities.
In your case: mysqldump. Anyway, this time it's not phpMyAdmins fault.

Quote:

---

> I create a new DB, I import the backup of the DB in that new DB and it
> throws an error that the table with the automnumber already has a value
> of 1 (it isn't incrementing the new table).
>
> Ok, go back and hack through, remove the autonumber. Now export and it
> imports fine.

---

I *guess* your original table has a row with value 0 in the
AUTO_INCREMENT column. At import time, this 0 is replaced with
the next AUTO_INCREMENT value - for an empty table that is 1.
Then the row with the "real" 1 gives you the duplicate key error.

How to fix:

Make sure you don't have the value 0 in an AUTO_INCREMENT column.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
MySQL User Forums: [url]http://forums.mysql.com/[/url]