Re: SOLVED: table lookup errors when postfix tries to read mysql virtual domain tables

Sorry, this part of the postmap output didn't paste in:

OUTPUT:
postmap: dict_lookup: additional_conditions = (notfound)
postmap: cfg_get_str: /usr/local/etc/postfix/mysql_virtual_alias_maps.cf:
additional_conditions =
postmap: dict_lookup: domain = (notfound)
postmap: cfg_get_str: /usr/local/etc/postfix/mysql_virtual_alias_maps.cf:
domain =
postmap: dict_lookup: hosts = localhost
postmap: cfg_get_str: /usr/local/etc/postfix/mysql_virtual_alias_maps.cf:
hosts = localhost
postmap: mysql host_init: host=localhost, port=0, type=unix
postmap: dict_open: mysql:/usr/local/etc/postfix/mysql_virtual_alias_maps.cf
postmap: dict_mysql_get_active: attempting to connect to host localhost
postmap: dict_mysql: successful connection to host localhost
postmap: warning: mysql 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 'goto FROM alias WHERE address='bud@obitori.net'' at line
1
postmap: dict_unregister: /usr/local/etc/postfix/mysql_virtual_alias_maps.cf 1

The bottom-most part with the syntax error is what is important.

On Sunday 05 June 2005 08:37, Bud Roth wrote:
> Per Magnus Black's suggestion, I ran:
>
> postmap -vv -q bud@obitori.net
> mysql:/usr/local/etc/postfix/mysql_virtual_alias_maps.cf
>
> OUTPUT:
> postmap: dict_lookup: additional_conditions = (notfound)
> postmap: cfg_get_str: /usr/local/etc/postfix/mysql_virtual_alias_maps.cf:
> additional_conditions =
> postmap: dict_lookup: domain = (notfound)
> postmap: cfg_get_str: /usr/local/etc/postfix/mysql_virtual_alias_maps.cf:
> domain =
> postmap: dict_lookup: hosts = localhost
>
> This seemed to confirm Keith Matthews' suspicion that I had a Mysql table
> problem such as a misnamed column. I decided to drop the postfix tables
> and users created by the DATABASE_MYSQL.TXT file that comes with
> postfixadmin. (The text file creates the virtual alias, domain, and mailbox
> tables used by postfix as well as the PHP-based postfixadmin GUI.) I reran
> the script:
>
> mysql -u root -p < DATABASE_MYSQL.TXT
>
> and got a table creation error. To make a long story short, I cut the
> DATABASE_MYSQL.TXT file into pieces and ran them thru mysql. Finally, I
> located the problem in this part of the file:
>
> CREATE TABLE alias (
> address varchar(255) NOT NULL default '',
> goto text NOT NULL,
> domain varchar(255) NOT NULL default '',
> created datetime NOT NULL default '0000-00-00 00:00:00',
> modified datetime NOT NULL default '0000-00-00 00:00:00',
> active tinyint(1) NOT NULL default '1',
> PRIMARY KEY (address),
> KEY address (address)
> ) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Aliases';
>
> The problem appears to be the "goto" column. See:
>
> http://dev.mysql.com/doc/mysql/en/reserved-words.html
>
> The above link lists "goto" as a reserved word. I changed the column name
> to "go2" in mysql's postfix.alias table and changed "goto" to "go2" in the
> postfix mysql_virtual_alias_maps.cf file. In addition, I had to make the
> same change in all the files located within /usr/local/www/postfixadmin,
> which I did with a perl script. The problem appears to be resolved.
>
> In conclusion, as Keith surmised, this is a postfixadmin problem, not a
> postfix problem. The initial table creation script is producing an error
> in my version of mysql (5.0.0-alpha Freebsd 5.3 RELEASE port.) Because the
> table is incorrectly created, postfix cannot access the columns it expects
> to finds and produces the errors contained in my original post. I opened a
> bug report with postfixadmin. Thanks for pointing me in the right
> direction.
>
> Bud
> Lake Barcroft, VA
>
> -------------------------------------------------------