dovecot - Sep 2021 - Storing Last Login Plugin value in SQL

> Assuming you use MariaDB / MySQL, you create this trigger in the 
> database.
> Assuming your int/bigint/varchar column is lastlogin and the table name 
> is mailacct,
> the trigger will update the datetime `logindate` column whenever the 
> table is updated,
> by whatever existing queries you have. This is your recourse when you 
> can't get your
> queries to do the right thing in the first place.

Thank you for the solution of using sql triggers. I was able to get it 
working that way.
I hope it doesn't add too much overhead as it feels like a band-aid and 
duct-tape fix.

On 9/14/21 05:44, dovecot at ptld.com wrote:
>
> Thank you for the solution of using sql triggers. I was able to get it
working that way.
> I hope it doesn't add too much overhead as it feels like a band-aid and
duct-tape fix.
Yes, it's a workaround rather than being able to customize the SQL query.
It's a bit of post-processing.

It will run on _any_ update on this table. If those are many, it may or may not
be worthwhile to add a conditional, if perhaps evaluating the conditional would
be cheaper than FROM_UNIXTIME().

In my case, I use a trigger to save a huge amount of overhead. Since I don't
need to know that an account is "recently" used at a resolution higher
than 15 minutes, and the database is replicated, and nearly all database
(+replication) IO is lastlogin, I'm able to cut it down tremendously when
using row-based replication, and nothing is logged when the row wasn't
actually modified. Of course this too could have been done in the original query
itself.

On another note, IIRC the lastlogin timestamp is a 32-bit integer. We need
64-bit timestamps, for winter is coming.