XF 1.2 "Repair with keycache" on xf_search_index blocking DB restore process

[DeltaHF]

I have a large forum (9.4 million posts) and I'm moving the database to a new server. I backed up the database using mysqldump --opt, then began to restore the .sql file using a plain old mysql db_name < db.sql command.

During the restore, the process seemed to hang (the size of the database in /var/lib/mysql wasn't increasing), and I ran SHOW PROCESSLIST in mysql to see what was going on. It showed a query in the state of "Repair with keycache", with the Info field of:

/*!40000 ALTER TABLE `xf_search_index` ENABLE KEYS */

What should I do next? I'm using ElasticSearch with XenForo, so perhaps there is some way I can avoid this? I found this old post from Kier which seems relevant, but I'm not sure.

 

[MattW]

If you have elasticsearch installed, you can truncate the xf_search_index table as it's not needed.

http://xenforo.com/community/threads/the-xf_search_index-table-can-be-truncated-if-using-xfes.26182/

Can you truncate it on the old server and re-do the export?

 

[DeltaHF]

Perfect! That's exactly what was needed. Thanks so much.

 

[MattW]

Perfect! That's exactly what was needed. Thanks so much.

Glad it's sorted. Should be saving you quite a bit of database space as well with that table now empty.