MySQL why does my auto increment not start at 1 when doing inserts?
auto-increment, insert, mysql
Solution
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...
Problem
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(); } } } ```