'Out of range value for column 'retained' at row 1'

[Christian]

piler -v
1.4.3 build 1001

It is not a fresh install and no updates were performed.

But:
Piler does not seem to index any more current e-Mails since 10.01.24.
Error message is (partly changed/redacted):

Jan 28 00:03:56 pilersrv piler-smtp[1229]: connected from 192.168.0.5:38950 on fd=6 (active connections: 1)
Jan 28 00:03:57 pilersrv piler-smtp[1229]: received: SNBC4ZPBCHD7OFKA, from=[e-mail], size=14276, client=192.168.0.5, fd=6, fsync=7318
Jan 28 00:03:57 pilersrv piler-smtp[1229]: disconnected from 192.168.0.5 on fd=6, slot=0, reason=done (0 active connections)
Jan 28 00:03:57 pilersrv piler[1243]: 5000000333b58b222ef4xkkc000e3483a78f: rcpt=[e-mail]
Jan 28 00:03:57 pilersrv piler[1243]: ERROR: 5000000333b58b222ef4xkkc000e3483a78f: mysql_stmt_execute() 'Out of range value for column 'retained' at row 1' (errno: 1264)
Jan 28 00:03:57 pilersrv piler[1243]: ERROR: 5000000333b58b222ef4xkkc000e3483a78f storing metadata, sql_errno=1264
Jan 28 00:03:57 pilersrv piler[1243]: 0/SNBC4ZPBCHD7OFKA: moved to /var/piler/error/SNBC4ZPBCHD7OFKA
Jan 28 00:03:57 pilersrv piler[1243]: 0/SNBC4ZPBCHD7OFKA: 5000000333b58b222ef4xkkc000e3483a78f, size=14276/4512, attachments=0, reference=, message-id=<[e-mail]>, retention=100000, folder=0, delay=0.0143, status=error

cat /var/piler/stat/error 
8453

what can i do to fix this?

[Janos SUTO]

Well, you set 100000 days for retention value which is 273 years. I can see your enthusiasm preserving the emails for a long time. The retained column is an unsigned integer that supports timestamps up to Sun Feb 7 06:28:16 UTC 2106, that’s roughly 80 years. So I’d calculate your retention period not to exceed this upper limit. However, there might be a simpler solution. Set the retention period to something more reasonable, eg. 15000 (=41 years), then restart piler, and you may disable the purging on the gui. Finally run mv /var/piler/error/* /var/piler/tmp/0 to re-process these emails. Anyway, I doubt that today’s emails will be relevant 40 years in the future.

[Christian]

Thank you so much!

I had to use “mv /var/piler/error/* /usr/local/var/piler/tmp/0” since my piler.conf says:

workdir=/usr/local/var/piler/tmp

It is working again, now!

[Janos SUTO]

Great!

[Christian]

Piler now has 2700+ (child) processes running:

ps aux | grep piler | wc -l
2763

● piler.service - Piler daemon
     Loaded: loaded (/lib/systemd/system/piler.service; enabled-runtime; vendor preset: enabled)
     Active: active (running) since Fri 2024-02-02 09:49:30 CET; 1min 13s ago
    Process: 200563 ExecStart=/usr/sbin/piler -d (code=exited, status=0/SUCCESS)
   Main PID: 200564 (piler)
      Tasks: 2751 (limit: 19051)
     Memory: 3.8G
        CPU: 1min 7.615s
     CGroup: /system.slice/piler.service
             ├─  2246 /usr/sbin/piler -d
             ├─  2247 /usr/sbin/piler -d

Could this be the fallout from re-processing some 8000+ e-mails, or is there something else amiss?

Would there be a config variable to limit child processes?

[Janos SUTO]

Yes, the number_of_worker_processes in piler.conf. Something is very odd in your archive.