3

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?

7
  • 1
    You need to be more specific than that. Are you changing values in a primary key, altering the column(s) used by the key, changing to a different candidate key, ...? Also how large is the target table? You should add to your question the current design of the table and its indexes/keys, the current size of the table, and the SQL you ran to make the change. Changing a column's properties on a large table might involve spooling a lot of data to disk so it could simply be a space issue. Jun 24, 2014 at 8:36
  • @DavidSpillett or maybe a access issue (mysql user not having access to temp dir?) Jun 24, 2014 at 8:46
  • @DavidSpillett I edited my question as you said. Jun 24, 2014 at 8:49
  • @ypercube I execute the query by root privilege. Jun 24, 2014 at 8:50
  • @ypercube I mean mysql root user, I use debian 7 wheezy, yes /tmp has enough space, MySQL correctly doing all other read/writes, so the system user (that mysql runs as) has write access to temp directories. Jun 24, 2014 at 9:24

1 Answer 1

1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.