piler on Debian 13 (trixie)

[Mikmu]

Hi everybody,

I run piler for me and my family since years and it's great. But i have to upgrade the underlying OS from debian 12 to 13, which means there are new libs piler don't know and other libs no longer exists. So libzip has changed from version 4 to 5 and as a result, installing

apt install ./piler_1.4.8-noble-2e863b0_amd64.deb

results in

piler: error while loading shared libraries: libzip.so.4: cannot open shared object file: No such file or directory

In Debian 13, libzip4 no longer exists. I'm not the coding guy and I'm not firm enough to run my one make piler - so I need your help!

Cu
Mikmu

[jsuto]

Are you familiar with docker?

[Mikmu]

Uhh...yes...no...a bit. I mean, yes, apt install docker.io is not too hard, isn't it? But I have to manage my old data to work with the docker image, right?

[larsen0815]

Maybe it would be better to setup a new Ubuntu server and migrate the data. As the deb package is named after an Ubuntu release, this probably should be fully compatible.

[Mikmu]

Yes, maybe. But why should I change my distro just because of one app? That's an work-around like docker. My goal is to run piler 1.4.x native unter Debian 13 :) And it's a long and rainy weekend here in Germany....

[jsuto]

Well, usually an email archive is a dedicated host to offer more benefits. Anyway, you are free to run Debian, but in that case you need to compile piler.

[Mikmu]

Good point - you're right. So, which files/dirs do I have to copy to runter under Ubuntu24?

/var/piler
/var/lib/mysql
/etc/piler

Anything else?

[jsuto]

That should do it.

[Mikmu]

Done, BUT....it doesn't work. I get Error: SQLSTATE[HY000] [2002] Connection refused on database: Manticore. in the web gui while mariadb/piler/pilersearch are running. It seems manticore will login into mysql on port 9306 without credentials. Any suggestion where to look at?

Oh, one important info: coming from 1.4.4, now 1.4.8 - I'll check the changelog, perhaps there is a gap in my config.

[jsuto]

Check if manticore config has port 9307 readonly mysql port.

[Mikmu]

Yes, that's it. Upgradeing from 1.4.4 to 1.4.8 was not as easy as I thought. I can logon, but find no mails. syslog sais

piler-webui[16956]: ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 table 'audit1' absent
piler-webui[16956]: ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 unknown local table(s) 'piler1' in search request

I read https://www.mailpiler.org/upgrade-instructions/ concerning the change from 1.4.5 to 1.4.6, changed /etc/piler/config-site.php but even with restart of pilersearch, the index won't created and the gui fails. New hints?

[Mikmu]

I still need help here concerning the upgrade from 1.4.4 to 1.4.8 with the two indices. Any idea, please?

[jsuto]

Do you have local indexes or real time (rt) indexes? How many emails do you have?

[Mikmu]

I'm using define('RT', 0); in manticore.conf and have

received messages : 137,813
duplicated messages : 49,257

My oldest mail is from 2018 :-)

[jsuto]

OK, got it. For the audit1 missing index, update manticore.conf (based on the manticore.conf.dist file). For piler1 unknown local index, set the following in config-site.php:

$config['RT'] = 0;
$config['SPHINX_MAIN_INDEX'] = 'main1,dailydelta1,delta1';

[Mikmu]

Great xmas gift, thanks. manticore.conf was already fine, but these two lines in config-site.php were missing. Thank you very much for your support here and with piler!

[Mikmu]

Damn....too eary. Everythning is fine, all cron jobs run without error, but on receiving a mail, piler throws an error I don't unterstand:

Dec 25 06:24:03 piler piler-smtp[816]: connected from xxxxxxx:49632 on fd=6 (active connections: 1)
Dec 25 06:24:03 piler piler-smtp[816]: received: TXX7QX13HEHMM2DY, from=mikmu@..., size=10070, client=xxxxxxx, fd=6, fsync=2110
Dec 25 06:24:03 piler piler-smtp[816]: disconnected from xxxxxxx on fd=6, slot=0, reason=done (0 active connections)
Dec 25 06:24:04 piler piler[1237]: ERROR: 50000000694cca7e1f6ab3a4006624ba40b2 failed to store index data for id=145166, errno=1064, append ret=0
Dec 25 06:24:04 piler piler[1237]: ERROR: 50000000694cca7e1f6ab3a4006624ba40b2: rollback sql stmt=DELETE FROM sph_index WHERE id=145166
Dec 25 06:24:04 piler piler[1237]: ERROR: 50000000694cca7e1f6ab3a4006624ba40b2: rollback sql stmt=DELETE FROM rcpt WHERE id=145166
Dec 25 06:24:04 piler piler[1237]: ERROR: 50000000694cca7e1f6ab3a4006624ba40b2: rollback sql stmt=DELETE FROM metadata WHERE id=145166
Dec 25 06:24:04 piler piler[1237]: ERROR: 50000000694cca7e1f6ab3a4006624ba40b2: rollback sql stmt=DELETE FROM attachment WHERE piler_id='50000000694cca7e1f6ab3a4006624ba40b2'
Dec 25 06:24:04 piler piler[1237]: 0/TXX7QX13HEHMM2DY: moved to /var/piler/error/TXX7QX13HEHMM2DY
Dec 25 06:24:04 piler piler[1237]: 0/TXX7QX13HEHMM2DY: 50000000694cca7e1f6ab3a4006624ba40b2, size=10070/5120, attachments=1, reference=, message-id=<2111aa96-de99-4c81-9b65-a525b360e47c@mucko.de>, retention=2557, folder=0, delay=0.0402, status=error

