Prevent Multiple INSERT in MySQL from API

216
June 06, 2017, at 01:15 AM

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 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 returns a result, of course transaction failed.

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

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

Unfortunately, call INSERT perform 3 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 it possible a multiple INSERT into a table and is it possible to prevent them?

Thank you

READ ALSO
mysqlclient pip failure on Linux

mysqlclient pip failure on Linux

I'm working on a Python34/Django project (on CentOS 6

478
php script does not work for too many records

php script does not work for too many records

The following php script works fine if i run it on my MYSQL table with less than 200 records but if the number of records exceeds 200, then it returns a blank screenPlease help

240
Java Spring Boot/Mysql - Doing a query with a HashSet as a Parameter

Java Spring Boot/Mysql - Doing a query with a HashSet as a Parameter

I'm doing a Spring Boot application with JPA and I have and object (let's call it Conversation) which has a property that is a HashSet of another object (User)In the ConversationRepository there's the following method:

284