Migrating from Debian OS running Piler 1.2.0 to Ubuntu 18 running Piler 1.3.7

[alu-one]

Hi,

I’ve been running Piler on Debian for about 2 years (the OVA appliance which was downloadable from your site back then). Because of the pending updates etc. I would like to combine it with switching to Ubuntu and upgrading to the latest Piler release.

/var/piler/store is mounted as a samba share. I have enabled SSO and LDAP and use Apache webserver.

What I did so far:

• Installed Ubuntu 18 server
• Installed Piler 1.3.7 deb package with all dependencies according to your install doc
• Mounted /var/piler/store
• Did the manual post installation steps according to your doc
• Switched from MySQL to MariaDB, as the deb package seems to need MariaDB
• Settings in piler.conf and config-site.conf
• Copied piler.key and piler.pem from old system

The system seems to work basically, but does not show emails because I haven’t copied or recreated sphinx db data yet.

Please give me some hints which steps you recommend from this point to fully migrate all data.

• Should I also export the MySQL table from the old system?
• Regarding the indexer, does it also use MySQL or do I have to copy other data?
• Do I have to consider anything else in this scenario, e.g. regarding the “old” migrated store and maybe SQL or sphinx compatibility?

Thank you,

best regards

‌

[Janos SUTO]

Hello, you are almost there. Be sure to copy the sphinx files as they are, and the mysql database too. The sphinx indexer reads new to be indexed emails from the mysql tables. So when both the mysql and sphinx data are copied to the new server, you should be fine.

Note that in the meantime piler supports sphinx version 3. However, the sphinx data files are for sphinx version 2, so be sure to install sphinx 2.2.x to the new server. Later you may upgrade to sphinx version 3, but it requires to rebuild the sphinx database from scratch. I’d do it sometimes later, though it’s not mandatory.

One more thing before copying the sphinx files from old server. Run indexer.delta.sh as user piler to flush sph_index table contents and update delta and dailydelta files on old server, and then copy the sphinx files to new server.

Also I’m not sure from which piler version you upgraded to 1.3.7, you may need to fix the mysql tables. Be sure to check the upgrade docs on the wiki.

Anyway, let me know how it goes.

[Janos SUTO]

No news is good news.

[alu-one]

Hello Janos,

thank you and sorry for the late response, I proceeded with the upgrading process today:

• sphinxsearch package is 2.2.11-2 on my new system, installed via apt
• su piler → /usr/local/libexec/piler/indexer.delta.sh

• SQL export and import (from old 1.2.0 system):

  • mysqldump --routines=true --triggers=true --events=true -p --force --databases piler > /var/piler/store/tmpdb.sql
  • mysql -p piler < /var/piler/store/tmpdb.sql

  • According to SQL hints in your upgrade doc

    • ALTER DATABASE piler CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
    • (Because I use MariaDB, the errors/steps you linked in your doc at “1.3.0 to 1.3.5” part I skipped converting all tables (cols) to utf8mb4 by now. Please tell me if this could cause errors.)

• On the new system, started rc.piler and rc.searchd

• No errors so far
• Logged in as auditor, I do not see any emails

• According to your FAQ, I am running “cd /tmp”, “reindex -a” (as root) at the moment.

  • This step could maybe run for several days. Hoping all emails will return in the web interface after it is finished, could you please tell me if I am on the right way here, or do you see any other possible issues?

Thank you, best regards

[Janos SUTO]

Before reindexing, be sure to verify that

• all sphinx data files are in place and their size is the same as on the legacy server
• both /var/piler/sphinx and its files are owned by piler
• when searchd starts it finds and recognizes the index files. Searchd should print how many documents it has read from each index

‌

‌

[alu-one]

Thank you, with your hint I just found out why the old system did not show emails of the last weeks any more:

/dev/mapper/debian-var 13G 12G 78M 100% /var

“store” subdir is on an SMB share so this one was not affected.

Just to be sure, should I still copy /var/piler/sphinx to the new server, or are those files fully recreated anyway by running

• reindex -a -p (as root)
• (Check /var/piler and subdirs owner permissions)
• the piler cron scripts (indexer.delta.sh, indexer.main.sh, indexer.attachment.sh, …) manually once, as user piler?

Thank you

‌

[Janos SUTO]

The index files are fully recreated by reindexing. Note that I don’t recommend running indexer.attachment.sh. The sql query is not efficient, and the more rows you have in the v_messages view, the slower it gets.

[alu-one]

