[Kea-users] migrating from DHCPd to Kea

[Darren Ankney]

Hi Brendan,

The ? are due to mysql prepared statements.  See here:
https://dev.mysql.com/doc/refman/8.4/en/sql-prepared-statements.html

On Tue, Oct 22, 2024 at 1:59 PM Brendan Kearney <bpk678 at gmail.com> wrote:
>
> On 4/17/24 5:49 AM, Peter Davies wrote:
>
> Hi Brendan,
>    It is not possible to see any details from this logging.
>
> You should check your haproxy configuration, Kea cannot continue processing
> requests if it is unable to connect to the lease database.
>
> I suggest that you temporarily enable Severity DEBUG and debuglevel 99 so you
> can see requests and how Kea is processing them.
>
>  As all your reservations are global, you should define
>  "reservations-global": true,"
>  the default is false.
>
>  All pools are guarded by client classes. The client may not be associated with
>  any of these classes.
>
> /Peter
>
>
> digging back into the database issue, i found the following messages in "journactl -lu kea-dhcp4":
>
> Oct 15 20:34:43 server1 systemd[1]: Started kea-dhcp4.service - Kea DHCPv4 Server.
> Oct 15 20:34:43 server1 kea-dhcp4[1930]: 2024-10-15 20:34:43.848 INFO  [kea-dhcp4.dhcp4/1930.140675063382400] DHCP4_STARTING Kea DHCPv4 server version 2.4.0 (stable) starting
> Oct 16 03:24:44 server1 kea-dhcp4[1930]: 2024-10-16 03:24:44.303 ERROR [kea-dhcp4.database/1930.140675063382400] DATABASE_MYSQL_FATAL_ERROR Unrecoverable MySQL error occurred: unable to execute for <SELECT  a.id,  a.object_type,  a.object_id,  a.modification_type,  r.modification_ts,  r.id,   r.log_message FROM dhcp4_audit AS a INNER JOIN dhcp4_audit_revision AS r   ON a.revision_id = r.id INNER JOIN dhcp4_server AS s  ON r.server_id = s.id WHERE (s.tag = ? OR s.id = 1) AND ((r.modification_ts, r.id) > (?, ?)) ORDER BY r.modification_ts, r.id>, reason: Lost connection to server during query (error code: 2013).
> Oct 16 03:24:44 server1 kea-dhcp4[1930]: 2024-10-16 03:24:44.303 ERROR [kea-dhcp4.dhcp4/1930.140675063382400] DHCP4_CB_PERIODIC_FETCH_UPDATES_FAIL error on periodic attempt to fetch configuration updates from the configuration backend(s): fatal database error or connectivity lost
> Oct 16 03:24:44 server1 systemd[1]: kea-dhcp4.service: Main process exited, code=exited, status=1/FAILURE
> Oct 16 03:24:44 server1 systemd[1]: kea-dhcp4.service: Failed with result 'exit-code'.
> Oct 16 03:24:44 server1 systemd[1]: kea-dhcp4.service: Consumed 14.206s CPU time.
>
> there is a select statement in the logs that failed and killed the instance.  i took the select statement in the logs and ran it in a mariadb client, phpMyAdmin.  when i submitted the query, i got the following error info back:
>
> Error
>
> Static analysis:
>
> 3 errors were found during analysis.
>
>     Variable name was expected. (near "?" at position 261)
>     Variable name was expected. (near "?" at position 310)
>     Variable name was expected. (near "?" at position 313)
>
> SQL query: Copy Documentation
>
> SELECT a.id, a.object_type, a.object_id, a.modification_type, r.modification_ts, r.id, r.log_message FROM dhcp4_audit AS a INNER JOIN dhcp4_audit_revision AS r ON a.revision_id = r.id INNER JOIN dhcp4_server AS s ON r.server_id = s.id WHERE (s.tag = ? OR s.id = 1) AND ((r.modification_ts, r.id) > (?, ?)) ORDER BY r.modification_ts, r.id LIMIT 0, 25
>
> MySQL said: Documentation
> #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? OR s.id = 1) AND ((r.modification_ts, r.id) > (?, ?)) ORDER BY r.modificati...' at line 1
>
> the problem is not my database or connectivity to it.  the issues is a malformed query, that uses question marks ( ? ) which seem to be invalid characters.  i logged into the database using the mysql command line client and issued the same query, to be sure the error was not related to something in phpMyAdmin, and i got the following error info back:
>
> MariaDB [kea]> SELECT  a.id,  a.object_type,  a.object_id,  a.modification_type,  r.modification_ts,  r.id,   r.log_message FROM dhcp4_audit AS a INNER JOIN dhcp4_audit_revision AS r   ON a.revision_id = r.id INNER JOIN dhcp4_server AS s  ON r.server_id = s.id WHERE (s.tag = ? OR s.id = 1) AND ((r.modification_ts, r.id) > (?, ?)) ORDER BY r.modification_ts, r.id;
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? OR s.id = 1) AND ((r.modification_ts, r.id) > (?, ?)) ORDER BY r.modificati...' at line 1
>
> from the looks of things, there is a malformed query and the error causes the kea instance to fail.  is there a "dialect" issue with the way the db query is formulated/constructed in kea for mariadb vs postgresql?  i dont have a postgresql instance handy to test against.  given the ERROR, not WARNING, status of the log message, i would assume that the malformed query causes the resulting failure of the instance because kea cannot continue.  how do i figure a way past these errors?
>
> thanks,
>
> brendan
>
> --
> ISC funds the development of this software with paid support subscriptions. Contact us at https://www.isc.org/contact/ for more information.
>
> To unsubscribe visit https://lists.isc.org/mailman/listinfo/kea-users.
>
> Kea-users mailing list
> Kea-users at lists.isc.org
> https://lists.isc.org/mailman/listinfo/kea-users