I was looking for a possibility to update a field of a mysql row on a successful SELECT
Background: I have a mysql table that lists networks which should be denied by my postfix servers. Per row one network with a field active
which contains a timestamp. So I wanted to update this field on successful SELECT
, that way I could find records which are not used anymore for a longer time.
The only way I found is to create a PROCEDURE
on the database, which first performs an UPDATE
of the timestamp field and then do the SELECT
for postfix. In my case the table is `postfix`.`blacklist-cidr`
mysql -uroot -psecret delimiter // CREATE PROCEDURE postfix.block (IN ip VARCHAR(15)) BEGIN UPDATE `blacklist-cidr` SET `active` = NOW() WHERE INET_ATON(ip) BETWEEN network AND broadcast; SELECT 'reject' FROM `blacklist-cidr` WHERE INET_ATON(ip) BETWEEN network AND broadcast; END; // delimiter ;
After that postfix can execute this PROCEDURE
the following way
hosts = 10.66.99.2 10.66.99.1 user = postfix password = secret dbname = postfix query = CALL postfix.block('%s')