duplicate created in AUTO INCREMENT in mysql if its possible?

[prasad guna]

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]

why are you using time entered as part of a primary key?

[prasad guna]

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]

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]

Hi,

  Auto increment can't create duplicates.  

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]

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]

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]

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 Vashko]

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]

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]

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]

i give example for that below i give the sql query run and check that,

CREATE TABLE `example` (
  `User_name` varchar(8) NOT NULL DEFAULT '',
  `Bus_run_date` date NOT NULL DEFAULT '0000-00-00',
  `Entry_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Exit_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Status` char(1) NOT NULL DEFAULT '',
  `Program_id` int(11) NOT NULL DEFAULT '0',
  `Module_id` int(11) NOT NULL DEFAULT '0',
  `instance_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`instance_id`,`Entry_time`) USING BTREE,
  KEY `FK_example_1` (`Program_id`,`Module_id`),
  CONSTRAINT `FK_example_1` FOREIGN KEY (`Program_id`, `Module_id`) REFERENCES `program_master` (`Program_id`, `Module_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4019 DEFAULT CHARSET=latin1 COMMENT='example';


INSERT INTO `example` (`User_name`,`Bus_run_date`,`Entry_time`,`Exit_time`,`Status`,`Program_id`,`Module_id`,`instance_id`) VALUES 
 ('example','2011-01-06','2011-01-06 18:15:30','2011-01-06 18:15:42','C',104,2,3897),
 ('example','2011-01-06','2011-01-06 18:15:42','2011-01-06 18:15:58','C',104,2,3898),
 ('example','2011-01-06','2011-01-06 18:15:58','2011-01-06 18:16:12','C',104,2,3899),
 ('example','2011-01-07','2011-01-07 11:50:39','2011-01-07 11:51:35','C',208,6,3900),
 ('example','2011-01-07','2011-01-07 11:51:59','2011-01-07 11:54:58','C',208,6,3901),
 ('example','2011-01-07','2011-01-07 11:56:54','2011-01-07 11:56:54','O',200,9,3901),
 ('example','2011-01-07','2011-01-07 11:54:58','2011-01-07 14:20:54','C',208,6,3902),
 ('example','2011-01-07','2011-01-07 12:14:31','2011-01-07 12:14:55','C',200,9,3903);

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]

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]

This is code for inserting the rows

Example at=new Example();
			Date date = new Date();	
			at.setBusRunDate(date);
			at.setEntryTime(date);
			at.setExitTime(date);
			at.setProgramId(54);
			at.setModuleId(654);
			at.setStatus('O');
			at.setUserName(username);
			Session session2=HibernateSessionFactory.getSession();
			Transaction trn=session2.beginTransaction();
			session2.save(at);
			trn.commit();

in hibernate i use ,

<id name="instanceId" type="integer">
            <column name="instance_id" />
            <generator class="increment" />
        </id>

[Paul Sturrock]

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]

Yes i am using linux, MYISAM is the default engine. if any other possible way to protect this.mysql version is 5.1.40