Kea issueshttps://gitlab.isc.org/isc-projects/kea/-/issues2023-06-19T10:40:00Zhttps://gitlab.isc.org/isc-projects/kea/-/issues/2856mysql v4 backend slowing down while using random and flq allocator2023-06-19T10:40:00ZWlodzimierz Wencelmysql v4 backend slowing down while using random and flq allocatorIt looks like Kea performance is hugely impacted when random or flq allocator is used. Screen of the charts that show performance degradation are attached. Results are generated using code from isc-projects/kea#2843
![Screenshot_2023-05...It looks like Kea performance is hugely impacted when random or flq allocator is used. Screen of the charts that show performance degradation are attached. Results are generated using code from isc-projects/kea#2843
![Screenshot_2023-05-11_at_13.56.08](/uploads/8fd6f88e0cd8078c130c11b224ea749c/Screenshot_2023-05-11_at_13.56.08.png)
![Screenshot_2023-05-11_at_13.56.38](/uploads/7a82938d13b13dec1b7c80388f3b4b1c/Screenshot_2023-05-11_at_13.56.38.png)
Issue is not observed in v6 random allocator:
![Screenshot_2023-05-11_at_14.01.09](/uploads/39181cd28e2e1fad03c70e2319a88fc1/Screenshot_2023-05-11_at_14.01.09.png)
[full internal report](https://jenkins.aws.isc.org/view/Kea-manual/job/kea-manual/job/performance/94/artifact/qa-dhcp/kea/performance-jenkins/report.html) (it's heavy, please wait patiently for it to load)
to check all allocator related tests please go to tab `Resource Consumption`, allocators tests starts at Scenario 7.
Mysql related scenarios:
* v4: 10, 11, 12, 19, 20, 21, 28, 29, 30
* v6: 39, 40, 45, 46, 51, 52
(number of tests will be reduced for regular monthly runs)
Additionally I should mention that in previous runs (master) I observed issues with iterative allocator in v4 mysql as well. Looked like kea stops assigning leases after assigning ~6mln leases, but I couldn't reproduce it on isc-projects/kea#2843 (I will repeat those tests on master overnight)
![Screenshot_2023-05-11_at_14.11.01](/uploads/6da24ed37a758e3e86653a31a2b2fbb1/Screenshot_2023-05-11_at_14.11.01.png)next-stable-2.6Marcin SiodelskiMarcin Siodelskihttps://gitlab.isc.org/isc-projects/kea/-/issues/3064performance drop during 2.3 release cycle for mysql2023-09-21T13:53:17ZWlodzimierz Wencelperformance drop during 2.3 release cycle for mysqlWe observed huge performance drop during 2.3 release cycle, Exactly between 2.3.8 and 2.3.9
![Screenshot_2023-09-14_at_09.55.50](/uploads/06cdfb127f6609246e68c58d63663a2e/Screenshot_2023-09-14_at_09.55.50.png)
![Screenshot_2023-09-14_at...We observed huge performance drop during 2.3 release cycle, Exactly between 2.3.8 and 2.3.9
![Screenshot_2023-09-14_at_09.55.50](/uploads/06cdfb127f6609246e68c58d63663a2e/Screenshot_2023-09-14_at_09.55.50.png)
![Screenshot_2023-09-14_at_09.57.01](/uploads/d9fb7fbabb11d49faa3e40efbd6f4bac/Screenshot_2023-09-14_at_09.57.01.png)
Please check [report on master](https://jenkins.aws.isc.org/job/kea-dev/job/performance/lastSuccessfulBuild/artifact/qa-dhcp/kea/performance-jenkins/report.html), during this work I was testing migration to binary addresses in mysql and this did not show performance degradation in [the report](https://jenkins.aws.isc.org/view/Kea-manual/job/kea-manual/job/performance/108/artifact/qa-dhcp/kea/performance-jenkins/report.html)next-stable-2.6https://gitlab.isc.org/isc-projects/kea/-/issues/2479Documentation - upgrading Kea servers with a common DB backend2023-07-31T13:34:54ZPeter DaviesDocumentation - upgrading Kea servers with a common DB backend**Documentation - upgrading Kea servers with a common DB backend**
Kea implementations where servers do not share common databases Kea may be upgraded individually.
In this way the down time of dhcp services may be limited.
Some u...**Documentation - upgrading Kea servers with a common DB backend**
Kea implementations where servers do not share common databases Kea may be upgraded individually.
In this way the down time of dhcp services may be limited.
Some users employ a common database backend for leases and/or configuration data.
As Kea software upgrades normally increment database schema versions, individual upgrades may have unfortunate side effects.
We would like advice regarding this type of upgrade added to:
4.3.2.2 Upgrading a MySQL Database From an Earlier Version of Kea
4.3.3.3 Upgrading a PostgreSQL Database From an Earlier Version of Keanext-stable-2.6https://gitlab.isc.org/isc-projects/kea/-/issues/2983mysql: Deprecated program name. It will be removed in a future release, use '...2023-08-10T13:48:04ZAndrei Pavelandrei@isc.orgmysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' insteadSome unit tests started showing these warnings:
```
[ RUN ] mysql.db-init
Wiping whole database keatest...
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
mysql: Deprecated pro...Some unit tests started showing these warnings:
```
[ RUN ] mysql.db-init
Wiping whole database keatest...
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
Wiping whole database keatest...
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
[ OK ] mysql.db-init
```
The suggestion is to follow the warning's advice and replace the mysql client invocation with mariadb.
I used this version of the mysql client:
```
$ mysql --version
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
mysql from 11.0.2-MariaDB, client 15.2 for Linux (x86_64) using readline 5.1
$ mariadb --version
mariadb from 11.0.2-MariaDB, client 15.2 for Linux (x86_64) using readline 5.1
```backloghttps://gitlab.isc.org/isc-projects/kea/-/issues/2703unit tests failure with mysql 5.7.39 and freebsd 12.2023-01-26T16:10:46ZWlodzimierz Wencelunit tests failure with mysql 5.7.39 and freebsd 12.unit test failures:
* https://jenkins.aws.isc.org/job/kea-dev/job/ut-extended/lastCompletedBuild/testReport/(root)/MySqlConfigBackendDHCPv6Test/run_tests___freebsd_12_1_amd64___freebsd_12_1_amd64_results___globalOptions6WithServerTagsTes...unit test failures:
* https://jenkins.aws.isc.org/job/kea-dev/job/ut-extended/lastCompletedBuild/testReport/(root)/MySqlConfigBackendDHCPv6Test/run_tests___freebsd_12_1_amd64___freebsd_12_1_amd64_results___globalOptions6WithServerTagsTest/
* https://jenkins.aws.isc.org/job/kea-dev/job/ut-extended/lastCompletedBuild/testReport/(root)/MySqlConfigBackendDHCPv6Test/run_tests___freebsd_12_1_amd64___freebsd_12_1_amd64_results___globalOptions6WithServerTagsTest/
* https://jenkins.aws.isc.org/job/kea-dev/job/ut-extended/lastCompletedBuild/testReport/(root)/MySqlConfigBackendDHCPv6Test/run_tests___freebsd_12_1_amd64___freebsd_12_1_amd64_results___unassignedSubnet6Test/
```
19:27:28 MySQL:
19:27:28 MYSQL_VERSION: 5.7.39
19:27:28 MYSQL_CPPFLAGS: -I/usr/local/include/mysql
19:27:28 MYSQL_LIBS: -L/usr/local/lib/mysql -lmysqlclient -lpthread -lm -lrt -lexecinfo -lssl -lcrypto -lssl -lcrypto
```
test report: https://jenkins.aws.isc.org/job/kea-dev/job/ut-extended/957/testReport/
Most of systems we are using at the build farm are using mysql version 8 or higher. Except centos 7 (5.5.68) and ubuntu 18.04 (5.7.39) but tests are not failing there.backloghttps://gitlab.isc.org/isc-projects/kea/-/issues/2373Examples / documentation for MySQL support of SSL/TLS on Debian2022-11-02T15:10:41ZFrancis DupontExamples / documentation for MySQL support of SSL/TLS on DebianOn Debian the support of SSL/TLS in MySQL does not use OpenSSL but GnuTLS as the inspection of the have_openssl MySQL global variable shows. This breaks the examples on at least two points:
- the private keys must be in PKCS#1 aka RSA f...On Debian the support of SSL/TLS in MySQL does not use OpenSSL but GnuTLS as the inspection of the have_openssl MySQL global variable shows. This breaks the examples on at least two points:
- the private keys must be in PKCS#1 aka RSA format
- the choice of the cipher fails for a reason to determine
Here's [KB article](https://kb.isc.org/docs/securing-the-mysql-connection) about securing MySQL connections using OpenSSL. Maybe this could be extended with Debian specific stuff or a separate (similar) one could be written.backloghttps://gitlab.isc.org/isc-projects/kea/-/issues/2230Optionally put the database password in a file2023-08-21T14:05:56ZFrancis DupontOptionally put the database password in a fileExtension of #2006 to database configuration. With #34 aka database communication over SSL/TLS this will greatly improve security.Extension of #2006 to database configuration. With #34 aka database communication over SSL/TLS this will greatly improve security.backloghttps://gitlab.isc.org/isc-projects/kea/-/issues/1645Changed title: migrate TIMESTAMP to DATETIMEs to support dates past 2038?2022-11-02T15:10:18ZAndrei Pavelandrei@isc.orgChanged title: migrate TIMESTAMP to DATETIMEs to support dates past 2038?For native MariaDB 10.5.8, `MySqlLeaseMgrTest.maxDate*` tests pass.
When starting MariaDB 10.5.8 in docker with [this script](https://github.com/andrei-pavel/scripts/blob/master/scripts/docker/start-mysql), they fail:
<details>
<summary...For native MariaDB 10.5.8, `MySqlLeaseMgrTest.maxDate*` tests pass.
When starting MariaDB 10.5.8 in docker with [this script](https://github.com/andrei-pavel/scripts/blob/master/scripts/docker/start-mysql), they fail:
<details>
<summary>$ ./src/lib/dhcpsrv/tests/libdhcpsrv_unittests --gtest_filter='MySqlLeaseMgrTest.maxDate*'</summary>
<pre>
Note: Google Test filter = MySqlLeaseMgrTest.maxDate*
[==========] Running 4 tests from 1 test suite.
[----------] Global test environment set-up.
[----------] 4 tests from MySqlLeaseMgrTest
[ RUN ] MySqlLeaseMgrTest.maxDate4
wipeMySQLData failed:[sh /home/andrei/work/isc/kea/src/share/database/scripts/mysql/wipe_data.sh 9.5 -N -B --user=keatest --password=keatest keatest 2>/dev/null ]
test_utils.cc:54: Failure
Expected equality of these values:
first->cltt_
Which is: 2147483647
second->cltt_
Which is: 1610629470
[ FAILED ] MySqlLeaseMgrTest.maxDate4 (1679 ms)
[ RUN ] MySqlLeaseMgrTest.maxDate4MultiThreading
test_utils.cc:54: Failure
Expected equality of these values:
first->cltt_
Which is: 2147483647
second->cltt_
Which is: 1610629470
[ FAILED ] MySqlLeaseMgrTest.maxDate4MultiThreading (56 ms)
[ RUN ] MySqlLeaseMgrTest.maxDate6
test_utils.cc:87: Failure
Expected equality of these values:
first->cltt_
Which is: 2147483647
second->cltt_
Which is: 1610629470
[ FAILED ] MySqlLeaseMgrTest.maxDate6 (56 ms)
[ RUN ] MySqlLeaseMgrTest.maxDate6MultiThreading
test_utils.cc:87: Failure
Expected equality of these values:
first->cltt_
Which is: 2147483647
second->cltt_
Which is: 1610629470
[ FAILED ] MySqlLeaseMgrTest.maxDate6MultiThreading (56 ms)
[----------] 4 tests from MySqlLeaseMgrTest (1847 ms total)
[----------] Global test environment tear-down
[==========] 4 tests from 1 test suite ran. (1847 ms total)
[ PASSED ] 0 tests.
[ FAILED ] 4 tests, listed below:
[ FAILED ] MySqlLeaseMgrTest.maxDate4
[ FAILED ] MySqlLeaseMgrTest.maxDate4MultiThreading
[ FAILED ] MySqlLeaseMgrTest.maxDate6
[ FAILED ] MySqlLeaseMgrTest.maxDate6MultiThreading
4 FAILED TESTS
</pre>
</details>
The date that is returned is the current time. When looking into the database, the date that is inserted inside the test is MAX_DATE. When doing a manual select via `mysql` client, it is MAX_DATE. So the problem must be on the SELECT statement when using the MySQL libs which were the same version when I tested `mariadb-libs 10.5.8`.backloghttps://gitlab.isc.org/isc-projects/kea/-/issues/1099Support for hostname-char like parameters in the CB2022-11-02T15:10:19ZMarcin SiodelskiSupport for hostname-char like parameters in the CBThe ticket #946 added support for some DDNS specific parameters into the Config Backend. However, it did not include the following two:
- hostname-char-replacement
- hostname-char-set
Those two must be added as well at global, subnet an...The ticket #946 added support for some DDNS specific parameters into the Config Backend. However, it did not include the following two:
- hostname-char-replacement
- hostname-char-set
Those two must be added as well at global, subnet and shared network level.backloghttps://gitlab.isc.org/isc-projects/kea/-/issues/349Restricting timestamps in Kea backends to max supported values2022-11-02T15:08:42ZMarcin SiodelskiRestricting timestamps in Kea backends to max supported valuesThe following chapter of the Kea User's Guide `Limitations Related to the use of SQL Databases` claims that we restricted timestamps to 32 bit value past the epoch. I don't see us restricting this anywhere. Maybe we should revise which d...The following chapter of the Kea User's Guide `Limitations Related to the use of SQL Databases` claims that we restricted timestamps to 32 bit value past the epoch. I don't see us restricting this anywhere. Maybe we should revise which databases have which limitation and explicitly report an error upon an attempt to add higher timestamps.backloghttps://gitlab.isc.org/isc-projects/kea/-/issues/792quality of life improvement: kea-admin db-version fails on empty db2022-03-31T08:12:51ZTomek Mrugalskiquality of life improvement: kea-admin db-version fails on empty dbkea-admin db-version prints the following error:
```
# kea-admin db-version mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1146 (42S02) at line 1: Table 'keatest.schema_version' doesn't exis...kea-admin db-version prints the following error:
```
# kea-admin db-version mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1146 (42S02) at line 1: Table 'keatest.schema_version' doesn't exist
```
when run on an empty DB (without any schema).
Instead, it should catch the fact that schema_version does not exist and should point user to kea-admin db-init command.
This is a quality of life improvement, so it's not terribly important.outstandinghttps://gitlab.isc.org/isc-projects/kea/-/issues/739More metadata checks in MySQL CB unit tests2019-08-08T16:23:54ZFrancis DupontMore metadata checks in MySQL CB unit testsAs we already do in CB cmds hook add more metadata checks in MySQL CB unit tests (reference https://gitlab.isc.org/isc-projects/kea/merge_requests/424#note_68391)As we already do in CB cmds hook add more metadata checks in MySQL CB unit tests (reference https://gitlab.isc.org/isc-projects/kea/merge_requests/424#note_68391)outstandinghttps://gitlab.isc.org/isc-projects/kea/-/issues/3013Prepared statement needs to be re-prepared2023-08-24T13:27:42ZYehuda KatzPrepared statement needs to be re-prepared**Describe the bug**
This probably doesn't qualify as a Kea bug, but I thought it might be worth asking about.
We are running 2 kea servers with a shared MySQL (MariaDB) backend. We recently upgraded from MariaDB 10.3 to MariaDB 10.5.
W...**Describe the bug**
This probably doesn't qualify as a Kea bug, but I thought it might be worth asking about.
We are running 2 kea servers with a shared MySQL (MariaDB) backend. We recently upgraded from MariaDB 10.3 to MariaDB 10.5.
We replaced the `hosts` table with a `hosts_history` TABLE (added a `deleted_at` column to allow for keeping host history) and a `hosts` VIEW that only shows non-deleted items. This has been working for us for many years.
Everything seems to work for a while until randomly, we start getting errors for systems with host reservations.
The error in the log is:
```
2023-08-14 18:52:59.613 DEBUG [kea-dhcp4.bad-packets/169655.139690552555648] DHCP4_PACKET_DROP_0007 [hwtype=1 xx:xx:xx:xx:xx:17], cid=[no info], tid=0xee1b36c3
: failed to process packet: unable to execute for <SELECT h.host_id, h.dhcp_identifier, h.dhcp_identifier_type, h.dhcp4_subnet_id, h.dhcp6_subnet_id, h.ipv4_address, h.hostname, h.dhcp4_client_classes, h.dhcp6_client_classes, h.us
er_context, h.dhcp4_next_server, h.dhcp4_server_hostname, h.dhcp4_boot_file_name, h.auth_key, o.option_id, o.code, o.value, o.formatted_value, o.space, o.persistent, o.user_context FROM hosts AS h LEFT JOIN dhcp4_options AS o ON h
.host_id = o.host_id WHERE h.dhcp4_subnet_id = ? AND h.dhcp_identifier_type = ? AND h.dhcp_identifier = ? ORDER BY h.host_id, o.option_id>, reason: Prepared statement needs to be re-prepared (error code 1615)
```
When this happens, the host making the request gets no response, not even an error. (Similar to #281?)
It is not clear exactly how to reproduce this error. We even created a completely new copy of the database using `kea-admin` and it ran fine for just less than 24 hours before stopping with the same error.
**To Reproduce**
Steps to reproduce the behavior:
1. Create a MySQL database. Rename the `hosts` table to `hosts_history` and create a VIEW for `hosts`:
```sql
CREATE VIEW `hosts` AS select distinct `hosts_history`.`host_id` AS `host_id`,
`hosts_history`.`dhcp_identifier` AS `dhcp_identifier`,`hosts_history`.`dhcp_identifier_type` AS `dhcp_identifier_type`,
`hosts_history`.`dhcp4_subnet_id` AS `dhcp4_subnet_id`,`hosts_history`.`dhcp6_subnet_id` AS `dhcp6_subnet_id`,
`hosts_history`.`ipv4_address` AS `ipv4_address`,`hosts_history`.`hostname` AS `hostname`,`hosts_history`.`dhcp4_client_classes` AS `dhcp4_client_classes`,
`hosts_history`.`dhcp6_client_classes` AS `dhcp6_client_classes`,`hosts_history`.`dhcp4_next_server` AS `dhcp4_next_server`,
`hosts_history`.`dhcp4_server_hostname` AS `dhcp4_server_hostname`,`hosts_history`.`dhcp4_boot_file_name` AS `dhcp4_boot_file_name`,
`hosts_history`.`user_context` AS `user_context`,`hosts_history`.`auth_key` AS `auth_key`
from `hosts_history` where `hosts_history`.`deleted` IS NULL
```
1. Run Kea dhcp4 with standard mysql configuration
1. The client uses the standard linux dhclient or the Nagios `check_dhcp` plugin.
1. The server usually send standard responses except sometimes when it logs this error and sends no response.
**Expected behavior**
1. Valid responses should always be sent
2. An error should be sent back to the client when there is a server error.
**Environment:**
- Kea version: Supplied from RHEL9 EPEL respository
```
# kea-dhcp4 -V
2.2.0
tarball
linked with:
log4cplus 2.0.5
OpenSSL 3.0.7 1 Nov 2022
database:
MySQL backend 14.0, library 3.2.6
PostgreSQL backend 13.0, library 130011
Memfile backend 2.1
```
- OS: RHEL 9.2outstandinghttps://gitlab.isc.org/isc-projects/kea/-/issues/2872Enable MySQL, PgSQL in CodeQL2023-05-25T13:47:57ZTomek MrugalskiEnable MySQL, PgSQL in CodeQL#2760 enabled github's CodeQL checks. However, there were difficulties for MySQL, and Postgres (see [this thread](https://gitlab.isc.org/isc-projects/kea/-/merge_requests/1952#note_362168) and the links in it). It seems hammer had some d...#2760 enabled github's CodeQL checks. However, there were difficulties for MySQL, and Postgres (see [this thread](https://gitlab.isc.org/isc-projects/kea/-/merge_requests/1952#note_362168) and the links in it). It seems hammer had some difficulties installing dependencies and then accessing the database.
Note: CodeQL is only available on github. This is the first pipeline we have for our mirror on github. This ticket may cover other generic changes.
The goal of this ticket is to figure out why exactly hammer had problems, fix them and enable both MySQL and Postgres.
We have a separate [repo](https://github.com/isc-projects/kea-experiments) for experiments with github. This may come in handy.outstandinghttps://gitlab.isc.org/isc-projects/kea/-/issues/2800Client classes are case insensitive in the leaseX_stat_by_client_class MySQL ...2023-04-20T13:29:20ZAndrei Pavelandrei@isc.orgClient classes are case insensitive in the leaseX_stat_by_client_class MySQL tableReplication steps:
1. Start `kea-dhcp4` with two classes having the same name, but with different capitalization, with limits hook library loaded, and a MySQL lease backend, like in the following example. Add to the configuration whatev...Replication steps:
1. Start `kea-dhcp4` with two classes having the same name, but with different capitalization, with limits hook library loaded, and a MySQL lease backend, like in the following example. Add to the configuration whatever else is needed for dealing with DHCP traffic like `interfaces-config` and `subnet4`.
```json
{
"Dhcp4": {
"client-classes": [
{
"name": "myclass",
"test": "member('ALL')"
},
{
"name": "MYCLASS",
"test": "member('ALL')"
}
],
"hooks-libraries": [
{
"library": "/opt/kea/lib/kea/hooks/libdhcp_limits.so"
}
],
"lease-database": {
"host": "127.0.0.1",
"name": "keatest",
"password": "keatest",
"type": "mysql",
"user": "keatest"
}
}
}
```
2. Send any number of DORAs. Each of the two client classes has to be assigned at least to one DORA.
3. Get all rows from `lease4_stat_by_client_class`. Notice that one of the classes is missing, and the other one is present with more leases than `ALL` which should not be possible. The problem is that both classes are registered under only one of them.
```sh
$ mysql --user=root --password=keatest --database=keatest --execute='SELECT * FROM lease4_stat_by_client_class;'
+--------------+--------+
| client_class | leases |
+--------------+--------+
| ALL | 29 |
| myclass | 58 |
| UNKNOWN | 29 |
+--------------+--------+
```
4. Get the user context from `lease4`. See that both classes are properly added to the user context.
```sh
$ mysql --user=root --password=keatest --database=keatest -e 'SELECT DISTINCT user_context FROM lease4;'
+-----------------------------------------------------------------------------+
| user_context |
+-----------------------------------------------------------------------------+
| { "ISC": { "client-classes": [ "ALL", "myclass", "MYCLASS", "UNKNOWN" ] } } |
+-----------------------------------------------------------------------------+
```
Replicates for `kea-dhcp6` too.
Does not replicate on a PostgreSQL lease backend, meaning there are separate entries for each class e.g. `myclass` and `MYCLASS`. This is the expected behavior.
Used this version to replicate: `Server version: 10.11.2-MariaDB-1:10.11.2+maria~ubu2204 mariadb.org binary distribution`outstandinghttps://gitlab.isc.org/isc-projects/kea/-/issues/2029Readonly Mysql configuration backend2023-07-17T13:58:20ZVitalij OsypenkoReadonly Mysql configuration backendHi, it is not possible to set CB as readonly, it breaks using views with data from different database. E.g.:
```
ERROR [kea-dhcp4.dhcp4/87919.139919704906176] DHCP4_CONFIG_LOAD_FAIL configuration error using file: /etc/kea/kea-dhcp4.conf...Hi, it is not possible to set CB as readonly, it breaks using views with data from different database. E.g.:
```
ERROR [kea-dhcp4.dhcp4/87919.139919704906176] DHCP4_CONFIG_LOAD_FAIL configuration error using file: /etc/kea/kea-dhcp4.conf, reason: during update from config backend database: unable to prepare MySQL statement <INSERT INTO dhcp4_options ( code, value, formatted_value, space, persistent, dhcp_client_class, dhcp4_subnet_id, scope_id, user_context, shared_network_name, pool_id, modification_ts) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)>, reason: The target table dhcp4_options of the INSERT is not insertable-into
```
Kea DHCPv4 server version 1.9.10outstandinghttps://gitlab.isc.org/isc-projects/kea/-/issues/1995How should structurally-nested MySQL transactions be handled?2021-09-03T04:15:16ZAndrei Pavelandrei@isc.orgHow should structurally-nested MySQL transactions be handled?The [MySQL docs](https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html) state:
> Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION...The [MySQL docs](https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html) state:
> Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms.
Since 1.9.10, Kea no longer relies on this default behavior. Commits 4ba460d0f9f1b4f532da8e13d2aa4109124229ec and 83a5989497ee8831ca55b4c0987ee2fd3a369c56 have made it so that the statements belonging to the inner transactions are re-assigned to the outermost transaction. This is arguably better than the MySQL default, because the statements of the inner transaction keep their atomicity (and probably other properties that transaction ensure), instead of it being split into smaller atomic portions, like in the default scenario.
But... A side effect is that the result of the inner transaction is ignored. I don't see this being a problem in case the inner transaction would have committed. But it might be a problem if the inner transaction had decided to rollback. Post-1.9.10, if the outermost transaction decides to commit, the otherwise rolled back statements will now also be committed.
Better(?) alternatives:
* prioritize rollbacks so that a rolled back inner transaction results in a rolled back outer transaction
* turn inner transactions (decided by an if branch in code) into savepoints:
* turn "START TRANSACTION" into "SAVEPOINT identifier"
* turn "ROLLBACK" into "ROLLBACK [WORK] TO [SAVEPOINT] identifier"
* turn "COMMIT" into "RELEASE SAVEPOINT identifier"outstandinghttps://gitlab.isc.org/isc-projects/kea/-/issues/1045Implement wipe commands for PgSQL and MySQL2020-04-29T10:35:15ZTomek MrugalskiImplement wipe commands for PgSQL and MySQL@fdupont reported that wipe commands for MySQL and PgSQL are not implemented. This is an unfortunate omission.
We need to implement them.
One thing to do is to look at older branches. Perhaps there's some code there. I vaguely recall t...@fdupont reported that wipe commands for MySQL and PgSQL are not implemented. This is an unfortunate omission.
We need to implement them.
One thing to do is to look at older branches. Perhaps there's some code there. I vaguely recall they were being discussed with some code written, but I may be misremembering.outstandinghttps://gitlab.isc.org/isc-projects/kea/-/issues/980MySQL Group Replication doesn't support foreign key cascade2021-04-06T09:26:00ZGhost UserMySQL Group Replication doesn't support foreign key cascade**COMPLETELY UPDATED**
I'm using Kea 1.7.0 (installed via the cloudsmith.io yum repo) on CentOS 7.7.1908 with all updates installed. I'm using MySQL 8.0.18 installed from MySQL's yum repo for the backend. MySQL is configured with Group ...**COMPLETELY UPDATED**
I'm using Kea 1.7.0 (installed via the cloudsmith.io yum repo) on CentOS 7.7.1908 with all updates installed. I'm using MySQL 8.0.18 installed from MySQL's yum repo for the backend. MySQL is configured with Group Replication.
I ran into this issue trying to insert into dhcp4_options while doing a host reservation.
After digging into MySQL logs I found these errors:
[ERROR] [MY-011543] [Repl] Plugin group_replication reported: 'Table dhcp4_audit has a foreign key with 'CASCADE' clause. This is not compatible with Group Replication.'
[ERROR] [MY-011543] [Repl] Plugin group_replication reported: 'Table dhcp6_audit has a foreign key with 'CASCADE' clause. This is not compatible with Group Replication.'
I set the following foreign keys to no action:
* fk_dhcp4_audit_revision on update
* fk_dhcp6_audit_revision on update
* fk_dhcp4_subnet_shared_network on delete
* fk_dhcp6_subnet_shared_network on delete
* fk_dhcp4_pool_subnet_id on update
* fk_dhcp6_pool_subnet_id on update
* fk_dhcp6_pd_pool_subnet_id on update
Making these changes appears to work. I can insert and delete reservations and reservation specific options and Kea uses the reservations to respond to requests. However, I'm assuming these constraints are in there for a reason so what have I broken by doing this?outstandinghttps://gitlab.isc.org/isc-projects/kea/-/issues/593Consider MySQL CB schema changes to make it compatible with NDBCLUSTER2023-05-10T07:22:33ZMarcin SiodelskiConsider MySQL CB schema changes to make it compatible with NDBCLUSTEROne of the Kea users attempted to use NDBCLUSTER instead of InnoDB engine with Kea 1.5.0. Some CB specific tables added in 1.5.0 use `UPDATE CASCADE` action. Specifically, the tables holding address/prefix pools include `UPDATE` action r...One of the Kea users attempted to use NDBCLUSTER instead of InnoDB engine with Kea 1.5.0. Some CB specific tables added in 1.5.0 use `UPDATE CASCADE` action. Specifically, the tables holding address/prefix pools include `UPDATE` action referencing the subnet_id primary key. This works fine for the InnoDB engine, but not for the NDB cluster.
The NDB cluster docs says this:
```
ON UPDATE CASCADE is not supported when the reference is to the parent table's primary key.
```
And further on it explains:
```
This is because an update of a primary key is implemented as a delete of the old row (containing the old primary key) plus an insert of the new row (with a new primary key). This is not visible to the NDB kernel, which views these two rows as being the same, and thus has no way of knowing that this update should be cascaded.
```
Even though, we use InnoDB by default, we may consider removing the `UPDATE CASCADE` actions on primary keys (which would require us to modify the code that updates subnet_id for a given prefix), to support users which want to play with cluster engines.outstanding