Duplicate inserts

yeah, the problem is that in release/project-release-serve-history.php we're doing:

    if (db_result(db_query("SELECT COUNT(*) FROM {project_usage_raw} WHERE project_uri = '%s' AND timestamp = %d AND site_key = '%s'", $project_name, $timestamp, $site_key))) {
      db_query("UPDATE {project_usage_raw} SET api_version = '%s', project_version = '%s', ip_addr = '%s' WHERE project_uri = '%s' AND timestamp = %d AND site_key = '%s'", $api_version, $project_version, $ip_addr, $project_name, $timestamp, $site_key);
    }
    else {
      db_query("INSERT INTO {project_usage_raw} (project_uri, timestamp, site_key, api_version, project_version, ip_addr) VALUES ('%s', %d, '%s', '%s', '%s', '%s')", $project_name, $timestamp, $site_key, $api_version, $project_version, $ip_addr);
    }

so i'm guessing that the client is pinging the server multiple times while the count it going? we really should be using mysql's replace operation.

More examples of this below. This might be less of an issue with code and more with timeouts. Not sure. There are also some messages like this in the log:

[16-Jun-2008 19:16:22] PHP Fatal error: Maximum execution time of 30 seconds exceeded in /var/www/drupal.org/htdocs/includes/common.inc on line 550

and www1 is segfaulting around once an hour now. (this usually goes along with a bug somewhere)

--------

query: INSERT INTO project_usage_raw (project_uri, timestamp, site_key, api_version, project_version, ip_addr) VALUES ('fckeditor', 1213574400, 'e565dc778a5a972213e43b6f9df7bdc0', '6.x', '6.x-1.2-1', '193.170.238.50') in /var/www/drupal.org/htdocs/includes/database.mysql.inc on line 179
[16-Jun-2008 17:59:45] PHP Warning: Duplicate entry '1213574400-db_maintenance-1a1fa2f1c5852f1818e4f2fc2be8089a' for key 1
query: INSERT INTO project_usage_raw (project_uri, timestamp, site_key, api_version, project_version, ip_addr) VALUES ('db_maintenance', 1213574400, '1a1fa2f1c5852f1818e4f2fc2be8089a', '6.x', '6.x-1.1', '78.110.50.104') in /var/www/drupal.org/htdocs/includes/database.mysql.inc on line 179
[16-Jun-2008 18:41:50] PHP Warning: Duplicate entry '1213574400-blockcache-49b1f2332ea714ae59aa5e80b852a092' for key 1
query: INSERT INTO project_usage_raw (project_uri, timestamp, site_key, api_version, project_version, ip_addr) VALUES ('blockcache', 1213574400, '49b1f2332ea714ae59aa5e80b852a092', '5.x', '5.x-1.x-dev', '208.97.166.83') in /var/www/drupal.org/htdocs/includes/database.mysql.inc on line 179
[16-Jun-2008 18:41:52] PHP Warning: Duplicate entry '1213574400-pagearray-49b1f2332ea714ae59aa5e80b852a092' for key 1
query: INSERT INTO project_usage_raw (project_uri, timestamp, site_key, api_version, project_version, ip_addr) VALUES ('pagearray', 1213574400, '49b1f2332ea714ae59aa5e80b852a092', '5.x', '5.x-1.x-dev', '208.97.166.83') in /var/www/drupal.org/htdocs/includes/database.mysql.inc on line 179
[16-Jun-2008 18:41:54] PHP Warning: Duplicate entry '1213574400-tinymce-49b1f2332ea714ae59aa5e80b852a092' for key 1
query: INSERT INTO project_usage_raw (project_uri, timestamp, site_key, api_version, project_version, ip_addr) VALUES ('tinymce', 1213574400, '49b1f2332ea714ae59aa5e80b852a092', '5.x', '5.x-1.9', '208.97.166.83') in /var/www/drupal.org/htdocs/includes/database.mysql.inc on line 179
[16-Jun-2008 18:41:55] PHP Warning: Duplicate entry '1213574400-webform-49b1f2332ea714ae59aa5e80b852a092' for key 1
query: INSERT INTO project_usage_raw (project_uri, timestamp, site_key, api_version, project_version, ip_addr) VALUES ('webform', 1213574400, '49b1f2332ea714ae59aa5e80b852a092', '5.x', '5.x-1.8', '208.97.166.83') in /var/www/drupal.org/htdocs/includes/database.mysql.inc on line 179
[16-Jun-2008 19:12:32] PHP Warning: Duplicate entry '1213574400-akismet-6a8ddbdbb489d2105e15dfb73e3a2db4' for key 1
query: INSERT INTO project_usage_raw (project_uri, timestamp, site_key, api_version, project_version, ip_addr) VALUES ('akismet', 1213574400, '6a8ddbdbb489d2105e15dfb73e3a2db4', '5.x', '5.x-1.3', ' 64.202.165.132') in /var/www/drupal.org/htdocs/includes/database.mysql.inc on line 179
[16-Jun-2008 19:42:28] PHP Warning: Duplicate entry 'ff58f7818d830d1eb8eba483ee621cef' for key 1

The pattern we follow for this in core is to do an update query and if that affects 0 rows then to do an insert.

I haven't tested this (it's, uh, a little hard to).

See user_set_authmaps in the Drupal6 user.module for an example of the pattern.

In Drupal 7 we can just use the merge query type...whenever we get there ;)

I don't have a site setup to test this but it looks like a valid approach to me.

Indeed, this is the same simple pattern as used in user_set_authmaps(). It does not completely solve the race condition (it is still possible that an INSERT happens between the UPDATE and INSERT, but it reduces the window for the race condition by trying to act right away, instead of just looking at the data. This is a well used pattern in core, so although I am not testing this either by hammering my test site with data, I am confident that this should help.

committed to 5.x and HEAD -- thanks!