1200635 - Exception when TC posts to the /jobs/ endpoint for some jobs ("IntegrityError: (1062, "Duplicate entry '0' for key 'PRIMARY'")")

Status: RESOLVED DUPLICATE of bug 1194226

(Tree Management :: Treeherder: API, defect, P1)

Description

[Greg Arndt [:garndt]]

When trying to update the job status for a particular set of jobs using the th api (this is mozilla-taskcluster using the nodejs client), both the production and staging versions of mozilla-taskcluster are receiving errors. 

This does not happen for every job, and seems to be very particular to these new XPCShell jobs that were triggered.

mozilla-taskcluster production: 

https://papertrailapp.com/systems/mozilla-taskcluster/events?r=575367950077571085-575367953126830107

mozilla-taskcluster staging:

https://papertrailapp.com/systems/mozilla-taskcluster-staging/events?r=575367954095714325-575367954770997265

Comment 1

[Ed Morley [:emorley]]

On th-stage-web1 (looking there since SnR of logs will be better due to lower traffic), I see quite a few of:

[2015-09-01 07:45:19,365] ERROR [treeherder.webapp.api.exceptions:25] (1062, "Duplicate entry '0' for key 'PRIMARY'")
[2015-09-01 07:45:19,366] ERROR [treeherder.webapp.api.exceptions:26] Traceback (most recent call last):
  File "/data/www/treeherder.allizom.org/venv/lib/python2.7/site-packages/rest_framework/views.py", line 453, in dispatch
    response = handler(request, *args, **kwargs)
  File "/data/www/treeherder.allizom.org/treeherder-service/treeherder/webapp/api/utils.py", line 105, in use_jobs_model
    return model_func(*args, jm=jm, **kwargs)
  File "/data/www/treeherder.allizom.org/treeherder-service/treeherder/webapp/api/jobs.py", line 174, in create
    jm.store_job_data(request.DATA)
  File "/data/www/treeherder.allizom.org/treeherder-service/treeherder/model/derived/jobs.py", line 1132, in store_job_data
    id_lookups = self.refdata_model.set_all_reference_data()
  File "/data/www/treeherder.allizom.org/treeherder-service/treeherder/model/derived/refdata.py", line 159, in set_all_reference_data
    self.id_lookup['job_types'] = self.process_job_types()
  File "/data/www/treeherder.allizom.org/treeherder-service/treeherder/model/derived/refdata.py", line 651, in process_job_types
    self.job_type_where_filters
  File "/data/www/treeherder.allizom.org/treeherder-service/treeherder/model/derived/refdata.py", line 877, in _process_names_and_symbols
    debug_show=self.DEBUG)
  File "/data/www/treeherder.allizom.org/treeherder-service/treeherder/model/derived/refdata.py", line 131, in execute
    return utils.retry_execute(self.dhub, logger, **kwargs)
  File "/data/www/treeherder.allizom.org/treeherder-service/treeherder/model/utils.py", line 78, in retry_execute
    return dhub.execute(**kwargs)
  File "/data/www/treeherder.allizom.org/venv/lib/python2.7/site-packages/datasource/bases/RDBSHub.py", line 37, in wrapper
    return func(self, **kwargs)
  File "/data/www/treeherder.allizom.org/venv/lib/python2.7/site-packages/datasource/bases/SQLHub.py", line 136, in execute
    return self.__execute(sql, kwargs)
  File "/data/www/treeherder.allizom.org/venv/lib/python2.7/site-packages/datasource/bases/SQLHub.py", line 300, in __execute
    self.__cursor_execute(sql, kwargs, cursor)
  File "/data/www/treeherder.allizom.org/venv/lib/python2.7/site-packages/datasource/bases/SQLHub.py", line 315, in __cursor_execute
    cursor.executemany(sql, kwargs['placeholders'])
  File "/data/www/treeherder.allizom.org/venv/lib/python2.7/site-packages/newrelic-2.54.0.41/newrelic/hooks/database_dbapi2.py", line 39, in executemany
    return self.__wrapped__.executemany(sql, seq_of_parameters)
  File "/data/www/treeherder.allizom.org/venv/lib/python2.7/site-packages/MySQLdb/cursors.py", line 237, in executemany
    r = r + self.execute(query, a)
  File "/data/www/treeherder.allizom.org/venv/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/data/www/treeherder.allizom.org/venv/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
IntegrityError: (1062, "Duplicate entry '0' for key 'PRIMARY'")


...though no way to tell with 100% certainty whether that's the same error you're seeing, due to bug 1193420 and bug 1191080 (I'd consider it likely though).

Comment 2

[Ed Morley [:emorley]]

That exception is occurring here:
https://github.com/mozilla/treeherder/blob/a06a006ed7814dbecb7e3cdd8e81c56b48be17dd/treeherder/model/derived/refdata.py#L873-L877

(The log from which is was taken was /var/log/run_gunicorn-supervisor.log)

There are multiple ways into _process_names_and_symbols(), in this case we came via process_job_types():
https://github.com/mozilla/treeherder/blob/a06a006ed7814dbecb7e3cdd8e81c56b48be17dd/treeherder/model/derived/refdata.py#L638-L652

And so the SQL proc being used here was `reference.inserts.create_job_type`:
https://github.com/mozilla/treeherder/blob/8d16b4052637f8bde3fc9e8013db1d352d42e18e/treeherder/model/sql/reference.json#L43-L53

"create_job_type":{
    "sql":"INSERT INTO `job_type` (`name`, `symbol`, `description`)
           SELECT ?, ?, 'fill me'
            FROM DUAL
            WHERE NOT EXISTS (
            SELECT `name`
            FROM `job_type`
            WHERE `name` = ? AND `symbol` = ?
            )",
    "host_type":"master_host"
}

Comment 3

[Ed Morley [:emorley]]

Mauro/Cameron, thoughts? :-)

Comment 4

[Ed Morley [:emorley]]

Bug 1191276 would make this a lot easier - we could actually see what was being sumbitting in the papertrail logs. Greg, would it be possible to get someone to look at that soon?

Comment 5

[Ed Morley [:emorley]]

This was caused by the stage/prod schema changes in bug 1194226, specifically the SQL in attachment 8651193 [details] (it accidentally dropped the auto increment on the primary key for several reference data tables, which would only start causing errors when previously unseen reference data types are added).

Will has a fix in the form of attachment 8655670 [details].

Comment 6

[William Lachance (:wlach)]

This bug is really just a duplicate of some fallout from bug 1194226, which I reopened and hopefully fixed.