Postfix with sqlite - Database becomes locked

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Postfix with sqlite - Database becomes locked

sebastian-2
Dear all,

I am running postfix 3.3 and recently migrated all my virtual domains from MySQL to Sqlite for performance reasons. So far, everything works fine however up to 5-8 times a day, I am seeing an error message in my mail.log saying that the Sqlite 3 database may be locked. Here is an example:

--

Jan 10 18:00:42 waldfest postfix/smtpd[88198]: connect from spring-chicken-bi.twitter.com[199.16.156.174]
Jan 10 18:00:43 waldfest postfix/smtpd[88198]: Anonymous TLS connection established from spring-chicken-bi.twitter.com[199.16.156.174]: TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)
Jan 10 18:00:45 waldfest postgrey[656]: action=pass, reason=client AWL, client_name=spring-chicken-bi.twitter.com, client_address=199.16.156.174, sender=n0541a68f73-b84c2c7eb58d4ba4a17c4affd751c95c-presse===[hidden email], recipient=[hidden email]
Jan 10 18:00:45 waldfest postfix/smtpd[88198]: 3D13F5076C7: client=spring-chicken-bi.twitter.com[199.16.156.174]
Jan 10 18:00:45 waldfest postfix/cleanup[88252]: warning: dict_sqlite_lookup: /etc/postfix/sqlite_virtual_alias_maps.cf: SQL step failed for query 'SELECT goto FROM alias WHERE address='[hidden email]' AND active = '1'': database is locked?
Jan 10 18:00:45 waldfest postfix/cleanup[88252]: fatal: dict_sqlite_lookup: /etc/postfix/sqlite_virtual_alias_maps.cf: SQL finalize failed for query 'SELECT goto FROM alias WHERE address='[hidden email]' AND active = '1'': database is locked?
Jan 10 18:00:45 waldfest dovecot: imap([hidden email]): Logged out in=291 out=2593
Jan 10 18:00:46 waldfest postfix/smtpd[88198]: warning: cannot send milters to service public/cleanup socket
Jan 10 18:00:46 waldfest postfix/master[54589]: warning: process /usr/libexec/postfix/cleanup pid 88252 exit status 1
Jan 10 18:00:46 waldfest postfix/smtpd[88198]: too many errors after END-OF-MESSAGE from spring-chicken-bi.twitter.com[199.16.156.174]
Jan 10 18:00:47 waldfest postfix/cleanup[88290]: 438125076C8: message-id=<[hidden email]>

--

I find this quite odd as the abovementioned SQL query just works fine. I tried to search Google for the error („database is locked?“), however I am unable to find any information on this matter. Also apart from these messages everything works fine and the server handles thousands of emails correctly per day. Any idea on how to troubleshoot this?

Thank you.

Best regards
Sebastian

Reply | Threaded
Open this post in threaded view
|

Re: Postfix with sqlite - Database becomes locked

Wietse Venema
Sebastian Wolfgarten:
> Dear all,
>
> I am running postfix 3.3 and recently migrated all my virtual
> domains from MySQL to Sqlite for performance reasons. So far,
> everything works fine however up to 5-8 times a day, I am seeing
> an error message in my mail.log saying that the Sqlite 3 database
> may be locked. Here is an example:

So what is locking the database? The query as shown does not attempt
to modify the database. Note that the query fails immediately, there
is no attempt to wait for a lock to be released.

        Wietse

> Jan 10 18:00:45 waldfest postfix/smtpd[88198]: 3D13F5076C7: client=spring-chicken-bi.twitter.com[199.16.156.174]
...
> Jan 10 18:00:45 waldfest postfix/cleanup[88252]: fatal: dict_sqlite_lookup: /etc/postfix/sqlite_virtual_alias_maps.cf: SQL finalize failed for query 'SELECT goto FROM alias WHERE address='[hidden email]' AND active = '1'': database is locked?
Reply | Threaded
Open this post in threaded view
|

Re: Postfix with sqlite - Database becomes locked

sebastian-2
Hi,

