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();
        }
    }
  }

• mysql
• insert
• auto-increment

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

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.

ALTER TABLE Owner AUTO_INCREMENT = 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.

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;