[Kea-users] DHCP4_CONFIG_LOAD_FAIL - "getNextRow failed" with MySQL

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

[Kea-users] DHCP4_CONFIG_LOAD_FAIL - "getNextRow failed" with MySQL

Robin Daermann
Dear all,

about 2-3 times a week, I get a strange problem with our Kea setup.

We have two Kea servers, each hosting a MySQL server and database for lease storage.  Both MySQL servers are replicated in both directions, so each Kea server reads and writes from the MySQL database on localhost and they share their lease storage (anycast DHCP solution).

So, the problem is: Sometimes, after a reconfiguration, I get the following error (either for kea-dhcp4 or kea-dhcp6):

--------------------
2019-03-06 07:13:48.379 ERROR [kea-dhcp4.dhcp4/89345] DHCP4_CONFIG_LOAD_FAIL configuration error using file: /usr/local/etc/kea/kea-dhcp4.conf, reason: Error while processing command 'config-set':getNextRow failed for <SELECT subnet_id, state, leases as state_count  FROM lease4_stat ORDER BY subnet_id, state>, reason:  (error code 0), params: '{ "Dhcp4": { "control-socket": {
...
--------------------

I cut off the rest of the JSON configuration, which is syntactically correct.

I can't explain that behaviour. Can anyone point me in the right direction here?

Thank you

Robin Därmann
(Network Operation Center RUB)

--
| Robin Därmann                             Network Operation Center |
| RS18625-RIPE                              Ruhr-Universitaet Bochum |
| Press any key to continue or any other key to quit...              |

_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users

