Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
Postfix errors after mysql 5.6 to 5.7 upgrade
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Networking & Security
View previous topic :: View next topic  
Author Message
Shmoo
n00b
n00b


Joined: 02 Jun 2007
Posts: 3

PostPosted: Sun Oct 21, 2018 6:43 pm    Post subject: Postfix errors after mysql 5.6 to 5.7 upgrade Reply with quote

After upgrading to mysql-5.7.23-r1 I get the following messages continually:

warning: mysql:/etc/postfix/mysql-virtual.cf lookup error for "****@gmail.com"
warning: AB749E5BB: virtual_alias_maps map lookup problem for ****@gmail.com

This happens for any mail sent or received regardless of domain.

I ran mysql_upgrade a bit after the fact with no errors, but still get the postfix errors.

So it seems I am without a mail server until I figure this out. Any help appreciated!

Edit:

Also from the mail logs a lot of this:
warning: mysql:/etc/postfix/mysql-virtual.cf: query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'virtual WHERE email='comcast.net'' at line 1

And lines like this from the mysql log:
2018-10-22T02:11:30.841825Z 35 Connect mailsql@localhost on using Socket
2018-10-22T02:11:30.842316Z 35 Init DB mailsql
2018-10-22T02:11:30.842679Z 35 Query SELECT email, "", clear, uid, gid, homedir, maildir, "", name, "" FROM users WHERE email = '*****@radiex.com'
2018-10-22T02:11:41.120560Z 36 Connect mailsql@localhost on mailsql using Socket
2018-10-22T02:11:41.121125Z 36 Quit

mysql-virtual.cf hasn't been modified for years, and looks like the example from the Gentoo wiki. Not sure what to change in any event.
Back to top
View user's profile Send private message
pjp
Administrator
Administrator


Joined: 16 Apr 2002
Posts: 17599

PostPosted: Mon Oct 22, 2018 1:32 pm    Post subject: Reply with quote

Was postfix upgraded as well?

Bug 665280 - mail-mta/postfix-3.3.1-r1: Many permissions incorrect after update, postfix functionality impaired
_________________
Luke Skywalker: I'm not afraid. Yoda: Oh, you will be. You will be.
Back to top
View user's profile Send private message
hanj
Veteran
Veteran


Joined: 19 Aug 2003
Posts: 1377

PostPosted: Mon Oct 22, 2018 2:21 pm    Post subject: Reply with quote

I'm running into a similar problem. Postfix is current.

But in the mail logs before the virtual lookup errors, I do see something similar to this, which might be the core of the problem:

Code:
postfix/cleanup[3598]: warning: connect to mysql server 127.0.0.1: SSL connection error: error:00000001:lib(0):func(0):reason(1)


I also performed mysql_upgrade. So outbound mails are in the queue, and flushing the queue will generate these errors.

Code:
[ebuild   R    ] dev-db/mysql-5.7.23-r2:0/18::gentoo  USE="latin1 perl server -cjk (-client-libs) -cracklib -debug -experimental -jemalloc -libressl -numa -profiling (-selinux) -static -static-libs -systemtap -tcmalloc -test -yassl" 47876 KiB
[ebuild   R    ] mail-mta/postfix-3.3.1-r1::gentoo  USE="berkdb eai hardened mysql pam sasl ssl -cdb -dovecot-sasl -ldap -ldap-bind -libressl -lmdb -mbox -memcached -nis -postgres (-selinux) -sqlite" 4318 KiB


I also tried to disable SSL at my.cnf [client] section, but that didn't seem to change the error:

Code:
ssl-mode=DISABLED


Thanks!
hanji
_________________
Server Admin Blog - Uno-Code.com | Gentoo Hosting at Rackspace!
Back to top
View user's profile Send private message
hanj
Veteran
Veteran


Joined: 19 Aug 2003
Posts: 1377

PostPosted: Mon Oct 22, 2018 2:44 pm    Post subject: Reply with quote

Interesting. I added 'skip_ssl' to my.cnf, and now I don't get the SSL error, but postfix is still complaining.

I enabled sql logging, and now seeing connections, but no queries with postfix. You're getting farther with the sql errors. Did you try setting sql_mode to blank in my.cnf? You might be dealing with 'strict' mode.

Code:
sql_mode=''