I realy understand the sentence "never change a running system"....

[jsuto]

This is the problem:

Dec 25 06:24:04 piler piler[1237]: ERROR: 50000000694cca7e1f6ab3a4006624ba40b2 failed to store index data for id=145166, errno=1064, append ret=0

The piler daemon cannot insert to the sph_index table for some reason. (Assuming you have rtindex=0 in piler.conf). Check if the table actually exists in the piler database.

[Mikmu]

The index appears to be missing. Therefor, I tried another aproach with a tabula-rasa installation from scratch on a brand new Ubuntu 24.04 LTS, all from your cookbook from https://www.mailpiler.org/documentation/installation except one thing: after dpkg -i, I removed the key because the postinstall.sh does not create all DB structures as long as the new but unused key is there.

On my running installation of piler, I exported all mails ( piler@piler> pilerexport -A -Z 10000 -z pilerorg.zip ) and on the new instance, I tried to import it (piler@piler2> pilerimport -d . -c /etc/piler/piler.conf).

As seaerch engine, Manticore should be used, so I created additionally a file /etc/piler/MANTICORE.

To say it clear: I did not took any other data from the old site, no config, no any other thing. I adapted the config files from scratch unter /etc/piler, started all the tings (piler, pileasearch, piler-smtp) and run into the same error as stated above:

 ERROR: 5000000069528fb4285b0824001ad7c2ac0b failed to store index data for id=304, errno=1064, append ret=0
 ERROR: 5000000069528fb4285b0824001ad7c2ac0b: rollback sql stmt=DELETE FROM sph_index WHERE id=304
 ERROR: 5000000069528fb4285b0824001ad7c2ac0b: rollback sql stmt=DELETE FROM rcpt WHERE id=304
 ERROR: 5000000069528fb4285b0824001ad7c2ac0b: rollback sql stmt=DELETE FROM metadata WHERE id=304
 ERROR: 5000000069528fb4285b0824001ad7c2ac0b: rollback sql stmt=DELETE FROM attachment WHERE piler_id='50000

So I assume, either a mandatory info is missing in den default config files or I forgot to mention any important change I did not do.

Since it seems to be important, here another info:

