[Pdns-users] (PDNSException): Database failed to start transaction: Could not execute mysql statement

[Steve Zeng]

I have our DBAs looked into this and got some findings and feedback:

I see an deadlock for:

(1) = delete from records where domain_id=217

(2) = delete from records where domain_id=217

So something tries to delete everything for domain=217 while other
transactions have a lock on some of the records.

Adding an ORDER BY to the delete query for whole domains might prevent that
from happening.


I searched PowerDNS 4.0.5 source code and find the following occurrences of
the matching SQL statements. are they configurable or we have to patch the
source code in order to test?


    declare(suffix, "remove-empty-non-terminals-from-zone-query", "remove
all empty non-terminals from zone", "delete from records where
domain_id=:domain_id and type is null");

    declare(suffix, "delete-empty-non-terminal-query", "delete empty
non-terminal from zone", "delete from records where domain_id=:domain_id
and name=:qname and type is null");

    declare(suffix, "delete-zone-query", "", "delete from records where
domain_id=:domain_id");

    declare(suffix, "delete-rrset-query", "", "delete from records where
domain_id=:domain_id and name=:qname and type=:qtype");

    declare(suffix, "delete-names-query", "", "delete from records where
domain_id=:domain_id and name=:qname");

    declare(suffix, "remove-empty-non-terminals-from-zone-query", "remove
all empty non-terminals from zone", "delete from records where domain_id=?
and type is null");

    declare(suffix, "delete-empty-non-terminal-query", "delete empty
non-terminal from zone", "delete from records where domain_id=? and name=?
and type is null");

    declare(suffix,"delete-zone-query","", "delete from records where
domain_id=?");

    declare(suffix,"delete-rrset-query","","delete from records where
domain_id=? and name=? and type=?");

    declare(suffix,"delete-names-query","","delete from records where
domain_id=? and name=?");

    declare(suffix, "remove-empty-non-terminals-from-zone-query", "remove
all empty non-terminals from zone", "delete from records where
domain_id=:domain_id and type is null");

    declare(suffix, "delete-empty-non-terminal-query", "delete empty
non-terminal from zone", "delete from records where domain_id=:domain_id
and name=:qname and type is null");

    declare(suffix, "delete-zone-query", "", "delete from records where
domain_id=:domain_id");

    declare(suffix, "delete-rrset-query", "", "delete from records where
domain_id=:domain_id and name=:qname and type=:qtype");

    declare(suffix, "delete-names-query", "", "delete from records where
domain_id=:domain_id and name=:qname");

    declare(suffix, "remove-empty-non-terminals-from-zone-query", "remove
all empty non-terminals from zone", "delete from records where domain_id=?
and type is null");

    declare(suffix, "delete-empty-non-terminal-query", "delete empty
non-terminal from zone", "delete from records where domain_id=? and name=?
and type is null");

    declare(suffix,"delete-zone-query","", "delete from records where
domain_id=?");

    declare(suffix,"delete-rrset-query","","delete from records where
domain_id=? and name=? and type=?");

    declare(suffix,"delete-names-query","","delete from records where
domain_id=? and name=?");

    declare(suffix,"remove-empty-non-terminals-from-zone-query", "remove
all empty non-terminals from zone", "delete from records where domain_id=$1
and type is null");

    declare(suffix,"delete-empty-non-terminal-query", "delete empty
non-terminal from zone", "delete from records where domain_id=$1 and
name=$2 and type is null");

    declare(suffix,"delete-zone-query","", "delete from records where
domain_id=$1");

    declare(suffix,"delete-rrset-query","","delete from records where
domain_id=$1 and name=$2 and type=$3");

    declare(suffix,"delete-names-query","","delete from records where
domain_id=$1 and name=$2");


Thanks,

Steve

On Tue, Feb 6, 2018 at 3:26 PM, Steve Zeng <steve.zeng at booking.com> wrote:

> Hi,
>
> Our PDNS environment is setup on Centos 7 + PDNS Authoritative 4.0.5 + mysql-community
> 5.7
>
> It is noticed that there are regular PowerDNS failure logs below:
>
> 2018-02-04T04:29:07.193742+01:00 mpdns.example.com pdns_server[40761]:
> Feb 04 04:29:07 Unable to AXFR zone ‘lom.example.com' from remote
> ‘x.x.x.x:53' (PDNSException): Database failed to start transaction: Could
> not execute mysql statement: delete from records where domain_id=?: Lock
> wait timeout exceeded; try restarting transaction
>
> I looked at PowerDNS list of settings at https://doc.powerdns.com/
> md/authoritative/backend-generic-mysql/ and do not see any configurable
> settings for it. Our correct pdns.conf can be seen below:
>
> launch=gmysql
> gmysql-host=y.y.y.y
> gmysql-user=auser
> gmysql-dbname=pdns
> gmysql-password=********
> gmysql-timeout=0
>
> not sure if this cause is on PowerDNS side or MySQL database side. can
> anybody shed a light here?
>
> Thanks,
> Steve
>
>


-- 
Steve Zeng
Linux System Administrator

Booking.com (USA) Inc.
220 W. Mercer St. #500 Seattle WA 98119 United States of America
Direct +12062094634
[image: Booking.com] <http://www.booking.com/>
The world's #1 accommodation site
43 languages, 198+ offices worldwide, 120,000+ global destinations,
1,550,000+ room nights booked every day
No booking fees, best price always guaranteed
Subsidiary of the Priceline Group (NASDAQ: PCLN)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.powerdns.com/pipermail/pdns-users/attachments/20180213/788c3803/attachment-0001.html>