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.

JMosey@gmail.com wrote:
> 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

On 22 May 2006 10:42:53 -0700, JMosey@gmail.com wrote:
> "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

That will reset the integer on a table with a preexisting autoincrement.
If your table has no autoincrement fields right now, setting that does
not magically make a random field autoincrement. You still must specify
an autoincrement option on one of the fields. The complete specification
would be more along the lines of

ALTER TABLE `tbl` MODIFY `fld` INT UNSIGNED NOT NULL AUTO_INCREMENT,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`fld`)
AUTO_INCREMENT = value;

(Autoincrement fields must be part of the primary key, so we need
to include that field in the key. You *may* make the autoincrement
field part of a compound key, but that makes importing data somewhat
complicated. One often ends up dumping the relevant tables, merging the
outputs into a single (correct) load script, and then running the merged
script through the mysql client to create a whole NEW table. Personally,
I stick with just the single autoincrement field as primary key on
almost every table I build, and then manage relationships with user
indexes.)

--
I didn't need to sabotage anything. Not being around to say "No that
won't work" or "you can't do it that way" is more than enough damage.
(Ego problem? It's not a problem.)
-- Graham Reed, on job endings

JMosey@gmail.com wrote:
> "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

JMosey@gmail.com wrote:

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

> 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: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/