piler@piler2> grep -r -i rtindex /etc/piler/*
piler.conf:rtindex=1
piler.conf.dist:rtindex=1

Cu
Mikmu

[jsuto]

OK, we are back to square 1. Execute /etc/piler/manticore.conf and show me its output.
Also show grep sphx /etc/piler/piler.conf

[Mikmu]

Happy new year to all of you!

grep is

sphxdb=piler1
sphxhost=127.0.0.1
sphxport=9306

but the thing with "execute the config"...well how?

[jsuto]

Just like you execute a binary like /path/to/aaa, ie. /etc/piler/manticore.conf

Also check if there's anything on 127.0.0.1:9306, and if so, then if it has an index called "piler1"

[Mikmu]

Her comes the manticore.conf:

#
# minimal manticore configuration suited to piler
#


source base
{
   type = mysql
   sql_host = localhost
   sql_db = piler
   sql_user = piler
   sql_pass = secret

   sql_attr_uint = size
   sql_attr_uint = sent
   sql_attr_uint = attachments
}

source delta : base
{
   sql_query_pre = SET NAMES utf8mb4
   sql_query_pre  = REPLACE INTO sph_counter SELECT 1, IFNULL(MAX(id), 0) FROM sph_index
   sql_query_post_index  = DELETE FROM sph_index WHERE id<=(SELECT max_doc_id FROM sph_counter WHERE counter_id=1)
   sql_query = SELECT id, `from` as sender, `to` as rcpt, `fromdomain` as senderdomain, `todomain` as rcptdomain, `subject`, `arrived`, `sent`, `body`, `size`, `direction`, `folder`, `attachments`, `attachment_types` FROM sph_index WHERE id <= (SELECT max_doc_id FROM sph_counter WHERE counter_id=1)

   sql_query_killlist = SELECT `id` FROM `metadata` WHERE `deleted`=1
}

source main1 : base
{
   sql_query_pre = SET NAMES utf8mb4
   sql_query = SELECT id, `from` as sender, `to` as rcpt, `fromdomain` as senderdomain, `todomain` as rcptdomain, `subject`, `arrived`, `sent`, `body`, `size`, `direction`, `folder`, `attachments`, `attachment_types` FROM sph_index WHERE id=-1
}

source main2 : base
{
   sql_query_pre = SET NAMES utf8mb4
   sql_query = SELECT id, `from` as sender, `to` as rcpt, `fromdomain` as senderdomain, `todomain` as rcptdomain, `subject`, `arrived`, `sent`, `body`, `size`, `direction`, `folder`, `attachments`, `attachment_types` FROM sph_index WHERE id=-1
}

source main3 : base
{
   sql_query_pre = SET NAMES utf8mb4
   sql_query = SELECT id, `from` as sender, `to` as rcpt, `fromdomain` as senderdomain, `todomain` as rcptdomain, `subject`, `arrived`, `sent`, `body`, `size`, `direction`, `folder`, `attachments`, `attachment_types` FROM sph_index WHERE id=-1
}

source main4 : base
{
   sql_query_pre = SET NAMES utf8mb4
   sql_query = SELECT id, `from` as sender, `to` as rcpt, `fromdomain` as senderdomain, `todomain` as rcptdomain, `subject`, `arrived`, `sent`, `body`, `size`, `direction`, `folder`, `attachments`, `attachment_types` FROM sph_index WHERE id=-1
}

source dailydelta : base
{
   sql_query_pre = SET NAMES utf8mb4
   sql_query = SELECT id, `from` as sender, `to` as rcpt, `fromdomain` as senderdomain, `todomain` as rcptdomain, `subject`, `arrived`, `sent`, `body`, `size`, `direction`, `folder`, `attachments`, `attachment_types` FROM sph_index WHERE id=-1
}

index main1
{
        source                  = main1
        path                    = /var/piler/manticore/main1
        min_prefix_len          = 5
        min_word_len            = 1
        stored_fields           =
        charset_table           = 0..9, english, _, \
                                  U+C1->U+E1, U+C4->U+E4, U+C5->U+E5, U+C6->U+E6, U+C9->U+E9, U+CD->U+ED, U+D3->U+F3, U+D6->U+F6, U+D8->U+F8, \
                                  U+DA->U+FA, U+DC->U+FC, U+0150->U+0151, U+0152->U+0153, U+0170->U+0171, U+01E2->U+E6, U+01E3->U+E6, U+01FC->U+E6, \
                                  U+01FD->U+E6, U+1D01->U+E6, U+1D02->U+E6, U+1D2D->U+E6, U+1D46->U+E6, \
                                  U+DF, U+E1, U+E4, U+E5, U+E6, U+E9, U+ED, U+00F3, U+F6, U+F8, U+FA, U+FC, U+0151, U+0153, U+0171
        #ngram_len               = 1
        #ngram_chars             = U+3000..U+2FA1F
}

index main2
{
        source                  = main2
        path                    = /var/piler/manticore/main2
        min_prefix_len          = 5
        min_word_len            = 1
        stored_fields           =
        charset_table           = 0..9, english, _, \
                                  U+C1->U+E1, U+C4->U+E4, U+C5->U+E5, U+C6->U+E6, U+C9->U+E9, U+CD->U+ED, U+D3->U+F3, U+D6->U+F6, U+D8->U+F8, \
                                  U+DA->U+FA, U+DC->U+FC, U+0150->U+0151, U+0152->U+0153, U+0170->U+0171, U+01E2->U+E6, U+01E3->U+E6, U+01FC->U+E6, \
                                  U+01FD->U+E6, U+1D01->U+E6, U+1D02->U+E6, U+1D2D->U+E6, U+1D46->U+E6, \
                                  U+DF, U+E1, U+E4, U+E5, U+E6, U+E9, U+ED, U+00F3, U+F6, U+F8, U+FA, U+FC, U+0151, U+0153, U+0171
        #ngram_len               = 1
        #ngram_chars             = U+3000..U+2FA1F
}

index main3
{
        source                  = main3
        path                    = /var/piler/manticore/main3
        min_prefix_len          = 5
        min_word_len            = 1
        stored_fields           =
        charset_table           = 0..9, english, _, \
                                  U+C1->U+E1, U+C4->U+E4, U+C5->U+E5, U+C6->U+E6, U+C9->U+E9, U+CD->U+ED, U+D3->U+F3, U+D6->U+F6, U+D8->U+F8, \
                                  U+DA->U+FA, U+DC->U+FC, U+0150->U+0151, U+0152->U+0153, U+0170->U+0171, U+01E2->U+E6, U+01E3->U+E6, U+01FC->U+E6, \
                                  U+01FD->U+E6, U+1D01->U+E6, U+1D02->U+E6, U+1D2D->U+E6, U+1D46->U+E6, \
                                  U+DF, U+E1, U+E4, U+E5, U+E6, U+E9, U+ED, U+00F3, U+F6, U+F8, U+FA, U+FC, U+0151, U+0153, U+0171
        #ngram_len               = 1
        #ngram_chars             = U+3000..U+2FA1F
}

index main4
{
        source                  = main4
        path                    = /var/piler/manticore/main4
        min_prefix_len          = 5
        min_word_len            = 1
        stored_fields           =
        charset_table           = 0..9, english, _, \
                                  U+C1->U+E1, U+C4->U+E4, U+C5->U+E5, U+C6->U+E6, U+C9->U+E9, U+CD->U+ED, U+D3->U+F3, U+D6->U+F6, U+D8->U+F8, \
                                  U+DA->U+FA, U+DC->U+FC, U+0150->U+0151, U+0152->U+0153, U+0170->U+0171, U+01E2->U+E6, U+01E3->U+E6, U+01FC->U+E6, \
                                  U+01FD->U+E6, U+1D01->U+E6, U+1D02->U+E6, U+1D2D->U+E6, U+1D46->U+E6, \
                                  U+DF, U+E1, U+E4, U+E5, U+E6, U+E9, U+ED, U+00F3, U+F6, U+F8, U+FA, U+FC, U+0151, U+0153, U+0171
        #ngram_len               = 1
        #ngram_chars             = U+3000..U+2FA1F
}

index dailydelta1
{
        source                  = dailydelta
        path                    = /var/piler/manticore/dailydelta1
        min_prefix_len          = 5
        min_word_len            = 1
        stored_fields           =
        charset_table           = 0..9, english, _, \
                                  U+C1->U+E1, U+C4->U+E4, U+C5->U+E5, U+C6->U+E6, U+C9->U+E9, U+CD->U+ED, U+D3->U+F3, U+D6->U+F6, U+D8->U+F8, \
                                  U+DA->U+FA, U+DC->U+FC, U+0150->U+0151, U+0152->U+0153, U+0170->U+0171, U+01E2->U+E6, U+01E3->U+E6, U+01FC->U+E6, \
                                  U+01FD->U+E6, U+1D01->U+E6, U+1D02->U+E6, U+1D2D->U+E6, U+1D46->U+E6, \
                                  U+DF, U+E1, U+E4, U+E5, U+E6, U+E9, U+ED, U+00F3, U+F6, U+F8, U+FA, U+FC, U+0151, U+0153, U+0171
        #ngram_len               = 1
        #ngram_chars             = U+3000..U+2FA1F
}

index delta1
{
        source                  = delta
        path                    = /var/piler/manticore/delta1
        min_prefix_len          = 5
        min_word_len            = 1
        stored_fields           =
        charset_table           = 0..9, english, _, \
                                  U+C1->U+E1, U+C4->U+E4, U+C5->U+E5, U+C6->U+E6, U+C9->U+E9, U+CD->U+ED, U+D3->U+F3, U+D6->U+F6, U+D8->U+F8, \
                                  U+DA->U+FA, U+DC->U+FC, U+0150->U+0151, U+0152->U+0153, U+0170->U+0171, U+01E2->U+E6, U+01E3->U+E6, U+01FC->U+E6, \
                                  U+01FD->U+E6, U+1D01->U+E6, U+1D02->U+E6, U+1D2D->U+E6, U+1D46->U+E6, \
                                  U+DF, U+E1, U+E4, U+E5, U+E6, U+E9, U+ED, U+00F3, U+F6, U+F8, U+FA, U+FC, U+0151, U+0153, U+0171
        killlist_target         = main1:kl, main2:kl, main3:kl, main4:kl, dailydelta1:kl
        #ngram_len               = 1
        #ngram_chars             = U+3000..U+2FA1F
}

source tag : base
{
   sql_query_pre = SET NAMES utf8mb4
   sql_query  = SELECT `_id`, `id` AS iid, `uid`, `tag` FROM `tag`

   sql_attr_uint = iid
   sql_attr_uint = uid
}

source note : base
{
   sql_query_pre = SET NAMES utf8mb4
   sql_query  = SELECT `_id`, `id` AS iid, `uid`, `note` FROM `note`

   sql_attr_uint = iid
   sql_attr_uint = uid
}

index tag1
{
        source                  = tag
        path                    = /var/piler/manticore/tag1
        min_prefix_len          = 5
        min_word_len            = 1
        charset_table           = 0..9, english, _, \
                                  U+C1->U+E1, U+C4->U+E4, U+C5->U+E5, U+C6->U+E6, U+C9->U+E9, U+CD->U+ED, U+D3->U+F3, U+D6->U+F6, U+D8->U+F8, \
                                  U+DA->U+FA, U+DC->U+FC, U+0150->U+0151, U+0152->U+0153, U+0170->U+0171, U+01E2->U+E6, U+01E3->U+E6, U+01FC->U+E6, \
                                  U+01FD->U+E6, U+1D01->U+E6, U+1D02->U+E6, U+1D2D->U+E6, U+1D46->U+E6, \
                                  U+DF, U+E1, U+E4, U+E5, U+E6, U+E9, U+ED, U+00F3, U+F6, U+F8, U+FA, U+FC, U+0151, U+0153, U+0171
        stored_fields           =
        #ngram_len               = 1
        #ngram_chars             = U+3000..U+2FA1F
}


index note1
{
        source                  = note
        path                    = /var/piler/manticore/note1
        min_prefix_len          = 5
        min_word_len            = 1
        charset_table           = 0..9, english, _, \
                                  U+C1->U+E1, U+C4->U+E4, U+C5->U+E5, U+C6->U+E6, U+C9->U+E9, U+CD->U+ED, U+D3->U+F3, U+D6->U+F6, U+D8->U+F8, \
                                  U+DA->U+FA, U+DC->U+FC, U+0150->U+0151, U+0152->U+0153, U+0170->U+0171, U+01E2->U+E6, U+01E3->U+E6, U+01FC->U+E6, \
                                  U+01FD->U+E6, U+1D01->U+E6, U+1D02->U+E6, U+1D2D->U+E6, U+1D46->U+E6, \
                                  U+DF, U+E1, U+E4, U+E5, U+E6, U+E9, U+ED, U+00F3, U+F6, U+F8, U+FA, U+FC, U+0151, U+0153, U+0171
        stored_fields           =
        #ngram_len               = 1
        #ngram_chars             = U+3000..U+2FA1F
}



searchd
{
        listen                  = 127.0.0.1:9312
        listen                  = 127.0.0.1:9306:mysql
        listen                  = 127.0.0.1:9307:mysql_readonly
        log                     = /var/piler/manticore/manticore.log
        binlog_max_log_size     = 256M
        binlog_path             = /var/piler/manticore
        binlog_flush            = 2
        query_log               = /var/piler/manticore/query.log
        network_timeout         = 5
        pid_file                = /var/run/piler/searchd.pid
        seamless_rotate         = 1
        preopen_tables          = 1
        unlink_old              = 1
        thread_stack            = 512k
        # https://manticoresearch.com/blog/manticoresearch-buddy-intro/
        # Give a value to the buddy_path variable to enable manticore-buddy
        # Be sure that user piler has read/write access to it
        buddy_path              =

}

And yes, apparently there is no table called piler and I don't know how to query the index infos since manticore has no information_schema.statistics:

MySQL [manticore]> show tables;
+-------------+-------+
| Table       | Type  |
+-------------+-------+
| dailydelta1 | local |
| delta1      | local |
| main1       | local |
| main2       | local |
| main3       | local |
| main4       | local |
| note1       | local |
| tag1        | local |
+-------------+-------+
8 rows in set (0.000 sec)

Cu
Mikmu

[jsuto]

The problem is that piler.conf is not in sync with manticore.conf. Piler expects real time indexes, however manticore provides the main-delta indexes.

Stop manticore, remove all files from /var/piler/manticore dir, then fix manticore.conf, and set define('RT', 1);, and start it. Then verify that "show tables;" returns "piler1 | rt"

[Mikmu]

Hi there,

now the config is in sync and runs. I exported all messages from the running environment, copied the zip files to the new instance and started importing. Unfortunately, 10% of all messages were rejected. I was busy for a week and would like to export messages from the old environment from january with

pilerexport -c /etc/piler/piler.conf -a 2025.01.01

So, a lot of messages are missing and I'll look into this. At least, this issue may be closed, thank you very much!

Kind regards