#5522 closed defect (fixed)

subnet_id column types are inconsistent between RDBMs tables

Reported by: tmark Owned by: tmark
Priority: high Milestone: Kea1.4
Component: database-all Version: git
Keywords: Cc:
CVSS Scoring: Parent Tickets:
Sensitive: no Defect Severity: N/A
Sub-Project: DHCP Feature Depending on Ticket:
Estimated Difficulty: 0 Add Hours to Ticket: 0
Total Hours: 0 Internal?: no

Description (last modified by tomek)

In Postgresql the subnet_id column is defined as BIGINT in the lease4 and lease6 tables. It is incorrectly typed as an IN in the host, dhcp4_options, and dhcp6_options table. Thus lease tables can store larger values than can be stored in the other tables.

In MySQL, the subnet_id column is an INT UNSIGNED everywhere except the dhcp4_options and dhcp6_options tables, where it is an INT.

  1. Postgresql - subnet_id must be BIG INT everywhere
  2. MySQL - subnet_id must be INT UNSIGNED everywhere

A user requires #1 as a patch applicable to 1.3 asap.

1 & 2 should be fixed properly in 1.3 and those changes need to be applied to 1.4.

See support ticket:

https://support.isc.org/Ticket/Display.html?id=12327

Subtickets

Attachments (6)

5522_v1_3_0.patch (11.2 KB) - added by tmark 17 months ago.
5416_plus_5522_v1_3_0.patch (23.6 KB) - added by tmark 17 months ago.
5416_plus_5522_v1_3_0-no-gitignore.patch (23.0 KB) - added by tomek 17 months ago.
corrected patch (.gitignore removed)
5416_5522_v1_3_0.final.patch (23.0 KB) - added by tmark 17 months ago.
Patch including fixes for both 5416 and 5522 for Kea 1.3.0
5522_v1_3_0.final.patch (10.2 KB) - added by tmark 17 months ago.
Patch that contains fixes for 5522 for Kea 1.3.0 patched with 5416
5522_1_3_0_addendum.patch (9.3 KB) - added by tmark 17 months ago.
Addendum to 5522 patches

Download all attachments as: .zip

Change History (17)

comment:1 Changed 17 months ago by tmark

  • Description modified (diff)

comment:2 Changed 17 months ago by tmark

There was patch to the schema made recently for Beanfield in response to a prior issue. That patch needs to be taken into consideration and/or included.

comment:3 Changed 17 months ago by tmark

  • Owner set to tmark
  • Status changed from new to assigned

Changed 17 months ago by tmark

Changed 17 months ago by tmark

comment:4 Changed 17 months ago by tmark

This patch was a bit of head jam. Marcin gave Beanfield a patch for 1.3 for #5416. This altered the Postgresql schema and tweaked the MySQL back end code. These changes, however, were not merged into the v1_3_0 branch.

As the changes for this ticket need to sit on top of 5416 and because Beanfield may well be running 1.3 for some time, I created branch trac5522_1_3_0 under branch v1_3_0 and merged Marcin's changes for 5416 into it. See git# 80986ab7eda0f24dd3516c921e11290f979ad370.

I then developed the changes for 5522. The changes correct the 1.3 schemas for both Postgresql and MySQL as prescribed. These changes were commited with git #fd8c37eaebd6bc34157fc2b37e21b7b649014749

I have attached two patch files:

  1. Includes only 5522 for 1.3
  2. Includes both 5416 and 5522 for 1.3

Ready for review. Michael McNally? has notified Beanfield that we have the patch ready for review.

comment:5 Changed 17 months ago by tmark

  • Owner changed from tmark to tomek
  • Status changed from assigned to reviewing

Changed 17 months ago by tomek

corrected patch (.gitignore removed)

comment:6 Changed 17 months ago by tmark

  • Owner changed from tomek to tmark

Tomek spotted a copy-and-paste error in the postgresql create and upgrade scripts that altered the hosts dhcp4_subnet_id column twice, rather dhcp4_subnet_id and dhcp6_subnet_id columns.

This has been corrected. In addition the patches I originally created include changes to .gitignore files which do not exist in source tar ball.

I have remade both patch files and tested them successfully against the 1.3.0 source tar ball.

  1. 5416_5522_v1_3_0.final.patch - this patch includes changes for both 5416 and 5522, for use with

unpatched 1.3.0 source

  1. 5522_v1_3_0.final.patch this patch includes only 5522, for use with source that already has the 5416 patch

Changed 17 months ago by tmark

Patch including fixes for both 5416 and 5522 for Kea 1.3.0

Changed 17 months ago by tmark

Patch that contains fixes for 5522 for Kea 1.3.0 patched with 5416

Changed 17 months ago by tmark

Addendum to 5522 patches

comment:7 Changed 17 months ago by tmark

Created the addendum patch.
Merged all the changes into v1_3_0 with git# 0fb62b7cc8f1c6c1401596c1a48c90ef05640b6f.
Added ChangeLog? entry 1335.

5522 changes need to be merged into master.

Last edited 17 months ago by tmark (previous) (diff)

comment:8 Changed 17 months ago by tomek

  • Description modified (diff)
  • Milestone changed from Kea-proposed to Kea1.4
  • Priority changed from medium to high

comment:9 Changed 16 months ago by tmark

  • Owner changed from tmark to UnAssigned

Changes have been applied from v1_3_0 to master/trac5522.

ChangeLog?:

xxxx.  [bug]       tmark
   Fixed a bug which prevented inserting multiple host reservations
   where IPv4 address was unspecified or when selected subnet identifier
   was not specified (5416).  Corrected inconsistent data types for
   subnet id columns in both Postgres and MySQL schemas.  Now both
   schemas support upto MAX UINT32 in all subnet id columns. Exiting
   databases may be updated without data migration.
   (Trac 5416, 5522, git TBD)

Ready for review.

comment:10 Changed 16 months ago by marcin

  • Owner changed from UnAssigned to tmark

I reviewed commit db2e3444d5b366e9d02859e7d68ee18c89022d1b. Your changes look fine. I just removed a couple of accidentally inserted whitespaces and committed this change. Please pull the change from the branch.
The changelog entry seems a bit long. Perhaps we could get rid of the last two sentences.

Otherwise, the ticket is ready to go.

comment:11 Changed 16 months ago by tmark

  • Resolution set to fixed
  • Status changed from reviewing to closed

Code merged with git# d1dd0f2e27ffa49515c17cf68d54f162359b6384
Created ChangeLog? entry 1368.

ticket is complete.

Note: See TracTickets for help on using tickets.