Fixes broken MySQL migration for retried

What does this MR do?

Fixes broken MySQL migration for retried

Mysql2::Error: Table 'ci_builds' is specified twice, both as a target for 'UPDATE' and as a separate source for data: UPDATE ci_builds SET retried = ((SELECT MAX(ci_builds2.id)

Closes https://gitlab.com/gitlab-org/gitlab-ce/issues/32647

This is slow, as this does create the temporary table and then updates whole retried.

mentioned in issue #32647 (closed)

@ayufan Could you also add this test? spec/migrations/update_retried_for_ci_builds_spec.rb

require 'spec_helper'
require Rails.root.join('db', 'post_migrate', '20170503004427_upate_retried_for_ci_build.rb')

describe UpateRetriedForCiBuild, truncate: true do
  let(:pipeline) { create(:ci_pipeline) }
  let!(:build_old) { create(:ci_build, pipeline: pipeline, name: 'test') }
  let!(:build_new) { create(:ci_build, pipeline: pipeline, name: 'test') }

  before do
    described_class.new.up
  end

  it 'updates ci_builds.is_retried' do
    expect(build_old).to be_retried
    expect(build_new).not_to be_retried
  end
end

I got an error?

 ActiveRecord::StatementInvalid:
   Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE ci_builds SET retried=
     (id NOT IN (
       SELECT * FROM (SELECT MAX(ci_bu' at line 1: UPDATE `ci_builds` SET `retried` = ((UPDATE ci_builds SET retried=
     (id NOT IN (
       SELECT * FROM (SELECT MAX(ci_builds.id) FROM ci_builds GROUP BY commit_id, name) AS latest_jobs
     ))
   WHERE retried IS NULL
   ) != ci_builds.id) WHERE `ci_builds`.`id` >= 1 AND `ci_builds`.`id` < 2 AND `ci_builds`.`retried` IS NULL

Oh, nevermind. It's not used against update_column_in_batches

@godfat Could you take this and verify it correctness?

assigned to @godfat

changed milestone to %9.2

added 1 commit

• 5d63a393 - Add a test to ensure this works on MySQL

Compare with previous version

I did run this test on mysql running in Docker for Mac with storage exposed through fusefs (slow for slow writes).

I did generate the test data for MySQL (importing a few times):

echo "INSERT INTO ci_builds (commit_id, name) VALUES"

i=0
while [[ $i -lt 100000 ]]; do
	j=0
	while [[ $j -lt 10 ]]; do
		printf "($i, \"test$j\"),\n"
		printf "($i, \"test$j\"),\n"
    	let j=j+1 
	done
    let i=i+1 
done
printf "($i, \"test$j\")\n"

Running that on my MySQL:

mysql>       UPDATE ci_builds SET retried=
    ->         (id NOT IN (
    ->           SELECT * FROM (SELECT MAX(ci_builds.id) FROM ci_builds GROUP BY commit_id, name) AS latest_jobs
    ->         ))
    ->       WHERE retried IS NULL;
Query OK, 2400000 rows affected (3 min 37.87 sec)
Rows matched: 2400000  Changed: 2400000  Warnings: 0

The numbers for that migration are reasonable. It took less than 4 minutes to process 2.4 million rows.

approved this merge request

enabled an automatic merge when the pipeline for 5d63a393 succeeds

assigned to @timothyandrew

mentioned in issue #31934 (closed)

@godfat: Not sure if you've seen this yet, but static-analysis failed with "Spec path should end with upate_retried_for_ci_build*_spec.rb"

added 1 commit

• cbafe24a - Respect the typo as rubocop said

Compare with previous version

@timothyandrew Yeah... I just pushed a commit. Thanks for reminding me

approved this merge request

enabled an automatic merge when the pipeline for cbafe24a succeeds