action #20930

Database upgrade fails on servers with many jobs (probably over 65,535)

Added by AdamWill over 2 years ago. Updated over 2 years ago.

Status:ResolvedStart date:29/07/2017
Priority:UrgentDue date:
Assignee:coolo% Done:

0%

Category:Concrete Bugs
Target version:Milestone 9
Difficulty:
Duration:

Description

As I reported to Martchus and he confirmed today, it seems that the 56-57 schema migration (with current git master openQA code) fails on servers with a lot of jobs, with an error message that starts like this:

failed to run Perl in /usr/share/openqa/script/../dbicdh/_common/upgrade/56-57/001-migrate-jobs.pl: DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::Pg::st execute failed: number of parameters must be between 0 and 65535 [for Statement "SELECT me.job_id, me.result, COUNT( id ) FROM job_modules me WHERE ( job_id IN (

I can't paste the entire error message because it's huge - the next part is a huge list of question marks for some reason, then tens of thousands of job IDs. Martchus had this to say about it:

adamw: I should have tested this migration with a production database. Obviously the select with all job IDs is a bit too much (and actually quite useless).
adamw: To work around this, replace line https://github.com/os-autoinst/openQA/blob/master/lib/OpenQA/Schema/Result/JobModules.pm#L365 with empty {}.
adamw: BTW: job_module_stats is not used anywhere else so modifying it should not cause any further trouble. It actually should be removed and its code moved into the migration.

Indeed his suggestion worked: I made that edit to JobModules.pm, re-ran the upgrade, and it worked fine, and openQA has subsequently seemed to be working fine.

This is a significant issue as most production deployments are probably going to be affected by this, I would think.

History

#1 Updated by coolo over 2 years ago

  • Status changed from New to Resolved
  • Assignee set to coolo
  • Target version set to Milestone 9

Also available in: Atom PDF