| View previous topic :: View next topic |
| Author |
Message |
Shmoo
n00b
Joined: 02 Jun 2007
Posts: 3
|
 Posted: Sun Oct 21, 2018 6:43 pm Post subject: Postfix errors after mysql 5.6 to 5.7 upgrade |
 |
|
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 |
|
 |
pjp
Administrator
Joined: 16 Apr 2002
Posts: 20067
|
|
| Back to top |
|
|
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
| Posted: Mon Oct 22, 2018 2:21 pm Post subject: |
|
|
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:
Thanks!
hanji
_________________
Server Admin Blog - Uno-Code.com |
|
| Back to top |
|
|
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
| Posted: Mon Oct 22, 2018 2:44 pm Post subject: |
|
|
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.
hanji
_________________
Server Admin Blog - Uno-Code.com |
|
| Back to top |
|
|
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
|
| Back to top |
|
|
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
| Posted: Mon Oct 22, 2018 2:56 pm Post subject: |
|
|
Okay.. I think I got it. It's working for me now.
Edit: /etc/postfix/mysql-virtual.cf
Change:
To:
Then reload
Thanks!
hanji
_________________
Server Admin Blog - Uno-Code.com |
|
| Back to top |
|
|
Shmoo
n00b
Joined: 02 Jun 2007
Posts: 3
|
| Posted: Mon Oct 22, 2018 8:53 pm Post subject: |
|
|
Thanks hanji!
Adding the back-tics to mysql-virtual.cf did the job and everything is now working fine. |
|
| Back to top |
|
|
Duncan Mac Leod
Guru
Joined: 02 May 2004
Posts: 312
Location: Germany
|
| Posted: Tue Oct 23, 2018 12:35 pm Post subject: |
|
|
Thank you!  |
|
| Back to top |
|
|
Hupf
Tux's lil' helper
Joined: 11 Sep 2005
Posts: 112
Location: Germany
|
| Posted: Wed Dec 05, 2018 6:15 pm Post subject: |
|
|
| 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 |
|
|
|
Networking & Security
