Postfix mysql lookup not working

You can connect to mysql at a command prompt like this ? (use whatever account you have the pass and account for):

mysql -h localhost -u root -p

If you can, and the above can't, evidence leans towards SELinux interfereing or something along those lines...

What does /etc/postfix/mysql-aliases.cf have in it ?

I am able to connect to my sql using the command

mysql -h locahost -u root -p

It took my to the mysql command prompt

mysql-aliases.cf file contents

user = root
password = abc123
dbname = testdb
table = postfix_alias
select_field = destination
where_field = alias
hosts = 127.0.0.1

my mysql user name is root and my password is abc123. The database I have is called testdb.

thanks for the reply

>>> Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Have you verified your install of MySQL is generating and using the socket file?

Okay, next step :

mysql --socket=/tmp/mysql.sock -h localhost -u root -p

Does this work ?

I know that mysqld demon used /tmp/mysock.sock file to connect to the mysql application.

Since I am able to login with mysqld, will this issue be due to mysql not using the socket file ?

How can I verify this ?

result of mysql --socket=/tmp/mysql.sock -h localhost -u root -p
gives me the below error.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Grrr...that last line should have been:

# /etc/init.d/mysqld start

thanks for your reply

[root@localhost ~]# /etc/init.d/mysqld restart
bash: /etc/init.d/mysqld: No such file or directory

[root@localhost ~]# service mysqld restart
mysqld: unrecognized service

