关于spark:mlflow-upgrade升级-Cannot-add-foreign-key-constraint

1次阅读

共计 3506 个字符，预计需要花费 9 分钟才能阅读完成。

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

2020/11/04 14:20:13 INFO mlflow.store.db.utils: Updating database tables
INFO  [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 table
Traceback (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 the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot 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 与 COLLATE
2. 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 就能解决

正文完