# Postfix errors after mysql 5.6 to 5.7 upgrade

## Shmoo

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.

----------

## pjp

Was postfix upgraded as well? 

Bug 665280 - mail-mta/postfix-3.3.1-r1: Many permissions incorrect after update, postfix functionality impaired

----------

## hanj

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:

```
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.

```
[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:

```
ssl-mode=DISABLED
```

Thanks!

hanji

----------

## hanj

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.

```
sql_mode=''
```

hanji

----------

## hanj

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

----------

## hanj

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

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

Change:

```
table        = virtual
```

To:

```
table        = `virtual` 
```

Then reload

```
postfix reload
```

Thanks!

hanji

----------

## Shmoo

Thanks hanji!

Adding the back-tics to mysql-virtual.cf did the job and everything is now working fine.

----------

## Duncan Mac Leod

Thank you!  :Smile: 

----------

## Hupf

 *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: 

```
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

```
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 

```
[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.

----------

