Модератор: xM
Код: Выделить всё
Warning: ACL "warn" statement skipped: condition test deferred: MYSQL: query failed: Duplicate entry 'box@moi_domen.ru-box1@domen.com' for key 1
Код: Выделить всё
mysql-client-5.0.27 Multithreaded SQL database (client)
mysql-server-5.0.27 Multithreaded SQL database (server)
Код: Выделить всё
# Закинем в переменные акла наличие в белых списках домена получателя, локальную часть получателя,
# доменную часть получателя
warn set acl_m10 = ${lookup mysql{MYSQL_DOMAINS}}
set acl_m11 = $local_part
set acl_m12 = $domain
# Для наших пользователей записываем наш $sender_address и адрес получателя($local_part@$domain).
# Существуют записи для данной пары src-dst email? Если нет, вернёт 0.
warn
hosts = +relay_from_hosts
condition = ${if and {{eq{$acl_m10}{}}{match_domain{$sender_address_domain}{$qualify_domain}} \
{match_ip{$sender_host_address}{127.0.0.0/8:192.168.0.0/24}}}{yes}{no}}
set acl_m19 = ${lookup mysql{MYSQL_TESTMYLIST}{$value}{0}}
logwrite = "acl_m10=$acl_m10"
logwrite = "acl_m19=$acl_m19 (0-not exist)"
logwrite = "sender_address_domain local?=${if match_domain{$sender_address_domain}{$qualify_domain}{yes}{no}}"
logwrite = "sender_address_ip local?=${if match_ip{$sender_host_address}{127.0.0.0/8:192.168.0.0/24}{yes}{no}}"
logwrite = "sender_address_domain=$sender_address_domain"
logwrite = "sender_address_ip=$sender_host_address"
# Если существует, изменяем поле record_expire
warn
hosts = +relay_from_hosts
condition = ${if and {{eq{$acl_m10}{}}{!eq{$acl_m19}{0}}{match_domain{$sender_address_domain}{$qualify_domain}} \
{match_ip{$sender_host_address}{127.0.0.0/8:192.168.0.0/24}}}{yes}{no}}
set acl_m19 = ${lookup mysql{MYSQL_UPDATEMYLIST}}
logwrite = "update record expire=now"
# Если не существует такой записи, добавляем в базу.
warn
hosts = +relay_from_hosts
condition = ${if and {{eq{$acl_m10}{}}{eq{$acl_m19}{0}}{match_domain{$sender_address_domain}{$qualify_domain}} \
{match_ip{$sender_host_address}{127.0.0.0/8:192.168.0.0/24}}}{yes}{no}}
set acl_m19 = ${lookup mysql{MYSQL_MYLIST}}
logwrite = "new record expire=now"
Код: Выделить всё
INSERT IGNORE INTO mylist(src_email,dst_email,record_expires) VALUES ('$sender_address','$acl_m11@$acl_m12',DATE_ADD(now(), INTERVAL 30 DAY)) ON DUPLICATE KEY UPDATE record_expires=DATE_ADD(now(), INTERVAL 30 DAY))
Вроде тогда контроль за пересечением первичного ключа отдается mysql и можно не волноваться что что-то не отработает. Или я не прав?#
If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued. Data conversions that would trigger errors abort the statement if IGNORE is not specified. With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort. You can determine with the mysql_info() C API function how many rows were actually inserted into the table.
#
If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. The affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated. See Section 12.2.5.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.