MySQL ERROR 1025 thrown when attempting to change table collation and character set
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Fisheye および Crucible は除く
問題
An attempt of repairing collation and character set on table level (MySQL database) resulted in the following errors.
ERROR 1025 (HY000): Error on rename of './confluence/#sql-23f9_59586' to './confluence/logininfo' (errno: 150)診断
Execute the following command against your MySQL database to get more detailed information on the error.
show engine innodb status;You should be seeing an error similar to the one shown below:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
151026 2:07:01 Error in foreign key constraint of table confluence/logininfo:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
CONSTRAINT "FK_logininfo_USERNAME" FOREIGN KEY ("USERNAME") REFERENCES "user_mapping" ("user_key")原因
Referential integrity (foreign key constrains check) failed while attempting to change the table's collation and character set.
For more information, please refer to this link.
回避策
Temporarily disable the foreign key constraints check when altering the table collation by executing the following queries before and after the alter attempt itself:
SET FOREIGN_KEY_CHECKS=0;ALTER TABLE `tableName` CHARACTER SET utf8 COLLATE utf8_binSET FOREIGN_KEY_CHECKS=1;Please do take note to generate a full backup of your Confluence database before then proceed with the suggested workaround as preventive measures.