Mysql tip of the day postfix virtual address

By markus. Filed in Mailserver  |  
TOP del.icio.us digg

I had some struggle with mailalias in a postfix-mysql.

Several tables with different type of user positions and addresses to combine.

i one table I have user and up to three diffrent fordwardingalias, in one other diffrent peoples positions in an organization, well

setup is ubuntuserver with postfix, mysql, postfixadmin

I cooked it down to
1. pic users setting from a joomla cms
2. pic users position from a diffrent organisation-menberdatabase
3. build various combinations of alias
4. check that user i still registered and valid

I run a cron for update data from and to mysql.
hourly run bashscript:

 

mysql -uuser -ppassword < /scripts/uppdatealias.cmd -vvv > /scripts/uppdated.tmp
sleep 1
< /scripts/uppdated.tmp mail -s “Uppdated alias” markus@example.org
sleep 1
rm /scripts/uppdated.tmp

 

uppdatealias.cmd:

use postfix;
SET SESSION group_concat_max_len = 8192 ;
UPDATE `alias` set `goto` = (SELECT group_concat(email) FROM databas1.org WHERE id < ’99’ AND databas1.org.email > ”) WHERE address=’division@example.org’;

UPDATE `alias` set `goto` = (SELECT group_concat(f) FROM (
SELECT CONCAT(databas1.org.user.namnalias,’@example.org’) AS f
FROM databas1.org.user, databas1.org.pos WHERE databas1.org.pos.id = databas1.org.user.username AND databas1.org.user.namnalias > ”
AND databas1.org.pos.befid = 1
UNION
SELECT databas1.org.user.email1 AS f
FROM databas1.org.user, databas1.org.pos WHERE databas1.org.pos.id = databas1.org.user.username AND databas1.org.user.namnalias = ”
AND databas1.org.user.email1 > ”
AND databas1.org.pos.befid = 1
) as T ) WHERE address=’all.f.vd@example.org’;

 

for postfix-mysql I want select my addresses i one row separated with commas, group_concat solves that
I have some users that dows not have an extra mailalias set and therefor the field ‘namnalias’  is empty but if there is data in email1 I want to select that. Perhaps there is other solutions but I found that with UNION I can combine two or more selects and at same time it remove duplicates.

And then I combine group_concat with my union with use of  AS and update my premade organization.positions mailaddresses

in mysql default group_concat is limit to 1024,
SET SESSION group_concat_max_len = 4096 ;
will increase that 4 times

 

Comments are closed.