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.
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:
ALTER TABLE `tableName` CHARACTER SET utf8 COLLATE utf8_bin
Please do take note to generate a full backup of your Confluence database before then proceed with the suggested workaround as preventive measures.