[root@localhost ~]# mv /tmp/mysql.sock ~
mv: cannot stat `/tmp/mysql.sock': No such file or directory

[root@localhost ~]# /usr/local/mysql/bin/mysqld_safe start
Starting mysqld daemon with databases from /usr/local/mysql/data
STOPPING server from pid file /var/run/mysqld/mysqld.pid
070601 03:55:23  mysqld ended

I checked the logfile now, and it says

070601 03:55:23  mysqld started
/usr/local/mysql/libexec/mysqld: Too many arguments (first extra is 'start').
Use --help to get a list of available options
070601 03:55:23  mysqld ended

How do I link the mysqld deomon to the /etc/init.d/mysqld location. Then I think, I can try to restart the server.

thanks
sijith

[root@localhost ~]# /usr/local/mysql/bin/mysqld_safe restart
Starting mysqld daemon with databases from /usr/local/mysql/data
STOPPING server from pid file /var/run/mysqld/mysqld.pid
070601 03:55:11  mysqld ended

same issue...

070601 03:55:11  mysqld started
/usr/local/mysql/libexec/mysqld: Too many arguments (first extra is 'restart').
Use --help to get a list of available options
070601 03:55:11  mysqld ended

That was unexpected...  :)  Are you using an RPM?  How do you normally start your server?  You'll probably want to read this section:

http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html#automatic-start

Does this command stop and start the server:

# mysql.server stop; mysql.server start

Before trying that, though, we need to know where the socket file is.  Use this command to find out:

# mysqladmin variables | grep 'socket'

I installed mysql using the .tar source file mysql-4.1.22.tar.gz and not the rpm version.

Usually I start mysql by the below command

[root@localhost bin]# /usr/local/mysql/bin/mysqld_safe &
[1] 9039
[root@localhost bin]# Starting mysqld daemon with databases from /usr/local/mysql/data

To check , i restarted my machine and tried the below commands

(1) [root@localhost ~]# mysqladmin variables | grep "socket"
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists!

(2)
[root@localhost bin]# /usr/local/mysql/share/mysql/mysql.server start
Starting MySQL      

[root@localhost bin]# /usr/local/mysql/share/mysql/mysql.server stop
Shutting down MySQL..  

[root@localhost bin]# /usr/local/mysql/share/mysql/mysql.server start
Starting MySQL      

(3)  [root@localhost bin]# mysqladmin -u root -p variables | grep "socket"
Enter password:
| socket                          | /var/lib/mysql/mysql.sock              |

As we can see the mysqladmin output, the socket file location is in /var/lib/mysql/mysql.sock

Even though the socket file is in /var/lib/mysql/mysql.sock , our postfix guy complaints
>>> Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

why the /tmp/mysql.sock needs to get involved here if the socket file is already in var/lib/mysql/mysql.sock.

Should we tell postfix to look at /var/lib/mysql/mysql.sock location and is postfix automatically detecting this .

thanks

copy of my.cnf file

[mysqld]
datadir=/usr/local/mysql/data
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

the socket is pointing to var/lib/mysql/mysql.sock file and not to tmp directory.

Even then why postfix is looking for the mysql socket file in /tmp.mysql.sock file.

should I resintall the postfix or will this problem without without that ?
According to my understanding, the postfix installation is fine. Ony that postfix is not able to talk to mysql correctly .

I did the installation by looking this site.

http://www.phparchitecture.com/howto_show.php?id=2

thanks
sijith

Hi all,

I did one thing and it look like one part of the problem is solved. I dont know whether I did is right or wrong

I did a linking of the mysql.sock to the tmp sock file.

ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

now when the stop and star the postfix, it gives me the following log.

Jun  3 10:35:40 localhost postfix/postfix-script[28346]: starting the Postfix mail system
Jun  3 10:35:40 localhost postfix/master[28347]: daemon started -- version 2.4.1, configuration /etc/postfix

So now the postfix is able to know the location of mysql.  To test that postfix is correctly able to check the recipient from mysql, I did the below test

telnet localhost 25

I get the below message

[root@localhost ~]# tail /var/log/maillog
Jun  3 10:37:35 localhost postfix/smtpd[28357]: cfg_get_str: /etc/postfix/mysql-aliases.cf: table = postfix_alias
Jun  3 10:37:35 localhost postfix/smtpd[28357]: cfg_get_str: /etc/postfix/mysql-aliases.cf: select_field = destination
Jun  3 10:37:35 localhost postfix/smtpd[28357]: cfg_get_str: /etc/postfix/mysql-aliases.cf: where_field = alias
Jun  3 10:37:35 localhost postfix/smtpd[28357]: cfg_get_str: /etc/postfix/mysql-aliases.cf: additional_conditions =
Jun  3 10:37:35 localhost postfix/smtpd[28357]: cfg_get_str: /etc/postfix/mysql-aliases.cf: domain =
Jun  3 10:37:35 localhost postfix/smtpd[28357]: cfg_get_str: /etc/postfix/mysql-aliases.cf: hosts = 127.0.0.1
Jun  3 10:37:35 localhost postfix/smtpd[28357]: dict_open: mysql:/etc/postfix/mysql-aliases.cf
Jun  3 10:37:35 localhost postfix/smtpd[28357]: fatal: open dictionary: expecting "type:name" form instead of """"
Jun  3 10:37:36 localhost postfix/master[28347]: warning: process /usr/libexec/postfix/smtpd pid 28357 exit status 1
Jun  3 10:37:36 localhost postfix/master[28347]: warning: /usr/libexec/postfix/smtpd: bad command startup -- throttling

if this is fine, then I think we would be done with the configuration of the postfix with mysql.

thanks

Interesting :)

I had tested that EXACT same thing as a soltuion for you but it didn't work for me! Glad the soft symlink was an answer (gunna hash it out later!)

As for your /etc/postfix/mysql-aliases.cf problem... you really need to resolve this or it will plague you ;)

Can you paste the output of /etc/postfix/mysql-aliases.cf ? it is complaining because it wants a key pair in there and isnt getting it. I'm wondering if you have a blank line after the hosts=127.0.0.1 or some other weirdness....

>>> I did a linking of the mysql.sock to the tmp sock file.
>>> ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

I'm glad that you found a temporary solution, but I want to stress that this is temporary.  I think keeping that symbolic link in the /tmp directory will not be a good idea for the long term.  Continue troubleshooting your postfix installation...I'm almost positive the problem is with its configuration.

Hi all, I have solved the problem after a bit of work

I changed the main.cf file to have the following line (This was initally empty)

virtual_mailbox_maps=mysql:/etc/postfix/mysql-aliases.cf

Also I added the following line to the postfix_users table.
mysql> select * from postfix_access;

+----+--------+--------+-----------+
| id  | source| access | type      |
+----+--------+--------+-----------+
|  1  | *       | OK     | recipient |
+----+--------+--------+-----------+
1 row in set (0.00 sec)

Also I noticed that I am using the postfix 2.4 and this support direct query. So I added this line to the  mysql-aliases.cf file.

query = select email from postfix_users where email='%s'

I also wanted to check whether postfix is trying to connect mysql or not.
For this, I added a line in my.cnf file [mysqld] section

log=/var/log/mysql/mysql.log

With this, any query being done on the mysql will be logged and looking at this I can see whether the problem is within the mysql table or whether the connection is not at all coming to mysql or not.

After everything, here comes my result

[root@localhost ~]# telnet localhost 25
Trying 127.0.0.1...
Connected to locathost (127.0.0.1).
Escape character is '^]'.
220 mail.test.com ESMTP Postfix
EHLO test.com
250-mail.test.com
250-PIPELINING
250-SIZE 10240000
250-VRFY
250-ETRN
250-ENHANCEDSTATUSCODES
250-8BITMIME
250 DSN
MAIL FROM:check@test.com
250 2.1.0 Ok
RCPT TO:admin@test.com
550 5.1.1 <admin@test.com>: Recipient address rejected: User unknown in local recipient table
RCPT TO:root@test.com
250 2.1.5 Ok
DATA
354 End data with <CR><LF>.<CR><LF>
This is my first postfix mail after a bit of hard work. But I like linux, because it just rocks.
.
250 2.0.0 Ok: queued as E81863CD2A8

cool........

One more thing, I also successfully completed the courier imap part of the mail server.  If I have any doubts on it, I post it in the group for you guys help.

Now I understood that if you have patience and support of other techie guys, you can do any configuration stuffs . Just should not give up in the middle.

Thanks to all for helping me out

Sijith

i also used the below site to verify my confgurations.

http://wiki.arslinux.com/Mail_Server:MySQL_Authentication

sijith