1

I'm using MySQL on a CentOS server from a website running Apache and Perl. I'm seeing this behavior (mocked up):

mysql> describe product;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into product (name) values ('Widget');
Query OK, 1 row affected (0.00 sec)

mysql> insert into product (name) values ('Thing');
Query OK, 1 row affected (0.00 sec)

mysql> select * from product;
+----+--------+
| id | name   |
+----+--------+
|  1 | Widget |
|  2 | Widget |
|  3 | Thing  |
+----+--------+
3 rows in set (0.00 sec)

My theory is that the Perl CGI script may be executed concurrently (perhaps due to double click, browser refresh, etc.) so the insert gets performed twice. This is fairly rare but causes problems when it happens.

In cases where this happens, all columns except 'id' have identical values. Other than column 'id' duplicate values are allowed.

Is there a way to prevent this behavior?

Timothy B.
  • 617
  • 1
  • 7
  • 15
  • 1
    Do you have control over the script and can modify it or do you look for something purely on the database layer? – Sven Feb 09 '15 at 19:34
  • Sven, it's my script. I wrote it. It uses the Perl MySQL DBI. – Timothy B. Feb 09 '15 at 19:58
  • You will have to look into either a front-end or back-end solution. Front-end meaning that you disable the Submit button upon clicking it and back-end meaning that you devise a double-submit prevention feature. Without knowing the details of your script, this question is nearly impossible to concisely answer – MonkeyZeus Feb 09 '15 at 20:23
  • 3
    Please show the relevant section of your Perl script. As a workaround, you could [create a unique constraint across all columns](http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql) except `id`, but that's just a band-aid; you should really figure out the root cause. – ThisSuitIsBlackNot Feb 09 '15 at 20:23
  • 1
    One thing: if you POST forms to insert new rows, you should redirect user to next page. So user can't accidentally reload page/run it twice. – w.k Feb 09 '15 at 20:23
  • @ThisSuitIsBlackNot even as a band-aid, that workaround would cause far more problems than it would solve and I would caution Timothy 100% against taking that advice. – MonkeyZeus Feb 09 '15 at 20:24
  • 1
    @MonkeyZeus It was a haphazard remark, so I don't doubt it could be mistaken, but please elaborate. – ThisSuitIsBlackNot Feb 09 '15 at 20:27
  • Through experience I've noticed that Perl CGI scripts can run concurrently within the same user session. I'm not sure why this happens. In the case of the "submit order" script I create a temporary "lock file" based on session ID. If the file is detected the script aborts (and assumes the previously running instance will finish the job). Perhaps the problem I'm having is one of interleaved database updates. – Timothy B. Feb 09 '15 at 21:36
  • Seconding @ThisSuitIsBlackNot... show us the Perl code! – mwp Feb 09 '15 at 21:45

1 Answers1

0

If you installed Perl, Apache, and MySQL from CentOS repositories and didn't make any radical configuration changes, it's highly unlikely you've found a bug in the platform. Are you using mod_perl or PSGI or is it definitely CGI? Have you installed any Perl modules from CPAN sources or has everything been installed through yum?

A stupid solution you might consider implementing is generating some kind of nonce (one-time-use string or number) in your script, adding a column for it and a unique index on that column to your table, and inserting it along with the rest of the form data. If you catch a duplicate key error, just disregard it. That won't explain what's happening but it will prevent it from happening.

mwp
  • 8,217
  • 20
  • 26