The indexing has finished, I ran indexer.delta.sh and indexer.main.sh afterwards.

My current result is, as auditor, I see about 2 million mails, that might be okay I think.

But as logged-in user, I only see 2 single emails, e.g. when doing a blank search. Those 2 mails came today (after the reindex, so they were not affected by it).

At the moment I am re-running reindex -a, this time as user piler (I did it as root before, because piler could not access /etc/piler/piler.conf; did a “chown piler” now).

Can this be the final solution, or could there be another cause?

(Maybe regarding the DB table charset… using MariaDB, do I have to convert all tables to utf8mb4_unicode_ci? They are utf8_general_ci at the moment)

Number of table entries:

• rcpt: ~2.8 mio
• metadata: ~1.9 mio
• attachment: ~1.7 mio
• audit: ~24.000
• other tables: less than 100 or 0

/var/log/mail.log, the query which only returns 2 messages (shortened and domain replaced):

sphinx query: 'SELECT id FROM main1,dailydelta1,delta1 WHERE MATCH(' (@from allXmydomainXat| dXmynameXmyXdomainXat| faxmasterXmyXdomainXat| webmasterXmyXdomainXat| webmasterXmydomainXat| rootXmydomainXat| rootXmyXdomainXat| dsXmyXdomainXat) ') ORDER BY sent DESC LIMIT 0,20 OPTION max_matches=1000000' in 0.04 s, 2 hits, 2 total found

‌

[Janos SUTO]

Get a message for this user from the metadata table, and record the ‘id’ column. Then connect to sphinx, and check if this id (eg. 123) is in the index. Ie.

mysql -h 127.0.0.1 -P9306

select * from main1,dailydelta1,delta1 where id=123;

‌

‌

‌

[alu-one]

It seems to be there:

MySQL [(none)]> select * from main1,dailydelta1,delta1 where id = 314203;
+--------+------------+------+-------------+
| id | sent | size | attachments |
+--------+------------+------+-------------+
| 314203 | 1337004641 | 0 | 0 |
+--------+------------+------+-------------+
1 row in set (0.00 sec)

(The size value in metadata table is “17978”.)

I am not familiar with the sphinx SQL syntax but also tried e.g.

SELECT id FROM main1,dailydelta1,delta1 WHERE MATCH(' (@from myXnameXmyXdomainXat) ') ORDER BY sent DESC LIMIT 0,20 OPTION max_matches=1000000

This delivers an empty set…

‌

Edit:

Tried a message of today, which is shown in the user’s piler web interface, the size is correct:

MySQL [(none)]> select * from main1,dailydelta1,delta1 where id = 2104391;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: *** NONE ***

+---------+------------+-------+-------------+
| id | sent | size | attachments |
+---------+------------+-------+-------------+
| 2104391 | 1588190999 | 16480 | 0 |
+---------+------------+-------+-------------+
1 row in set (0.00 sec)

But when I click the message within the web interface to open it, an error appears, “message verification failed” (German: “Nachrichtenverifizierung fehlgeschlagen”).

‌

Edit 2:

Re-Reindexing has finished now, but result is the same: Logged in as user in the web interface, only new mails are visible.

Local auditor user has about 2.4 mio emails now, but every email shows the note “message verification failed”.

Please tell me some approaches which I could try next, thank you.

‌

Edit 3:

Message verification issue is solved (for new mails, not for old mails); according to issue #971, I added to config-site.php:

$config['DECRYPT_BINARY'] = '/usr/bin/pilerget';
\$config['DECRYPT_ATTACHMENT_BINARY'] = '/usr/bin/pilerget';

I still do not see the older (reindexed) email as logged-in user; I guess my next try will be deleting sphinx dir and truncating some mysql tables, then another reindexing… please tell me if you have other ideas.

‌

Edit 4:

A final test reindexing of a single old and new message shows this result:

MySQL [(none)]> select * from main1,dailydelta1,delta1;
+---------+------------+------+-------------+
| id | sent | size | attachments |
+---------+------------+------+-------------+
| 314203 | 1337004641 | 0 | 0 |
| 2105604 | 1588315768 | 6570 | 0 |
+---------+------------+------+-------------+

I cannot download the old message via web interface (as auditor), I do not see it as the user who owns the email, and pilerexport produces a 0 KB .eml file.

So I am giving up on the migration process.

As pilerexport works on the old system, I will export all old emails from the old system, export the newly incoming emails from the new system and import all to a fresh appliance.

Anyway, thanks jsuto for your advices.