916628 - postgres errors in crontabber after weekly data refresh

Status: RESOLVED FIXED

(Socorro :: Database, task)

Description

[Lonnen :lonnen]

From the crontabber log:

2013-09-15 16:02:11,893 DEBUG - MainThread - about to run <class 'socorro.cron.jobs.matviews.SignatureSummaryCronApp'>
2013-09-15 16:09:46,406 DEBUG - MainThread - error when running <class 'socorro.cron.jobs.matviews.SignatureSummaryCronApp'> on None
Traceback (most recent call last):
  File "/data/socorro/application/socorro/cron/crontabber.py", line 706, in _run_one
    for last_success in self._run_job(job_class, config, info):
  File "/data/socorro/application/socorro/cron/base.py", line 174, in main
    function(when)
  File "/data/socorro/application/socorro/cron/base.py", line 213, in _run_proxy
    self.run(connection, date)
  File "/data/socorro/application/socorro/cron/jobs/matviews.py", line 50, in run
    self.run_proc(connection, [target_date])
  File "/data/socorro/application/socorro/cron/jobs/matviews.py", line 21, in run_proc
    cursor.callproc(self.get_proc_name(), signature)
IntegrityError: duplicate key value violates unique constraint "signature_summary_products_pkey"
DETAIL:  Key (signature_id, product_version_id, report_date)=(4873272, 1725, 2013-09-13) already exists.
CONTEXT:  SQL statement "INSERT INTO signature_summary_products (
    signature_id
    , product_version_id
    , product_name
    , version_string
    , report_count
    , report_date
)
SELECT
    signature_id
    , product_version_id
    , product_name
    , version_string
    , count(*) AS report_count
    , updateday AS report_date
FROM reports_clean
    JOIN product_versions USING (product_version_id)
WHERE
    date_processed::date = updateday
GROUP BY product_version_id, product_name, version_string, signature_id, updateday"
PL/pgSQL function update_signature_summary(date,boolean) line 23 at SQL statement



2013-09-15 16:35:02,370 DEBUG - MainThread - about to run <class 'socorro.cron.jobs.matviews.GraphicsDeviceCronApp'>
2013-09-15 16:35:02,377 DEBUG - MainThread - error when running <class 'socorro.cron.jobs.matviews.GraphicsDeviceCronApp'> on None
Traceback (most recent call last):
  File "/data/socorro/application/socorro/cron/crontabber.py", line 706, in _run_one
    for last_success in self._run_job(job_class, config, info):
  File "/data/socorro/application/socorro/cron/base.py", line 174, in main
    function(when)
  File "/data/socorro/application/socorro/cron/base.py", line 213, in _run_proxy
    self.run(connection, date)
  File "/data/socorro/application/socorro/cron/jobs/matviews.py", line 50, in run
    self.run_proc(connection, [target_date])
  File "/data/socorro/application/socorro/cron/jobs/matviews.py", line 21, in run_proc
    cursor.callproc(self.get_proc_name(), signature)
ProgrammingError: function update_graphics_devices(date) does not exist
LINE 1: SELECT * FROM update_graphics_devices('2013-09-13'::date)
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Comment 1

[Brandon Savage [:brandon]]

So the update_graphics_devices() was added last week, not sure how that migration gets run but we can take a look at it. Dev or stage?

Comment 2

[Lonnen :lonnen]

Could be both. Stage is alerting.

Comment 3

[Lonnen :lonnen]

Selena manually runs migrations on stage for now. I bet it got wiped out in the DB copy from prod.

Comment 4

[Selena Deckelmann :selenamarie :selena]

I'll run it now.

Comment 5

[Selena Deckelmann :selenamarie :selena]

The migration completed. I'll see about adding an 'alembic upgrade head' to the database refresh script.

Comment 6

[Lonnen :lonnen]

Still encountering Integrity error, but I think those occasionally build up. The graphics function appears to have an error in it. 

