Kea issueshttps://gitlab.isc.org/isc-projects/kea/-/issues2023-07-18T13:51:46Zhttps://gitlab.isc.org/isc-projects/kea/-/issues/2724Kea with CB configured on PostgreSQL 15 cannot prepare statement2023-07-18T13:51:46ZAndrei Pavelandrei@isc.orgKea with CB configured on PostgreSQL 15 cannot prepare statementNewer PostgreSQL servers seem to be more picky about how prepared statements are formatted. Kea fails to start because of a missing space between a placeholder and the `WHERE` keyword.
```
ERROR [kea-dhcp6.dhcp6] DHCP6_INIT_FAIL failed ...Newer PostgreSQL servers seem to be more picky about how prepared statements are formatted. Kea fails to start because of a missing space between a placeholder and the `WHERE` keyword.
```
ERROR [kea-dhcp6.dhcp6] DHCP6_INIT_FAIL failed to initialize Kea server: configuration error using file '/etc/kea-dhcp6.conf': during update from config backend database: unable to prepare PostgreSQL statement: name: UPDATE_SUBNET6, reason: ERROR: trailing junk after parameter at or near "$33W"
LINE 1: ...threshold = cast($32 as float), cache_max_age = $33WHERE s...
```
This is also visible in multiple unit tests in `PgSqlConfigBackendDHCPv6Test`.
```
[ RUN ] PgSqlConfigBackendDHCPv6Test.getType
*** ERROR: unable to open database. The test
*** environment is broken and must be fixed before
*** the tests will run correctly.
*** The reason for the problem is described in the
*** accompanying exception output.
unknown file: Failure
C++ exception with description "unable to prepare PostgreSQL statement: name: UPDATE_SUBNET6, reason: ERROR: trailing junk after parameter at or near "$33W"
LINE 1: ...threshold = cast($32 as float), cache_max_age = $33WHERE s...
^
, text: UPDATE dhcp6_subnet SET subnet_id = $1, subnet_prefix = $2, client_class = $3, interface = $4, modification_ts = $5, preferred_lifetime = $6, min_preferred_lifetime = $7, max_preferred_lifetime = $8, rapid_commit = $9, rebind_timer = $10, relay = $11, renew_timer = $12, require_client_classes = $13, reservations_global = $14, shared_network_name = $15, user_context = cast($16 as json), valid_lifetime = $17, min_valid_lifetime = $18, max_valid_lifetime = $19, calculate_tee_times = $20, t1_percent = cast($21 as float), t2_percent = cast($22 as float), interface_id = $23, ddns_send_updates = $24, ddns_override_no_update = $25, ddns_override_client_update = $26, ddns_replace_client_name = $27, ddns_generated_prefix = $28, ddns_qualifying_suffix = $29, reservations_in_subnet = $30, reservations_out_of_pool = $31, cache_threshold = cast($32 as float), cache_max_age = $33WHERE subnet_id = $34 OR subnet_prefix = $35" thrown in SetUp().
[ FAILED ] PgSqlConfigBackendDHCPv6Test.getType (249 ms)
```
Adding the space makes the unit tests pass.
Affected PostgreSQL versions: 15.0, 15.1.
It works properly on PostgreSQL 14.6.kea2.3.4Andrei Pavelandrei@isc.orgAndrei Pavelandrei@isc.orghttps://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/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/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/2891Handling optional columns in SELECT for PostgreSQL2023-06-15T13:51:46ZMarcin SiodelskiHandling optional columns in SELECT for PostgreSQLDuring the discussions about #2886 it was apparent that we need to design a way of making SELECT queries in PostgreSQL with excluding some columns. It is particularly important for the `lease6` table queries where we recently started add...During the discussions about #2886 it was apparent that we need to design a way of making SELECT queries in PostgreSQL with excluding some columns. It is particularly important for the `lease6` table queries where we recently started adding new columns (related to BLQ) that are excluded in some (many?) cases. The `PgSqlLease6Exchange` contains the list of constants designating the indexes of the columns in the SELECT statements. A temporary solution was to assign the highest indexes for optional columns (put them at the end), but it may be good for a single optional column. If you have two optional columns this solution has serious limitations. For example, to query the last optional column you need to query all preceding optional columns, even if they are not needed in the particular case.
The following thread in #2886 is relevant: https://gitlab.isc.org/isc-projects/kea/-/merge_requests/2016#note_377155
It includes some ideas how to address this problem.https://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/2452Postgres - New key for hosts table2022-07-22T14:35:23ZPeter DaviesPostgres - New key for hosts tablewe did some performance testing in our dhcp setup and discovered, that the session setup rate is very bad (~ 20/sec.).
We use postgresql as host reservation backend and noticed, that it utilizes nearly 100% of the CPU during the test.
...we did some performance testing in our dhcp setup and discovered, that the session setup rate is very bad (~ 20/sec.).
We use postgresql as host reservation backend and noticed, that it utilizes nearly 100% of the CPU during the test.
We discovered that the cause for that are pgsql queries inside the hosts database, which take about 7ms for each host.
There exist indexes for the "hosts" table, but none matches for the query that Kea uses in our case. So we added that index and the queries were about 400 times faster (session setup rate also over 200/sec., seems to be limited only by our dhcp relay, because the CPU utilization of the server stays below 50% now).
We did not test IPv6, but the problem could exist there as well.
In the case of IPv4, the following modifications of the pgsql database solved our problem:
1. added the following index:
CREATE UNIQUE INDEX key_dhcp4_identifier on hosts (dhcp_identifier, dhcp_identifier_type);
--> there is already an index, but it contains the dhcp4_subnet_id, which Kea didn't use in the host lookup query.
2. modified the following existing index:
"key_dhcp4_identifier_subnet_id" UNIQUE, btree (dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id) WHERE dhcp4_subnet_id IS NO
T NULL AND dhcp4_subnet_id <> 0
to
"key_dhcp4_identifier_subnet_id" UNIQUE, btree (dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id) WHERE dhcp4_subnet_id IS NO
T NULL
--> we use global reservations, where dhcp4_subnet_id = 0 is set in the hosts database, so the index was not used because of "dhcp4_subnet_id <> 0"
Some background information:
We use Kea to dynamically assign addresses out of pools inside a shared subnet, but only if the host has an entry in the "hosts" table. So the host entry does not have an IP address defined.
Please feel free to ask if you have further questions of if we can test/debug anything.
see also:
https://gitlab.isc.org/isc-projects/kea/-/issues/2037
https://gitlab.isc.org/isc-projects/kea/-/issues/1458
[RT #20893](https://support.isc.org/Ticket/Display.html?id=20893 )kea2.2.0 - a new stable branchThomas MarkwalderThomas Markwalderhttps://gitlab.isc.org/isc-projects/kea/-/issues/2309PgSqlBasicsTest.ptimeTimestamp fails on Debian 92022-07-08T13:55:27ZAndrei Pavelandrei@isc.orgPgSqlBasicsTest.ptimeTimestamp fails on Debian 9There is a Postgres unit test that claims we can insert year 3021 timestamps in Postgres, but I think we can only go up to 2038 in Debian 9.
https://jenkins.aws.isc.org/job/kea-dev/job/ut-extended/637/#showFailuresLink
```
15:11:23 [ ...There is a Postgres unit test that claims we can insert year 3021 timestamps in Postgres, but I think we can only go up to 2038 in Debian 9.
https://jenkins.aws.isc.org/job/kea-dev/job/ut-extended/637/#showFailuresLink
```
15:11:23 [ RUN ] PgSqlBasicsTest.ptimeTimestamp
15:11:23 NOTICE: table "basics" does not exist, skipping
15:11:23 pgsql_exchange_unittest.cc:984: Failure
15:11:23 Failed
15:11:23 no exception, expected: BadValue
15:11:23 [ FAILED ] PgSqlBasicsTest.ptimeTimestamp (13 ms)
```
Here are some hints on what might be wrong with values taken from `PsqlBindArray::addTimestamp(const boost::posix_time::ptime& timestamp)` called from the unit test:
| | Debian 9 | Other OSs |
| ------------------------------- | ---------------------------------------------------- | ---------------------------------------------------- |
| lscpu | Architecture: x86_64, CPU op-mode(s): 32-bit, 64-bit | Architecture: x86_64, CPU op-mode(s): 32-bit, 64-bit |
| sizeof(time_duration::sec_type) | 4 | 8 |
| timestamp | 3021-Jan-21 10:14:15 | 3021-Jan-21 10:14:15 |
| epoch | 1970-Jan-01 00:00:00 | 1970-Jan-01 00:00:00 |
| since_epoch | -1191605513 | 33168132855 |
| input_time | -1191605513 | 33168132855 |kea2.2.0 - a new stable branchhttps://gitlab.isc.org/isc-projects/kea/-/issues/2445PostgreSQL schema support for lease limits2022-07-07T11:47:52ZAndrei Pavelandrei@isc.orgPostgreSQL schema support for lease limitsAdd the functionality to support lease limits to the PostgreSQL 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...Add the functionality to support lease limits to the PostgreSQL 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.2.0 - a new stable branchAndrei Pavelandrei@isc.orgAndrei Pavelandrei@isc.orghttps://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/96CB: Implement PgSQLConfigBackendDHCPv62022-03-14T17:38:45ZMarcin SiodelskiCB: Implement PgSQLConfigBackendDHCPv6The PgSQLConfigBackendDHCPv6 class implements Config Backend for MySQL as described in https://gitlab.isc.org/isc-projects/kea/wikis/designs/configuration-in-db-designThe PgSQLConfigBackendDHCPv6 class implements Config Backend for MySQL as described in https://gitlab.isc.org/isc-projects/kea/wikis/designs/configuration-in-db-designkea2.1.4Thomas MarkwalderThomas Markwalderhttps://gitlab.isc.org/isc-projects/kea/-/issues/2355PgSql CB V6 Add support for client-classes2022-03-14T16:06:39ZThomas MarkwalderPgSql CB V6 Add support for client-classesAdd support to Postgresql CB for client classes. This ticket will complete the Postgresql CB implementation.Add support to Postgresql CB for client classes. This ticket will complete the Postgresql CB implementation.kea2.1.4Thomas MarkwalderThomas Markwalderhttps://gitlab.isc.org/isc-projects/kea/-/issues/2349PgSql CB V6 Add support for shared-networks, subnets, pools2022-03-14T13:23:24ZThomas MarkwalderPgSql CB V6 Add support for shared-networks, subnets, poolskea2.1.4Thomas MarkwalderThomas Markwalderhttps://gitlab.isc.org/isc-projects/kea/-/issues/2346PgSql CB V6 Add support for globals, option defs, and options2022-03-14T13:18:32ZThomas MarkwalderPgSql CB V6 Add support for globals, option defs, and optionsNext step in Postgresql CB v6:
global parameters
option definitions
options
Note does not include implement methods or tests for definitions or options that belong to other objects (such as networks, subnets...)Next step in Postgresql CB v6:
global parameters
option definitions
options
Note does not include implement methods or tests for definitions or options that belong to other objects (such as networks, subnets...)kea2.1.4Thomas MarkwalderThomas Markwalderhttps://gitlab.isc.org/isc-projects/kea/-/issues/2342PgSql CB V6 core, queries and support for servers2022-03-14T13:18:32ZThomas MarkwalderPgSql CB V6 core, queries and support for servers All queries complete, support for unit tests, and CRUD for servers All queries complete, support for unit tests, and CRUD for serverskea2.1.4Thomas MarkwalderThomas Markwalder