0

I am using cleardb for mysql database in php. And Situation is something like this:

I am adding record in beneficiary table. and after adding that I am fetching last_inserted _id. And using this id to add payment method in beneficiary_payment_info. It works fine in most of the cases. But, sometimes I found below message in error log:

Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (cdb_85c337008c.beneficiary_payment_info, CONSTRAINT beneficiary_payment_info_ibfk_1 FOREIGN KEY (ben_id) REFERENCES beneficiary (id))'

When I checked in database, I got this screenshots:

TABLE: beneficiary

TABLE: beneficiary_payment_info

As you can see there is a id 6073 in first table but it's not in second table. I am getting above error just because of this. And there are 2 identical records(6073 and 6081) in first table because user has requested that page second time I guess.

How can I solve this problem? Please guide me. Thanks.

EDIT (Reply from clearDB)

The best course to resolve this issue is to stop involving the auto_increment values.

Rather than trying to guess or set them, you may be able to use a subquery, similar to the following style:

INSERT INTO CHILDREN (ParentID, FirstName, LastName)
VALUES ((SELECT ID FROM PARENTS WHERE FirstName = 'John' AND LastName = 'Doe'),
'Jane', 'Doe');
RNK
  • 5,582
  • 11
  • 65
  • 133
  • Well the records are not actually identical, they have different reference column values... so does that still pose an issue ? Did your insert function succeed (can you see the data you tried to insert in the table?) – Tech Savant May 06 '15 at 14:39
  • but the problem is first time it didn't add `6073` id as foreign key in second table. Now I have 2 records in database. And the one with id `6073` is use less. B'coz id doesn't have any payment method in second table. – RNK May 06 '15 at 14:41
  • Do you have a check to make sure that you insert into 'beneficiary' table has worked before you insert in the 'beneficiary_payment_info' table? This insert may fail sometimes for a reason. – Yasen Zhelev May 06 '15 at 14:42
  • In insert query it's self I am getting above error message. It says there is no foreign key reference in parent table. – RNK May 06 '15 at 14:42
  • And as you see: the autoincrement is by 10. In that particular case, it's 6073. and again it's coming as 6081. @YasenZhelev: How can I check this. And record is already there as we see in the first table. – RNK May 06 '15 at 14:44
  • @Ronak Patel just have an IF statement around your query and make sure it went well. Like IF(mysqli_query("....")) { // make second insert here } – Yasen Zhelev May 06 '15 at 14:50
  • @YasenZhelev: I am already checking that thing. If I get `last_inserted_id` only then I add new record to second table. But, I assume that it was returning `6081` and it changed the that id to `6073`. But, I don't know the reason behind that. May be it's `cleardb`? – RNK May 06 '15 at 14:53
  • @Ronak Patel http://php.net/manual/en/function.mysql-insert-id.php getting a last inserted ID does not guarantee you that your query worked. Maybe you are getting 0 or FALSE. That could not be the case of course. – Yasen Zhelev May 06 '15 at 14:57

1 Answers1

1

There's not enough information given in the question to accurately diagnose the problem.

If auto_increment_increment is set to a value of 10, and the values 6061, 6071, 6081 were automatically generated... then it looks like the value 6073 was not an automatically generated value.

The last_insert_id function returns the value that was automatically generated; it does not return a value that was explicitly assigned to a column.

(We do note that it is possible to change the value of auto_increment_increment. But but it seems very odd that auto_increment_increment would have been changed from a value of 10, to a value of 2, then changed to a value of 8, then changed back to a value of 10. And that doesn't at all explain the observed behavior of last_insert_id.)

Replication

And this discussion entirely ignores the possibility that replication is involved, that the 6073 value may have been generated by a different server. Apparently, ClearDB supports "multi-master" replication.

Reference: https://www.cleardb.com/developers/help/faq#general_16

Is it possible that the transaction got "split" across two different MySQL servers?

https://www.cleardb.com/developers/help/faq#general_10

There's a long list of caveats with using the last_insert_id function. That has to be called from the same session, immediately following a successful INSERT (or UPDATE) that generates an AUTO_INCREMENT value, yada, yada, yada.

Reference: https://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • I am also shocked by this _auto_increment_ thing. And I am not adding that that id explicitly from my side. – RNK May 06 '15 at 15:05
  • Thanks for the reference and clear instructions. But, How can we solve this replication issue? – RNK May 06 '15 at 15:18
  • My suspicion is that it's multi-master replication/failover is the culprit. That is, `6073` value was generated by a different MySQL server in the cluster. After the INSERT, the call to the `last_insert_id()` function didn't run in that same MySQL session. I think that's a question for ClearDB support. **"How do I reliably retrieve the AUTO_INCREMENT id value, if the INSERT gets run on a different server? How do I guarantee that `last_insert_id()` gets run on the *same* MySQL session?"** – spencer7593 May 06 '15 at 15:18
  • Yeah. I already submitted a ticket to them. Let's see what happens next. Thanks. – RNK May 06 '15 at 15:22
  • @RonakPatel: I suspect your code references a "ClearDB" connection, and has a handle to that, and doesn't have a handle to the native MySQL connection. And the "ClearDB" connection is probably "switching" to a different MySQL connection. For the `last_insert_id()` function to give the desired behavior, it *has* to be executed in the *same* MySQL session/connection that performed the INSERT. The question for ClearDB support is what your code needs to look like to get that guaranteed. – spencer7593 May 06 '15 at 15:47
  • Can you check my edit in question? I got that reply from them. – RNK May 06 '15 at 15:52
  • @RonakPatel: The reply you received from ClearDB support seems to avoid the crux of the question, whether there's a mechanism available have the value from **`SELECT last_insert_id()`** reliably returned, following a successful `INSERT` that generated an auto_increment value. It seems like their answer to the actual question is "no". It looks like they are recommending that you not use `last_insert_id`, that you retrieve the auto_generated value using *another* unique key. Or, your application use some mechanism *other* than MySQL auto_increment to generate unique integer `id` values. – spencer7593 May 06 '15 at 18:16
  • Yes. It seems like that. It's kind of weird situation for us. Because, we are using this **last_insert_id** everywhere in our project. – RNK May 06 '15 at 19:00
  • @RonakPatel: ... using `last_insert_id` is not at all an unusual pattern. Seems like ClearDB would have a more concrete answer to the question, or at least some kind of indication that using `last_insert_id` is problematic and not supported. Seems like something I'd want to know about *before* I moved my application to ClearDB. – spencer7593 May 06 '15 at 19:07
  • I think the problem is with different database connections itself. For example: If you are getting **last_insert_id()** from first connection then in some cases you can not use that id in different connection (I mean second query object). because, `cleardb` runs on `multi-master` as you mentioned in your answer. To overcome this situation, either sub-query helps or single query object (with `transaction` feature in mysql helps). – RNK May 06 '15 at 19:37
  • What do you recommend here? Should we choose different database provider, should I remove foreign key constraint for now, or should I change my code? – RNK May 06 '15 at 20:20