Duplicate inserts
Gerhard Killesreiter - June 16, 2008 - 19:41
| Project: | Project |
| Version: | 5.x-1.x-dev |
| Component: | Usage statistics |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | patch (code needs review) |
Description
Narayan has informed me that we are getting quite a few MySQL errors like this one:
INSERT INTO project_usage_raw (project_uri, timestamp,
site_key, api_version, project_version, ip_addr) VALUES
('faq', 1213574400,
'b23892e84b585bf819a54c28eea99833', '5.x',
'5.x-2.10', '213.81.189.130') in
/var/www/drupal.org/htdocs/includes/database.mysql.inc on line
179
#1
yeah, the problem is that in release/project-release-serve-history.php we're doing:
<?phpif (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.
#2
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
#3
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 ;)
#4
I don't have a site setup to test this but it looks like a valid approach to me.