Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "how to solve mysql Cannot add foreign key constraint errors in the process of mysql upgrade". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Background

What to do if you encounter errors in mysql Cannot add foreign key constraint during the upgrade process?

Where:

Mlflow upgraded from 1.4.0 to 1.11.0

Mysql version of 5.7.21-log

Encountered the following problems:

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

We directly copied the above CREATE TABLE registered_model_tags statement to the client side of mysql for execution, and found that it was also a Cannot add foreign key constraint error.

Continue to execute SHOW ENGINE INNODB STATUS to intercept part of the Status field:

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 thereferenced table where thereferenced columns appear as the first columns, or column typesin the table and thereferenced 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.

You can see that the registered_model_tags foreign key restriction is established: the field type must be the same, but it is found that the field type is the same.

Important: mysql field character set and check set must also be consistent

It turns out that when I created the database, the dataset I chose was utf8, the checkset was utf8_bin, and look at the DDL of table registered_models in our database:

CREATE TABLE `registered_ models` (`name` varchar (256) NOT NULL, `last_updated_ time` bigint (20) DEFAULT NULL, `last_updated_ time` bigint (20) DEFAULT NULL, `substitution` varchar (5000) DEFAULT NULL, PRIMARY KEY (`name`), UNIQUE KEY `name` (`name`) ENGINE=InnoDB DEFAULT CHARSET=utf8

But the table registered_models dataset is also utf8, why there is still a problem? at this time, we have to add a few knowledge points:

1. Inheritance order of CHARSET and COLLATE in mysql if CHARSET and COLLATE are not set at the library level, the default CHARSET and COLLATE at the library level use instance-level settings. If the table level is not set for CHARSET and COLLATE, then the table level inherits CHARSET and COLLATE at the library level. If CHARSET and COLLATE are not set at the column level, the column level inherits CHARSET and COLLATE2 at the table level. CHARSET and COLLATE priority in mysql if the table specifies CHARSET and COLLATE, the CHARSET and COLLATE are adopted. If the table specifies CHARSET but does not specify COLLATE, then COLLATE uses the default COLLATE of CHARSET

Let me see what the default COLLATE for uft8 is in mysql 5.7.21, which executes show collation on the mysql client

You can see that the default is utf8_general_ci

So CHARSET in the registered_models table is utf8,COLLATE and utf8_general_ci

CHARSET and COLLATE are not specified in registered_model_tags, so CHARSET and COLLATE inherited from the database are utf8 and utf8_bin, respectively.

So there will be the above Cannot add foreign key constraint problems.

Solve

It can be solved by changing the COLLATE of the database to utf8_general_ci.

This is the end of the content of "how to solve mysql Cannot add foreign key constraint errors in the process of mysql upgrade". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.