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

On Sun, 5 Jun 2005 08:37:53 -0400
Bud Roth <budroth@obitori.net> 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"


goto has actually been a reserved word in ANSI sql since 1992, however
it is legal as long as it is in quotes. However many people do not
understand that bit.

You seem to be using MySQL 5, older versions seem to accept it OK so it
looks as though the MySQL team have tightened up on such matters and not
made it very obvious (I've not played with version 5 yet myself). If
its not in the README for the upgrade then that's a bit naughty although
people should check up on such things beforehand.

I'd have thought go_to was probably more user-friendly than go2, but
them YMMV.


--
I do not reply to directly addressed mail unless it is clearly a matter
that should be dealt with off-list.