Kea issueshttps://gitlab.isc.org/isc-projects/kea/-/issues2023-07-17T13:58:23Zhttps://gitlab.isc.org/isc-projects/kea/-/issues/2654Detect JSON path bug in MariaDB2023-07-17T13:58:23ZFrancis DupontDetect JSON path bug in MariaDBMariaDB > 10.9.2 has a bug in JSON path handling which considers a '-' character in a quoted member name as a syntax error.
This ticket proposes to detect this bug in isJsonSupported and to fix all unit tests depending on json_extract b...MariaDB > 10.9.2 has a bug in JSON path handling which considers a '-' character in a quoted member name as a syntax error.
This ticket proposes to detect this bug in isJsonSupported and to fix all unit tests depending on json_extract but failing to test if JSON is supported.
And finally to update the known bug entry from Kea does not work with recent MariaDB to limits hook does not work with recent MariaDB.kea2.3.4Francis DupontFrancis Duponthttps://gitlab.isc.org/isc-projects/kea/-/issues/2727mysql_get_option was not declared2023-07-17T13:58:23ZAndrei Pavelandrei@isc.orgmysql_get_option was not declaredKea fails to build on CentOS 7.
```
mysql_connection_unittest.cc: In member function ‘virtual void {anonymous}::MySqlConnectionTest_connectionTimeout_Test::TestBody()’:
mysql_connection_unittest.cc:633:77: error: ‘mysql_get_option’ wa...Kea fails to build on CentOS 7.
```
mysql_connection_unittest.cc: In member function ‘virtual void {anonymous}::MySqlConnectionTest_connectionTimeout_Test::TestBody()’:
mysql_connection_unittest.cc:633:77: error: ‘mysql_get_option’ was not declared in this scope
EXPECT_EQ(0, mysql_get_option(mysql, MYSQL_OPT_CONNECT_TIMEOUT, &timeout));
^
```kea2.3.4Andrei Pavelandrei@isc.orgAndrei Pavelandrei@isc.orghttps://gitlab.isc.org/isc-projects/kea/-/issues/2710Kea ARM database tweaks and recomendations2023-07-17T13:58:23ZMarcin GodzinaKea ARM database tweaks and recomendationsKea requires changes in documentation about recomended database and tweaks to improve performance.
Related issue: isc-projects/kea#2706Kea requires changes in documentation about recomended database and tweaks to improve performance.
Related issue: isc-projects/kea#2706kea2.3.5Marcin GodzinaMarcin Godzinahttps://gitlab.isc.org/isc-projects/kea/-/issues/2752Update SQL schemas for BLQ v4 new columns2023-07-17T13:58:22ZFrancis DupontUpdate SQL schemas for BLQ v4 new columnsFor BLQ v4 the SQL schemas must be updated to add two columns to DHCPv4 lease table for relay id and remote id, and of course the corresponding index.
This ticket is about the schema update and associated code (but not about lease API i...For BLQ v4 the SQL schemas must be updated to add two columns to DHCPv4 lease table for relay id and remote id, and of course the corresponding index.
This ticket is about the schema update and associated code (but not about lease API implementation).kea2.3.7Francis DupontFrancis Duponthttps://gitlab.isc.org/isc-projects/kea/-/issues/2867Update SQL schemas for v6 BLQ2023-07-17T13:58:21ZFrancis DupontUpdate SQL schemas for v6 BLQkea2.3.8Francis DupontFrancis Duponthttps://gitlab.isc.org/isc-projects/kea/-/issues/2757Upgrade extended info in SQL lease database2023-07-17T13:58:21ZFrancis DupontUpgrade extended info in SQL lease databaseDefine a new Lease Query hook command which upgrade extended info in SQL lease database (can do nothing with memfile because this operation is performed during the loading phase).
Note this can be done in parallel with server service be...Define a new Lease Query hook command which upgrade extended info in SQL lease database (can do nothing with memfile because this operation is performed during the loading phase).
Note this can be done in parallel with server service because all changes are done using current settings. Standard update can be used, only the retrieval should use a specialized version of GET_LEASEx_PAGE to filter out leases with NULL user_context.
Proposed parameter: size of the page (but progress will be visible only in debug logs).
Depends on #2752 and perhaps also on #2753, related to #2623kea2.3.8Francis DupontFrancis Duponthttps://gitlab.isc.org/isc-projects/kea/-/issues/2868Implement v6 BLQ by-link using binary address2023-07-17T13:58:21ZFrancis DupontImplement v6 BLQ by-link using binary addressDepends on #2867Depends on #2867kea2.3.8Francis DupontFrancis Duponthttps://gitlab.isc.org/isc-projects/kea/-/issues/2886C++ API and SQL do not use the same column order2023-07-17T13:58:21ZFrancis DupontC++ API and SQL do not use the same column orderJust move the adding of pool_id before binaddr in create and last upgrade scripts.Just move the adding of pool_id before binaddr in create and last upgrade scripts.kea2.3.8Razvan BecheriuRazvan Becheriuhttps://gitlab.isc.org/isc-projects/kea/-/issues/2909Migrate DB V6 address string column to inet or binary types2023-07-17T13:58:20ZThomas MarkwalderMigrate DB V6 address string column to inet or binary typeskea2.4.0Thomas MarkwalderThomas Markwalderhttps://gitlab.isc.org/isc-projects/kea/-/issues/2962addr6_buffer_ no longer used2023-07-17T13:58:20ZFrancis Dupontaddr6_buffer_ no longer usedFrom sanity check of 2.4.0:
```
mysql_host_data_source.cc:1793:13: error: private field 'addr6_buffer_' is not used [-Werror,-Wunused-private-field]
uint8_t addr6_buffer_[isc::asiolink::V6ADDRESS_LEN];
```From sanity check of 2.4.0:
```
mysql_host_data_source.cc:1793:13: error: private field 'addr6_buffer_' is not used [-Werror,-Wunused-private-field]
uint8_t addr6_buffer_[isc::asiolink::V6ADDRESS_LEN];
```kea2.5.0Razvan BecheriuRazvan Becheriuhttps://gitlab.isc.org/isc-projects/kea/-/issues/2869Implement v6 BLQ using SQL extended info tables2023-07-17T13:58:20ZFrancis DupontImplement v6 BLQ using SQL extended info tablesBased on #2868 which implements v6 BLQ by-link using new SQL binary address columnBased on #2868 which implements v6 BLQ by-link using new SQL binary address columnkea2.5.0Francis DupontFrancis Duponthttps://gitlab.isc.org/isc-projects/kea/-/issues/2971Unreliable retrieve of hardware addresses for v4 MySQL lease backend.2023-07-12T12:58:50ZFrancis DupontUnreliable retrieve of hardware addresses for v4 MySQL lease backend.In mysql_lease_mgr.cc getLeaseData() uses the hwaddr_null_ flag to set the hwaddr_ field of v4 leases but this variable is not passed to MySQL as search for it in the v4 code shows (MySqlLease4Exchange ctor and definition, twice in creat...In mysql_lease_mgr.cc getLeaseData() uses the hwaddr_null_ flag to set the hwaddr_ field of v4 leases but this variable is not passed to MySQL as search for it in the v4 code shows (MySqlLease4Exchange ctor and definition, twice in createBindForSend, getLeaseData, but not in createBindForReceive as expected). The fix is simple because the v6 code is correct so just do the same.
Attaching a diff file from my #2870 debugkea2.5.0Francis DupontFrancis Duponthttps://gitlab.isc.org/isc-projects/kea/-/issues/2640Store extended info for BLQ in SQL databases2023-07-10T18:23:48ZFrancis DupontStore extended info for BLQ in SQL databasesDiscussion opened in https://gitlab.isc.org/isc-projects/kea/-/merge_requests/1821#note_328463.
There is a need to store remote-id and other extended info in the lease database. There are at least two possible approaches:
1. **updates*...Discussion opened in https://gitlab.isc.org/isc-projects/kea/-/merge_requests/1821#note_328463.
There is a need to store remote-id and other extended info in the lease database. There are at least two possible approaches:
1. **updates** - once a lease is created, lease-update is used to extend its user context with extra info, such as remote-id. This is what we implemented for memfile. It worked well, because the price to update memfile is negligible. The benefit of this approach is its relative simplicity and uniformity between backends. The drawback is that it makes multiple additional round trips to the DB. In extreme case, such as client getting 2 addresses and a prefix and there's remote-id and subscriber-id configured, there would be 6 extra DB queries.
2. **trigger-based** - the alternative approach is to use DB triggers to update the data. This is slightly more complicated (need to understand how triggers work and how to disable them when BLQ is not used). The benefit is, however, no extra round trip times for the DB.
For the sake of time constraints, let's go with approach 1 for now. If users complain the solution is not performant enough, we will implement 2.kea2.5.0Francis DupontFrancis Duponthttps://gitlab.isc.org/isc-projects/kea/-/issues/2896Move to binary address as the lease6 primary key2023-06-15T13:24:43ZFrancis DupontMove to binary address as the lease6 primary keyThis details the long term plan sumarized by this comment:
```
# Holds the IPv6 leases.
# N.B. The use of a VARCHAR for the address is temporary for development:
# it will eventually be replaced by BINARY(16).
CREATE TABLE lease6 (
a...This details the long term plan sumarized by this comment:
```
# Holds the IPv6 leases.
# N.B. The use of a VARCHAR for the address is temporary for development:
# it will eventually be replaced by BINARY(16).
CREATE TABLE lease6 (
address VARCHAR(39) PRIMARY KEY NOT NULL, # IPv6 address
...
```https://gitlab.isc.org/isc-projects/kea/-/issues/2706performance drop on mySQL 8 vs mySQL 5.7 (and mariadb, postgresql)2023-02-21T10:13:34ZMarcin Godzinaperformance drop on mySQL 8 vs mySQL 5.7 (and mariadb, postgresql)A massive performance drop (60-90%) is observed on MySQL 8 in comparison to MySQL 5.7 or other databases (MariaDB or PostgreSQL).
Both databases use out of the box configuration except of `innodb_flush_log_at_trx_commit=2` being added.
...A massive performance drop (60-90%) is observed on MySQL 8 in comparison to MySQL 5.7 or other databases (MariaDB or PostgreSQL).
Both databases use out of the box configuration except of `innodb_flush_log_at_trx_commit=2` being added.
But without this setting, we also have big performance difference.
~~I narrowed down the offending query~~ to `UPDATE schema_version` on our test setup
- on mysql 5.7 query is made about 1.6 times per 1k processed leases and takes about 224us
- on mysql 8 query is made about 229 times per 1k processed leases and takes about 30ms
Looks like most of the queries are slower.
on mysql 5.7 the longest query was 2s, and 95% of queries were 167us \
on mysql 8 the longest query was 2s, and 95% of queries were 31ms - thats huge
on mysql 8 INSERT INTO lease4 takes around 36ms \
on mysql 5.7 INSERT INTO lease4 takes around 214us
**MySQL 8** \
**229 query per 1k leases, 95percentile 30ms** (837 leases processed at 1500/s, drop rate 95%):
```
# Query 3: 0.06 QPS, 0.00x concurrency, ID 0x9D37850BFEFB2411FC8CD039CCABE4BB at byte 20172
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-01-09T09:55:16 to 2023-01-09T10:44:41
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 2 192
# Exec time 4 3s 10ms 37ms 18ms 30ms 6ms 13ms
# Lock time 2 2ms 0 57us 8us 12us 5us 7us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 30 384 2 2 2 2 0 2
# Query size 0 9.98k 52 57 53.21 56.92 2.16 51.63
# String:
# Databases keatest
# Hosts localhost
# Users keatest
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms ################################################################
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `keatest` LIKE 'schema_version'\G
# SHOW CREATE TABLE `keatest`.`schema_version`\G
UPDATE schema_version
SET version = '3', minor = '0'\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select version = '3', minor = '0' from schema_version \G
```
**MySQL 5.7** \
**1.6 query per 1k leases, 95percentile 224us** (about 15k leases processed at 1500/s, drop rate 20%):
```
# Query 122: 1 QPS, 0.00x concurrency, ID 0x9D37850BFEFB2411FC8CD039CCABE4BB at byte 535872
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-01-09T10:35:48 to 2023-01-09T10:36:12
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 24
# Exec time 0 3ms 86us 242us 143us 224us 43us 131us
# Lock time 0 874us 21us 49us 36us 44us 7us 36us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 48 2 2 2 2 0 2
# Query size 0 1.25k 52 57 53.21 56.92 2.16 51.63
# String:
# Databases keatest
# Hosts localhost
# Users keatest
# Query_time distribution
# 1us
# 10us #########
# 100us ################################################################
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `keatest` LIKE 'schema_version'\G
# SHOW CREATE TABLE `keatest`.`schema_version`\G
UPDATE schema_version
SET version = '9', minor = '0'\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select version = '9', minor = '0' from schema_version \G
```
All queries performance log (using pt-query-digest) \
Please note that the test procedure drops and creates a database on test start, so there are queries from this procedure.
[mysql8-slow.log](/uploads/60e1c9a97f937ff46154fb2bb60d7f9e/mysql8-slow.log)
[mysql57-slow.log](/uploads/a631a22472242fc6aa4c3d010fcbd634/mysql57-slow.log)
[kea-config.txt](/uploads/7e3f27d2d3357dbabd8207e5efbea356/kea-config.txt)
[mysql8-slow-full_run.log](/uploads/12a155c852ab7a3c73d421cede1d0d15/mysql8-slow-full_run.log)
[mysql57-slow-full_run.log](/uploads/546d9c5ab49e815d02316a0c4aef55a8/mysql57-slow-full_run.log)kea2.3.5Marcin GodzinaMarcin Godzinahttps://gitlab.isc.org/isc-projects/kea/-/issues/2438MySQL schema support for lease limits2022-07-07T11:47:52ZAndrei Pavelandrei@isc.orgMySQL schema support for lease limitsAdd the functionality to support lease limits to the MySQL lease manager as outlined below:
* [x] Create lease count tables to track leases per client class and per subnet
* [x] Create the triggers to update lease counts upon lease even...Add the functionality to support lease limits to the MySQL lease manager as outlined below:
* [x] Create lease count tables to track leases per client class and per subnet
* [x] Create the triggers to update lease counts upon lease events: INSERT, UPDATE, DELETE
* [x] Create a function or procedure that compares all the configured lease limits relevant to a packet to all the current lease counts of the respective client classes and subnetskea2.1.7Andrei Pavelandrei@isc.orgAndrei Pavelandrei@isc.orghttps://gitlab.isc.org/isc-projects/kea/-/issues/2299MySQL cascaded foreign keys do not activate triggers2022-06-07T19:48:23ZThomas MarkwalderMySQL cascaded foreign keys do not activate triggersConfig Back end relies on triggers to create audit entries (among many other things). However, we were not aware that MySQL has the following caveat:
Cascaded foreign keys do not activate triggers.
(https://dev.mysql.com/doc/refm...Config Back end relies on triggers to create audit entries (among many other things). However, we were not aware that MySQL has the following caveat:
Cascaded foreign keys do not activate triggers.
(https://dev.mysql.com/doc/refman/8.0/en/create-trigger.html)
This breaks at least one use case:
When a shared network is deleted all subnets which belonged to it must have their shared network name column set to null. An audit entry will be created for the shared network via the ADEL trigger on dhcpX_shared_network table, however no audit entries will be made for the subnets as these entries are expected to be created by the AUPD trigger on the dhcpX_subnet table because those updates are triggered the following foreign key constraint:
```
CONSTRAINT fk_dhcpX_subnet_shared_network FOREIGN KEY (shared_network_name)
REFERENCES dhcpX_shared_network (name)
ON DELETE SET NULL ON UPDATE NO ACTION
```
While the following unit test currently passes:
MySqlConfigBackendDHCPv4Test.getAllSharedNetworks4Test
In reality it should not. It should fail when checking audit entries after deleting networks which contain subnets.kea2.1.7Thomas MarkwalderThomas Markwalderhttps://gitlab.isc.org/isc-projects/kea/-/issues/1366MySQL log_bin_trust_function_creators global variable2022-04-06T07:29:22ZFrancis DupontMySQL log_bin_trust_function_creators global variableOn some systems MySQL fails with this error:
```
ERROR 1419 (HY000) at line 21: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
```
The se...On some systems MySQL fails with this error:
```
ERROR 1419 (HY000) at line 21: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
```
The setting log_bin_trust_function_creators global variable to 1 fixes the problem util the next restart so:
- add this to the documentation
- if there is an easy way to make the setting persistent document it (beware of the MySQL version here: the problem shows on 5.7 for me)https://gitlab.isc.org/isc-projects/kea/-/issues/2334mysql_cb and pgsql_cb typo in createOptionAuditDHCP62022-03-14T18:58:01ZRazvan Becheriumysql_cb and pgsql_cb typo in createOptionAuditDHCP6```
SELECT dhcp6_pd_pool.subnet_id INTO sid FROM dhcp6_pd_pool WHERE id = pool_id;
UPDATE dhcp6_subnet SET modification_ts = p_modification_ts
WHERE subnet_id = sid;
```
vs
```
SELECT dhcp6_pd_pool.subnet_id I...```
SELECT dhcp6_pd_pool.subnet_id INTO sid FROM dhcp6_pd_pool WHERE id = pool_id;
UPDATE dhcp6_subnet SET modification_ts = p_modification_ts
WHERE subnet_id = sid;
```
vs
```
SELECT dhcp6_pd_pool.subnet_id INTO sid FROM dhcp6_pd_pool WHERE id = pd_pool_id;
UPDATE dhcp6_subnet SET modification_ts = p_modification_ts
WHERE subnet_id = sid;
```
notice ```pool_id``` vs ```pd_pool_id```kea2.1.4https://gitlab.isc.org/isc-projects/kea/-/issues/2039ability to migrate memfile leases to database leases2022-02-18T08:00:19ZAndrei Pavelandrei@isc.orgability to migrate memfile leases to database leasesThe reverse action that #2038 would take.
Something like: `kea-admin lease-import mysql -6 -o leases.csv`.
These two kea-admin commands would cover all the cases. If someone wants to migrate from MySQL to PostgreSQL they can go through...The reverse action that #2038 would take.
Something like: `kea-admin lease-import mysql -6 -o leases.csv`.
These two kea-admin commands would cover all the cases. If someone wants to migrate from MySQL to PostgreSQL they can go through CSV. That is:
```
kea-admin lease-dump mysql -6 -o leases.csv
kea-admin lease-import pgsql -6 -o leases.csv
```kea2.1.2