I am uncertain as to what’s causing the DB to get locked - I am also running Roundcube (webmail) on the server, maybe that’s the reason. I will check the permission roundcube uses to access the db, I think it can be read-only which will hopefully fix the locking issue.

As a feature request, would it maybe make sense to add a waiting period to be able to wait for the lock for 1-2 seconds and then retry with the database query?

Thanks.

Kind regards
Sebastian

> Am 10.01.2018 um 21:15 schrieb Wietse Venema <[hidden email]>:
>
> Sebastian Wolfgarten:
>> Dear all,
>>
>> I am running postfix 3.3 and recently migrated all my virtual
>> domains from MySQL to Sqlite for performance reasons. So far,
>> everything works fine however up to 5-8 times a day, I am seeing
>> an error message in my mail.log saying that the Sqlite 3 database
>> may be locked. Here is an example:
>
> So what is locking the database? The query as shown does not attempt
> to modify the database. Note that the query fails immediately, there
> is no attempt to wait for a lock to be released.
>
> Wietse
>
>> Jan 10 18:00:45 waldfest postfix/smtpd[88198]: 3D13F5076C7: client=spring-chicken-bi.twitter.com[199.16.156.174]
> ...
>> Jan 10 18:00:45 waldfest postfix/cleanup[88252]: fatal: dict_sqlite_lookup: /etc/postfix/sqlite_virtual_alias_maps.cf: SQL finalize failed for query 'SELECT goto FROM alias WHERE address='[hidden email]' AND active = '1'': database is locked?

Reply | Threaded
Open this post in threaded view
|

Re: Postfix with sqlite - Database becomes locked

John Fawcett
On 01/10/2018 09:28 PM, Sebastian Wolfgarten wrote:

> Hi,
>
> I am uncertain as to what’s causing the DB to get locked - I am also running Roundcube (webmail) on the server, maybe that’s the reason. I will check the permission roundcube uses to access the db, I think it can be read-only which will hopefully fix the locking issue.
>
> As a feature request, would it maybe make sense to add a waiting period to be able to wait for the lock for 1-2 seconds and then retry with the database query?
>
> Thanks.
>
> Kind regards
> Sebastian
>
>> Am 10.01.2018 um 21:15 schrieb Wietse Venema <[hidden email]>:
>>
>> Sebastian Wolfgarten:
>>> Dear all,
>>>
>>> I am running postfix 3.3 and recently migrated all my virtual
>>> domains from MySQL to Sqlite for performance reasons. So far,
>>> everything works fine however up to 5-8 times a day, I am seeing
>>> an error message in my mail.log saying that the Sqlite 3 database
>>> may be locked. Here is an example:
>> So what is locking the database? The query as shown does not attempt
>> to modify the database. Note that the query fails immediately, there
>> is no attempt to wait for a lock to be released.
>>
>> Wietse
>>
>>> Jan 10 18:00:45 waldfest postfix/smtpd[88198]: 3D13F5076C7: client=spring-chicken-bi.twitter.com[199.16.156.174]
>> ...
>>> Jan 10 18:00:45 waldfest postfix/cleanup[88252]: fatal: dict_sqlite_lookup: /etc/postfix/sqlite_virtual_alias_maps.cf: SQL finalize failed for query 'SELECT goto FROM alias WHERE address='[hidden email]' AND active = '1'': database is locked?

Could be useful to see the return code from sqlite3_step. If this is
different to SQLITE_DONE or SQLITE_ROW then the warning message is
triggered. When using sqlite3_step() after the sqlite3_prepare_v2()
function the return code can contain extended error information.

https://www.sqlite.org/rescode.html

If you're able to compile from source:

