MiaB bricks users.sqlite after adding new user through web admin

Maintenance
#1

So my problem is similar to this thread, presumably after user changed their password through roundcube MiaB bricked and

sqlite3 users.sqlite PRAGMA journal_mode=delete;’;

fixed it for us. I’ve also disabled “password” plugin in roundcube config. But now adding new user through web admin also causes same problem and I’m a bit lost :frowning:

Error log after trying to add user:

postfix/smtpd[16765]: fatal: dict_sqlite_lookup: /etc/postfix/virtual-alias-maps.cf: SQL finalize failed for query ‘SELECT destination from (SELECT destination, 0 as priority FROM aliases WHERE source=‘key@mydomainhere’ AND destination<>’’ UNION SELECT email as destination, 1 as priority FROM users WHERE email=‘key@mydomainhere’ UNION SELECT destination, 2 as priority FROM auto_aliases WHERE source=‘key@mydomainhere’ AND destination<>’’) ORDER BY priority LIMIT 1;’: database is locked

postfix/trivial-rewrite[17448]: fatal: dict_sqlite_lookup: /etc/postfix/virtual-alias-maps.cf: SQL prepare failed: database is locked?
#3

Actually, now that I’m thinking about it, I don’t know if a password change through roundcube is what caused it in the first place. At least one user successfully changed their password around 10 am and problem manifested itself around 5 pm

so fist error would be this:

SELECT destination from (SELECT destination, 0 as priority FROM aliases WHERE source=‘key@…’ AND destination<>’’ UNION SELECT email as destination, 1 as priority FROM users WHERE email=‘key@…’ UNION SELECT destination, 2 as priority FROM auto_aliases WHERE source=‘key@…’ AND destination<>’’) ORDER BY priority LIMIT 1;

#4

I may have also had this issue, or something related. We did add a user the afternoon before, and then yesterday morning (around 12 hours after adding the user) my server appeared to be continuing to receive emails but we were unable to login either by Roundcube or IMAP. I tried the sqlite3 command above, but it didn’t appear to help immediately. I determined it would likely be faster to restore from backups than attempt to troubleshoot with my limited abilities, so that is what I did. I still have the old virtual machine, so I can provide what info I can if anyone who is more technically able is interested.

#5

So, what functionality of Mail-in-a-Box is not working? You cannot add a user? Mail is not received or sent?
More questions: what version of Mail-in-a-Box are you on? And does the error happen only once (when creating the user?) Or is it a continuously reported?
The error you report is not the same as in the thread you link to, so that’s probably not what’s happening here. I also note that the sqlite3 command you quote has a missing ' so that might mess up stuff as well.

#6

@KiekerJan
Right now it’s working, I’ve restored previous users.sqlite, but yes, it was impossible to add new users. Admin interface just refused to load after adding new user and it was impossible to send any mail. It looked like WAL was enabled on users.sqlite. First error was not the same as in the thread I linked, but subsequent errors was exactly the same

I’m hesitant to try to replicate this error on production server right now, I’ll post later if it manifests itself again

I also note that the sqlite3 command you quote has a missing '

You mean command disabling WAL? It’s a typo in my post, sorry. I’ve used sqlite3 users.sqlite 'PRAGMA journal_mode=delete;';

#7

I understand that debugging on a production server is not a thing you want to do :innocent:
You might be able to recover some debugging information from journalctl -u mailinabox That might still contain clues to what went wrong for you.