I recently moved my MySQL database from a failing server to a new one. To move, I used rsync to copy everything from /var/lib/mysql/database_name to the same location in the new server, then restarted MySQL.

A few days later, I was making a change to a row using phpMyAdmin, and received the error in the subject:

Incorrect key file for table '****.MYI'; try to repair it

I was able to fix it using REPAIR table table_name QUICK, no problem.

But today, working on a different table, I get the same error. I'm about to use REPAIR on this table now.

The question, though, is why am I suddenly getting errors? Is this a symptom of a larger problem?

It may be worth noting that the old server ran MySQL v. 5.0.92, and the new server runs 5.1.56.

On 10/23/2011 8:15 PM, Jason C wrote:
It's a symptom of screwing up when moving your databases to a new
server. You should have dumped your database on the old one then
restored them to the new one, especially when changing releases.

You *may* get it to work if you stop MySQL and copy the files IF your
old and new system are the same level. But what you did is not supported.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On Sunday, October 23, 2011 9:38:40 PM UTC-4, Jerry Stuckle wrote:
I had actually read to do that here:

http://dev.mysql.com/doc/refman/5.0/...databases.html

After re-reading, though, I guess that I took for granted that the two different versions of MySQL would be considered the same architecture "that support the same floating-point format".

Will a REPAIR fix the problem by rebuilding the index, or should I expect more problems to pop up over time?

On 10/23/2011 9:59 PM, Jason C wrote:
Unknown. It depends on whether you have other problems in the database
or not.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Jason C <jwcarlton (AT) gmail (DOT) com> wrote:

Did you stop the source MySQL server before copying? You cannot
reliably copy data files while MySQL is running.

This could be another explanation. Have you run mysql_upgrade?
Did you RTFM? Why not?


XL

Jason C wrote:

I would suggest that if you are transferring from version 5.0 to 5.1, you
should read the manual for version 5.1 (as well), not (only) for version
5.0.

The term "architecture" there does not refer to the MySQL version or MySQL
server architecture, but most likely to the computer and therefore kernel
architecture of the server system (hence the reference to floating-point
values, and to automatically swapped bytes). For example, Intel 686 (i686)
is one computer architecture, Sun SPARC (sparc) is another.

<http://en.wikipedia.org/wiki/Computer_architecture>

<http://dev.mysql.com/doc/refman/5.1/en/repair-table.html>

In essence: Probably not, yes.

--
PointedEars

Please do not Cc: me. / Bitte keine Kopien per E-Mail.

Did you run mysql_upgrade after starting the new server? This is a required
part of the upgrade process.

I recommend that you shut off any clients accessing the database, then run
mysqlcheck -r --all-databases
(and without --quick). Something bad happened when you transferred
the files, either because the server was up and making changes at
the time while the copy was going on, or because of the version
change. No point in discovering problems with each table individually
one at a time.

If, after doing this, you still need table repairs occasionally, something
is badly wrong.