MySQL – Resolving ‘Temporary File Write Failure’ When Changing Primary Key

errorsMySQLprimary-key

In MySQL, When I trying to change primary key by this query:

 ALTER TABLE `resources` DROP PRIMARY KEY, ADD
 PRIMARY KEY (`role_id`, `category_id`, `lang`, `model`, `rule`);

I execute this query with MySQL root user, but this error occurred:

#1878 - Temporary file write failure.

My table design is:

CREATE TABLE `resources` (
    `role_id` INT(11) UNSIGNED NOT NULL ,
    `category_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT 1 ,
    `lang` BINARY(2) NOT NULL ,
    `model` SMALLINT(5) UNSIGNED NOT NULL ,
    `rule` CHAR(4) NOT NULL ,
    PRIMARY KEY (`category_id`, `lang`, `model`, `rule`, `role_id`)
 )
 ENGINE = InnoDB;

This table have about 2000 rows.

I use MySQL 5.6

Why this error has occurred?

what should I do?

Best Answer

I had the same problem, I did the following:

create table resources_with_key like resources;
alter table resources_with_key add primary key(`category_id`, `lang`, `model`, `rule`, `role_id`);
insert into resources_with_key select * from resources;
Related Question