0

I have a MySQL MyISAM Table "transaction".

Schema is similar to:

+-----+--------+-------------------+---------------------+
| id  | amount | duplication_check |        date         |
+-----+--------+-------------------+---------------------+
| 123 |     80 |         123456789 | 2017-06-05 11:12:23 |
| 124 |     80 |         123456788 | 2017-06-05 11:12:23 |
+-----+--------+-------------------+---------------------+

duplication_check can be empty/null but if it is passed from a script (for example from an API call) my PHP code performs a simple check:

SELECT id FROM transaction WHERE duplication_check == 'passed_from_external'.

If this returns a result, of course the transaction failed.

Now, I have an Android app developed myself that uses - when submit a transaction via API - this duplication_check (I generate it with a time() function).

When the smartphone lose the connection, I can read in the API log (another table to log the API call) 2 or 3 times in different seconds that the API called my server 2-3 times, with same duplication code.

Unfortunately, the INSERT call is done three times. But, from a PHP test, I cannot replicate the bug (following call with same duplication_check are failed).

API log example (note the 3 different dates):

+--------+--------+-------------------+---------------------+
| id_api | amount | duplication_check |        date         |
+--------+--------+-------------------+---------------------+
|    123 |     80 | AAAAAAA           | 2017-06-05 11:12:23 |
|    124 |     80 | AAAAAAA           | 2017-06-05 11:12:25 |
|    125 |     80 | AAAAAAA           | 2017-06-05 11:12:27 |
+--------+--------+-------------------+---------------------+

Transaction result (note the 3 same dates, next to the API Log):

+--------+--------+-------------------+---------------------+
|   id   | amount | duplication_check |        date         |
+--------+--------+-------------------+---------------------+
| 123111 |     80 | AAAAAAA           | 2017-06-05 11:12:28 |
| 124112 |     80 | AAAAAAA           | 2017-06-05 11:12:28 |
| 125113 |     80 | AAAAAAA           | 2017-06-05 11:12:28 |
+--------+--------+-------------------+---------------------+

Probably, I need to edit the Android app to block multiple call, but, is this possibly a multiple INSERT into a table and is it possible to prevent them?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
sineverba
  • 5,059
  • 7
  • 39
  • 84
  • 1
    Why don´t you make a UNIQUE index with your duplication_check row? This will prevent to write it more than once – nacho Jun 05 '17 at 17:45
  • @nacho can I edit the table adding the UNIQUE and insert a "random" selected unique code? – sineverba Jun 05 '17 at 17:48
  • You´ll need a UNIQUE and NULL column, so when you don´t have any value the null is admited (and don´t generates a duplicated value). And yes, you can ALTER (or edit) the table and ADD (or modify) your column with a random selected unique code. – nacho Jun 05 '17 at 17:58
  • OK. If my "theory" is right, the UNIQUE prevent the write more than one time also in the case which database perform multiple INSERT at once? – sineverba Jun 05 '17 at 19:13
  • Yes, your "theory" is right – nacho Jun 05 '17 at 19:16
  • Query should be SELECT id FROM transaction WHERE duplication_check = 'passed_from_external'. (Single equal to symbol in SQL query). – Senthil Jun 06 '17 at 00:55
  • Create a unique key constraint on "duplicate check" column and throw error in ur php code . if( mysql_errno() == 1062) {
    // Duplicate key
    } Ref: https://stackoverflow.com/questions/8449540/php-detect-mysql-update-insertion-failure-due-to-violated-unique-constraint     – Senthil Jun 06 '17 at 00:58
  • @nacho thank you. Seems that I solved my issue. If you publish your solution as answer, I can approve it. Thank you! – sineverba Jun 09 '17 at 07:43
  • 1
    @sineverba Thank you. I did copy & paste over the answer so you can approve it – nacho Jun 09 '17 at 12:12

1 Answers1

1

Why don´t you make a UNIQUE index with your duplication_check row? This will prevent to write it more than once. You´ll need a UNIQUE and NULL column, so when you don´t have any value the null is admited (and don´t generates a duplicated value). And yes, you can ALTER (or edit) the table and ADD (or modify) your column with a random selected unique code.

nacho
  • 5,280
  • 2
  • 25
  • 34