Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Getting wrong auto_increment number when inserting into database table.

 
Mark O' Sullivan
Ranch Hand
Posts: 160
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 754
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34384
346
Eclipse IDE Java VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 160
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 160
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry, actually should do the locking when I'm deleting from the table instead of insertion, apologises about mistake.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 64827
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bad bad bad bad idea. Bad.
 
Mark O' Sullivan
Ranch Hand
Posts: 160
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic