MySQL why does my auto increment not start at 1 when doing inserts?

Go To StackoverFlow.com

4

Why when I'm using jdbc to do inserts into my data base my table auto_increments gets jacked-up.

Example Of totally empty tables being populated:

Dog table

DogId DogName
3     Woofer
4     Kujo
5     Spike

Owner Table

OwnerId DogID OwnerName
6       3     George
7       4     John
8       5     Sam

Desired Results

Dog table

DogId DogName
1     Woofer
2     Kujo
3     Spike

Owner Table

OwnerId DogID OwnerName
1       1     George
2       2     John
3       3     Sam

Actual code:

 public void insertStuff(Something d)
  {
    Connection con = null;

    try
    {
      Class.forName("com.mysql.jdbc.Driver");
      con = (Connection) DriverManager.getConnection(
          "jdbc:mysql://" + this.getServer() + "/" + this.getDatabase(), user,
          password);
      con.setAutoCommit(false);

      Statement s1 = (Statement) con.createStatement();
      s1.executeUpdate("INSERT IGNORE INTO DOG (DOG_NAME) VALUES(\""
          + d.getDogName() + "\")");

      Statement s2 = (Statement) con.createStatement();
      s2.executeUpdate("INSERT IGNORE INTO OWNER (DOG_ID,OWNER_TITLE) VALUES ("
          + "(SELECT DOG_ID FROM DEVICE WHERE DOG_NAME =\""
          + d.getDogName()
          + "\"),\"" + d.getOWNER() + "\")");

      Statement s3 = (Statement) con.createStatement();
      s3.executeUpdate("INSERT IGNORE INTO KENNEL " + "("
          + "KENNEL_NAME,+ "OWNER_ID) " + "VALUES " + "( \""
          + d.getKennelName()
          + "\","
          + "\""
          + ","
          + "(SELECT OWNER_ID FROM OWNER WHERE OWNER_TITLE=\""
          + d.getOWNER() + "\")" + ")");

      }

      con.commit();

    }
    catch (Exception e)
    {
      if (con != null)
        try
        {
          con.rollback();
        }
        catch (SQLException e1)
        {
          // TODO Auto-generated catch block
          e1.printStackTrace();
        }

      e.printStackTrace();
    }
    finally
    {
      if (con != null)
        try
        {
          con.close();
        }
        catch (SQLException e)
        {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
    }
  }
2012-04-03 20:17
by stackoverflow
Can you post some code? Show us your create table statement and the code you're using to do the inserts - Sam Dufel 2012-04-03 20:25
@SamDufel There ya go. posted above in the edi - stackoverflow 2012-04-03 20:36
@Sam these tables are created once and inserted n times. There is no create/delete happenin - stackoverflow 2012-04-03 20:47
Try running "truncate dog; truncate owner;" and then re-running your insert code, it will just work - BluesRockAddict 2012-04-03 20:55
@BluesRockAddict that would be a very terrible thing. I would lose all my data in those tables at the expense of adding ne - stackoverflow 2012-04-03 21:01
I see, from your question it wasn't clear that you're not using dummy data sets. Then resetting auto_increment would be your best option. Or maybe you're trying to "fix" the ids with existing data in those tables (i.e. without re-inserting) - BluesRockAddict 2012-04-03 21:04


2

Only two scenarios I know about are:

(1) some records have been deleted

(2) there is some trigger on table that modify such id

Please note that even you made fresh inserts to empty table, if there were previously some rows, emptying table don't reset auto-increment ID counter and it continues on order from last issued number, not from the number of actual records in table...

2012-04-03 20:26
by Ωmega
How do I make sure every table with auto_increment starts at one then - stackoverflow 2012-04-03 20:38
@stackoverflow - (1) alter table myTable auto_increment = 1 or (2) create a new table and start ove - Ωmega 2012-04-03 20:41
Is there any way to set auto_increment default=1 or something along those lines - stackoverflow 2012-04-03 20:42
@stackoverflow - CREATE TABLE myTable (id INT NOT NULL auto_increment, ...) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1Ωmega 2012-04-03 20:50
@stackoverflow - An integer or floating-point column can have the additional attribute AUTOINCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTOINCREMENT column, the column is set to the next sequence value. Typically this is value+1, where value is the largest value for the column currently in the table. AUTOINCREMENT sequences begin with 1. For a multiple-row insert, LASTINSERTID() and mysqlinsertid() actually return the AUTOINCREMENT key from the first of the inserted rows - Ωmega 2012-04-03 20:51
stackoverflow will this work with MYISAM engine also - stackoverflow 2012-04-03 20:53
@stackoverflow - yes, it will... The above CREATE TABLE with ENGINE=InnoDB was just an example, but it is same for ENGINE=MyISAMΩmega 2012-04-03 20:56
Yes thank you for your time and hel - stackoverflow 2012-04-03 21:29


3

Auto-increment values can also be affected by inserting a row within a transaction, then backing out of the transaction.

You can always reset the auto_increment value on mysql directly:

alter table <tablename> auto_increment = <some_number>;

But honestly, what does it matter? The values need to be unique, but they shouldn't indicate an order.

2012-04-03 20:24
by Matt Fenwick
Good point and rightly so I just wanted to make extra sure there wasn't something totally wrong going on her - stackoverflow 2012-04-03 21:29


2

ALTER TABLE Owner AUTO_INCREMENT = 1;
2016-01-28 06:41
by vishal sharma
Code-only answers are discouraged; better add a short explanation to your code. You can [edit] your answer - Benjamin W. 2016-01-28 06:50


1

Most likely you have previously deleted rows from tables Dog/Owner which increased auto increment number. You'd need to reset it (or just do DROP/CREATE for those tables and reinsert the data):

ALTER TABLE Dog AUTO_INCREMENT = 1;
ALTER TABLE Owner AUTO_INCREMENT = 1;

Yet another way to accomplish the same is to run truncate:

TRUNCATE Dog;
TRUNCATE OWNER;

This will delete the data and reset the index.

2012-04-03 20:21
by BluesRockAddict
This is an example of a fresh insert. I do no deletes or any drops when this happen - stackoverflow 2012-04-03 20:22
Yes, but you do it on existing tables - BluesRockAddict 2012-04-03 20:25


1

There is the AUTO_INCREMENT variable that is the id for the newly inserted record. If you have inserted 10 records AUTO_INCREMENT will be set to 11. Now if you delete all the records from your table and try to insert new records it will use the AUTO_INCREMENT value. So new id will be 11 . You can update it by altering the table

ALTER TABLE <tablename> AUTO_INCREMENT = 1;
2015-06-11 07:26
by Sunil Garg