1UP_SQL = """ 2CREATE UNIQUE INDEX host_queue_entries_job_id_and_host_id 3ON host_queue_entries (job_id, host_id); 4 5DROP INDEX host_queue_entries_job_id ON host_queue_entries; 6""" 7 8 9DOWN_SQL = """ 10CREATE INDEX host_queue_entries_job_id ON host_queue_entries (job_id); 11 12DROP INDEX host_queue_entries_job_id_and_host_id ON host_queue_entries; 13""" 14 15 16def 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 37def 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) 57