2013-09-16 07:18:12,659 DEBUG - MainThread - error when running <class 'socorro.cron.jobs.matviews.SignatureSummaryCronApp'> on None
Traceback (most recent call last):
  File "/data/socorro/application/socorro/cron/crontabber.py", line 706, in _run_one
    for last_success in self._run_job(job_class, config, info):
  File "/data/socorro/application/socorro/cron/base.py", line 174, in main
    function(when)
  File "/data/socorro/application/socorro/cron/base.py", line 213, in _run_proxy
    self.run(connection, date)
  File "/data/socorro/application/socorro/cron/jobs/matviews.py", line 50, in run
    self.run_proc(connection, [target_date])
  File "/data/socorro/application/socorro/cron/jobs/matviews.py", line 21, in run_proc
    cursor.callproc(self.get_proc_name(), signature)
IntegrityError: duplicate key value violates unique constraint "signature_summary_products_pkey"
DETAIL:  Key (signature_id, product_version_id, report_date)=(4873272, 1725, 2013-09-13) already exists.
CONTEXT:  SQL statement "INSERT INTO signature_summary_products (
    signature_id
    , product_version_id
    , product_name
    , version_string
    , report_count
    , report_date
)
SELECT
    signature_id
    , product_version_id
    , product_name
    , version_string
    , count(*) AS report_count
    , updateday AS report_date
FROM reports_clean
    JOIN product_versions USING (product_version_id)
WHERE
    date_processed::date = updateday
GROUP BY product_version_id, product_name, version_string, signature_id, updateday"
PL/pgSQL function update_signature_summary(date,boolean) line 23 at SQL statement

2013-09-16 07:50:36,856 DEBUG - MainThread - error when running <class 'socorro.cron.jobs.matviews.GraphicsDeviceCronApp'> on None
Traceback (most recent call last):
  File "/data/socorro/application/socorro/cron/crontabber.py", line 706, in _run_one
    for last_success in self._run_job(job_class, config, info):
  File "/data/socorro/application/socorro/cron/base.py", line 174, in main
    function(when)
  File "/data/socorro/application/socorro/cron/base.py", line 213, in _run_proxy
    self.run(connection, date)
  File "/data/socorro/application/socorro/cron/jobs/matviews.py", line 50, in run
    self.run_proc(connection, [target_date])
  File "/data/socorro/application/socorro/cron/jobs/matviews.py", line 21, in run_proc
    cursor.callproc(self.get_proc_name(), signature)
ProgrammingError: permission denied for relation graphics_device
CONTEXT:  SQL statement "INSERT INTO graphics_device (
    vendor_hex
    , adapter_hex
)
SELECT
    new_graphics_devices.vendor_hex
    , new_graphics_devices.adapter_hex
FROM new_graphics_devices
LEFT OUTER JOIN graphics_device
ON new_graphics_devices.vendor_hex = graphics_device.vendor_hex
    AND new_graphics_devices.adapter_hex = graphics_device.adapter_hex
WHERE graphics_device.graphics_device_id IS NULL
AND new_graphics_devices.vendor_hex IS NOT NULL
GROUP BY
    new_graphics_devices.vendor_hex
    , new_graphics_devices.adapter_hex"
PL/pgSQL function update_graphics_devices(date,boolean) line 30 at SQL statement

Comment 7

[Brandon Savage [:brandon]]

So, with the migration run this looks very much like a permissions problem on the database. :selenamarie can you take a look and see what you think?

Comment 8

[[github robot]]

Commits pushed to master at https://github.com/mozilla/socorro

https://github.com/mozilla/socorro/commit/d44aca8acbbfb821f71a281196468576e744a1fc
Fixes Bug 916628 - Resolving permissions problems on new migration.

https://github.com/mozilla/socorro/commit/05594e84dfbca9817fef1b53f0cacc913603cf84
Merge pull request #1526 from brandonsavage/bug916628migrationupdate

Fixes Bug 916628 - Resolving permissions problems on new migration. r=selenamarie