--- dict_sqlite.c    2015-01-11 17:52:40.000000000 +0100
+++ dict_sqlite_new.c    2018-01-10 21:55:12.149559110 +0100
@@ -244,8 +244,8 @@
     }
     /* Fix 20100616 */
     else {
-        msg_warn("%s: %s: SQL step failed for query '%s': %s\n",
-             myname, dict_sqlite->parser->name,
+        msg_warn("%s: %s: SQL step failed with result %d for query
'%s': %s\n",
+             myname, dict_sqlite->parser->name,status,
              vstring_str(query), sqlite3_errmsg(dict_sqlite->db));
         dict->error = DICT_ERR_RETRY;
         break;

John

Reply | Threaded
Open this post in threaded view
|

Re: Postfix with sqlite - Database becomes locked

Wietse Venema
In reply to this post by sebastian-2
Sebastian Wolfgarten:
> Hi,
>
> I am uncertain as to what?s causing the DB to get locked - I am also running Roundcube (webmail) on the server, maybe that?s the reason. I will check the permission roundcube uses to access the db, I think it can be read-only which will hopefully fix the locking issue.

It certainly looks like a problem specific to your setup. The Postfix
sqlite client has been around since Postfix 2.8 and it has hardly
changed.

> As a feature request, would it maybe make sense to add a waiting
> period to be able to wait for the lock for 1-2 seconds and then
> retry with the database query?

The only lock that can prevent sqlite from reading is a write lock,
and there is nothing in Postfix that generate an sqlite write request.

Look at the file modification time. Did the file change recently?

Let's find out more about the error first. See John Fawcett's suggestion.

        Wietse

>
> Kind regards
> Sebastian
>
> > Am 10.01.2018 um 21:15 schrieb Wietse Venema <[hidden email]>:
> >
> > Sebastian Wolfgarten:
> >> Dear all,
> >>
> >> I am running postfix 3.3 and recently migrated all my virtual
> >> domains from MySQL to Sqlite for performance reasons. So far,
> >> everything works fine however up to 5-8 times a day, I am seeing
> >> an error message in my mail.log saying that the Sqlite 3 database
> >> may be locked. Here is an example:
> >
> > So what is locking the database? The query as shown does not attempt
> > to modify the database. Note that the query fails immediately, there
> > is no attempt to wait for a lock to be released.
> >
> > Wietse
> >
> >> Jan 10 18:00:45 waldfest postfix/smtpd[88198]: 3D13F5076C7: client=spring-chicken-bi.twitter.com[199.16.156.174]
> > ...
> >> Jan 10 18:00:45 waldfest postfix/cleanup[88252]: fatal: dict_sqlite_lookup: /etc/postfix/sqlite_virtual_alias_maps.cf: SQL finalize failed for query 'SELECT goto FROM alias WHERE address='[hidden email]' AND active = '1'': database is locked?
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Postfix with sqlite - Database becomes locked

Viktor Dukhovni


> On Jan 10, 2018, at 7:45 PM, Wietse Venema <[hidden email]> wrote:
>
>> I am uncertain as to what?s causing the DB to get locked - I am also running Roundcube (webmail) on the server, maybe that?s the reason. I will check the permission roundcube uses to access the db, I think it can be read-only which will hopefully fix the locking issue.
>
> It certainly looks like a problem specific to your setup. The Postfix
> sqlite client has been around since Postfix 2.8 and it has hardly
> changed.
>
>> As a feature request, would it maybe make sense to add a waiting
>> period to be able to wait for the lock for 1-2 seconds and then
>> retry with the database query?
>
> The only lock that can prevent sqlite from reading is a write lock,
> and there is nothing in Postfix that generate an sqlite write request.
>
> Look at the file modification time. Did the file change recently?
>
> Let's find out more about the error first. See John Fawcett's suggestion.

SQLite is designed primarily for embedded access and writers acquire
exclusive locks when making updates, or merging the write-ahead-log
into the database ...  Read-only users need to be willing to retry
database operations when it is locked by a writer.  The easiest way
to do that is by making the first raw SQL command at the start of a
connection a pragma to set a busy timeout:

    https://www.sqlite.org/pragma.html#pragma_busy_timeout

    PRAGMA busy_timeout = milliseconds

For a database with email-related info large transactions
and long write-locks should be rare, so 1000ms or so should
be enough.

--
        Viktor.