[GitHub] [airflow] jedcunningham edited a comment on issue #18939: Upgrading from version 2.1.4 to 2.2.0 fails during mysql db upgrade with error Can't DROP 'dag_id'

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

jedcunningham edited a comment on issue #18939:
URL: https://github.com/apache/airflow/issues/18939#issuecomment-953395944


   I was able to reproduce this. The following traceback is the original failure:
   
   ```                                                                                                                                                                                     
     File "/Users/jedc/github/airflow/airflow/migrations/versions/7b2661a43ba3_taskinstance_keyed_to_dagrun.py", line 322, in upgrade                                                                                    
       ondelete=ondelete,                                                                                                                                                                                                
   ...                                                              
   sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (3780, "Referencing column 'dag_id' and referenced column 'dag_id' in foreign key constraint 'task_reschedule_ti_fkey' are incompatible.")    
   [SQL: ALTER TABLE task_reschedule ADD CONSTRAINT task_reschedule_ti_fkey FOREIGN KEY(dag_id, task_id, run_id) REFERENCES task_instance (dag_id, task_id, run_id) ON DELETE CASCADE]
   ```
   
   Which is this section:
   https://github.com/apache/airflow/blob/20847fdbf8ecd3be394d24d47ce151c26d018ea1/airflow/migrations/versions/7b2661a43ba3_taskinstance_keyed_to_dagrun.py#L303-L310
   
   This fails because the column in `task_instance` and `task_reschedule` differ:
   ```
   # task_instance
   `dag_id` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
   # task_reschedule
   `dag_id` varchar(250) NOT NULL,
   ```
   
   The traceback in the description then happens on subsequent `airflow db upgrade` runs.
   
   The root cause is incorrect encoding/collation settings. If I follow the documentation for using a [MySQL database with Airflow](https://airflow.apache.org/docs/apache-airflow/stable/howto/set-up-database.html#setting-up-a-mysql-database), everything is happy. If I use the defaults we run into this.


-- 
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