• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

duplicate created in AUTO INCREMENT in mysql if its possible?

 
prasad guna
Ranch Hand
Posts: 46
Java MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

In my Project i use table "exampletrail" its have one composite key one of the primary key is "entrytime(timestamp)" another one is "instanceid (integer) - AUTO INCREMENT".my issue is Autoincrement field instanceid created one duplicate value. i dont know whether if its possible or not? yes means "how?". I need clear clarification.


Thanks in advance
 
Campbell Ritchie
Sheriff
Pie
Posts: 49827
69
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
why are you using time entered as part of a primary key?
 
prasad guna
Ranch Hand
Posts: 46
Java MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Hi Campbell,

This is necessory and it can be maintain uniqueness. but sometime it can make duplicate so that we create instance id with auto increment (for composite). i want to know if its possible the auto increment create the duplicate.


thanks
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Auto increment can't create duplicates.

If I were implementing this I would not use a composite key, since you already have a surrogate key in the auto increment field. If you need a unique index on those two fields, use a unique index. But life is usually easier in the long run if you use a simple surrogate key as primary key.
 
prasad guna
Ranch Hand
Posts: 46
Java MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Hi,



I Know but in that scenario ok.
but wherever the auto increment is part of the composite key it will create the duplicate.are you sure about this.



thanks
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

but wherever the auto increment is part of the composite key it will create the duplicate

What makes you think this? Why does auto increment being used as part of a composite key imply duplicates?
 
prasad guna
Ranch Hand
Posts: 46
Java MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Paul

.... auto increment is part of the composite key it will create the duplicate ....


Take this scenario and answer me .because the duplicaton created in my table that's why i create the thread here.

 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Like I said before: Auto increment can't create duplicates. You'll get the details of this if you read the MySQL documentation (which I'm not going to repeat here).
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
prasad guna wrote:
Take this scenario and answer me .because the duplicaton created in my table that's why i create the thread here.


Are you sure the duplicated value was not created by another process? Eg. if the next value would be 100 and the table already contained a row with value 100, it would appear as if the error was caused by the insert statement, while in fact the problem could be that someone inserted or updated a row outside of your application.

(I don't know MySQL, but I assume that is is possible to change value of an AUTO INCREMENT column.)
 
prasad guna
Ranch Hand
Posts: 46
Java MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin,

Thanks for reply only one process for inserting rows through hibernate. I find some where while using on auto increment as a part of composite it will create the duplicates because it's not violate the composite rules so it will not keep the uniqueness.but i dont know the exact reason for that.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

I find some where while using on auto increment as a part of composite it will create the duplicates because it's not violate the composite rules so it will not keep the uniqueness.but i dont know the exact reason for that.

Like I said before, auto increment will not generate duplicates. I would examine what you are doing to cause this - are you assigning values to the auto increment field?
 
prasad guna
Ranch Hand
Posts: 46
Java MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i give example for that below i give the sql query run and check that,



This was taken from my table.I Use Hibernate i am not assigning the values to autoincrement column.its generate automatically.above data "3901" is a duplicate why its created there.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you post your Hibernate code? There should not be a value in your insert statement for the auto increment field at all.
 
prasad guna
Ranch Hand
Posts: 46
Java MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is code for inserting the rows




in hibernate i use ,
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hmm. You sure about the database engine you are using? The behaviour you are seeing is only supposed to be possible with the MyISAM engine.
 
prasad guna
Ranch Hand
Posts: 46
Java MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes i am using linux, MYISAM is the default engine. if any other possible way to protect this.mysql version is 5.1.40
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic