MySQL Bugs: #13226: AUTO_INCREMENT attempting to insert zero value

Bug #13226	AUTO_INCREMENT attempting to insert zero value
Submitted:	15 Sep 2005 15:18	Modified:	18 Oct 2005 10:14
Reporter:	Dave Cosgrove	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.11	OS:	Windows (WinXP SP2)
Assigned to:		CPU Architecture:	Any

Description:
When performing an INSERT INTO ... SELECT FROM operation via a Java PreparedStatement, AUTO_INCREMENT appears to attempt an insert of the value 0 (zero) rather than the next positive AUTO_INCREMENT value in sequence (in our last failure this value should have been 25434) .  This statement is potentially being executed concurrently by multiple threads.  Unfortunately, the issue surfaces intermittently.  As a result of the zero value INSERT attempt, we repeatedly see the following trace in our tomcat log for subsequent INSERTs:

java.sql.SQLException: Duplicate entry ''1'' for key 1
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2851)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531)
        at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1366)
        at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:952)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1974)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1897)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1758)
        at com.aprisma.spectrum.app.repmgr.web.model.AbstractSRMAvailabilityHandler.insertOutageByMH(AbstractSRMAvailabilityHandler.java:69
)
        at com.aprisma.spectrum.app.repmgr.web.model.DeviceAvailabilityHandler.insertOutageByMH(DeviceAvailabilityHandler.java:628)
        at com.aprisma.spectrum.app.repmgr.web.model.AbstractSRMAvailabilityHandler.unplannedOutageEvent(AbstractSRMAvailabilityHandler.jav
:563)
        at com.aprisma.spectrum.app.repmgr.web.model.DeviceAvailabilityHandler.handleEvent(DeviceAvailabilityHandler.java:503)
        at com.aprisma.spectrum.app.event.web.model.AbstractAvailabilityHandler.handleRecordEvent(AbstractAvailabilityHandler.java:544)
        at com.aprisma.spectrum.app.event.web.model.AbstractAvailabilityHandler.historicalEventUpdate(AbstractAvailabilityHandler.java:775)
M
        at com.aprisma.spectrum.app.repmgr.web.model.DeviceAvailabilityHandler.historicalEventUpdate(DeviceAvailabilityHandler.java:335)
        at com.aprisma.spectrum.app.event.web.model.BackEndEventDataModel$HistoricalUpdateJob.fireUpdate(BackEndEventDataModel.java:1924)
        at com.aprisma.spectrum.app.event.web.model.BackEndEventDataModel$HistoricalUpdateJob.getEvents(BackEndEventDataModel.java:2069)
        at com.aprisma.spectrum.app.event.web.model.BackEndEventDataModel$HistoricalUpdateJob.run(BackEndEventDataModel.java:1858)
        at java.lang.Thread.run(Thread.java:534)

Specifically, the following DML code is part of a Java PreparedStatement executed via Connector/J 3.1.8 that triggers the problem:

INSERT INTO outage (entity_ID, start_time, outage_type)
SELECT entity_ID, ?, ? FROM entitymodel WHERE model_h = ?

Below is the DDL for both tables ('MYISAM' is our default storage engine):

CREATE TABLE IF NOT EXISTS outage (
  outage_ID   int unsigned not null auto_increment,
  entity_ID   int unsigned not null,
  start_time  DATETIME not null,
  end_time    DATETIME,
  outage_type int unsigned not null,
  notes       varchar(255),

PRIMARY KEY (outage_ID),
 
FOREIGN KEY (entity_ID)   REFERENCES entity(entity_ID),
FOREIGN KEY (outage_type) REFERENCES outagetype(outage_type),

INDEX (entity_ID),
INDEX (outage_type) );

CREATE TABLE IF NOT EXISTS entitymodel (
  entity_ID int unsigned not null,
  model_h   int unsigned not null,
  timestamp TIMESTAMP,

PRIMARY KEY (entity_ID, model_h),

FOREIGN KEY (entity_ID) REFERENCES entity(entity_ID),

INDEX (entity_ID),
INDEX (model_h) );

When issuing a 'CHECK TABLE' on the outage table, I see the following warning:

+------------------+-------+----------+---------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------+-------+----------+---------------------------------------------------------------+
| reporting.outage| check | warning | Auto-increment value: 0 is smaller than max used value: 25433 | 

For test data, here's an INSERT statement to load the source entitymodel table:

INSERT INTO `entitymodel`
VALUES (14079,79691855,'2005-09-14 11:13:35'),
       (14080,79691892,'2005-09-14 11:13:36'),
       (14081,148911589,'2005-09-14 11:13:37'),
       (14082,148911648,'2005-09-14 11:13:38'),
       (14083,148911649,'2005-09-14 11:13:38'),
       (14084,148911650,'2005-09-14 11:13:38');

Please let me know if you require additional information.

Thanks,

Dave Cosgrove

How to repeat:
Cannot consistently repeat...  appears to be a timing issue due to our multi-threaded environemnt.

Thanks for bug report. But I guess I need some test case (on Java) to reproduce the bug. Could you please write and send test case ?

OK, I believe I am seeing the same error, but am (1) new to MySQL and (2) cannot reduce this to a simpler case, so I am unsure this will help.  However, this is repeatable for me (Win2K, fully patched, 5.0.13-rc-nt-log, Java).

I would attach a file that defines a new database, assuming that the delimiter is set to "|", but am unable to do so.  Instead, you can download it from
  https://acooke.dyndns.org/~andrew/resources.sql

Immediately (with no other SQL statements) the the db is defined, if you execute the following command, 
  select createuser('a', 'b');
you see the error
  ERROR 1062 (23000): Duplicate entry '3' for key 1

However, if you then try
  select createuser('p', 'q');
there is no error (and all subsquent attempts with different user names succeed).

Also, if you uncomment the references to table "foo" near the end of the file, this behaviour no longer occurs and the inserts and auto_increment appear to work fine.  Since the table foo "does nothing", I assume it's disrupting timing issues or influencing variable scope, optimisation or casting in some way.

It's also possible this error is unrelated, or is something stupid I am doing, of course.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".