MySQL ERROR 1025 thrown when attempting to change table collation and character set
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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.
*Except Fisheye and Crucible
Problem
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)Diagnosis
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")Cause
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.
Workaround
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.