MySQL problem: "Incorrect key file for table ..." > Mysql 정보

본문 바로가기


MySQL problem: "Incorrect key file for table ..."

검색 이전 다음 목록


I'm trying to migrate a mysql server to new hardware. The old server runs mysql 4.1.12 on RHEL 4; the new server is mysql 5.1.41 on Ubuntu 10.04. I've been having some strange issues trying to migrate one of the databases. I tried using mysqldump, but the file won't import properly into the new server because it complains about duplicate keys on a particular table, even though I've checked and am pretty sure there are in fact no duplicate keys.

Since I spent several days fighting with the mysqldump approach without finding a solution, I decided instead to copy the entire /var/lib/mysql directory from the old server to the new one. This seemed to work well, with mysql starting up without complaining and all of the data seemingly present. However, a Web application that uses one of the databases fails to work, complaining, "Incorrect key file for table 'activities'; try to repair it, line194" (interestingly, the table that I'm having trouble with is not the one that mysqldump was unable to import, although they are both on the same database).

I've run REPAIR TABLE on this table from within the mysql shell, but it fails with the output:

| Table                | Op     | Msg_type | Msg_text                                                   |
| databasename.activities | repair | Error    | Incorrect key file for table 'activities'; try to repair it |
| databasename.activities | repair | error    | Corrupt                                                    |
2 rows in set (0.00 sec)
I also tried myisamchk with the --recover and --safe-recover flags for this table. In both cases, it says it fixed errors, but the Web application still complains about an incorrect key file.

What confuses me most is that (while mysqld is shut down) I've run an md5sum on the MYI file for the table in question, and it's identical on both the old server and the new server. If the table files are identical on the two machines, I'm puzzled as to why the key file is incorrect on one but not the other. I almost suspect this is a bug with mysql, but I'm not positive.

I'd be really grateful for any help or suggestions, as I'm out of ideas on how to solve this. But I'm no mysql guru, so maybe I'm missing something obvious. I would even upload the problematic table to anyone who wanted to take a look.

Last edited by pytheas22; July 28th, 2010 at 10:11 PM..
pytheas22 is offline  Reply With Quote
Old July 29th, 2010  #2
Has an Ubuntu Drip
rubylaser's Avatar
Join Date: Jul 2010
Location: Michigan, USA
Beans: 748
Ubuntu 10.10 Maverick Meerkat
Re: MySQL problem: "Incorrect key file for table ..."

Why don't you upload the table so that we can actually look at, and maybe repair the problem for you. Running REPAIR TABLE 'activities'; Should have fixed it for you, so I'd be interested to see if I can find the problem.

You could try to repair this way...

REPAIR TABLEe activities use_frm;
It's a more forceful way of getting it to repair it.
“Have fun storming the castle!”

Last edited by rubylaser; July 29th, 2010 at 03:22 PM.. Reason: Added more.
rubylaser is offline  Reply With Quote
Old July 30th, 2010  #3
Ubuntu Master Roaster
pytheas22's Avatar
Join Date: Aug 2007
Location: Paris
Beans: 5,377
Ubuntu 11.04 Natty Narwhal
Re: MySQL problem: "Incorrect key file for table ..."

Thanks for your response. I don't want to upload the table publicly because it contains private data, although I appreciate your offer to look at it.

After more investigation, I think at least part of the issue is mysql versions. According to the mysql manual, you can't take databases created on 4.1 and have them work on 5.1. Instead, you're supposed to do incremental upgrades (4.1>5.0>5.1), repairing the tables after each upgrade.

Strangely, I tried that aproach and although many errors with the databases were fixed after each upgrade, there were a few tables that just wouldn't repair, and I couldn't get useful output about what was wrong.

In the end, we decided just to downgrade to mysql 4.1 on the new server (we installed it by using the Dapper repositories). Since the Web application that uses the database whose tables were causing all the problems was written in-house and has no documentation, we're not even sure it would work properly with mysql 5.1, so it seems safer to stick with an older version until someone can sit down and look at the php to figure out what exactly is going on. Under 4.1, mysqlcheck says all the databases are clean, whether we import them with a dump file or by rsyncing over the data files manually.

If I figure out anything else about this I'll post back. Thanks again for your help--I appreciate the response and the tip about the use_frm option (for what it's worth, I think I ran mysqlcheck --repair yesterday with an option that is equivalent to use_frm in the mysql shell, according to the manual and it still didn't work).

등록된 댓글이 없습니다.


Mysql 정보

  • no image
  • [MySQL] ARCHIVE 엔진 및 INNODB 테이블 압축

    MySQL (혹은 MariaDB도 동일)에는 기본적으로 다양한 테이블 엔진이 있습니다.최근에 필요에 따라 ARCHIVE 엔진을 사용해 볼 기회가 있었습니다.이 엔진은 로그 저장과 같이 계속해서 Append 되기만 하…

  • no image
  • [펌] MySQL 운영하면서 겪은 문제점과 해법들

     MySQL 운영하면서 겪은 문제점과 해법들지난 2년간 번개장터 서비스를 운영하면서 MySQL 을 사용하였는데 여러가지 한계에 부딛히면서 전문 업체에게 컨설팅을 받아서 시행착오를 줄이는게 낫겠다는 생각을 했습니다. …

  • LIKE vs INSTR() vs BINARY LIKE  속도 비교
  • LIKE vs INSTR() vs BINARY LIKE 속도 비교

    [MYSQL] LIKE vs INSTR()0. 배경1. 영문 검색어 테스트  1-1. 앞 부분 검색  1-2. 중간 부분 검색  1-3. 끝 부분 검색2. 한글 검색어 테스트  2-1. 앞 부분 검색  2-2. 중간…

  • no image
  • [DBMS] MySQL 의 InnoDB 엔진 의 특성

    다들 한번씩은 들어봄직한 InnoDB 엔진에는 개발시 매우 중요한 부분을 차지할 수도 있을 만한 특성이 있습니다. 이 특성 3가지를 언급해 보고자 합니다. 행여 InnoDB 를 사용하여 개발하시는 분들은 참고하시길 …

  • no image
  • 로그활용4 - slow query log

    로그활용4 - slow query logslow-query-log(--slow-query-log)처리에 시간이 걸린 쿼리를 기록하기 위한 옵션이다.쿼리 실행에 long_query_time에 세팅된 초수(표준 10초)…

  • no image
  • Mysql 기록하기 sql log record log 기록

    5.11.3. 일반 쿼리 로그일반 쿼리 로그는 mysqld의 일반적인 실행 결과를 기록한 것이다. 서버는 클라이언트가 접속을하거나 또는 접속을 끊을 때 정보를 이 로그에 기록하고, 클라이언트에서 받는 각 SQL 명령…