mysql​升级过程中的mysql Cannot add foreign key constraint错误怎么解决

本篇内容介绍了“mysql升级过程中的mysql Cannot add foreign key constraint错误怎么解决”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

背景

升级过程中如果遇到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就能解决

“mysql升级过程中的mysql Cannot add foreign key constraint错误怎么解决”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！