[Kea-users] migrating from DHCPd to Kea

[Brendan Kearney]

On 10/25/24 6:28 AM, Darren Ankney wrote:
> 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

since i am using MariaDB, does that make a difference?  MariaDB does 
have a prepared statement capability, but are there differences between 
MySQL and MariaDB? https://mariadb.com/kb/en/prepared-statements/  
clearly, there is something askew... are there "dialects" for MySQL and 
MariaDB, where i should be specifying MariaDB, instead of MySQL?

thanks,

brendan