Mailing List Archive

[DBMail 0001078]: DBMail stopped working after upgrading to mysql 5.7
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: 13-Dec-17 09:57 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
info@ic-s.nl and/or support@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 (flo@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.

----------------------------------------------------------------------
(0003794) thelounge (reporter) - 26-Sep-17 17:15
http://dbmail.org/mantis/view.php?id=1078#c3794
----------------------------------------------------------------------
since it works fine with MariaDB 10.1 and even with MariaDB 10.2 as far as
my tests are showing i guess your libzdb shoud be rebuilt, as you can see
below libzb and not dbmail itself is linked against the client library and
i just don't believe that it works with all MariaDB releases but not MySQL
5.7

[root@rh:~]$ rpm -q --filesbypkg libzdb
libzdb /usr/lib64/libzdb.so.11
libzdb /usr/lib64/libzdb.so.11.0.0

[root@rh:~]$ ldd /usr/lib64/libzdb.so.11.0.0
linux-vdso.so.1 (0x00007ffc367e5000)
libmariadb.so.3 => /usr/lib64/mysql/libmariadb.so.3
(0x00007f2642168000)
libz.so.1 => /lib64/libz.so.1 (0x00007f2641f51000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f2641d32000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f2641b2e000)
libm.so.6 => /lib64/libm.so.6 (0x00007f2641818000)
libssl.so.1.1 => /lib64/libssl.so.1.1 (0x00007f26415ac000)
libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x00007f2641121000)
libc.so.6 => /lib64/libc.so.6 (0x00007f2640d50000)
libgomp.so.1 => /lib64/libgomp.so.1 (0x00007f2640b21000)
/lib64/ld-linux-x86-64.so.2 (0x00007f26423b5000)

----------------------------------------------------------------------
(0003796) lewa (reporter) - 13-Dec-17 09:57
http://dbmail.org/mantis/view.php?id=1078#c3796
----------------------------------------------------------------------
There's a half-a-year-old pull request on GitHub related to this issue.

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
26-Sep-17 17:15 thelounge Note Added: 0003794
13-Dec-17 09:57 lewa Note Added: 0003796
======================================================================

_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://lists.nfg.nl/mailman/listinfo/dbmail-dev