smime.p7s (8K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [Kea-users] DHCP4_CONFIG_LOAD_FAIL - "getNextRow failed" with MySQL

Jason Guy
Hi Robin,

Please specify the Kea version, and what type of reconfiguration is being done.

Jason

On Wed, Mar 6, 2019 at 1:14 PM Robin Daermann <[hidden email]> wrote:
Dear all,

about 2-3 times a week, I get a strange problem with our Kea setup.

We have two Kea servers, each hosting a MySQL server and database for lease storage.  Both MySQL servers are replicated in both directions, so each Kea server reads and writes from the MySQL database on localhost and they share their lease storage (anycast DHCP solution).

So, the problem is: Sometimes, after a reconfiguration, I get the following error (either for kea-dhcp4 or kea-dhcp6):

--------------------
2019-03-06 07:13:48.379 ERROR [kea-dhcp4.dhcp4/89345] DHCP4_CONFIG_LOAD_FAIL configuration error using file: /usr/local/etc/kea/kea-dhcp4.conf, reason: Error while processing command 'config-set':getNextRow failed for <SELECT subnet_id, state, leases as state_count  FROM lease4_stat ORDER BY subnet_id, state>, reason:  (error code 0), params: '{ "Dhcp4": { "control-socket": {
...
--------------------

I cut off the rest of the JSON configuration, which is syntactically correct.

I can't explain that behaviour. Can anyone point me in the right direction here?

Thank you

Robin Därmann
(Network Operation Center RUB)

--
| Robin Därmann                             Network Operation Center |
| RS18625-RIPE                              Ruhr-Universitaet Bochum |
| Press any key to continue or any other key to quit...              |
_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users

_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users
Reply | Threaded
Open this post in threaded view
|

Re: [Kea-users] DHCP4_CONFIG_LOAD_FAIL - "getNextRow failed" with MySQL

Robin Daermann
Hi Jason,

On Wed, Mar 06, 2019 at 02:46:16PM -0500, Jason Guy wrote:
> Please specify the Kea version, and what type of reconfiguration is being
> done.

Sorry, I missed that. Kea version is 1.5.0 and during reconfigurations,
additional host reservations are created. Sometimes, they are changed.

I did two main changes at a time, upgrade from Kea 1.3.0 to Kea 1.5.0
and setup MySQL replication, so unfortunately, I don't know if it's Kea
or MySQL. But I would assume, it's Kea.

Whenever the Kea configuration gets recreated, the process runs on both
our Kea machines independently. Config is generated from our
configuration management automatically and aat roughly the same time,
then Kea gets restarted and with a bit of bad luck, stops working with
the mentioned ERROR on one or both the machines.

Yesterday, I had to clean the complete tables lease4 and lease4_stat to
be able to start the server again, so I guess, there is something with
the processing of leases and not my configuration.

Thanks

Robin Därmann
(Network Operation Center RUB)

--
| Robin Därmann                             Network Operation Center |
| RS18625-RIPE                              Ruhr-Universitaet Bochum |
| Press any key to continue or any other key to quit...              |

_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users

smime.p7s (8K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [Kea-users] DHCP4_CONFIG_LOAD_FAIL - "getNextRow failed" with MySQL

Jason Guy
Hi Robin,

I have had terrible issues with Mysql replication and anycast reachability. The problem may occurring when Kea writes to one server, which will propagate the changes to the other servers. If Kea then reads from the database, but perhaps the SQL connection lands on another host before the SQL replication is completed, it will cause issues. I am about to change my database from mysql replication to a galera cluster. I think I will still continue to set the metric on each server advertising the anycast address to be selective for the primary/secondary/tertiary.

Jason

On Thu, Mar 7, 2019 at 1:27 AM Robin Daermann <[hidden email]> wrote:
Hi Jason,

On Wed, Mar 06, 2019 at 02:46:16PM -0500, Jason Guy wrote:
> Please specify the Kea version, and what type of reconfiguration is being
> done.

Sorry, I missed that. Kea version is 1.5.0 and during reconfigurations,
additional host reservations are created. Sometimes, they are changed.

I did two main changes at a time, upgrade from Kea 1.3.0 to Kea 1.5.0
and setup MySQL replication, so unfortunately, I don't know if it's Kea
or MySQL. But I would assume, it's Kea.

Whenever the Kea configuration gets recreated, the process runs on both
our Kea machines independently. Config is generated from our
configuration management automatically and aat roughly the same time,
then Kea gets restarted and with a bit of bad luck, stops working with
the mentioned ERROR on one or both the machines.

Yesterday, I had to clean the complete tables lease4 and lease4_stat to
be able to start the server again, so I guess, there is something with
the processing of leases and not my configuration.

Thanks

Robin Därmann
(Network Operation Center RUB)

--
| Robin Därmann                             Network Operation Center |
| RS18625-RIPE                              Ruhr-Universitaet Bochum |
| Press any key to continue or any other key to quit...              |
_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users

_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users
Reply | Threaded
Open this post in threaded view
|

Re: [Kea-users] DHCP4_CONFIG_LOAD_FAIL - "getNextRow failed" with MySQL

Robin Daermann
Hi Jason,

On Thu, Mar 07, 2019 at 10:36:03AM -0500, Jason Guy wrote:
>I have had terrible issues with Mysql replication and anycast reachability.
>The problem may occurring when Kea writes to one server, which will
>propagate the changes to the other servers. If Kea then reads from the
>database, but perhaps the SQL connection lands on another host before the
>SQL replication is completed, it will cause issues.

I got around this specific issue with specifying

  slave-skip-errors = 1062

in the MySQL/MariaDB configuration. 1062 is "Duplicate Key error".

> I am about to change my
>database from mysql replication to a galera cluster. I think I will still
>continue to set the metric on each server advertising the anycast address
>to be selective for the primary/secondary/tertiary.

Since the last occurence of my problem, when I wrote the initial mail to
the list, the mentioned error never happened again. And we had lots of
configuration changes in the last two weeks (about 50). So I would
assume, that my problem got solved by simply deleting the lease
information from the database completely. It could definitely be, that I
screwed sometinh up during migration from standalone to replicated
database/anycast setup.

Thank you for listening to me.

Mit freundlichen Grüßen
Robin Därmann
(Network Operation Center RUB)

--
| Robin Därmann                             Network Operation Center |
| RS18625-RIPE                              Ruhr-Universitaet Bochum |
| Press any key to continue or any other key to quit...              |

_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users

smime.p7s (8K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [Kea-users] DHCP4_CONFIG_LOAD_FAIL - "getNextRow failed" with MySQL

Carlos Carluccio
Robin, how you doing the insertion on the database? Because you should only add leases to a single kea server.  Sounds like the slave might be writing in the database too.

Is possible you share a dump of the database to see?

Carlos

On Mar 19, 2019 05:37, Robin Daermann <[hidden email]> wrote:
*** THIS EMAIL ORIGINATED OUTSIDE THE ORGANIZATION ***

Hi Jason,

On Thu, Mar 07, 2019 at 10:36:03AM -0500, Jason Guy wrote:
>I have had terrible issues with Mysql replication and anycast reachability.
>The problem may occurring when Kea writes to one server, which will
>propagate the changes to the other servers. If Kea then reads from the
>database, but perhaps the SQL connection lands on another host before the
>SQL replication is completed, it will cause issues.

I got around this specific issue with specifying

  slave-skip-errors = 1062

in the MySQL/MariaDB configuration. 1062 is "Duplicate Key error".

> I am about to change my
>database from mysql replication to a galera cluster. I think I will still
>continue to set the metric on each server advertising the anycast address
>to be selective for the primary/secondary/tertiary.

Since the last occurence of my problem, when I wrote the initial mail to
the list, the mentioned error never happened again. And we had lots of
configuration changes in the last two weeks (about 50). So I would
assume, that my problem got solved by simply deleting the lease
information from the database completely. It could definitely be, that I
screwed sometinh up during migration from standalone to replicated
database/anycast setup.

Thank you for listening to me.

Mit freundlichen Grüßen
Robin Därmann
(Network Operation Center RUB)

--
| Robin Därmann                             Network Operation Center |
| RS18625-RIPE                              Ruhr-Universitaet Bochum |
| Press any key to continue or any other key to quit...              |

_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users
Reply | Threaded
Open this post in threaded view
|

Re: [Kea-users] DHCP4_CONFIG_LOAD_FAIL - "getNextRow failed" with MySQL

vm0001
In reply to this post by Robin Daermann
Hi! And thanks, clearing the lease4_stat table, helped, in my case.
Some details:
According to error message there could be a problem  in the following query
"SELECT subnet_id, state, leases as state_count  FROM lease4_stat ORDER BY
subnet_id, state", respectively, run it directly in mysql.
As a result, I obtained some subnet_ids which had -1 value for leases column
and some subnets having this value equal 0.
After delete those rows:
"delete from lease4_stat where leases<1;" , the kea-dhcp4 started
successfully.



--
Sent from: http://kea-users.7364.n8.nabble.com/
_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users
Reply | Threaded
Open this post in threaded view
|

Re: [Kea-users] DHCP4_CONFIG_LOAD_FAIL - "getNextRow failed" with MySQL

flingo
This post was updated on .
Exactly the same here: the Kea DHCP server setup worked for weeks and all
over sudden, after a configuration change / restart the servers refused to
start with the mentioned error.

DELETE FROM lease4_stat where leases < 0

solved the issue so far. Since I don't want to wait for the next occurence
of this quite unfrequent issue, I changed the table description of lease4_stat,
so that field 'leases' is now 'BIGINT(20) UNSIGNED NULL DEFAULT NULL'. This
should prevent any further negative values here. Hope this doesn't break the code anywhere
else. Let's wait and see ...

Here is my setup:
- 2 CentOS7 machines
- each running KEA DHCP 1.5.0 and
- MYSQL 5.7.x Leases Database
- MYSQL Master-Master-Replication
- KEA HA / Failover (not load balancing) w/ send-lease-updates and
sync-leases set to false (i.e. leases are replicated via MySQL replication)

UPDATE:
it turned out that changing 'leases' to  'BIGINT(20) UNSIGNED NULL DEFAULT NULL' will yield another error later on during lease reclamation:

ERROR [kea-dhcp4.alloc-engine/8838] ALLOC_ENGINE_V4_LEASE_RECLAMATION_FAILED failed to reclaim the lease 10.214.89.130: unable to execute for <UPDATE lease4 SET address = ?, hwaddr = ?, client_id = ?, valid_lifetime = ?, expire = ?, subnet_id = ?, fqdn_fwd = ?, fqdn_rev = ?, hostname = ?, state = ?, user_context = ? WHERE address = ?>, reason: BIGINT UNSIGNED value is out of range in '(`keadhcp4`.`lease4_stat`.`leases` - 1)' (error code 1690)


So, switched back to BIGINT signed and now I'm getting negative values again. I guess this will crash the server during next restart ...

UPDATE 2:

I found the DB triggers being responsible for the lease4_stat table:
- stat_lease4_delete
- stat_lease4_insert
- stat_lease4_update

In stat_lease4_delete and stat_lease4_update I modified the update statement for lease count decrements:
            UPDATE lease4_stat SET leases = GREATEST(leases - 1, 0)
            WHERE subnet_id = OLD.subnet_id AND OLD.state = state;
This will not solve the issue, but at least it will prevent negative leases counts in lease4_stat, which would cause the next restart to fail.

--
Sent from: http://kea-users.7364.n8.nabble.com/
_______________________________________________
Kea-users mailing list
Kea-users@lists.isc.org
https://lists.isc.org/mailman/listinfo/kea-users
Reply | Threaded
Open this post in threaded view
|

Re: [Kea-users] DHCP4_CONFIG_LOAD_FAIL - "getNextRow failed" with MySQL

Fernando Ruza Rodriguez
The same it happened to me today. Delete records with leases < 0 solved the problem.

One question, Who updates this table? a kea-dhcp proccess, a trigger?. When is it updated?. In my case, it doesn't show the real and updated information.

Thanks for any help. Regards,

Fernando.


-----Mensaje original-----
De: flingo <[hidden email]>
Para: [hidden email]
Asunto: Re: [Kea-users] DHCP4_CONFIG_LOAD_FAIL - "getNextRow failed" with MySQL
Fecha: Mon, 1 Jul 2019 13:14:28 -0700 (MST)

Exactly the same here: the Kea DHCP server setup worked for weeks and all
over sudden, after a configuration change / restart the servers refused to
start with the mentioned error. 

DELETE FROM lease4_stat where leases < 0 

solved the issue so far. Since I don't want to wait for the next occurence
of quite unfrequent issue, I changed the table description of lease4_stat,
so that field 'leases' is now 'BIGINT(20) UNSIGNED NULL DEFAULT NULL'. This
should prevent any further negative values. Hope this doesn't break the code
elsewhere. Let's wait and see ...

Here is my setup:
- 2 CentOS7 machines 
- each running KEA DHCP 1.5.0 and 
- MYSQL 5.7.x Leases Database 
- MYSQL Master-Master-Replication
- KEA HA / Failover (not load balancing) w/ send-lease-updates and
sync-leases set to false (i.e. leases are replicated via MySQL replication)




--
Sent from: http://kea-users.7364.n8.nabble.com/
_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users

_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users
Reply | Threaded
Open this post in threaded view
|

Re: [Kea-users] DHCP4_CONFIG_LOAD_FAIL - "getNextRow failed" with MySQL

Thomas Markwalder
The stats tables are updated by triggers on the lease tables.  If replication mechanisms are not statement-based, i.e. cause trigger execution, these values can certainly be wrong.

On 7/9/19 9:10 AM, Fernando Ruza Rodriguez wrote:
The same it happened to me today. Delete records with leases < 0 solved the problem.

One question, Who updates this table? a kea-dhcp proccess, a trigger?. When is it updated?. In my case, it doesn't show the real and updated information.

Thanks for any help. Regards,

Fernando.


-----Mensaje original-----
De: flingo <[hidden email]>
Para: [hidden email]
Asunto: Re: [Kea-users] DHCP4_CONFIG_LOAD_FAIL - "getNextRow failed" with MySQL
Fecha: Mon, 1 Jul 2019 13:14:28 -0700 (MST)

Exactly the same here: the Kea DHCP server setup worked for weeks and all
over sudden, after a configuration change / restart the servers refused to
start with the mentioned error. 

DELETE FROM lease4_stat where leases < 0 

solved the issue so far. Since I don't want to wait for the next occurence
of quite unfrequent issue, I changed the table description of lease4_stat,
so that field 'leases' is now 'BIGINT(20) UNSIGNED NULL DEFAULT NULL'. This
should prevent any further negative values. Hope this doesn't break the code
elsewhere. Let's wait and see ...

Here is my setup:
- 2 CentOS7 machines 
- each running KEA DHCP 1.5.0 and 
- MYSQL 5.7.x Leases Database 
- MYSQL Master-Master-Replication
- KEA HA / Failover (not load balancing) w/ send-lease-updates and
sync-leases set to false (i.e. leases are replicated via MySQL replication)




--
Sent from: http://kea-users.7364.n8.nabble.com/
_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users

_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users


_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users