Re: Invalid/Unmapped Key with Last Login Plugin

On 17/07/2024 23:35, Adam Miller via dovecot wrote:

Yes, I did see that, thank you. If I did want to get this working globally, do you know what the issue is here and how to resolve it? I think it has something to do with my database table but I am not positive what the issue is. Here’s my database table (MySQL):

CREATE TABLE mailboxLastLogin ( email varchar(192) NOT NULL DEFAULT ‘’, service varchar(16) NOT NULL DEFAULT ‘’, timestamp bigint(20) NOT NULL, ipaddress varchar(40) NOT NULL, createdAt datetime NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

I also have two triggers on this table so when a new row is inserted or an existing row is updated, a new row is inserted into another tables named mailboxHistory. This is working fine and should not be causing any issues with LMTP trying to query itself.

BEGIN DECLARE mailboxUuid VARCHAR(36);

    SELECT uuid INTO mailboxUuid FROM mailbox WHERE email = NEW.email;

    INSERT INTO mailboxHistory (mailboxUuid, service, command, ipaddress, uuid) VALUES (mailboxUuid, NEW.service, 'login', NEW.ipaddress, uuidv4()); END

BEGIN DECLARE mailboxUuid VARCHAR(36);

    SELECT uuid INTO mailboxUuid FROM mailbox WHERE email = NEW.email;

    INSERT INTO mailboxHistory (mailboxUuid, service, command, ipaddress, uuid) VALUES (mailboxUuid, NEW.service, 'login', NEW.ipaddress, uuidv4()); END

CREATE TABLE mailboxHistory ( id int(11) unsigned NOT NULL AUTO_INCREMENT, uuid varchar(36) DEFAULT ‘’, mailboxUuid varchar(36) NOT NULL DEFAULT ‘’, service varchar(16) NOT NULL, command varchar(36) DEFAULT NULL, extra longtext DEFAULT NULL, ipaddress varchar(40) NOT NULL, createdAt datetime NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (id), UNIQUE KEY UUID (uuid) ) ENGINE=InnoDB AUTO_INCREMENT=2047 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Thank you,

Adam

Hi Adam

the solution is to change

mail_plugins = " quota mailbox_alias last_login"

to

mail_plugins = quota mailbox_alias

(without the last_login)

The last_login should be configured inside the protocols where you need it, like imap. I don't think it's useful to have the plugin setup for services different to imap, pop3 and smtp where you have users logging in. If you want to have last_login for lmtp without that error, I'd need a bit more info

What are the contents of /etc/dovecot/inc.d/inc.lastlogin.sql.conf (masking any passwords) and what is the output of doveconf plugin/last_login_key

John