From ebd3358bd5995d4a64d51ba4db6708b49eea0ff4 Mon Sep 17 00:00:00 2001 From: Thomas Markwalder Date: Tue, 8 May 2018 14:41:40 -0400 Subject: [PATCH] [5586] Added schema unit tests src/share/database/scripts/mysql/upgrade_5.2_to_6.0.sh.in Added SQL statements to populate lease4/6_stat tables from existing lease table content. src/bin/admin/tests/dhcpdb_create_5.1.mysql New file used for upgrade testing src/bin/admin/tests/mysql_tests.sh.in run_statement() - new convenience function for running SQL statements with an optional expected return value mysql_lease4_stat_test() - verifies lease4_stat table and trigger operations on a new database mysql_lease6_stat_test() mysql_lease6_stat_per_type() - new functions which lease6_stat table and trigger operations on a new database mysql_lease_stat_upgrade_test() - new function which verifies data migration from an earlier schema and trigger operation --- src/bin/admin/tests/dhcpdb_create_5.1.mysql | 538 ++++++++++++++++++ src/bin/admin/tests/mysql_tests.sh.in | 317 ++++++++++- .../scripts/mysql/upgrade_5.2_to_6.0.sh.in | 15 + 3 files changed, 865 insertions(+), 5 deletions(-) create mode 100644 src/bin/admin/tests/dhcpdb_create_5.1.mysql diff --git a/src/bin/admin/tests/dhcpdb_create_5.1.mysql b/src/bin/admin/tests/dhcpdb_create_5.1.mysql new file mode 100644 index 0000000000..ac050f6cf1 --- /dev/null +++ b/src/bin/admin/tests/dhcpdb_create_5.1.mysql @@ -0,0 +1,538 @@ +# Copyright (C) 2012-2017 Internet Systems Consortium, Inc. ("ISC") +# +# This Source Code Form is subject to the terms of the Mozilla Public +# License, v. 2.0. If a copy of the MPL was not distributed with this +# file, You can obtain one at http://mozilla.org/MPL/2.0/. +# +# This is the Kea schema 5.1 specification for MySQL. +# Note: this is outdated version on purpose and it used to test upgrade +# process. Do not update this file. +# +# The schema is reasonably portable (with the exception of the engine +# specification, which is MySQL-specific). Minor changes might be needed for +# other databases. + +# To create the schema, either type the command: +# +# mysql -u -p < dhcpdb_create.mysql +# +# ... at the command prompt, or log in to the MySQL database and at the 'mysql>' +# prompt, issue the command: +# +# source dhcpdb_create.mysql +# +# This script is also called from kea-admin, see kea-admin lease-init mysql +# +# Over time, Kea database schema will evolve. Each version is marked with +# major.minor version. This file is organized sequentially, i.e. database +# is initialized to 1.0, then upgraded to 2.0 etc. This may be somewhat +# sub-optimal, but it ensues consistency with upgrade scripts. (It is much +# easier to maintain init and upgrade scripts if they look the same). +# Since initialization is done only once, it's performance is not an issue. + +# This line starts database initialization to 1.0. + +# Holds the IPv4 leases. +CREATE TABLE lease4 ( + address INT UNSIGNED PRIMARY KEY NOT NULL, # IPv4 address + hwaddr VARBINARY(20), # Hardware address + client_id VARBINARY(128), # Client ID + valid_lifetime INT UNSIGNED, # Length of the lease (seconds) + expire TIMESTAMP, # Expiration time of the lease + subnet_id INT UNSIGNED, # Subnet identification + fqdn_fwd BOOL, # Has forward DNS update been performed by a server + fqdn_rev BOOL, # Has reverse DNS update been performed by a server + hostname VARCHAR(255) # The FQDN of the client + ) ENGINE = INNODB; + + +# Create search indexes for lease4 table +# index by hwaddr and subnet_id +CREATE INDEX lease4_by_hwaddr_subnet_id ON lease4 (hwaddr, subnet_id); + +# index by client_id and subnet_id +CREATE INDEX lease4_by_client_id_subnet_id ON lease4 (client_id, subnet_id); + +# 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 + duid VARBINARY(128), # DUID + valid_lifetime INT UNSIGNED, # Length of the lease (seconds) + expire TIMESTAMP, # Expiration time of the lease + subnet_id INT UNSIGNED, # Subnet identification + pref_lifetime INT UNSIGNED, # Preferred lifetime + lease_type TINYINT, # Lease type (see lease6_types + # table for possible values) + iaid INT UNSIGNED, # See Section 10 of RFC 3315 + prefix_len TINYINT UNSIGNED, # For IA_PD only + fqdn_fwd BOOL, # Has forward DNS update been performed by a server + fqdn_rev BOOL, # Has reverse DNS update been performed by a server + hostname VARCHAR(255) # The FQDN of the client + + ) ENGINE = INNODB; + +# Create search indexes for lease4 table +# index by iaid, subnet_id, and duid +CREATE INDEX lease6_by_iaid_subnet_id_duid ON lease6 (iaid, subnet_id, duid); + +# ... and a definition of lease6 types. This table is a convenience for +# users of the database - if they want to view the lease table and use the +# type names, they can join this table with the lease6 table. +# Make sure those values match Lease6::LeaseType enum (see src/bin/dhcpsrv/ +# lease_mgr.h) +CREATE TABLE lease6_types ( + lease_type TINYINT PRIMARY KEY NOT NULL, # Lease type code. + name VARCHAR(5) # Name of the lease type + ) ENGINE = INNODB; + +START TRANSACTION; +INSERT INTO lease6_types VALUES (0, 'IA_NA'); # Non-temporary v6 addresses +INSERT INTO lease6_types VALUES (1, 'IA_TA'); # Temporary v6 addresses +INSERT INTO lease6_types VALUES (2, 'IA_PD'); # Prefix delegations +COMMIT; + +# Finally, the version of the schema. We start at 1.0 during development. +# This table is only modified during schema upgrades. For historical reasons +# (related to the names of the columns in the BIND 10 DNS database file), the +# first column is called 'version' and not 'major'. +CREATE TABLE schema_version ( + version INT PRIMARY KEY NOT NULL, # Major version number + minor INT # Minor version number + ) ENGINE = INNODB; +START TRANSACTION; +INSERT INTO schema_version VALUES (1, 0); +COMMIT; + +# This line concludes database initialization to version 1.0. + +# This line starts database upgrade to version 2.0. +ALTER TABLE lease6 + ADD COLUMN hwaddr varbinary(20), # Hardware/MAC address, typically only 6 + # bytes is used, but some hardware (e.g. + # Infiniband) use up to 20. + ADD COLUMN hwtype smallint unsigned, # hardware type (16 bits) + ADD COLUMN hwaddr_source int unsigned; # Hardware source. See description + # of lease_hwaddr_source below. + +# Kea keeps track of the hardware/MAC address source, i.e. how the address +# was obtained. Depending on the technique and your network topology, it may +# be more or less trustworthy. This table is a convenience for +# users of the database - if they want to view the lease table and use the +# type names, they can join this table with the lease6 table. For details, +# see constants defined in src/lib/dhcp/dhcp/pkt.h for detailed explanation. +CREATE TABLE lease_hwaddr_source ( + hwaddr_source INT PRIMARY KEY NOT NULL, + name VARCHAR(40) +) ENGINE = INNODB; + +# Hardware address obtained from raw sockets +INSERT INTO lease_hwaddr_source VALUES (1, 'HWADDR_SOURCE_RAW'); + +# Hardware address converted from IPv6 link-local address with EUI-64 +INSERT INTO lease_hwaddr_source VALUES (2, 'HWADDR_SOURCE_IPV6_LINK_LOCAL'); + +# Hardware address extracted from client-id (duid) +INSERT INTO lease_hwaddr_source VALUES (4, 'HWADDR_SOURCE_DUID'); + +# Hardware address extracted from client address relay option (RFC6939) +INSERT INTO lease_hwaddr_source VALUES (8, 'HWADDR_SOURCE_CLIENT_ADDR_RELAY_OPTION'); + +# Hardware address extracted from remote-id option (RFC4649) +INSERT INTO lease_hwaddr_source VALUES (16, 'HWADDR_SOURCE_REMOTE_ID'); + +# Hardware address extracted from subscriber-id option (RFC4580) +INSERT INTO lease_hwaddr_source VALUES (32, 'HWADDR_SOURCE_SUBSCRIBER_ID'); + +# Hardware address extracted from docsis options +INSERT INTO lease_hwaddr_source VALUES (64, 'HWADDR_SOURCE_DOCSIS'); + +UPDATE schema_version SET version='2', minor='0'; + +# This line concludes database upgrade to version 2.0. + +# This line starts database upgrade to version 3.0. +# Upgrade extending MySQL schema with the ability to store hosts. + +CREATE TABLE IF NOT EXISTS hosts ( + host_id INT UNSIGNED NOT NULL AUTO_INCREMENT, + dhcp_identifier VARBINARY(128) NOT NULL, + dhcp_identifier_type TINYINT NOT NULL, + dhcp4_subnet_id INT UNSIGNED NULL, + dhcp6_subnet_id INT UNSIGNED NULL, + ipv4_address INT UNSIGNED NULL, + hostname VARCHAR(255) NULL, + dhcp4_client_classes VARCHAR(255) NULL, + dhcp6_client_classes VARCHAR(255) NULL, + PRIMARY KEY (host_id), + INDEX key_dhcp4_identifier_subnet_id (dhcp_identifier ASC , dhcp_identifier_type ASC), + INDEX key_dhcp6_identifier_subnet_id (dhcp_identifier ASC , dhcp_identifier_type ASC , dhcp6_subnet_id ASC) +) ENGINE=INNODB; +-- ----------------------------------------------------- +-- Table `ipv6_reservations` +-- ----------------------------------------------------- +CREATE TABLE IF NOT EXISTS ipv6_reservations ( + reservation_id INT NOT NULL AUTO_INCREMENT, + address VARCHAR(39) NOT NULL, + prefix_len TINYINT(3) UNSIGNED NOT NULL DEFAULT 128, + type TINYINT(4) UNSIGNED NOT NULL DEFAULT 0, + dhcp6_iaid INT UNSIGNED NULL, + host_id INT UNSIGNED NOT NULL, + PRIMARY KEY (reservation_id), + INDEX fk_ipv6_reservations_host_idx (host_id ASC), + CONSTRAINT fk_ipv6_reservations_Host FOREIGN KEY (host_id) + REFERENCES hosts (host_id) + ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=INNODB; +-- ----------------------------------------------------- +-- Table `dhcp4_options` +-- ----------------------------------------------------- +CREATE TABLE IF NOT EXISTS dhcp4_options ( + option_id INT UNSIGNED NOT NULL AUTO_INCREMENT, + code TINYINT UNSIGNED NOT NULL, + value BLOB NULL, + formatted_value TEXT NULL, + space VARCHAR(128) NULL, + persistent TINYINT(1) NOT NULL DEFAULT 0, + dhcp_client_class VARCHAR(128) NULL, + dhcp4_subnet_id INT NULL, + host_id INT UNSIGNED NULL, + PRIMARY KEY (option_id), + UNIQUE INDEX option_id_UNIQUE (option_id ASC), + INDEX fk_options_host1_idx (host_id ASC), + CONSTRAINT fk_options_host1 FOREIGN KEY (host_id) + REFERENCES hosts (host_id) + ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=INNODB; +-- ----------------------------------------------------- +-- Table `dhcp6_options` +-- ----------------------------------------------------- +CREATE TABLE IF NOT EXISTS dhcp6_options ( + option_id INT UNSIGNED NOT NULL AUTO_INCREMENT, + code INT UNSIGNED NOT NULL, + value BLOB NULL, + formatted_value TEXT NULL, + space VARCHAR(128) NULL, + persistent TINYINT(1) NOT NULL DEFAULT 0, + dhcp_client_class VARCHAR(128) NULL, + dhcp6_subnet_id INT NULL, + host_id INT UNSIGNED NULL, + PRIMARY KEY (option_id), + UNIQUE INDEX option_id_UNIQUE (option_id ASC), + INDEX fk_options_host1_idx (host_id ASC), + CONSTRAINT fk_options_host10 FOREIGN KEY (host_id) + REFERENCES hosts (host_id) + ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=INNODB; + +DELIMITER $$ +CREATE TRIGGER host_BDEL BEFORE DELETE ON hosts FOR EACH ROW +-- Edit trigger body code below this line. Do not edit lines above this one +BEGIN +DELETE FROM ipv6_reservations WHERE ipv6_reservations.host_id = OLD.host_id; +END +$$ +DELIMITER ; + +UPDATE schema_version +SET version = '3', minor = '0'; +# This line concludes database upgrade to version 3.0. + +# This line starts database upgrade to version 4.0. +# Upgrade extending MySQL schema with the state columns for lease tables. + +# Add state column to the lease4 table. +ALTER TABLE lease4 + ADD COLUMN state INT UNSIGNED DEFAULT 0; + +# Add state column to the lease6 table. +ALTER TABLE lease6 + ADD COLUMN state INT UNSIGNED DEFAULT 0; + +# Create indexes for querying leases in a given state and segregated +# by the expiration time. One of the applications is to retrieve all +# expired leases. However, these indexes can be also used to retrieve +# leases in a given state regardless of the expiration time. +CREATE INDEX lease4_by_state_expire ON lease4 (state ASC, expire ASC); +CREATE INDEX lease6_by_state_expire ON lease6 (state ASC, expire ASC); + +# Create table holding mapping of the lease states to their names. +# This is not used in queries from the DHCP server but rather in +# direct queries from the lease database management tools. +CREATE TABLE IF NOT EXISTS lease_state ( + state INT UNSIGNED PRIMARY KEY NOT NULL, + name VARCHAR(64) NOT NULL +) ENGINE=INNODB; + +# Insert currently defined state names. +INSERT INTO lease_state VALUES (0, 'default'); +INSERT INTO lease_state VALUES (1, 'declined'); +INSERT INTO lease_state VALUES (2, 'expired-reclaimed'); + +# Add a constraint that any state value added to the lease4 must +# map to a value in the lease_state table. +ALTER TABLE lease4 + ADD CONSTRAINT fk_lease4_state FOREIGN KEY (state) + REFERENCES lease_state (state); + +# Add a constraint that any state value added to the lease6 must +# map to a value in the lease_state table. +ALTER TABLE lease6 + ADD CONSTRAINT fk_lease6_state FOREIGN KEY (state) + REFERENCES lease_state (state); + +# Add a constraint that lease type in the lease6 table must map +# to a lease type defined in the lease6_types table. +ALTER TABLE lease6 + ADD CONSTRAINT fk_lease6_type FOREIGN KEY (lease_type) + REFERENCES lease6_types (lease_type); + +# Modify the name of one of the HW address sources, and add a new one. +UPDATE lease_hwaddr_source + SET name = 'HWADDR_SOURCE_DOCSIS_CMTS' + WHERE hwaddr_source = 64; + +INSERT INTO lease_hwaddr_source VALUES (128, 'HWADDR_SOURCE_DOCSIS_MODEM'); + +# Add UNSIGNED to match with the lease6. +ALTER TABLE lease_hwaddr_source + MODIFY COLUMN hwaddr_source INT UNSIGNED NOT NULL; + +# Add a constraint that non-null hwaddr_source in the lease6 table +# must map to an entry in the lease_hwaddr_source. +ALTER TABLE lease6 + ADD CONSTRAINT fk_lease6_hwaddr_source FOREIGN KEY (hwaddr_source) + REFERENCES lease_hwaddr_source (hwaddr_source); + +# FUNCTION that returns a result set containing the column names for lease4 dumps +DROP PROCEDURE IF EXISTS lease4DumpHeader; +DELIMITER $$ +CREATE PROCEDURE lease4DumpHeader() +BEGIN +SELECT 'address,hwaddr,client_id,valid_lifetime,expire,subnet_id,fqdn_fwd,fqdn_rev,hostname,state'; +END $$ +DELIMITER ; + +# FUNCTION that returns a result set containing the data for lease4 dumps +DROP PROCEDURE IF EXISTS lease4DumpData; +DELIMITER $$ +CREATE PROCEDURE lease4DumpData() +BEGIN +SELECT + INET_NTOA(l.address), + IFNULL(HEX(l.hwaddr), ''), + IFNULL(HEX(l.client_id), ''), + l.valid_lifetime, + l.expire, + l.subnet_id, + l.fqdn_fwd, + l.fqdn_rev, + l.hostname, + s.name +FROM + lease4 l + LEFT OUTER JOIN lease_state s on (l.state = s.state) +ORDER BY l.address; +END $$ +DELIMITER ; + +# FUNCTION that returns a result set containing the column names for lease6 dumps +DROP PROCEDURE IF EXISTS lease6DumpHeader; +DELIMITER $$ +CREATE PROCEDURE lease6DumpHeader() +BEGIN +SELECT 'address,duid,valid_lifetime,expire,subnet_id,pref_lifetime,lease_type,iaid,prefix_len,fqdn_fwd,fqdn_rev,hostname,hwaddr,hwtype,hwaddr_source,state'; +END $$ +DELIMITER ; + +# FUNCTION that returns a result set containing the data for lease6 dumps +DROP PROCEDURE IF EXISTS lease6DumpData; +DELIMITER $$ +CREATE PROCEDURE lease6DumpData() +BEGIN +SELECT + l.address, + IFNULL(HEX(l.duid), ''), + l.valid_lifetime, + l.expire, + l.subnet_id, + l.pref_lifetime, + IFNULL(t.name, ''), + l.iaid, + l.prefix_len, + l.fqdn_fwd, + l.fqdn_rev, + l.hostname, + IFNULL(HEX(l.hwaddr), ''), + IFNULL(l.hwtype, ''), + IFNULL(h.name, ''), + IFNULL(s.name, '') +FROM lease6 l + left outer join lease6_types t on (l.lease_type = t.lease_type) + left outer join lease_state s on (l.state = s.state) + left outer join lease_hwaddr_source h on (l.hwaddr_source = h.hwaddr_source) +ORDER BY l.address; +END $$ +DELIMITER ; + +# Update the schema version number +UPDATE schema_version +SET version = '4', minor = '0'; + +# This line concludes database upgrade to version 4.0. + +# In the event hardware address cannot be determined, we need to satisfy +# foreign key constraint between lease6 and lease_hardware_source +INSERT INTO lease_hwaddr_source VALUES (0, 'HWADDR_SOURCE_UNKNOWN'); + +# Update the schema version number +UPDATE schema_version +SET version = '4', minor = '1'; + +# This line concludes database upgrade to version 4.1. + +# Update index used for searching DHCPv4 reservations by identifier and subnet id. +# This index is now unique (to prevent duplicates) and includes DHCPv4 subnet +# identifier. +DROP INDEX key_dhcp4_identifier_subnet_id ON hosts; +CREATE UNIQUE INDEX key_dhcp4_identifier_subnet_id ON hosts (dhcp_identifier ASC , dhcp_identifier_type ASC , dhcp4_subnet_id ASC); + +# Update index used for searching DHCPv6 reservations by identifier and subnet id. +# This index is now unique to prevent duplicates. +DROP INDEX key_dhcp6_identifier_subnet_id ON hosts; +CREATE UNIQUE INDEX key_dhcp6_identifier_subnet_id ON hosts (dhcp_identifier ASC , dhcp_identifier_type ASC , dhcp6_subnet_id ASC); + +# Create index to search for reservations using IP address and subnet id. +# This unique index guarantees that there is only one occurrence of the +# particular IPv4 address for a given subnet. +CREATE UNIQUE INDEX key_dhcp4_ipv4_address_subnet_id ON hosts (ipv4_address ASC , dhcp4_subnet_id ASC); + +# Create index to search for reservations using address/prefix and prefix +# length. +CREATE UNIQUE INDEX key_dhcp6_address_prefix_len ON ipv6_reservations (address ASC , prefix_len ASC); + +# Create a table mapping host identifiers to their names. Values in this +# table are used as a foreign key in hosts table to guarantee that only +# identifiers present in host_identifier_type table are used in hosts +# table. +CREATE TABLE IF NOT EXISTS host_identifier_type ( + type TINYINT PRIMARY KEY NOT NULL, # Lease type code. + name VARCHAR(32) # Name of the lease type +) ENGINE = INNODB; + +START TRANSACTION; +INSERT INTO host_identifier_type VALUES (0, 'hw-address'); +INSERT INTO host_identifier_type VALUES (1, 'duid'); +INSERT INTO host_identifier_type VALUES (2, 'circuit-id'); +COMMIT; + +# Add a constraint that any identifier type value added to the hosts +# must map to a value in the host_identifier_type table. +ALTER TABLE hosts + ADD CONSTRAINT fk_host_identifier_type FOREIGN KEY (dhcp_identifier_type) + REFERENCES host_identifier_type (type); + +# Store DHCPv6 option code as 16-bit unsigned integer. +ALTER TABLE dhcp6_options MODIFY code SMALLINT UNSIGNED NOT NULL; + +# Subnet identifier is unsigned. +ALTER TABLE dhcp4_options MODIFY dhcp4_subnet_id INT UNSIGNED NULL; +ALTER TABLE dhcp6_options MODIFY dhcp6_subnet_id INT UNSIGNED NULL; + +# Scopes associate DHCP options stored in dhcp4_options and +# dhcp6_options tables with hosts, subnets, classes or indicate +# that they are global options. +CREATE TABLE IF NOT EXISTS dhcp_option_scope ( + scope_id TINYINT UNSIGNED PRIMARY KEY NOT NULL, + scope_name VARCHAR(32) +) ENGINE = INNODB; + +START TRANSACTION; +INSERT INTO dhcp_option_scope VALUES (0, 'global'); +INSERT INTO dhcp_option_scope VALUES (1, 'subnet'); +INSERT INTO dhcp_option_scope VALUES (2, 'client-class'); +INSERT INTO dhcp_option_scope VALUES (3, 'host'); +COMMIT; + +# Add scopes into table holding DHCPv4 options +ALTER TABLE dhcp4_options ADD COLUMN scope_id TINYINT UNSIGNED NOT NULL; +ALTER TABLE dhcp4_options + ADD CONSTRAINT fk_dhcp4_option_scope FOREIGN KEY (scope_id) + REFERENCES dhcp_option_scope (scope_id); + +# Add scopes into table holding DHCPv6 options +ALTER TABLE dhcp6_options ADD COLUMN scope_id TINYINT UNSIGNED NOT NULL; +ALTER TABLE dhcp6_options + ADD CONSTRAINT fk_dhcp6_option_scope FOREIGN KEY (scope_id) + REFERENCES dhcp_option_scope (scope_id); + +# Add UNSIGNED to reservation_id +ALTER TABLE ipv6_reservations + MODIFY reservation_id INT UNSIGNED NOT NULL AUTO_INCREMENT; + +# Add columns holding reservations for siaddr, sname and file fields +# carried within DHCPv4 message. +ALTER TABLE hosts ADD COLUMN dhcp4_next_server INT UNSIGNED NULL; +ALTER TABLE hosts ADD COLUMN dhcp4_server_hostname VARCHAR(64) NULL; +ALTER TABLE hosts ADD COLUMN dhcp4_boot_file_name VARCHAR(128) NULL; + +# Update the schema version number +UPDATE schema_version +SET version = '5', minor = '0'; +# This line concludes database upgrade to version 5.0. + +# Add missing 'client-id' and new 'flex-id' host identifier types. +INSERT INTO host_identifier_type VALUES (3, 'client-id'); +INSERT INTO host_identifier_type VALUES (4, 'flex-id'); + +# Recreate the trigger removing dependent host entries. +DROP TRIGGER host_BDEL; + +DELIMITER $$ +CREATE TRIGGER host_BDEL BEFORE DELETE ON hosts FOR EACH ROW +-- Edit trigger body code below this line. Do not edit lines above this one +BEGIN +DELETE FROM ipv6_reservations WHERE ipv6_reservations.host_id = OLD.host_id; +DELETE FROM dhcp4_options WHERE dhcp4_options.host_id = OLD.host_id; +DELETE FROM dhcp6_options WHERE dhcp6_options.host_id = OLD.host_id; +END +$$ +DELIMITER ; + + +# Update the schema version number +UPDATE schema_version +SET version = '5', minor = '1'; +# This line concludes database upgrade to version 5.1. + +# Notes: +# +# Indexes +# ======= +# It is likely that additional indexes will be needed. However, the +# increase in lookup performance from these will come at the expense +# of a decrease in performance during insert operations due to the need +# to update the indexes. For this reason, the need for additional indexes +# will be determined by experiment during performance tests. +# +# The most likely additional indexes will cover the following columns: +# +# hwaddr and client_id +# For lease stability: if a client requests a new lease, try to find an +# existing or recently expired lease for it so that it can keep using the +# same IP address. +# +# Field Sizes +# =========== +# If any of the VARxxx field sizes are altered, the lengths in the MySQL +# backend source file (mysql_lease_mgr.cc) must be correspondingly changed. +# +# Portability +# =========== +# The 'ENGINE = INNODB' on some tables is not portable to another database +# and will need to be removed. +# +# Some columns contain binary data so are stored as VARBINARY instead of +# VARCHAR. This may be non-portable between databases: in this case, the +# definition should be changed to VARCHAR. diff --git a/src/bin/admin/tests/mysql_tests.sh.in b/src/bin/admin/tests/mysql_tests.sh.in index 8fc4b44558..9cbfb285d8 100644 --- a/src/bin/admin/tests/mysql_tests.sh.in +++ b/src/bin/admin/tests/mysql_tests.sh.in @@ -1,6 +1,6 @@ #!/bin/sh -# Copyright (C) 2014-2017 Internet Systems Consortium, Inc. ("ISC") +# Copyright (C) 2014-2018 Internet Systems Consortium, Inc. ("ISC") # # This Source Code Form is subject to the terms of the Mozilla Public # License, v. 2.0. If a copy of the MPL was not distributed with this @@ -22,6 +22,38 @@ db_name="keatest" # Set location of the kea-admin. keaadmin=@abs_top_builddir@/src/bin/admin/kea-admin +# Convenience function for running an SQL statement +# param hdr - text message to prepend to any error +# param qry - SQL statement to run +# param exp_valu - optional expected value. This can be used IF the SQL statement +# generates a single value, such as a SELECT which returns one column for one row. +# Examples: +# +# qry="insert into lease6 (address, lease_type, subnet_id, state) values ($addr,$ltype,1,0);" +# run_statement "#2" "$qry" +# +# qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and state = 0"; +# run_statement "#3" "$qry" 1 +run_statement() { + hdr="$1";shift; + qry="$1";shift; + exp_value="$1"; + + # Execute the statment + value=`mysql_execute "${qry}"` + ERRCODE=$? + + # Execution should succeed + assert_eq 0 $ERRCODE "$hdr: SQL=[$qry] failed: (expected status code %d, returned %d)" + + # If there's an expected value, test it + if [ "x$exp_value" != "x" ] + then + assert_str_eq "$exp_value" "$value" "$hdr: SQL=[$qry] wrong: (expected value %s, returned %s)" + fi +} + + # Wipe all tables from the DB: mysql_wipe() { printf "Wiping whole database %s\n" $db_name @@ -189,7 +221,7 @@ mysql_upgrade_test() { mysql -u$db_user -p$db_password $db_name < @abs_top_srcdir@/src/bin/admin/tests/dhcpdb_create_1.0.mysql # Sanity check - verify that it reports version 1.0. - version=$(${keaadmin} lease-version mysql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir) + version=$(${keaadmin} lease-version mysql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir) assert_str_eq "1.0" ${version} "Expected kea-admin to return %s, returned value was %s" @@ -319,14 +351,14 @@ EOF assert_eq 0 $ERRCODE "select from dhcp_option_scope failed. (expected status code %d, returned %d)" assert_eq 4 "$count" "dhcp_option_scope does not contain correct number of entries. (expected %d, returned %d)" - #table: scope_id columns to dhcp4_options (upgrade 4.1 -> 5.0) + #table: scope_id columns to dhcp4_options (upgrade 4.1 -> 5.0) # verify that dhcp4_options table includes scope_id qry="select scope_id from dhcp4_options"; count=`mysql_execute "${qry}"` ERRCODE=$? assert_eq 0 $ERRCODE "select scope_id from dhcp4_options failed. (expected status code %d, returned %d)" - #table: scope_id columns to dhcp6_options (upgrade 4.1 -> 5.0) + #table: scope_id columns to dhcp6_options (upgrade 4.1 -> 5.0) # verify that dhcp6_options table includes scope_id qry="select scope_id from dhcp6_options"; count=`mysql_execute "${qry}"` @@ -362,7 +394,7 @@ EOF ERRCODE=$? assert_eq 0 $ERRCODE "select from host_identifier_type failed. (expected status code %d, returned %d)" assert_eq 5 "$count" "host_identifier_type does not contain correct number of entries. (expected count %d, returned %d)" - + #table: user_context columns to hosts, dhcp4_options and dhcp6_options (upgrade 5.2 -> 6.0) # verify that hosts table includes user_context qry="select user_context from hosts"; @@ -382,6 +414,8 @@ EOF ERRCODE=$? assert_eq 0 $ERRCODE "select user_context from dhcp6_options failed. (expected status code %d, returned %d)" + # lease4/6_stats changes are tested separately + # Verify upgraded schema reports version 6.0 version=$(${keaadmin} lease-version mysql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir) assert_str_eq "6.0" ${version} "Expected kea-admin to return %s, returned value was %s" @@ -512,6 +546,276 @@ insert into lease6 values(12,21,30,'2015-06-06 11:01:07',40,50,1,60,70,1,1,'thre test_finish 0 } +# Verifies lease4_stat trigger operations on +# an new, empty database. It inserts, updates, and +# deletes various leases, checking lease4_stat +# values along the way. +mysql_lease4_stat_test() { + test_start "mysql.lease4_stat_test" + + # Let's wipe the whole database + mysql_wipe + + # Ok, now let's initialize the database + ${keaadmin} lease-init mysql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir + ERRCODE=$? + assert_eq 0 $ERRCODE "kea-admin lease-init mysql returned non-zero status code %d, expected %d" + + # Verify lease4 stat table is present + qry="select count(subnet_id) from lease4_stat"; + run_statement "#1" "$qry" 0 + + # Insert lease4 + qry="insert into lease4 (address, subnet_id, state) values (111,1,0);" + run_statement "#2" "$qry" + + # Assigned state count should be 1 + qry="select leases from lease4_stat where subnet_id = 1 and state = 0"; + run_statement "#3" "$qry" 1 + + # Set lease state to declined + qry="update lease4 set state = 1 where address = 111;" + run_statement "#4" "$qry" + + # Leases state count for assigned should be 0 + qry="select leases from lease4_stat where subnet_id = 1 and state = 0"; + run_statement "#5" "$qry" 0 + + # Leases state count for declined should be 1 + qry="select leases from lease4_stat where subnet_id = 1 and state = 1"; + run_statement "#6" "$qry" 1 + + # Delete the lease + qry="delete from lease4 where address = 111;" + run_statement "#7" "$qry" + + # Leases state count for declined should be 0 + qry="select leases from lease4_stat where subnet_id = 1 and state = 1"; + run_statement "#8" "$qry" 0 + + # Let's wipe the whole database + mysql_wipe + + test_finish 0 +} + +# Verifies that lease6_stat triggers operate correctly +# for using a given address and lease_type. It will +# insert a lease, update it, and delete checking the +# lease stat counts along the way. It assumes the +# database has been created but is empty. +# param addr - address to use to add to subnet 1 +# param ltype - type of lease to create +mysql_lease6_stat_per_type() { + addr=$1;shift; + ltype=$1; + + # insert a lease6 for addr and ltype, state assigned + qry="insert into lease6 (address, lease_type, subnet_id, state) values ($addr,$ltype,1,0);" + run_statement "#2" "$qry" + + # assigned stat should be 1 + qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and state = 0"; + run_statement "#3" "$qry" 1 + + # update the lease, changing state to declined + qry="update lease6 set state = 1 where address = $addr;" + run_statement "#4" "$qry" + + # leases stat for assigned state should be 0 + qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and state = 0"; + run_statement "#5" "$qry" 0 + + # leases count for declined state should be 1 + qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and state = 1"; + run_statement "#6" "$qry" 1 + + # delete the lease + qry="delete from lease6 where address = 111;" + run_statement "#7" "$qry" + + # leases count for declined state should be 0 + qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and state = 0"; + run_statement "#6" "$qry" 0 +} + +# Verifies that lease6_stat triggers operation correctly +# for both NA and PD lease types, mysql_lease6_stat_per_type() +mysql_lease6_stat_test() { + + test_start "mysql.lease6_stat_test" + + # Let's wipe the whole database + mysql_wipe + + # Ok, now let's initialize the database + ${keaadmin} lease-init mysql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir + ERRCODE=$? + + assert_eq 0 $ERRCODE "kea-admin lease-init mysql returned non-zero status code %d, expected %d" + + # verify lease6 stat table is present + qry="select count(subnet_id) from lease6_stat" + run_statement "#1" "$qry" + + # Test for address 111, NA lease type + mysql_lease6_stat_per_type "111" "0" + + # Test for address 222, PD lease type + mysql_lease6_stat_per_type "222" "1" + + # Let's wipe the whole database + mysql_wipe + + test_finish 0 +} + +# Verifies that you can upgrade from earlier version and +# lease<4/6>_stat tables will be populated based on existing +# leases and that the stat triggers work properly. +mysql_lease_stat_upgrade_test() { + test_start "my_sql_lease_stat_upgrade_test" + + # Let's wipe the whole database + mysql_wipe + + # We need to create an older database with lease data so we can + # verify the upgrade mechanisms which prepopulate the lease stat + # tables. + # + # Initialize database to scheme 5.1. We need a schema that has lease state + # might as well used the one right before 6.0 which adds lease4/6_stat stuff. + mysql -u$db_user -p$db_password $db_name < @abs_top_srcdir@/src/bin/admin/tests/dhcpdb_create_5.1.mysql + + # Sanity check - verify that it reports version 5.1 + version=$(${keaadmin} lease-version mysql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir) + assert_str_eq "5.1" ${version} "Expected kea-admin to return %s, returned value was %s" + + # Now we need insert some leases to "migrate" for both v4 and v6 + qry=\ +"insert into lease4 (address, subnet_id, state) values (111,10,0);\ + insert into lease4 (address, subnet_id, state) values (222,10,0);\ + insert into lease4 (address, subnet_id, state) values (333,10,1);\ + insert into lease4 (address, subnet_id, state) values (444,10,2);\ + insert into lease4 (address, subnet_id, state) values (555,77,0);" + run_statement "insert v4 leases" "$qry" + + qry=\ +"insert into lease6 (address, lease_type, subnet_id, state) values (111,0,40,0);\ + insert into lease6 (address, lease_type, subnet_id, state) values (222,0,40,1);\ + insert into lease6 (address, lease_type, subnet_id, state) values (333,1,40,0);\ + insert into lease6 (address, lease_type, subnet_id, state) values (444,1,50,0);\ + insert into lease6 (address, lease_type, subnet_id, state) values (555,1,50,0);\ + insert into lease6 (address, lease_type, subnet_id, state) values (666,1,40,2);" + run_statement "insert v6 leases" "$qry" + + # Ok, we have a 5.1 database with leases. Let's upgrade it to 6.0 + ${keaadmin} lease-upgrade mysql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir + ERRCODE=$? + + # + # First we'll verify lease4_stats are correct after migration. + # + + # Assigned leases for subnet 10 should be 2 + qry="select leases from lease4_stat where subnet_id = 10 and state = 0" + run_statement "#4.1" "$qry" 2 + + # Assigned leases for subnet 77 should be 1 + qry="select leases from lease4_stat where subnet_id = 77 and state = 0" + run_statement "#4.2" "$qry" 1 + + # Should be no records for EXPIRED + qry="select count(subnet_id) from lease4_stat where state = 2" + run_statement "#4.3" "$qry" 0 + + # + # Now we'll verify v4 trigger operation for insert,update, and delete + # + + # Insert a new lease subnet 77 + qry="insert into lease4 (address, subnet_id, state) values (777,77,0);" + run_statement "#4.4" "$qry" + + # Assigned count for subnet 77 should be 2 + qry="select leases from lease4_stat where subnet_id = 77 and state = 0" + run_statement "#4.5" "$qry" 2 + + # Update the state of the new lease to declined + qry="update lease4 set state = 1 where address = 777;" + run_statement "#4.6" "$qry" + + # Assigned count for subnet 77 should be 1 again + qry="select leases from lease4_stat where subnet_id = 77 and state = 0" + run_statement "#4.7" "$qry" 1 + + # Declined count for subnet 77 should be 1 + qry="select leases from lease4_stat where subnet_id = 77 and state = 1" + run_statement "#4.8" "$qry" 1 + + # Delete the lease. + qry="delete from lease4 where address = 777;" + run_statement "#4.9" "$qry" + + # Declined count for subnet 77 should be 0 + qry="select leases from lease4_stat where subnet_id = 77 and state = 1" + run_statement "#4.10" "$qry" 0 + + # + # Next we'll verify lease6_stats are correct after migration. + # + + # Assigned leases for subnet 40 should be 1 + qry="select leases from lease6_stat where subnet_id = 40 and lease_type = 0 and state = 0" + run_statement "#6.1" "$qry" 1 + + # Assigned (PD) leases for subnet 40 should be 1 + qry="select leases from lease6_stat where subnet_id = 40 and lease_type = 1 and state = 0" + run_statement "#6.2" "$qry" 1 + + # Declined leases for subnet 40 should be 1 + qry="select leases from lease6_stat where subnet_id = 40 and lease_type = 0 and state = 1" + run_statement "#6.3" "$qry" 1 + + # Assigned (PD) leases for subnet 50 should be 2 + qry="select leases from lease6_stat where subnet_id = 50 and lease_type = 1 and state = 0" + run_statement "#6.4" "$qry" 2 + + # Should be no records for EXPIRED + qry="select count(subnet_id) from lease4_stat where state = 2" + run_statement "#6.5" "$qry" 0 + + # + # Finally we'll verify v6 trigger operation for insert,update, and delete + # + + # Insert a new lease subnet 50 + qry="insert into lease6 (address, subnet_id, lease_type, state) values (777,50,1,0);" + run_statement "#6.5" "$qry" + + # Assigned count for subnet 50 should be 3 + qry="select leases from lease6_stat where subnet_id = 50 and lease_type = 1 and state = 0" + run_statement "#6.6" "$qry" 3 + + # Update the state of the new lease to expired + qry="update lease6 set state = 2 where address = 777;" + run_statement "#6.7" "$qry" + + # Assigned count for subnet 50 should be 2 again + qry="select leases from lease6_stat where subnet_id = 50 and lease_type = 1 and state = 0" + run_statement "#6.8" "$qry" 2 + + # Delete another PD lease. + qry="delete from lease6 where address = 555;" + run_statement "#6.9" "$qry" + + # Assigned leases for subnet 50 should be 1 + qry="select leases from lease6_stat where subnet_id = 50 and lease_type = 1 and state = 0" + run_statement "#6.10" "$qry" 1 + + # Let's wipe the whole database + mysql_wipe +} mysql_lease_init_test mysql_host_reservation_init_test @@ -519,3 +823,6 @@ mysql_lease_version_test mysql_upgrade_test mysql_lease4_dump_test mysql_lease6_dump_test +mysql_lease4_stat_test +mysql_lease6_stat_test +mysql_lease_stat_upgrade_test diff --git a/src/share/database/scripts/mysql/upgrade_5.2_to_6.0.sh.in b/src/share/database/scripts/mysql/upgrade_5.2_to_6.0.sh.in index fbdb297a80..3bf129cce4 100644 --- a/src/share/database/scripts/mysql/upgrade_5.2_to_6.0.sh.in +++ b/src/share/database/scripts/mysql/upgrade_5.2_to_6.0.sh.in @@ -175,6 +175,21 @@ CREATE TRIGGER stat_lease6_delete AFTER DELETE ON lease6 END $$ DELIMITER ; +# Populate lease4_stat table based on existing leases +# We only care about assigned and declined states +INSERT INTO lease4_stat (subnet_id, state, leases) + SELECT subnet_id, state, count(state) + FROM lease4 WHERE state < 2 + GROUP BY subnet_id, state ORDER BY subnet_id; + +# Populate lease6_stat table based on existing leases +# We only care about assigned and declined states +INSERT INTO lease6_stat (subnet_id, lease_type, state, leases) + SELECT subnet_id, lease_type, state, count(state) + FROM lease6 WHERE state < 2 + GROUP BY subnet_id, lease_type, state + ORDER BY subnet_id; + # Update the schema version number UPDATE schema_version SET version = '6', minor = '0'; -- 2.47.2