I
I
Ivan Melnikov2018-04-24 14:50:03
MySQL
Ivan Melnikov, 2018-04-24 14:50:03

How to synchronize uploading data from an application to a remote MySQL server?

There is a table

CREATE TABLE t1(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
link CHAR(36) NOT NULL UNIQUE
);

An external application (1C:Enterprise) writes to this table through the function:
CREATE FUNCTION insert_t1 (_link char(36)) RETURNS INT UNSIGNED
BEGIN
INSERT INTO t1(link) VALUE(_link);
RETURN LAST_INSERT_ID();
END

This function returns the generated id. The problem is that sometimes it happens that a record is made in the table, and the application does not get back the generated id. As a result, the application tries to re-write, which causes an error due to a duplicate of the unique key.
How to avoid such troubles? With a transaction? But, as I understand it, it will not help here. The returned id after the successful completion of the transaction may be lost somewhere in the network and not reach the application. Or I'm wrong?

Answer the question

In order to leave comments, you need to log in

2 answer(s)
R
Rsa97, 2018-04-24
@immelnikoff

Insert through INSERT IGNORE, then there will be no duplication. Then do a SELECT on link and return the resulting id.

V
Vitaly Arkhipov, 2018-04-24
@arvitaly

Generate a unique key on the client, receive from the server not an id, but a confirmation of the record. If it was not received, send the request again, but something like insert or select, and let the server return success if the record is found or tries to write.
For the key, make a special field in the table with UNIQUE INDEX.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question