CODEWITHSUNDEEP
mysql
Prazanna
Prazanna

Reputation: 43

MySql bug realated to LAST_INSERT_ID needs workaround

I was working on a stored procedure that inserts data into more than one tables, gets last inserted id and stores it in another table. I'm stuck in issue related to LAST_INSERT_ID bug in Mysql 5.0.24

reference

Is there any possible workaround for this problem?

For eg.

// if agent is new one

INSERT INTO `agent`(`agn_name`, `agn_cus_id`) VALUES ( agentName, customerId );
SET agnId = LAST_INSERT_ID();

//else get agnId = existing one

INSERT INTO `order`(`orderno`, `description`, `agent`) VALUES (orderNo, description,     agnId);

SET orderId = LAST_INSERT_ID();

INSERT INTO `suborder1`(`orderid`, `description`) VALUES(orderId, subdescription1);

INSERT INTO `suborder2`(`orderid`, `description`) VALUES(orderId, subdescription2);

Problem is when agent get inserted, orderId gets id from agent table

Upvotes: 0

Views: 160

Answers (1)

Andreas Wederbrand
Andreas Wederbrand

Reputation: 40061

Basically you have to find out some way of getting the orderId from the newly inserted row, either by timestamp (lock the table so you can be sure that this is the newest row in the table) or something else. In this case it works fine to use the fact that there can only be one row in order for this agent as the agent was just created and cannot possibly have added another order earlier.

I've written this in some kind of pseudo code

if (agent is new one) {
  INSERT INTO agent(agn_name, agn_cus_id) VALUES ( agentName, customerId );
  SET agnId = LAST_INSERT_ID();
  INSERT INTO order(orderno, description, agent) VALUES (orderNo, description, agnId);
  -- there can only be one row in order for this new agent, as the agent was just created
  SET orderId = SELECT orderId FROM order WHERE agent = agnId;
}
else {
  SET agnId = <some existing value>;
  INSERT INTO order (orderno, description, agent) VALUES (orderNo, description, agnId);
  -- this is the first call to LAST_INSERT_ID() since the agent wasn't created
  SET orderId = LAST_INSERT_ID();
}

Upvotes: 3

Related Questions