Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes Getting wrong auto_increment number when inserting into database table. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Getting wrong auto_increment number when inserting into database table." Watch "Getting wrong auto_increment number when inserting into database table." New topic
Author

Getting wrong auto_increment number when inserting into database table.

Mark O' Sullivan
Ranch Hand

Joined: Aug 17, 2009
Posts: 160
Hi,
Does anyone understand what could have happened here? I previously had 73 entries in my mysql database table, I ran delete from table, and then inserted 26 entries in that table. The problem I have is when I try and insert another record in the table it comes out as the number 74 for that record entry instead of the number I'm expecting 27, any ideas why this could be the case. Could it be to do with my jdbc connection string? This is part of it.
maxActive="100" maxIdle="30" amxWait="10000"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/marcowebsite?autoReconnect=true&&characterEncoding=UTF-8"
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18656
    
    8

The only thing guaranteed for an auto-increment number is that it won't be the same as any existing number in that column. So you have no reason to expect 27, let alone to require it.
Hebert Coelho
Ranch Hand

Joined: Jul 14, 2010
Posts: 754

You could save the tables, drop the database and restore all the tables again.

I believe that you table will start from 0 all over again. Worked like that with me one time...


[uaiHebert.com] [Full WebApplication JSF EJB JPA JAAS with source code to download] One Table Per SubClass [Web/JSF]
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30758
    
156

If there is a sequence affiliated with the table, you can explicitly reset what # the sequence is up to. I agree that you shouldn't be dependent on consecutive numbers though.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Hebert Coelho wrote:You could save the tables, drop the database and restore all the tables again.

I believe that you table will start from 0 all over again. Worked like that with me one time...

Generally, just recreating the relevant table(s) should be enough.

However, the question is why? The sole purpose of the id generator is to generate unique keys. If there is anything that depends on these generated keys to have predetermined values, it looks like a bad design.
Dwarka Damodaran
Greenhorn

Joined: Nov 10, 2011
Posts: 7
I ran delete from table


When talking about ID's getting automatically generated for a table, it has nothing to do with the delete command. It just keeps incrementing from the previous inserted value. The requirement that you are expecting can be easily be achieved either by using a database sequence or by writing your own sequence generator (This is pointless unless you hava a very unique requirement in ID generation). When using Database sequence, you wll have to just reset the sequence to the required value every time you encounter a delete operation.

Hope this helps.

Thanks and Regards,
Dwarka
Mark O' Sullivan
Ranch Hand

Joined: Aug 17, 2009
Posts: 160
Cheers, thanks everyone for their responses. What I'll do is everytime I'm doing an insert on the table, I'll lock the table, delete the auto_increment column and recreate again. I'm trying to create a forum that can be navigated by sequence numbers, say display entries, 1-15,16-30, etc. I should be ok because I won't be doing deletions often, if ever. Any other possibilities I be half afraid I lose entries. Thanks everyone for their help here, much appreciated.
Mark O' Sullivan
Ranch Hand

Joined: Aug 17, 2009
Posts: 160
Sorry, actually should do the locking when I'm deleting from the table instead of insertion, apologises about mistake.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

I'll reiterate that it is really a bad idea to rely on concrete values created by identity generators or sequences. Many things may get wrong in this design, eg. if a rollback occurs, gaps can be introduced into the generated values (at least in some databases).

The functionality you're probably looking for is usually called "pagination" or "pagination query", and searching this term on this site or the internet will yield you many interesting articles.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18656
    
    8

Just to expand a bit on what Martin said: you wouldn't store the sequence number in your database at all. You would store something else, like a timestamp, to keep the data in the right sequence, but you would only assign sequence numbers when you read a series of rows.

Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61420
    
  67

Bad bad bad bad idea. Bad.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Mark O' Sullivan
Ranch Hand

Joined: Aug 17, 2009
Posts: 160
Thanks very much for the advice. I ended up using synchronized methods and generated a unique number for each row in table. Thanks for your honesty, has saved me a lot of heartache.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Getting wrong auto_increment number when inserting into database table.