Archive table does not work

Question

I have a 19GB archive table that is working as expected on one server. I copied the following 2 files to another server. The table is not working on the new server.

r_mi_raw_20120712.ARZ
r_mi_raw_20120712.frm

The first server version is 5.5.22-log MySQL Community Server (GPL) by Remi The second server version is: 5.5.20 MySQL Community Server (GPL)

What can be the reason? When I used mysqlshow db_name it would list the table, but when I use --count I do not get count. Instead I get "Opening tables" state for command Field list.

update:

mysql seems to be writing to the ARN file...

r_mi_raw_20120712.ARN

Is this one time optimization? How do I improve the speed of ARN file creation?

Answer 1

There are a couple of nice links you should check out on the ARCHIVE storage engine

• http://www.mysqlperformanceblog.com/2006/11/12/trying-archive-storage-engine/
• http://datacharmer.blogspot.com/2006/03/mysql-51-improving-archive-performance.html

You may want to think about running OPTIMIZE TABLE on the table before copying it over. I cannot make any promises on an ARCHIVE table be copied to another server. Everybody trusts MyISAM tables to be that portable.

IMHO you are probably better off, definitely safer, doing a mysqldump of the ARCHIVE table and reloading. That way, you could trust mysqld to get the table into a readable, trustworthy format on disk in the new server.