Re: 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
logindatecolumn 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@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.
participants (2)
-
dovecot＠ptld.com
-
Gedalya