failed to add to sph_index table

[Bernardo Ortega]

When I try to recreate the full indexing by running the reindex -a command, I receive the following error: failed to add to sph_index table. I did the process with the user piler and with root, on both occasions I get the same error.

[Janos SUTO]

This message means that the given message is already in the sph_index table. You may check by running a select command on the table.

[Bernardo Ortega]

But is there any way to regenerate all the indexed ?, the content I have was a recovery from a previous installation where I have more than 600,000 files and the current installation takes only about 2 days running. It could eliminate indexing and reindex the recovered files?

[Janos SUTO]

I don't understand what you are trying to do. Please elaborate why you want to reindex all emails? Is your index data corrupted?

[Bernardo Ortega]

The previous server suffered a failure and lost the database, I could only recover the content of the emails, I could migrate the content of all emails from /var/piler/store/ to the new server on the same route, but the New server does not show the content that I have copied only the emails it has been receiving since installing it. Could it be reset in some way to show the contents of the store folder?

[Janos SUTO]

OK, it's a bit clearer to me now. Piler uses both a mysql database and a sphinx database. Mysql is used to store crucial metadata, while sphinx is used to store index data. Reindex is a tool to regenerate the sphinx data in case it's lots, damaged, etc. There's no tool however to recover the mysql data. So please clarify which is lost: mysql or sphinx or both?

[Bernardo Ortega]

Wao, I was not clear about the operation as such and only retrieve the files of the emails in the indicated route, thinking that the only database engine was MySQL. Sphinx works well in the previous server, because it performs the search until the moment in which the failure occurred, but how would it be able to merge the contents of the current server with the previous one?

[Janos SUTO]

If sphinx database was fine on the other server, and you have the files from /var/piler/sphinx/*, then copy them to new server, and reindex what's missing only.

However, you still haven't clarified if you have the piler mysql database or not. So?

[Bernardo Ortega]

But if I copy the contents of / var / piler / sphinx / * to the new server, what will happen with the indexing of the new server ?, with this I can see all the contents of the recovered plus the new list in the graphical interface?

[Bernardo Ortega]

Sorry, I do not have the MySQL database, I only have the files for the following routes /var/piler/store/ and /var/piler/sphinx/, thank you very much for your help.

[Janos SUTO]

Finally, we've just come to the problem. Well, unfortunately (assuming that you have nothing from /var/lib/mysql directory from the old host), I have bad news for you: the piler mysql database is crucial, the archive won't work without it.

I have no recovery tool for that. However, there's a tech-savvy piler user who has a commercial recovery tool. If you are interested in I may give his contact details (or go for a user called 'extremeshokk' in the piler project). If you won't pay for his tools / services, then your best bet is to get the original emails, then import them to a fresh, empty archive.

[Janos SUTO]

It's not a bug in the software, rather it's a lost database issue.