Ho to recreate piler db from the store after InnoDB crash

Janos SUTO repo owner

What about other tables, eg. rcpt, attachment?

2014-06-10T09:52:41+00:00

Alessandro Apostoli

I tried to force inno_db_recovery = 6 with no success, I get partial dump of metadata table, the dump fails before dumping any other table

2014-06-10T09:57:39+00:00

Janos SUTO repo owner

So we have basically no sql data, right? That's bad. Anyway, such a scan of the store is not entirely impossible, however deduplicated data may have problems, since restoring a deduplicated attachment requires the attachment table without it... well. Give me some time to come up with a salvaging utility, and we will see.

2014-06-10T10:13:42+00:00

Alessandro Apostoli

I managed to dump the attachment table, but rcpt and metadata fail, any other table that could prove useful? thanks a lot anyway mailpiler version is 1.23

2014-06-10T10:18:27+00:00

Janos SUTO repo owner

For retrieving a message you need metadata and attachment tables. rcpt table is used to check user permissions for the given email inside the gui. I'll let you know when the utility is ready to test. I strongly recommend you to rename the current piler database in case if anything goes wrong you'll still have something.

2014-06-10T10:24:06+00:00

Alessandro Apostoli

update: with percona innodb tools I managed to recover the metadata and rcpt tables but not the sph_index table which is unrecoverable. Apparentely I'm missing 6 emails by comparison of the count from the damaged metadata table and the recovered one. With an empty sph_index table should I reindex all or if I start the daemons the indexing simply continues on the new messages? thanks

2014-06-10T15:49:39+00:00

Janos SUTO repo owner

That's a good news. The empty sph_index table is not a problem. Do the following:

#1: get the last message serial id from sphinx

mysql -h 127.0.0.1 -P 9306 select id from main1,delta1 order by sent desc;

#2: then run reindex -f <the_last_id_in_sphinx> -t <the_last_serial_in_metadata>

Btw. can you describe what happened (to ruin the piler database), and how you fixed it? It would be a good entry in the FAQ.

2014-06-10T18:11:12+00:00

Janos SUTO repo owner

Forgot to say that you don't have to reindex all the messages, so don't run "reindex -a".

2014-06-10T18:13:03+00:00

Alessandro Apostoli

now i have a working piler setup but the sphinx indexes have been damaged as well, indexer segfaults. I don't mind rebuilding the indexes but I would like to spread the data among main1 thru main4 indexes. What is the proper procedure to reindex all?

As i understand it should be: * stop daemons * clear sphinx folders * rebuild sphinx folders with indexer --all * optionally empty sph_index table * start daemons * reindex -a * call indexer rotate and merge on main1...main4

As for the FAQ entry the recover procedure is more mysql related than piler related but I can document it no problem

2014-06-11T08:37:08+00:00

Janos SUTO repo owner

It's possible to spread data among main1, ... main4. Make sure you have entries for main1, ... main4 in sphinx.conf. Then stop searchd, remove everything under /var/piler/sphinx, and initialize the sphinx files by running "indexer --all" by user piler.

Spreading data should be as simple as changing main1 to main2, then later from main2 to main3 after reindexing a chunk of data in the crontab of piler. Or even better disable the cron jobs entries until the reindexing is finished. Note that according to sphinx docs (or forum suggestion) you shouldn't have more indices then cores in your machine.

Finally edit config.php, and fix the sphinx index references. I think you have "main1,delta1", change it to "main1,main2,main3,main4,delta1".