A NOTE has been added to this issue. ====================================================================== http://dbmail.org/mantis/view.php?id=1078 ====================================================================== Reported By: px3 Assigned To: ====================================================================== Project: DBMail Issue ID: 1078 Category: Database layer Reproducibility: have not tried Severity: minor Priority: normal Status: new target: ====================================================================== Date Submitted: 16-May-16 12:39 CEST Last Modified: 14-Mar-17 14:15 CET ====================================================================== Summary: DBMail stopped working after upgrading to mysql 5.7 Description: After upgrading mysql to 5.7.12 dbmail stopped working. Inserts into the database are no longer possible.
Using mysql and executing the same statements from the mysql-client, everything works fine. All other applications on the machine are runnning fine, expect dbmail. dbmail-util -a shows no errors. Even trying to create a new user with dbmail-users ends up in: May 16 12:42:51 rdsroot dbmail/user[29631]: Debug:[user] do_add(+65): Adding user px3 with password type crypt,0 bytes mailbox limit and clientid 0... May 16 12:42:51 rdsroot dbmail/user[29631]: Database:[db] db_con_get(+263): [0xe05ca0] connection from pool May 16 12:42:51 rdsroot dbmail/user[29631]: Database:[db] db_stmt_prepare(+419): [0xe05ca0] [SELECT user_idnr FROM dbmail_users WHERE lower(userid) = lower(?)] May 16 12:42:51 rdsroot dbmail/user[29631]: Database:[db] db_stmt_set_str(+427): [0xdf5810] 1:[px3] May 16 12:42:51 rdsroot dbmail/user[29631]: Database:[db] db_con_close(+291): [0xe05ca0] connection to pool May 16 12:42:51 rdsroot dbmail/user[29631]: Database:[db] db_con_get(+263): [0xe05ca0] connection from pool May 16 12:42:51 rdsroot dbmail/user[29631]: Database:[db] db_stmt_prepare(+419): [0xe05ca0] [SELECT user_idnr FROM dbmail_users WHERE lower(userid) = lower(?)] May 16 12:42:51 rdsroot dbmail/user[29631]: Database:[db] db_stmt_set_str(+427): [0xdf5aa0] 1:[px3] May 16 12:42:51 rdsroot dbmail/user[29631]: Database:[db] db_con_close(+291): [0xe05ca0] connection to pool May 16 12:42:51 rdsroot dbmail/user[29631]: Database:[db] db_con_get(+263): [0xe05ca0] connection from pool May 16 12:42:51 rdsroot dbmail/user[29631]: Database:[db] db_begin_transaction(+542): BEGIN May 16 12:42:51 rdsroot dbmail/user[29631]: Database:[db] db_stmt_prepare(+419): [0xe05ca0] [INSERT INTO dbmail_users (userid,passwd,client_idnr,maxmail_size,encryption_type) VALUES (?,?,?,?,?) ] May 16 12:42:51 rdsroot dbmail/user[29631]: Database:[db] db_stmt_set_str(+427): [0xdf5d10] 1:[px3] May 16 12:42:51 rdsroot dbmail/user[29631]: Database:[db] db_stmt_set_str(+427): [0xdf5d10] 2:[fE3UF87llocO6] May 16 12:42:51 rdsroot dbmail/user[29631]: Database:[db] db_stmt_set_u64(+439): [0xdf5d10] 3:[0] May 16 12:42:51 rdsroot dbmail/user[29631]: Database:[db] db_stmt_set_u64(+439): [0xdf5d10] 4:[0] May 16 12:42:51 rdsroot dbmail/user[29631]: Database:[db] db_stmt_set_str(+427): [0xdf5d10] 5:[crypt] And then nothing more happens. Mysql shows no errors in the logfiles. ====================================================================== ---------------------------------------------------------------------- (0003734) px3 (reporter) - 16-May-16 13:08 http://dbmail.org/mantis/view.php?id=1078#c3734 ---------------------------------------------------------------------- The severity is "block", but i can't change it :( ---------------------------------------------------------------------- (0003735) Wouter0100 (reporter) - 19-Jul-16 23:16 http://dbmail.org/mantis/view.php?id=1078#c3735 ---------------------------------------------------------------------- I agree with you, this is a major blocker. The SQL files are also incorrect (datetime default), but we're able to fix that manually. Although I'm not experienced with C, it seems to hang around here https://github.com/pjstevns/dbmail/blob/f5b73b342b4a6cae1bb15340e5da7330e29319c1/src/dm_db.c#L3976 . Not sure why - yet. ---------------------------------------------------------------------- (0003736) Wouter0100 (reporter) - 19-Jul-16 23:28 http://dbmail.org/mantis/view.php?id=1078#c3736 ---------------------------------------------------------------------- Now I'm pretty sure it has to do with the transactions. Removing aliases works fine (https://github.com/pjstevns/dbmail/blob/f5b73b342b4a6cae1bb15340e5da7330e29319c1/src/modules/authsql.c#L571-L589), but it hangs on removing a user (https://github.com/pjstevns/dbmail/blob/f5b73b342b4a6cae1bb15340e5da7330e29319c1/src/dm_db.c#L4006-L4024). ---------------------------------------------------------------------- (0003737) Wouter0100 (reporter) - 19-Jul-16 23:52 http://dbmail.org/mantis/view.php?id=1078#c3737 ---------------------------------------------------------------------- Tomorrow I'll try compiling dbmail so I'm able to test some code changes (currently using from debian packages). Could also be a bug in libzdb, because PreparedStatement_execute seems to hang when it's a transaction. ---------------------------------------------------------------------- (0003738) px3 (reporter) - 20-Jul-16 15:40 http://dbmail.org/mantis/view.php?id=1078#c3738 ---------------------------------------------------------------------- This behaviour accours on every "insert" Even new mails are no longer written to the DB ---------------------------------------------------------------------- (0003741) px3 (reporter) - 05-Sep-16 17:05 http://dbmail.org/mantis/view.php?id=1078#c3741 ---------------------------------------------------------------------- close this ticket. i fixed it myself by patching a bunch of code :( ---------------------------------------------------------------------- (0003742) alan (reporter) - 07-Sep-16 11:41 http://dbmail.org/mantis/view.php?id=1078#c3742 ---------------------------------------------------------------------- Please consider donating your patches so others can share in your success and avoid issues that upgrading to mysql 5.7 brings. ---------------------------------------------------------------------- (0003743) px3 (reporter) - 07-Sep-16 20:18 http://dbmail.org/mantis/view.php?id=1078#c3743 ---------------------------------------------------------------------- Cross my heard and hope to die: i won't publish my fixes. I have been waiting 4 month that someone of the community might take care of this bug and none did. so, in my point of view, this project is dead. i am thinking about creating a fork and keep on maintaining, but donating to this project in the current status isn't worth it. ---------------------------------------------------------------------- (0003744) alan (reporter) - 07-Sep-16 20:57 http://dbmail.org/mantis/view.php?id=1078#c3744 ---------------------------------------------------------------------- I'm sorry you feel that way, I would have offered more help but am running dbmail on FreeBSD with PostgreSQL, MySQL here is still 5.6 and as my life is falling apart am unlikely to upgrade anytime soon. Paul Stevens put so much effort in last year he's almost burnt out. Not sure where the others are. Please do fork and maintain, it's a great project, reasonably stable and with plenty of opportunities for improvement. ---------------------------------------------------------------------- (0003787) flds23 (reporter) - 12-Mar-17 17:48 http://dbmail.org/mantis/view.php?id=1078#c3787 ---------------------------------------------------------------------- Hey px3 might I politely ask you to reconsider? I ran into this yesterday and am now stuck with a broken service. I'm running dbmail 3.2.3 with MySQL 5.7 (switched servers and used the opportunity to use a new MySQL version) ---------------------------------------------------------------------- (0003788) px3 (reporter) - 13-Mar-17 15:45 http://dbmail.org/mantis/view.php?id=1078#c3788 ---------------------------------------------------------------------- Sorry flds23, but: "Both IC&S and NFG provide commercial support and consultancy for this product. For more information about this you can send an email to i...@ic-s.nl and/or supp...@nfg.nl." They should probably be able to fix your problem. ---------------------------------------------------------------------- (0003789) alan (reporter) - 13-Mar-17 17:43 http://dbmail.org/mantis/view.php?id=1078#c3789 ---------------------------------------------------------------------- Hi flds23, Have you tried the latest from https://github.com/pjstevns/dbmail It has a number of fixes including transactions. Else can you increase logging and check MySQL logs to see if there is any additional information. ---------------------------------------------------------------------- (0003790) flds23 (reporter) - 13-Mar-17 18:42 http://dbmail.org/mantis/view.php?id=1078#c3790 ---------------------------------------------------------------------- Hi Alan, I'm using the latest version of the FreeBSD port, which got a whole bunch of fixes from git recently https://svnweb.freebsd.org/ports?view=revision&revision=434749 I just ran a quick diff of that against master and there are very minimal changes left in git, mostly Makefile.in and a bit of stuff regarding systemd. So I don't think that will change anything, but i'm willing to try if you think I should. dbmail locks up hard with this line: Mar 13 18:39:25 xxx.xxx dbmail-lmtpd[50591]: [0x806023c00] Database:[db] db_query(+414): [0x80603bcc0] [INSERT INTO dbmail_physmessage (internal_date) VALUES (TIMESTAMP('2017-03-13 17:39:25'))] The only thing i can do is kill -9, it won't respond to SIGTERM. I think it has something to do with transactions, as i cannot do certain operations on the dbmail DB while the process is stuck. As soon as it's killed the other operations finish successfully. In the meantime I moved my dbmail database to another MySQL 5.6 Server, but it would be great to get this fixed. There is nothing in mysql logs. Thanks for your time. Florian (f...@freebsd.org) ---------------------------------------------------------------------- (0003791) alan (reporter) - 13-Mar-17 19:14 http://dbmail.org/mantis/view.php?id=1078#c3791 ---------------------------------------------------------------------- Good to hear you have it working with a 5.6 server. FreeBSD here though with PostgreSQL. Currently maxed out but will look into this, probably early next week. ---------------------------------------------------------------------- (0003792) px3 (reporter) - 14-Mar-17 14:15 http://dbmail.org/mantis/view.php?id=1078#c3792 ---------------------------------------------------------------------- It has nothing to do with transactions. DBMail simply stops on every (!) insert, regardless, what you are trying to insert into the DB. You won't find log-entrys in mysql, nor in dbmail. Issue History Date Modified Username Field Change ====================================================================== 16-May-16 12:39 px3 New Issue 16-May-16 13:06 px3 Issue Monitored: px3 16-May-16 13:08 px3 Note Added: 0003734 19-Jul-16 23:16 Wouter0100 Note Added: 0003735 19-Jul-16 23:16 Wouter0100 Issue Monitored: Wouter0100 19-Jul-16 23:28 Wouter0100 Note Added: 0003736 19-Jul-16 23:52 Wouter0100 Note Added: 0003737 20-Jul-16 15:40 px3 Note Added: 0003738 05-Sep-16 17:05 px3 Note Added: 0003741 07-Sep-16 11:41 alan Note Added: 0003742 07-Sep-16 20:18 px3 Note Added: 0003743 07-Sep-16 20:57 alan Note Added: 0003744 12-Mar-17 17:48 flds23 Note Added: 0003787 13-Mar-17 15:45 px3 Note Added: 0003788 13-Mar-17 17:43 alan Note Added: 0003789 13-Mar-17 18:42 flds23 Note Added: 0003790 13-Mar-17 19:14 alan Note Added: 0003791 14-Mar-17 14:15 px3 Note Added: 0003792 ====================================================================== _______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://lists.nfg.nl/mailman/listinfo/dbmail-dev