[GitHub] [airflow] flrn77 commented on issue #24526: upgrading from 2.2.3 or 2.2.5 to 2.3.2 fails on migration-job

[GitBox <gi...@apache.org>]

flrn77 commented on issue #24526:
URL: https://github.com/apache/airflow/issues/24526#issuecomment-1179043246

   Hello,
   Since I've better understanding on migration procees by had a deeper look into it ;) I could fix the migration manually.
   @dstandish It was all about the charset and at one point I had remove duplicates from xcom. Don't know, guess due to failed migration run. @potiuk I've taken your offer and put into what I think could help user on it as pull to the docs.
   
   @miimsam have a look at my modified sql dry-run code and into my [pull request 24926](https://github.com/apache/airflow/pull/24926) you should ba able fix it manual.
   
   ```sql
   UPDATE alembic_version SET version_num='e655c0453f75' WHERE alembic_version.version_num = 'f9da662e7089';
   
   -- Running upgrade e655c0453f75 -> a3bcd0914482
   
   ALTER TABLE serialized_dag MODIFY data JSON NULL;
   
   ALTER TABLE serialized_dag ADD COLUMN data_compressed BLOB;
   
   UPDATE alembic_version SET version_num='a3bcd0914482' WHERE alembic_version.version_num = 'e655c0453f75';
   
   -- Running upgrade a3bcd0914482 -> c306b5b5ae4a
   
   CREATE TABLE __airflow_tmp_xcom (
       dag_run_id INTEGER NOT NULL,
       task_id VARCHAR(250) COLLATE utf8mb3_bin NOT NULL,
       `key` VARCHAR(512) COLLATE utf8mb3_bin NOT NULL,
       value BLOB,
       timestamp TIMESTAMP(6) NOT NULL,
       dag_id VARCHAR(250) COLLATE utf8mb3_bin NOT NULL,
       run_id VARCHAR(250) COLLATE utf8mb3_bin NOT NULL,
       map_index INTEGER NOT NULL DEFAULT '-1'
   );
   
   /* it took 5min */
   INSERT INTO __airflow_tmp_xcom SELECT dag_run.id, xcom.task_id, xcom.`key`, xcom.value, xcom.timestamp, xcom.dag_id, dag_run.run_id, -1
   FROM xcom INNER JOIN dag_run ON xcom.dag_id = dag_run.dag_id AND xcom.execution_date = dag_run.execution_date;
   
   DROP TABLE xcom;
   
   ALTER TABLE __airflow_tmp_xcom RENAME TO xcom;
   
   /* don't know why but had to delete duplicates */
   create table tmp_xcom as select * from xcom limit 1;
   truncate table tmp_xcom;
   ALTER TABLE tmp_xcom ADD CONSTRAINT xcom_pkey PRIMARY KEY (dag_run_id, task_id, map_index, `key`);
   INSERT IGNORE INTO tmp_xcom SELECT * FROM xcom;
   DROP TABLE xcom;
   RENAME TABLE tmp_xcom TO xcom;
   
   /* done on tmp_xcom before renaming */
   -- ALTER TABLE xcom ADD CONSTRAINT xcom_pkey PRIMARY KEY (dag_run_id, task_id, map_index, `key`);
   
   CREATE INDEX idx_xcom_key ON xcom (`key`);
   
   ALTER TABLE xcom ADD CONSTRAINT xcom_task_instance_fkey FOREIGN KEY(dag_id, task_id, run_id, map_index) REFERENCES task_instance (dag_id, task_id, run_id, map_index) ON DELETE CASCADE;
   
   UPDATE alembic_version SET version_num='c306b5b5ae4a' WHERE alembic_version.version_num = 'a3bcd0914482';
   
   -- Running upgrade c306b5b5ae4a -> c97c2ab6aa23
   
   CREATE TABLE callback_request (
       id INTEGER NOT NULL AUTO_INCREMENT,
       created_at TIMESTAMP(6) NOT NULL,
       priority_weight INTEGER NOT NULL,
       callback_data JSON NOT NULL,
       callback_type VARCHAR(20) NOT NULL,
       dag_directory VARCHAR(1000),
       PRIMARY KEY (id)
   );
   
   UPDATE alembic_version SET version_num='c97c2ab6aa23' WHERE alembic_version.version_num = 'c306b5b5ae4a';
   
   -- Running upgrade c97c2ab6aa23 -> 4eaab2fe6582
   
   ALTER TABLE rendered_task_instance_fields ADD COLUMN map_index INTEGER NOT NULL DEFAULT '-1';
   
   ALTER TABLE rendered_task_instance_fields ADD COLUMN run_id VARCHAR(250) COLLATE utf8mb3_bin;
   
   UPDATE rendered_task_instance_fields, dag_run SET rendered_task_instance_fields.run_id=dag_run.run_id WHERE dag_run.dag_id = rendered_task_instance_fields.dag_id AND dag_run.execution_date = rendered_task_instance_fields.execution_date;
   
   ALTER TABLE rendered_task_instance_fields DROP PRIMARY KEY;
   
   ALTER TABLE rendered_task_instance_fields MODIFY run_id VARCHAR(250) COLLATE utf8mb3_bin NOT NULL;
   
   ALTER TABLE rendered_task_instance_fields DROP COLUMN execution_date;
   
   ALTER TABLE rendered_task_instance_fields ADD CONSTRAINT rendered_task_instance_fields_pkey PRIMARY KEY (dag_id, task_id, run_id, map_index);
   
   
   
   /* change cahrset caused by following error*/
   ALTER TABLE rendered_task_instance_fields MODIFY task_id VARCHAR(255) CHARACTER SET utf8 COLLATE utf8mb3_bin;
   ALTER TABLE rendered_task_instance_fields MODIFY dag_id VARCHAR(255) CHARACTER SET utf8 COLLATE utf8mb3_bin;
   /* [Code: 3780, SQL State: HY000]  Referencing column 'dag_id' and referenced column 'dag_id' in foreign key constraint 'rtif_ti_fkey' are incompatible. */
   ALTER TABLE rendered_task_instance_fields ADD CONSTRAINT rtif_ti_fkey FOREIGN KEY(dag_id, task_id, run_id, map_index) REFERENCES task_instance (dag_id, task_id, run_id, map_index) ON DELETE CASCADE;
   
   UPDATE alembic_version SET version_num='4eaab2fe6582' WHERE alembic_version.version_num = 'c97c2ab6aa23';
   
   -- Running upgrade 4eaab2fe6582 -> 48925b2719cb
   
   DROP INDEX idx_task_fail_dag_task_date ON task_fail;
   
   ALTER TABLE task_fail ADD COLUMN map_index INTEGER NOT NULL DEFAULT '-1';
   
   ALTER TABLE task_fail ADD COLUMN run_id VARCHAR(250) COLLATE utf8mb3_bin;
   
   UPDATE task_fail, dag_run SET task_fail.run_id=dag_run.run_id WHERE dag_run.dag_id = task_fail.dag_id AND dag_run.execution_date = task_fail.execution_date;
   
   ALTER TABLE task_fail MODIFY run_id VARCHAR(250) COLLATE utf8mb3_bin NOT NULL;
   
   ALTER TABLE task_fail DROP COLUMN execution_date;
   
   /* change cahrset caused by following error*/
   ALTER TABLE task_fail MODIFY task_id VARCHAR(255) CHARACTER SET utf8 COLLATE utf8mb3_bin;
   ALTER TABLE task_fail MODIFY dag_id VARCHAR(255) CHARACTER SET utf8 COLLATE utf8mb3_bin;
   
   ALTER TABLE task_fail ADD CONSTRAINT task_fail_ti_fkey FOREIGN KEY(dag_id, task_id, run_id, map_index) REFERENCES task_instance (dag_id, task_id, run_id, map_index) ON DELETE CASCADE;
   
   UPDATE alembic_version SET version_num='48925b2719cb' WHERE alembic_version.version_num = '4eaab2fe6582';
   
   -- Running upgrade 48925b2719cb -> 909884dea523
   
   ALTER TABLE ab_register_user MODIFY username VARCHAR(256) NOT NULL;
   
   ALTER TABLE ab_register_user MODIFY email VARCHAR(256) NOT NULL;
   
   ALTER TABLE ab_user MODIFY username VARCHAR(256) NOT NULL;
   
   ALTER TABLE ab_user MODIFY email VARCHAR(256) NOT NULL;
   
   UPDATE alembic_version SET version_num='909884dea523' WHERE alembic_version.version_num = '48925b2719cb';
   
   -- Running upgrade 909884dea523 -> 75d5ed6c2b43
   
   ALTER TABLE log ADD COLUMN map_index INTEGER;
   
   UPDATE alembic_version SET version_num='75d5ed6c2b43' WHERE alembic_version.version_num = '909884dea523';
   
   -- Running upgrade 75d5ed6c2b43 -> b1b348e02d07
   
   UPDATE dag SET default_view='grid' WHERE dag.default_view = 'tree';
   
   UPDATE alembic_version SET version_num='b1b348e02d07' WHERE alembic_version.version_num = '75d5ed6c2b43';
   
   -- Running upgrade b1b348e02d07 -> 1de7bc13c950
   
   CREATE INDEX idx_log_event ON log (event);
   
   UPDATE alembic_version SET version_num='1de7bc13c950' WHERE alembic_version.version_num = 'b1b348e02d07';
   
   -- Running upgrade 1de7bc13c950 -> 3c94c427fdf6
   
   ALTER TABLE dag_tag DROP FOREIGN KEY dag_tag_ibfk_1;
   
   ALTER TABLE dag_tag ADD CONSTRAINT dag_tag_dag_id_fkey FOREIGN KEY(dag_id) REFERENCES dag (dag_id) ON DELETE CASCADE;
   
   UPDATE alembic_version SET version_num='3c94c427fdf6' WHERE alembic_version.version_num = '1de7bc13c950';
   ```
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org