Closed Bug 916628 Opened 11 years ago Closed 11 years ago

postgres errors in crontabber after weekly data refresh

Categories

(Socorro :: Database, task)

Product:
Socorro
Component:
Database
Platform:
x86
macOS
Type:
task
Priority:
Not set
Severity:
normal

Tracking

(Not tracked)

Status:
RESOLVED FIXED

People

(Reporter: lonnen, Assigned: brandon)

References

Details

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.
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?
Could be both. Stage is alerting.
Selena manually runs migrations on stage for now. I bet it got wiped out in the DB copy from prod.
I'll run it now.
The migration completed. I'll see about adding an 'alembic upgrade head' to the database refresh script.
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
Blocks: 853468
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?
Flags: needinfo?(sdeckelmann)
Assignee: nobody → bsavage
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
Status: NEW → RESOLVED
Closed: 11 years ago
Resolution: --- → FIXED
Target Milestone: --- → 60
Flags: needinfo?(sdeckelmann)
You need to log in before you can comment on or make changes to this bug.