| Summary: | net-mail/dbmail-3.1.13 - LMTP daemon fails to connect to PostgreSQL database - dbmail/lmtpd[10204]: Error:[db] db_query(+370): SQLException: ERROR: insert or update on table "dbmail_messages" violates foreign key constraint "dbmail_messages_phys | ||
|---|---|---|---|
| Product: | Gentoo Linux | Reporter: | David W Noon <david.w.noon> |
| Component: | [OLD] Server | Assignee: | Thomas Raschbacher <lordvan> |
| Status: | RESOLVED TEST-REQUEST | ||
| Severity: | normal | CC: | net-mail+disabled |
| Priority: | Normal | Keywords: | PATCH |
| Version: | unspecified | ||
| Hardware: | All | ||
| OS: | Linux | ||
| Whiteboard: | |||
| Package list: | Runtime testing required: | --- | |
| Attachments: |
emerge --info
ebuild log Patch to use a reliable connection algorithm Syslog entries showing dbmail start-up problems. Patch for reliable retrieval of physical message id Patch to defer database connection until after root privileges have been dropped |
||
|
Description
David W Noon
2014-06-14 20:59:06 UTC
Created attachment 378898 [details]
emerge --info
Created attachment 378900 [details]
ebuild log
Compressed to avoid size limtations.
Created attachment 378904 [details, diff]
Patch to use a reliable connection algorithm
This patch has been tested here and is working with PostgreSQL. I have not tested it with MySQL/MariaDB or SQLite.
Hmm I have no such problems with postgres (9.2.3-r1) here. What version are you using? Also it would be good if you sent this patch upstream instead of just here. Paul is usually fairly fast with things like this. Also did you check if 3.3.13 still does this? (In reply to Thomas Raschbacher from comment #4) > Hmm I have no such problems with postgres (9.2.3-r1) here. > > What version are you using? I am using PostgreSQL 9.3.3 and dbmail 3.1.13. I also did not have this problem with earlier builds of PostgreSQL, but that was also without libzdb inside dbmail. > Also it would be good if you sent this patch upstream instead of just here. > Paul is usually fairly fast with things like this. I don't have a bugzilla account with dbmail, but I can create one. > Also did you check if 3.3.13 still does this? 3.3.13 of what? The highest version of dbmail in Portage is 3.1.15. Note that I originally created this patch for dbmail 3.1.2. It has been a problem ever since dbmail has been using libzdb for database connectivity. I stated in the original report that the login sequence [using libzdb] uses an old kluge that is no longer valid with newer PostgreSQL. This is the hub of the problem: the code has always been wrong, but older PostgreSQL allowed us to get away with it. sorry that was a typo i meant 3.1.15 of course. Did you make a bugreport on the dbmail bugtracker yet? if not I can do this if you want and link it here I put in a bugreport upstream for you: http://www.dbmail.org/mantis/view.php?id=1050 (In reply to Thomas Raschbacher from comment #7) > I put in a bugreport upstream for you: > > http://www.dbmail.org/mantis/view.php?id=1050 Thanks for that. I have been a bit ill the last few days and haven't had a chance to report it. I primarily want to be certain that there is nothing weird about my PostgreSQL installation. I am still using the same USE flags as I was when running 9.2.x and earlier, so I am confident that the issue is in dbmail/libzdb. The tracing I did to develop the patch also indicated it was dbmail, so hopefully we will get an "official" fix soon. I know there was no activity upstream, but are you still having this problem with recent versions of dbmail (and libzdb) ? (In reply to Thomas Raschbacher from comment #9) > I know there was no activity upstream, but are you still having this problem > with recent versions of dbmail (and libzdb) ? Yes. There has not been any new releases of dbMail since I reported this problem, and the existing release still needs my patch/kluge to start properly. actually there are new releases -- current one is 3.2.2 but it is not marked stable yet as I only added it yesterday - although it might get to stable faster than usual as it is a security fix. could you test with ~arch libzdb and dbmail if possible? (In reply to Thomas Raschbacher from comment #11) > actually there are new releases -- current one is 3.2.2 but it is not marked > stable yet as I only added it yesterday - although it might get to stable > faster than usual as it is a security fix. > > could you test with ~arch libzdb and dbmail if possible? Okay, I updated to libzdb 3.0 and dbmail 3.2.2 and the result was an unmitigated disaster. The only saving grace was that the new version was not able to corrupt the database. We should note that there was no upgrade script to go from 3.1.x to 3.2.y of dbmail. The only upgrade recommendation was to run dbmail-util -by. I did this. I will attach some lines from my syslog that will show the problems. Created attachment 392812 [details]
Syslog entries showing dbmail start-up problems.
(In reply to Thomas Raschbacher from comment #11) > actually there are new releases -- current one is 3.2.2 but it is not marked > stable yet as I only added it yesterday - although it might get to stable > faster than usual as it is a security fix. > > could you test with ~arch libzdb and dbmail if possible? This new release has more bugs than a cheap hotel room. It appears to be a major rewrite. The old problem is still there in the new code and I have reworked the patch (above) for the new code base. There is a small security issue with the server daemons connecting to PostgreSQL while still holding root permissions. Most PostgreSQL DBAs prohibit root from connecting to the database cluster. I have written patches to resolve these problems and I shall upload them when I am satisfied they are rock solid. Worst of all is the socket handling. The socket routines are hard-coded to use IPv4 data structures. This means that an IPv6 connection is handled by creating a control block containing IPv4 versions of sock_addr, etc., which are too small to contain the IPv6 data. The upshot is extensive heap corruption causing random crashes and substantial database corruption (typically dropped messages). This bug it too extensive for me to produce simple patches; it will need to be fixed upstream. For now, I have removed the IPv6 addresses from the "bindip" parameters in the dbmail.conf file. I think the "resolved" status of this bug is now invalid, as the original bug is still extant in the unpatched code. Patches to arrive in the next day or two. Created attachment 395404 [details, diff]
Patch for reliable retrieval of physical message id
Algorithm varies with the DBMS for backing store. This patch is simpler than its predecessor.
Created attachment 395406 [details, diff]
Patch to defer database connection until after root privileges have been dropped
The 2 patches I just added are for dbmail 3.2.2, not for earlier releases. |
