We have made two almost-successful attempts at a production Hail deployment today, and unfortunately both of them have happened to be running the batch_database job simultaneously. This job applies database migration scripts, and unfortunately it has neither good logging indicating which migrations are actually being applied nor good database locking to ensure absolutely that migrations cannot interfere with each other.
Our first attempt was batch 429683, which finished with the following:
This 112 entry indicates that migration 110 has already been recorded as applied.
Considering our previous deployment which included up to migration 108 (dedup-agg-batch-resources), it should only be these last four migrations that were candidates for actually needing to be applied by today's jobs. (However the second job spent ~2 hours at 94 (dedup-billing-project-users-by-date) suggesting that it had not in fact been previously applied. :shrug:)
I have examined the last four scripts and state of the production database, and tentatively conclude that:
setup-job-groups is mostly idempotent and the database state reflects its successful application;
dedup-job-resources is probably vaguely idempotent, affects 147,377,473 rows, and might well have been successfully applied;
fix-job-groups-state-enum has probably been successfully applied (though I can't see any triggers via the mysql client) and was not attempted by the second job;
populate-job-groups returned nonzero in the first job and was not attempted by the second job;
batch_migration_version.version is 113, indicating that 111 (fix-job-groups-state-enum) is the last migration that has been recorded as successfully applied.
Consequently the database is likely in a consistently partly migrated state, and we can safely just submit another batch in the expectation that it may run to completion this time — modulo the unexplained nonzero result from populate-job-groups. But we should clarify the logging as per this PR first.
In future until these migrations have better locking to ensure only one is running at a time, before starting a new production deploy batch we should manually ensure that no previous batch's batch_database job is still running.
We have made two almost-successful attempts at a production Hail deployment today, and unfortunately both of them have happened to be running the
batch_database
job simultaneously. This job applies database migration scripts, and unfortunately it has neither good logging indicating which migrations are actually being applied nor good database locking to ensure absolutely that migrations cannot interfere with each other.Our first attempt was batch 429683, which finished with the following:
Our second attempt was batch 429692, which finished with the following:
This 112 entry indicates that migration 110 has already been recorded as applied.
Considering our previous deployment which included up to migration 108 (dedup-agg-batch-resources), it should only be these last four migrations that were candidates for actually needing to be applied by today's jobs. (However the second job spent ~2 hours at 94 (dedup-billing-project-users-by-date) suggesting that it had not in fact been previously applied. :shrug:)
I have examined the last four scripts and state of the production database, and tentatively conclude that:
mysql
client) and was not attempted by the second job;Consequently the database is likely in a consistently partly migrated state, and we can safely just submit another batch in the expectation that it may run to completion this time — modulo the unexplained nonzero result from populate-job-groups. But we should clarify the logging as per this PR first.
In future until these migrations have better locking to ensure only one is running at a time, before starting a new production deploy batch we should manually ensure that no previous batch's
batch_database
job is still running.