mysql?升級過程中的mysql Cannot add foreign key constraint錯誤怎么解決

本篇內(nèi)容介紹了“mysql升級過程中的mysql Cannot add foreign key constraint錯誤怎么解決”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

背景

升級過程中如果遇到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 語句復(fù)制到mysql的客戶端執(zhí)行，發(fā)現(xiàn)也是Cannot add foreign key constraint錯誤，
繼續(xù)執(zhí)行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 外鍵限制：字段的類型必須一致，可是發(fā)現(xiàn)字段類型也是一樣的，
重點： mysql字段字符集和校驗集也必須要一致

原來是我創(chuàng)建數(shù)據(jù)庫的時候選擇的數(shù)據(jù)集是utf8，校驗集是utf8_bin，而看看我們數(shù)據(jù)庫中表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數(shù)據(jù)集也是utf8，為啥還會出問題呢，這個時候我們就得補(bǔ)充幾個知識點了：

1. mysql中CHARSET和COLLATE的繼承順序
如果庫級別沒有設(shè)置CHARSET和COLLATE，則庫級別默認(rèn)的CHARSET和COLLATE使用實例級別的設(shè)置
如果表級別沒有設(shè)置CHARSET和COLLATE，則表級別會繼承庫級別的CHARSET與COLLATE
如果列級別沒有設(shè)置CHARSET和COLLATE，則列級別會繼承表級別的CHARSET與COLLATE
2. mysql中CHARSET和COLLATE優(yōu)先級
如果表指定了CHARSET和COLLATE，就采用該CHARSET和COLLATE
如果表指定了CHARSET,但是沒有指定COLLATE,那么COLLATE采用CHARSET默認(rèn)的COLLATE

我看看在mysql 5.7.21中uft8默認(rèn)的COLLATE 是什么， 在mysql客戶端執(zhí)行 show collation;

可以看到默認(rèn)的是utf8_general_ci
所以registered_models表中 CHARSET 為utf8，COLLATE 為utf8_general_ci
而registered_model_tags中沒有指定CHARSET和COLLATE，所以繼承自數(shù)據(jù)庫的CHARSET和COLLATE，分別為utf8和utf8_bin

所以就會出現(xiàn)以上的Cannot add foreign key constraint 問題

解決

把數(shù)據(jù)庫的COLLATE改成utf8_general_ci就能解決

“mysql升級過程中的mysql Cannot add foreign key constraint錯誤怎么解決”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！