frontend/migrations/042_unique_index_on_hqe_job_and_host.py

   1 UP_SQL = """
   2 CREATE UNIQUE INDEX host_queue_entries_job_id_and_host_id
   3 ON host_queue_entries (job_id, host_id);
   4
   5 DROP INDEX host_queue_entries_job_id ON host_queue_entries;
   6 """
   7
   8
   9 DOWN_SQL = """
  10 CREATE INDEX host_queue_entries_job_id ON host_queue_entries (job_id);
  11
  12 DROP INDEX host_queue_entries_job_id_and_host_id ON host_queue_entries;
  13 """
  14
  15
  16 def null_out_duplicate_hqes(manager, hqe_ids):
  17     if not hqe_ids:
  18         return
  19     ids_to_null_string = ','.join(str(hqe_id) for hqe_id in hqe_ids)
  20
  21     # check if any of the HQEs we're going to null out are active. if so, it's
  22     # too dangerous to proceed.
  23     rows = manager.execute('SELECT id FROM host_queue_entries '
  24                            'WHERE active AND id IN (%s)' % ids_to_null_string)
  25     if rows:
  26         raise Exception('Active duplicate HQEs exist, cannot proceed.  Please '
  27                         'manually abort these HQE IDs: %s' % ids_to_null_string)
  28
  29     # go ahead and null them out
  30     print 'Nulling out duplicate HQE IDs: %s' % ids_to_null_string
  31     manager.execute('UPDATE host_queue_entries '
  32                     'SET host_id = NULL, active = FALSE, complete = TRUE, '
  33                     'aborted = TRUE, status = "Aborted" '
  34                     'WHERE id IN (%s)' % ids_to_null_string)
  35
  36
  37 def migrate_up(manager):
  38     # cleanup duplicate host_queue_entries. rather than deleting them (and
  39     # dealing with foreign key references), we'll just null out their host_ids
  40     # and set them to aborted.
  41     rows = manager.execute('SELECT GROUP_CONCAT(id), COUNT(1) AS count '
  42                            'FROM host_queue_entries '
  43                            'WHERE host_id IS NOT NULL '
  44                            'GROUP BY job_id, host_id HAVING count > 1')
  45     # gather all the HQE IDs we want to null out
  46     ids_to_null = []
  47     for ids_string, _ in rows:
  48         id_list = ids_string.split(',')
  49         # null out all but the first one.  this isn't terribly important, but
  50         # the first one is the most likely to have actually executed, so might
  51         # as well keep that one.
  52         ids_to_null.extend(id_list[1:])
  53
  54     null_out_duplicate_hqes(manager, ids_to_null)
  55
  56     manager.execute_script(UP_SQL)