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

2018-02-13 Thread David 

On 2018-02-13 10:57 AM, Steve Zeng via Pdns-users wrote:

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?




Bottom half of 
https://doc.powerdns.com/md/authoritative/backend-generic-sql/#regular-queries


___
Pdns-users mailing list
Pdns-users@mailman.powerdns.com
https://mailman.powerdns.com/mailman/listinfo/pdns-users

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

2018-02-13 Thread Steve Zeng via Pdns-users 
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  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

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

2018-02-06 Thread Steve Zeng via Pdns-users 
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

___
Pdns-users mailing list
Pdns-users@mailman.powerdns.com
https://mailman.powerdns.com/mailman/listinfo/pdns-users