3

I have a particular table that I cannot add a key for:

mysql> ALTER TABLE tasks ADD KEY `fruitful_user_count` (`user_id`, `is_fruitful`);
ERROR 1034 (HY000): Incorrect key file for table 'tasks'; try to repair it

Googling the issue it seems that this problem is often either a configuration issue or a disk space issue. In fact, this database is running on an Amazon RDS instance, which means that it is basically a managed server dedicated to MySQL with a very standard configuration. Also, the disk allocated to us is only about 25% full.

Considering that perhaps there disk on the VM (powered by Xen I believe) is full, and not my allocated disk space which is likely not even in the same room (network storage), I rebooted the RDS instance in the hope that I would get a new instance on another VM. However, that did not help.

What should be my next troubleshooting step?

This is the table:

mysql> show create table tasks;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tasks | CREATE TABLE `tasks` (
`user_id` char(32) NOT NULL,
`module_id` int(11) NOT NULL DEFAULT '0',
`is_successful` tinyint(1) DEFAULT NULL,
`is_fruitful` tinyint(1) DEFAULT NULL,
`last_run` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`last_pulled` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`user_id`,`module_id`),
KEY `phone_scrapes_user` (`user_id`),
KEY `phone_scrapes_module` (`module_id`),
KEY `urgency` (`last_pulled`,`last_run`),
KEY `successful_user_count` (`user_id`,`is_successful`),
KEY `is_successful` (`is_successful`),
KEY `fruitness` (`is_fruitful`,`is_successful`),
CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
CONSTRAINT `tasks_ibfk_2` FOREIGN KEY (`module_id`) REFERENCES `modules` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
  • 1
    FWIW, rebooting an RDS instance shouldn't be expected to move it to new hardware. Another approach would be to take a snapshot of the database, restore to a new instance of the same size, and try the ALTER TABLE there, on the clone, to see what happens. – Michael - sqlbot Dec 9 '14 at 1:42
  • @Michael-sqlbot: Thank you. I understand that rebooting an EC2 instance does move it, so I assumed that is valid for RDS as well. – dotancohen Dec 9 '14 at 9:29
5

Try to repair the table:

mysql> LOCK TABLES `fruitful_user_count` WRITE;
mysql> CREATE TABLE `fruitful_user_count_new` LIKE `fruitful_user_count`;
mysql> INSERT INTO `fruitful_user_count_new` SELECT * FROM `fruitful_user_count`;
mysql> RENAME TABLE `fruitful_user_count` TO `fruitful_user_count_old`;
mysql> RENAME TABLE `fruitful_user_count_new` TO `fruitful_user_count`;
mysql> UNLOCK TABLES;
  • Thank you, in fact I did try this. I could make the new table, but in populating it I get the error he table 'replacement_tasks' is full. – dotancohen Dec 8 '14 at 17:34
  • maybe there is no enough disk space? – akuzminsky Dec 8 '14 at 17:40
  • Yes, I think so too. However the console shows that there is space. I've now filed an issue with Amazon support. – dotancohen Dec 8 '14 at 18:59
  • Sure enough, the issue was disk space. Thanks. – dotancohen Dec 9 '14 at 19:20
4

Suggested solutions:

  1. Execute the following command REPAIR TABLE table_name;
  2. If that (step one) doesn't help try to change the temp directory in the cnf file to new location with bigger space;
  3. If step one and two did not help you need to restore the database to previous stage.
  • Thank you. When I try to repair the table I get the message The storage engine for the table doesn't support repair. And since this is a managed server in RDS, I cannot access the configuration file. – dotancohen Dec 8 '14 at 16:10
  • in-fact even for innodb you need cnf file and add the following line 'innodb_force_recovery = 4' if you can not access the cnf file can you please ask Amazon support to add the line for you, otherwise I guess you need to restore the database – Ahmad Abuhasna Dec 8 '14 at 16:36
1

When we are doing Alter on innodb the "tmpdir" need to be observed. The Erro 1034 : Key repair is definetly because of "tmpdir" full, and your alter will throw the error. So I suggest when the Alter is got triggered give one more eye on your "tmpdir" utilization. If tmp is using 100% there you can see this error for sure :).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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