背景
在mlflow upgrade这篇文章中，咱们说到了mlflow 降级的步骤， 很侥幸，零打碎敲, 并没有产生什么谬误，
明天要说的就是降级过程中如果遇到mysql Cannot add foreign key constraint的谬误该怎么解决
其中：
mlflow 从1.4.0降级到1.11.0
mysql版本 5.7.21-log
遇到如下问题：
(mlflow-1.11.0) ➜ mlflow db upgrade mysql://root:root@localhost/mlflow-online2020/11/04 14:20:13 INFO mlflow.store.db.utils: Updating database tablesINFO [alembic.runtime.migration] Context impl MySQLImpl.INFO [alembic.runtime.migration] Will assume non-transactional DDL.INFO [alembic.runtime.migration] Running upgrade 0a8213491aaa -> 728d730b5ebd, add registered model tags tableTraceback (most recent call last): File "/Users/ljh/opt/miniconda3/envs/mlflow-1.11.0/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1246, in _execute_context cursor, statement, parameters, context File "/Users/ljh/opt/miniconda3/envs/mlflow-1.11.0/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 588, in do_execute cursor.execute(statement, parameters) File "/Users/ljh/opt/miniconda3/envs/mlflow-1.11.0/lib/python3.6/site-packages/MySQLdb/cursors.py", line 206, in execute res = self._query(query) File "/Users/ljh/opt/miniconda3/envs/mlflow-1.11.0/lib/python3.6/site-packages/MySQLdb/cursors.py", line 319, in _query db.query(q) File "/Users/ljh/opt/miniconda3/envs/mlflow-1.11.0/lib/python3.6/site-packages/MySQLdb/connections.py", line 259, in query _mysql.connection.query(self, query)MySQLdb._exceptions.IntegrityError: (1215, 'Cannot add foreign key constraint')...sqlalchemy.exc.IntegrityError: (MySQLdb._exceptions.IntegrityError) (1215, 'Cannot add foreign key constraint')[SQL:CREATE TABLE registered_model_tags ( `key` VARCHAR(250) NOT NULL, value VARCHAR(5000), name VARCHAR(256) NOT NULL, CONSTRAINT registered_model_tag_pk PRIMARY KEY (`key`, name), FOREIGN KEY(name) REFERENCES registered_models (name) ON UPDATE cascade)问题剖析
咱们间接把以上的CREATE TABLE registered_model_tags 语句复制到mysql的客户端执行，发现也是Cannot add foreign key constraint谬误，
继续执行SHOW ENGINE INNODB STATUS 截取Status字段中一部分:
2020-11-04 11:34:18 0x700004a77000 Error in foreign key constraint of table mlflow@002donline/registered_model_tags:FOREIGN KEY(name) REFERENCES registered_models (name) ON UPDATE cascade):Cannot find an index in the referenced table where thereferenced columns appear as the first columns, or column typesin the table and the referenced table do not match for constraint.Note that the internal storage type of ENUM and SET changed intables created with >= InnoDB-4.1.12, and such columns in old tablescannot be referenced by such columns in new tables.Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.能够看到是建设registered_model_tags 外键限度：字段的类型必须统一，可是发现字段类型也是一样的，
重点： mysql字段字符集和校验集也必须要统一
原来是我创立数据库的时候抉择的数据集是utf8，校验集是utf8_bin，而看看咱们数据库中表registered_models的DDL：
CREATE TABLE `registered_models` ( `name` varchar(256) NOT NULL, `creation_time` bigint(20) DEFAULT NULL, `last_updated_time` bigint(20) DEFAULT NULL, `description` varchar(5000) DEFAULT NULL, PRIMARY KEY (`name`), UNIQUE KEY `name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8可是表registered_models数据集也是utf8，为啥还会出问题呢，这个时候咱们就得补充几个知识点了：
1. mysql中CHARSET和COLLATE的继承程序如果库级别没有设置CHARSET和COLLATE，则库级别默认的CHARSET和COLLATE应用实例级别的设置如果表级别没有设置CHARSET和COLLATE，则表级别会继承库级别的CHARSET与COLLATE如果列级别没有设置CHARSET和COLLATE，则列级别会继承表级别的CHARSET与COLLATE2. mysql中CHARSET和COLLATE优先级如果表指定了CHARSET和COLLATE，就采纳该CHARSET和COLLATE如果表指定了CHARSET,然而没有指定COLLATE,那么COLLATE采纳CHARSET默认的COLLATE我看看在mysql 5.7.21中uft8默认的COLLATE 是什么， 在mysql客户端执行 show collation;
能够看到默认的是utf8_general_ci
所以registered_models表中 CHARSET 为utf8，COLLATE 为utf8_general_ci
而registered_model_tags中没有指定CHARSET和COLLATE，所以继承自数据库的CHARSET和COLLATE，别离为utf8和utf8_bin
所以就会呈现以上的Cannot add foreign key constraint 问题
解决
把数据库的COLLATE改成utf8_general_ci就能解决