MySQL ERROR 1025 thrown when attempting to change table collation and character set

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

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:

Disable Foreign Key Constraints
SET FOREIGN_KEY_CHECKS=0;
Reattempt to change the collation and character set for a particular table
ALTER TABLE `tableName` CHARACTER SET utf8 COLLATE utf8_bin
Enable back the Foreign Key Constraints
SET FOREIGN_KEY_CHECKS=1;
tip/resting Created with Sketch.

Please do take note to generate a full backup of your Confluence database before then proceed with the suggested workaround as preventive measures.

Last modified on Nov 12, 2018

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.