hanji
_________________
Server Admin Blog - Uno-Code.com | Gentoo Hosting at Rackspace!
Back to top
View user's profile Send private message
hanj
Veteran
Veteran


Joined: 19 Aug 2003
Posts: 1377

PostPosted: Mon Oct 22, 2018 2:49 pm    Post subject: Reply with quote

I think it's the table name virtual. It's a reserved word so it should have backtick `virtual`. Looking at how to make this happen without changing the tablename. Not sure what else is referencing that, so don't want to change the table (for now).

https://dev.mysql.com/doc/refman/8.0/en/keywords.html

hanji
_________________
Server Admin Blog - Uno-Code.com | Gentoo Hosting at Rackspace!
Back to top
View user's profile Send private message
hanj
Veteran
Veteran


Joined: 19 Aug 2003
Posts: 1377

PostPosted: Mon Oct 22, 2018 2:56 pm    Post subject: Reply with quote

Okay.. I think I got it. It's working for me now.

Edit: /etc/postfix/mysql-virtual.cf

Change:
Code:
table        = virtual


To:
Code:
table        = `virtual`


Then reload

Code:
postfix reload


Thanks!
hanji
_________________
Server Admin Blog - Uno-Code.com | Gentoo Hosting at Rackspace!
Back to top
View user's profile Send private message
Shmoo
n00b
n00b


Joined: 02 Jun 2007
Posts: 3

PostPosted: Mon Oct 22, 2018 8:53 pm    Post subject: Reply with quote

Thanks hanji!

Adding the back-tics to mysql-virtual.cf did the job and everything is now working fine.
Back to top
View user's profile Send private message
Duncan Mac Leod
Apprentice
Apprentice


Joined: 02 May 2004
Posts: 241
Location: Germany

PostPosted: Tue Oct 23, 2018 12:35 pm    Post subject: Reply with quote

Thank you! :)
Back to top
View user's profile Send private message
Hupf
Tux's lil' helper
Tux's lil' helper


Joined: 11 Sep 2005
Posts: 110
Location: Germany

PostPosted: Wed Dec 05, 2018 6:15 pm    Post subject: Reply with quote

hanj wrote:
I think it's the table name virtual. It's a reserved word so it should have backtick `virtual`. Looking at how to make this happen without changing the tablename. Not sure what else is referencing that, so don't want to change the table (for now).

https://dev.mysql.com/doc/refman/8.0/en/keywords.html

hanji

Now that I know what to look for, I find your post... So for future googlers, I'll include the symptoms that would show for me in this thread.
  • outgoing and incoming mails are immediately bounced as undeliverable
  • the mail.info / mail.log would contain entries of the form:
    Code:
    postfix/smtpd[ ]: NOQUEUE: reject: RCPT from  : 451 4.3.0 < >: Temporary lookup failure;

  • with increased verbosity (master.cf smtp service command + args set to smptd -v), the mail.info also contains
    Code:
    Dec  4 03:22:50 xxx postfix/smtpd[12927]: dict_mysql: successful connection to host unix:/var/run/mysqld/mysqld.sock
    Dec  4 03:22:50 xxx postfix/smtpd[12927]: maps_find: virtual_alias_maps: xxx@xxx.de: search aborted
    Dec  4 03:22:50 xxx postfix/smtpd[12927]: mail_addr_find: xxx@xxx.de -> (try again)

  • mysqld.err would contain entries like
    Code:
    [Note] Aborted connection  to db: ' ' user: ' ' host: 'localhost' (Got an error reading communication packets)



The guide that I followed back in the day, https://wiki.gentoo.org/wiki/Complete_Virtual_Mail_Server/Postfix_to_Database#MySQL_2 was updated on 2018-11-21 in light of this issue, but the change with the quote characters in only one of the config files seems easy to miss.
An alternative solution (the one I implemented) would be to use a non-reserved table name altogether (RENAME TABLE 'virtual' virtualfoo; or RENAME TABLE dbname.virtual virtualfoo).

Also, I find it strange that according to https://dev.mysql.com/doc/refman/5.7/en/keywords.html#keywords-5-7-detailed-V the keyword is only reserved as per MySQL 5.7.6 whereas portage claims it has installed mysql-5.6-r12.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Networking & Security All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum