Professional Web Applications Themes

autonumber troubles - MySQL

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

  1. #1

    Default 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 Guest

  2. #2

    Default Re: autonumber troubles

    [email]JMosey[/email] 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


    Rik Guest

  3. #3

    Default Re: autonumber troubles

    "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

    JMosey@gmail.com Guest

  4. #4

    Default Re: autonumber troubles

    [email]JMosey[/email] 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


    Rik Guest

  5. #5

    Default Re: autonumber troubles

    [email]JMosey[/email] 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: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

Similar Threads

  1. autonumber question
    By dthatsme in forum Coldfusion - Getting Started
    Replies: 4
    Last Post: March 11th, 10:39 AM
  2. ASP Access Autonumber property
    By mike in forum ASP Database
    Replies: 1
    Last Post: August 11th, 05:44 PM
  3. Replies: 0
    Last Post: August 5th, 01:56 AM
  4. autonumber custom code
    By tina in forum Microsoft Access
    Replies: 0
    Last Post: July 30th, 01:07 AM
  5. Assign autonumber before update
    By kRISSE in forum Microsoft Access
    Replies: 2
    Last Post: July 14th, 01:04 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139