All,
On Wed, 2008-08-20 at 16:52 +0200, Shane Kerr wrote:
> I assume this is because SQLite does table-level locking, and multiple
> messages are arriving at one time. Maybe this has something to do with
> the truncation? I have moved to PostgreSQL, and will see if the problem
> seems to go away.
>
> If this is the case, I think I'll put a big scary warning on the wiki
> site saying, "DO NOT USE SQLite FOR ANYTHING OTHER THAN TESTING
> PURPOSES". Or is that already there and I missed it? :)
I turned off the SQLite version at 16:22 yesterday, and turned up the
PostgreSQL version shortly afterwords.
Mails that arrived at 15:29, 15:41, and 15:57 were all truncated at the
end.
Mails that arrived since then, at 16:46, 16:53, 16:57, 16:59, 19:58,
22:18, 23:15, 00:04, 06:59, and 10:01 have no truncation.
I am not 100% sure, but I think the problem is fixed.
So, that is fine for me. But what about other users, and the state of
SQLite in dbmail in general?
Does anyone use SQLite in production? If not, I will update the Wiki
with a warning if nobody objects.
It is possible that using lmtp makes the problem much less severe, or
even eliminates it, since you have a lot less chances for concurrent
access to the database file. (It will still be possible sometimes,
because dbmail-imapd also needs to modify the database.)
It is possible that dbmail 2.3 does not have this problem, because of
the Zild database library may have slightly different semantics.
Finally, it is also possible to use Unix file-locking operations to lock
the database file on open (meaning lockf() or something like that),
rather than relying on SQLite to do this. I went ahead and coded this
up, although I haven't tested it. It's a bit of a hack, but I think it
should work. See the patch at the end.
--
Shane
--- dbsqlite.c.orig 2008-03-24 15:49:33.000000000 +0100
+++ dbsqlite.c 2008-08-21 12:23:01.000000000 +0200
@@ -34,6 +34,9 @@
static sqlite3 *conn;
+/* file descriptor for system-level locking of database */
+static int conn_lock_fd = -1;
+
const char * db_get_sql(sql_fragment_t frag)
{
switch(frag) {
@@ -60,6 +63,7 @@
break;
case SQL_REGEXP:
TRACE(TRACE_ERROR, "We deliberately don't support
REGEXP operations.");
+ close(conn_lock_fd);
sqlite3_close(conn);
exit(255);
break;
@@ -245,12 +249,21 @@
int db_connect()
{
int result;
+
+ conn_lock_fd = open(_db_params.db, O_RDWR);
+ if (conn_lock_fd == -1) {
+ TRACE(TRACE_FATAL, "open of '%s' failed: %s", _db_params.db,
strerror(errno));
+ return -1;
+ }
+
if ((result = sqlite3_open(_db_params.db, &conn)) != SQLITE_OK) {
TRACE(TRACE_FATAL, "sqlite3_open failed: %s",
sqlite3_errmsg(conn));
+ close(conn_lock_fd);
sqlite3_close(conn);
return -1;
}
if (sqlite3_create_function(conn, "REGEXP", 2, SQLITE_ANY, NULL, (void
*)dbsqlite_cslike, NULL, NULL) != SQLITE_OK) {
+ close(conn_lock_fd);
sqlite3_close(conn);
TRACE(TRACE_FATAL, "sqlite3_create_function failed");
return -1;
@@ -276,6 +289,7 @@
int db_disconnect()
{
db_free_result();
+ close(conn_lock_fd);
sqlite3_close(conn);
conn = 0;
return 0;
@@ -317,6 +331,16 @@
}
TRACE(TRACE_DEBUG,"%s", the_query);
+ /* if we are beginnning a transaction, lock the database */
+ if (strcasecmp(the_query, "BEGIN") == 0) {
+ if (lockf(conn_lock_fd, F_LOCK, 0) != 0) {
+ TRACE(TRACE_ERROR, "lockf failed locking: %s",
strerror(errno));
+ return -1;
+ }
+ }
+
+ /* iterate until we execute our query without a busy table */
+ /* note: we should *never* get a busy table, since we now lock the file
*/
while (TRUE) {
res = sqlite3_get_table(conn, the_query, &lastq->resp,
(int *)&lastq->rows, (int *)&lastq->cols, &errmsg);
@@ -340,6 +364,14 @@
lastq->rows = 0;
lastq->cols = 0;
}
+
+ /* if we are ending a transaction, unlock the database */
+ if ((strcasecmp(the_query, "COMMIT") == 0) || (strcasecmp(the_query,
"ROLLBACK") == 0)) {
+ if (lockf(conn_lock_fd, F_ULOCK, 0) != 0) {
+ TRACE(TRACE_ERROR, "lockf failed unlocking: %s",
strerror(errno));
+ return -1;
+ }
+ }
return 0;
}
unsigned long db_escape_string(char *to, const char *from, unsigned long
length)
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
