path: root/src/bin/admin/tests/mysql_tests.sh.in

#!/bin/sh

# Copyright (C) 2014-2020 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/.

# shellcheck disable=SC1091
# SC1091: Not following: ... was not specified as input (see shellcheck -x).

# shellcheck disable=SC2154
# SC2154: ... is referenced but not assigned.
# Reason: some variables are sourced.

# Exit with error if commands exit with non-zero and if undefined variables are
# used.
set -eu

# Include common test library.
. "@abs_top_builddir@/src/lib/testutils/dhcp_test_lib.sh"

# Include admin utilities
. "@abs_top_srcdir@/src/bin/admin/admin-utils.sh"

# Set path to the production schema scripts
db_scripts_dir="@abs_top_srcdir@/src/share/database/scripts"

# Set location of the kea-admin.
kea_admin="@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_value - 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-}" # Optional value. If not given, replace with empty string.

    # Execute the statment
    run_command \
        mysql_execute "${qry}"
    value="${OUTPUT}"

    # Execution should succeed
    assert_eq 0 "${EXIT_CODE}" "$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}"

    run_command \
        mysql_execute_script "${db_scripts_dir}/mysql/dhcpdb_drop.mysql"

    assert_eq 0 "${EXIT_CODE}" "mysql-wipe: drop table sql failed, epected %d, returned %d"
}

mysql_db_init_test() {
    test_start "mysql.db-init"

    # Let's wipe the whole database
    mysql_wipe

    # Ok, now let's initialize the database
    run_command \
        "${kea_admin}" db-init mysql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"

    assert_eq 0 "${EXIT_CODE}" "kea-admin db-init mysql failed, expected %d, returned non-zero status code %d"

    # Ok, now let's check if the tables are indeed there.
    # First table: schema_version. Should have 2 columns: version and minor.
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT version, minor FROM schema_version;'
    assert_eq 0 "${EXIT_CODE}" "schema_version table is missing or broken. (expected status code %d, returned %d)"

    # Second table: lease4
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT address, hwaddr, client_id, valid_lifetime, expire, subnet_id, fqdn_fwd, fqdn_rev, hostname FROM lease4;'
    assert_eq 0 "${EXIT_CODE}" "lease4 table is missing or broken. (expected status code %d, returned %d)"

    # Third table: lease6
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT address, duid, valid_lifetime, expire, subnet_id, pref_lifetime, lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname, hwaddr, hwtype, hwaddr_source FROM lease6;'
    assert_eq 0 "${EXIT_CODE}" "lease6 table is missing or broken. (expected status code %d, returned %d)"

    # Fourth table: lease6_types
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT lease_type, name FROM lease6_types;'
    assert_eq 0 "${EXIT_CODE}" "lease6_types table is missing or broken. (expected status code %d, returned %d)"

    # Fifth table: lease_hwaddr_source
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT hwaddr_source, name FROM lease_hwaddr_source;'
    assert_eq 0 "${EXIT_CODE}" "lease_hwaddr_source table is missing or broken. (expected status code %d, returned %d)"

    # Let's wipe the whole database
    mysql_wipe

    test_finish 0
}

mysql_db_version_test() {
    test_start "mysql.db-version"

    # Let's wipe the whole database
    mysql_wipe

    # Do not create any table so db-version will raise an error
    printf 'Checking db-version error case...\n'
    run_command \
        "${kea_admin}" db-version mysql -u "${db_user}" -p "${db_password}" -n "${db_name}"
    assert_eq 1 "${EXIT_CODE}" "schema_version table still exists. (expected %d, exit code %d)"

    # Ok, now let's create a version 1.7
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
'CREATE TABLE schema_version (
    version INT PRIMARY KEY NOT NULL,
    minor INT
);
INSERT INTO schema_version VALUES (1, 7);'
    assert_eq 0 "${EXIT_CODE}" "schema_version table cannot be created. (expected %d, exit code %d)"

    run_command \
        "${kea_admin}" db-version mysql -u "${db_user}" -p "${db_password}" -n "${db_name}"
    version="${OUTPUT}"
    assert_str_eq "1.7" "${version}" "Expected kea-admin to return %s, returned value was %s"

    # Let's wipe the whole database
    mysql_wipe

    test_finish 0
}

mysql_host_reservation_init_test() {
    test_start "mysql.host_reservation-init"

    # Let's wipe the whole database
    mysql_wipe

    # Ok, now let's initialize the database
    run_command \
        "${kea_admin}" db-init mysql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
    assert_eq 0 "${EXIT_CODE}" "kea-admin db-init mysql failed, expected %d, returned non-zero status code %d"

    # Ok, now let's check if the tables are indeed there.
    # First table: schema_version. Should have 2 columns: version and minor.
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT version, minor FROM schema_version;'
    assert_eq 0 "${EXIT_CODE}" "schema_version table is missing or broken. (expected status code %d, returned %d)"

    # Second table: hosts
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT host_id, dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, dhcp6_subnet_id, ipv4_address, hostname, dhcp4_client_classes, dhcp6_client_classes, dhcp4_next_server, dhcp4_server_hostname, dhcp4_boot_file_name, auth_key FROM hosts;'
    assert_eq 0 "${EXIT_CODE}" "hosts table is missing or broken. (expected status code %d, returned %d)"

    # Third table: ipv6_reservations
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT reservation_id, address, prefix_len, type, dhcp6_iaid, host_id FROM ipv6_reservations;'
    assert_eq 0 "${EXIT_CODE}" "ipv6_reservations table is missing or broken. (expected status code %d, returned %d)"

    # Fourth table: dhcp4_options
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp4_subnet_id, host_id, scope_id FROM dhcp4_options;'
    assert_eq 0 "${EXIT_CODE}" "dhcp4_options table is missing or broken. (expected status code %d, returned %d)"

    # Fifth table: dhcp6_options
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp6_subnet_id, host_id, scope_id FROM dhcp6_options;'
    assert_eq 0 "${EXIT_CODE}" "dhcp6_options table is missing or broken. (expected status code %d, returned %d)"

    # Sixth table: host_identifier_type
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT type, name FROM host_identifier_type;'
    assert_eq 0 "${EXIT_CODE}" "host_identifier_type table is missing or broken. (expected status code %d, returned %d)"

    # Seventh table: dhcp_option_scope
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT scope_id, scope_name FROM dhcp_option_scope;'
    assert_eq 0 "${EXIT_CODE}" "dhcp_option_scope table is missing or broken. (expected status code %d, returned %d)"

    # Let's wipe the whole database
    mysql_wipe

    test_finish 0
}

# Upgrades an existing schema to a target newer version
# param target_version - desired schema version as "major.minor"
mysql_upgrade_schema_to_version() {
    target_version=$1

    # Check if the scripts directory exists at all.
    if [ ! -d ${db_scripts_dir}/mysql ]; then
        log_error "Invalid scripts directory: ${db_scripts_dir}/mysql"
        exit 1
    fi

    # Check if there are any files in it
    num_files=$(find ${db_scripts_dir}/mysql/upgrade*.sh -type f | wc -l)
    if [ "${num_files}" -eq 0 ]; then
        log_error "No scripts in ${db_scripts_dir}/mysql?"
        exit 1
    fi

    for script in "${db_scripts_dir}"/mysql/upgrade*.sh
    do
        if [ "${version}" = "${target_version}" ]
        then
            break
        fi

        echo "Processing $script file..."
        "${script}" --user="${db_user}" --password="${db_password}" "${db_name}"
        version=$(mysql_version)
    done

    echo "Schema upgraded to $version"
}

mysql_upgrade_test() {

    test_start "mysql.upgrade"

    # Let's wipe the whole database
    mysql_wipe

    # Initialize database to scheme 1.0.
    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=$("${kea_admin}" db-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"

    # Ok, we have a 1.0 database. Let's upgrade it to 9.5
    run_command \
        "${kea_admin}" db-upgrade mysql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
    assert_eq 0 "${EXIT_CODE}" "kea-admin db-upgrade mysql failed, expected %d, returned non-zero status code %d\n"

    # Let's check that the new tables are indeed there.

    #table: lease6 (upgrade 1.0 -> 2.0)
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT hwaddr, hwtype, hwaddr_source FROM lease6;'
    assert_eq 0 "${EXIT_CODE}" "lease6 table not upgraded to 2.0 (expected status code %d, returned %d)"

    #table: lease_hwaddr_source (upgrade 1.0 -> 2.0)
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT hwaddr_source, name FROM lease_hwaddr_source;'
    assert_eq 0 "${EXIT_CODE}" "lease_hwaddr_source table is missing or broken. (expected status code %d, returned %d)"

    #table: hosts (upgrade 2.0 -> 3.0)
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT host_id, dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, dhcp6_subnet_id, ipv4_address, hostname, dhcp4_client_classes, dhcp6_client_classes FROM hosts;'
    assert_eq 0 "${EXIT_CODE}" "hosts table is missing or broken. (expected status code %d, returned %d)"

    #table: ipv6_reservations (upgrade 2.0 -> 3.0)
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT reservation_id, address, prefix_len, type, dhcp6_iaid, host_id FROM ipv6_reservations;'
    assert_eq 0 "${EXIT_CODE}" "ipv6_reservations table is missing or broken. (expected status code %d, returned %d)"

    #table: dhcp4_options (upgrade 2.0 -> 3.0)
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp4_subnet_id, host_id FROM dhcp4_options;'
    assert_eq 0 "${EXIT_CODE}" "dhcp4_options table is missing or broken. (expected status code %d, returned %d)"

    #table: dhcp6_options (upgrade 2.0 -> 3.0)
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp6_subnet_id, host_id FROM dhcp6_options;'
    assert_eq 0 "${EXIT_CODE}" "dhcp6_options table is missing or broken. (expected status code %d, returned %d)"

    #table: lease_state table added (upgrade 3.0 -> 4.0)
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT state,name from lease_state;'
    assert_eq 0 "${EXIT_CODE}" "dhcp6_options table is missing or broken. (expected status code %d, returned %d)"

    #table: state column added to lease4 (upgrade 3.0 -> 4.0)
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT state from lease4;'
    assert_eq 0 "${EXIT_CODE}" "lease4 is missing state column. (expected status code %d, returned %d)"

    #table: state column added to lease6 (upgrade 3.0 -> 4.0)
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT state from lease6;'
    assert_eq 0 "${EXIT_CODE}" "lease6 is missing state column. (expected status code %d, returned %d)"

    #table: stored procedures for lease dumps added (upgrade 3.0 -> 4.0)
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'call lease4DumpHeader(); call lease4DumpData(); call lease6DumpHeader(); call lease6DumpHeader();'
    assert_eq 0 "${EXIT_CODE}" "lease dump stored procedures are missing or broken. (expected status code %d, returned %d)"

    #lease_hardware_source should have row for source = 0 (upgrade 4.0 -> 4.1)
    qry="select count(hwaddr_source) from lease_hwaddr_source where hwaddr_source = 0 and name='HWADDR_SOURCE_UNKNOWN';"
    run_command \
        mysql_execute "${qry}"
    count="${OUTPUT}"
    assert_eq 0 "${EXIT_CODE}" "select from lease_hwaddr_source failed. (expected status code %d, returned %d)"
    assert_eq 1 "${count}" "lease_hwaddr_source does not contain entry for HWADDR_SOURCE_UNKNOWN. (record count %d, expected %d)"

    # table: stored procedures for lease data dumps were modified (upgrade 4.0 -> 4.1)
    # verify  lease4DumpData has order by lease address
    qry="show create procedure lease4DumpData"
    run_command \
        mysql_execute "${qry}"
    assert_eq 0 "${EXIT_CODE}" "procedure text fetch for lease4DumpData failed. (returned status code %d, expected %d)"
    count=$(echo "${OUTPUT}" | grep -Fci 'order by l.address') || true
    assert_eq 1 "${count}" "lease4DumpData doesn't have order by clause. (returned count %d, expected %d)"

    # verify  lease6DumpData has order by lease address
    qry="show create procedure lease6DumpData"
    run_command \
        mysql_execute "${qry}"
    assert_eq 0 "${EXIT_CODE}" "procedure text fetch for lease6DumpData failed. (returned status code %d, expected %d)"
    count=$(echo "${OUTPUT}" | grep -Fci 'order by l.address') || true
    assert_eq 1 "${count}" "lease6DumpData doesn't have order by clause. (returned count %d, expected %d)"

    #table: host_identifier_type (upgrade 4.1 -> 5.0)
    # verify that host_identifier_type table exists.
    qry="select count(*) from host_identifier_type";
    run_command \
        mysql_execute "${qry}"
    count="${OUTPUT}"
    assert_eq 0 "${EXIT_CODE}" "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)"

    # verify that foreign key fk_host_identifier_type exists
    qry="show create table hosts";
    run_command \
        mysql_execute "${qry}"
    count=$(echo "${OUTPUT}" | grep -Fci -m 1 'fk_host_identifier_type') || true
    assert_eq 0 "${EXIT_CODE}" "show create table hosts failed. (expected status code %d, returned %d)"
    assert_eq 1 "${count}" "show create table hosts did not return correct number of fk_host_identifier_type instances. (expected %d, returned %d)"

    #table: dhcp_option_scope (upgrade 4.1 -> 5.0)
    # verify that dhcp_option_scope table exists.
    qry="select count(*) from dhcp_option_scope";
    run_command \
        mysql_execute "${qry}"
    count="${OUTPUT}"
    assert_eq 0 "${EXIT_CODE}" "select from dhcp_option_scope failed. (expected status code %d, returned %d)"
    # verify that dhcp_option_scope table contains correct number of entries.
    assert_eq 7 "${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)
    # verify that dhcp4_options table includes scope_id
    qry="select scope_id from dhcp4_options";
    run_command \
        mysql_execute "${qry}"
    count="${OUTPUT}"
    assert_eq 0 "${EXIT_CODE}" "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)
    # verify that dhcp6_options table includes scope_id
    qry="select scope_id from dhcp6_options";
    run_command \
        mysql_execute "${qry}"
    count="${OUTPUT}"
    assert_eq 0 "${EXIT_CODE}" "select scope_id from dhcp6_options failed. (expected status code %d, returned %d)"

    #table: DHCPv4 fixed field colums (upgrade 4.1 -> 5.0)
    # verify that hosts table has columns holding values for DHCPv4 fixed fields
    qry="select dhcp4_next_server, dhcp4_server_hostname, dhcp4_boot_file_name, auth_key from hosts";
    run_command \
        mysql_execute "${qry}"
    count="${OUTPUT}"
    assert_eq 0 "${EXIT_CODE}" "select dhcp4_next_server, dhcp4_server_hostname, dhcp4_boot_file_name, auth_key failed. (expected status code %d, returned %d)"

    # verify that dhcp4_subnet_id is unsigned
    qry="show columns from hosts like 'dhcp4_subnet_id'"
    run_command \
        mysql_execute "${qry}"
    assert_eq 0 "${EXIT_CODE}" "show columns from hosts like 'dhcp4_subnet_id' failed. (expected status code %d, returned %d)"
    count=$(echo "${OUTPUT}" | grep -Fci unsigned) || true
    assert_eq 1 "${count}" "dhcp4_subnet_id is not of unsigned type. (returned count %d, expected %d)"

    # verify that dhcp6_subnet_id is unsigned
    qry="show columns from hosts like 'dhcp6_subnet_id'"
    run_command \
        mysql_execute "${qry}"
    assert_eq 0 "${EXIT_CODE}" "show columns from hosts like 'dhcp6_subnet_id' failed. (expected status code %d, returned %d)"
    count=$(echo "${OUTPUT}" | grep -Fci unsigned) || true
    assert_eq 1 "${count}" "dhcp6_subnet_id is not of unsigned type. (expected count %d, returned %d)"

    #host_identifier_type should have rows for types 3 and 4 (upgrade 5.0 -> 5.1)
    qry="select count(*) from host_identifier_type";
    run_command \
        mysql_execute "${qry}"
    count="${OUTPUT}"
    assert_eq 0 "${EXIT_CODE}" "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";
    run_command \
        mysql_execute "${qry}"
    count="${OUTPUT}"
    assert_eq 0 "${EXIT_CODE}" "select user_context from hosts failed. (expected status code %d, returned %d)"

    # verify that dhcp4_options table includes user_context
    qry="select user_context from dhcp4_options";
    run_command \
        mysql_execute "${qry}"
    count="${OUTPUT}"
    assert_eq 0 "${EXIT_CODE}" "select user_context from dhcp4_options failed. (expected status code %d, returned %d)"

    # verify that dhcp6_options table includes user_context
    qry="select user_context from dhcp6_options";
    run_command \
        mysql_execute "${qry}"
    count="${OUTPUT}"
    assert_eq 0 "${EXIT_CODE}" "select user_context from dhcp6_options failed. (expected status code %d, returned %d)"

    # lease4/6_stats changes are tested separately

    #table: user_context to lease4 and lease6 (upgrade 6.0 -> 7.0)
    # verify that lease4 table includes user_context
    qry="select user_context from lease4";
    run_command \
        mysql_execute "${qry}"
    count="${OUTPUT}"
    assert_eq 0 "${EXIT_CODE}" "select user_context from lease4 failed. (expected status code %d, returned %d)"

    # verify that lease6 table includes user_context
    qry="select user_context from lease6";
    run_command \
        mysql_execute "${qry}"
    count="${OUTPUT}"
    assert_eq 0 "${EXIT_CODE}" "select user_context from lease6 failed. (expected status code %d, returned %d)"

    #table: logs (upgrade 6.0 -> 7.0)
    run_command \
        mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \
        'SELECT timestamp, address, log FROM logs;'
    assert_eq 0 "${EXIT_CODE}" "logs table is missing or broken. (expected status code %d, returned %d)"

    # table: modification (upgrade 6.0 -> 7.0)
    qry="select id, modification_type from modification"
    run_statement "modification"  "$qry"

    # table: modification table should have 3 entries (upgrade 6.0 -> 7.0)
    qry="select count(*) from modification"
    run_statement "modification count" "$qry" 3

    # table: dhcp4_server
    qry="select id, tag, description, modification_ts from dhcp4_server"
    run_statement "dhcp4_server" "$qry"

    # table: dhcp4_server - check if it contains default entry
    qry="select count(*) from dhcp4_server"
    run_statement "dhcp4_server" "$qry" 1

    # table: dhcp4_audit
    qry="select id, object_type, object_id, modification_type from dhcp4_audit"
    run_statement "dhcp4_audit" "$qry"

    # table: dhcp4_global_parameter
    qry="select id, name, value, parameter_type, modification_ts from dhcp4_global_parameter"
    run_statement "dhcp4_global_parameter" "$qry"

    # table: dhcp4_global_parameter_server
    qry="select parameter_id, server_id, modification_ts from dhcp4_global_parameter_server"
    run_statement "dhcp4_global_parameter_server" "$qry"

    # table: dhcp4_option_def
    qry="select id, code, name, space, type, modification_ts, is_array, encapsulate, record_types, user_context from dhcp4_option_def"
    run_statement "dhcp4_option_def" "$qry"

    # table: dhcp4_option_def_server
    qry="select option_def_id, server_id, modification_ts from dhcp4_option_def_server"
    run_statement "dhcp4_option_def_server" "$qry"

    # table: dhcp4_shared_network
    qry="select id, name, client_class, interface, match_client_id, modification_ts, rebind_timer, relay, renew_timer, require_client_classes, user_context, valid_lifetime, authoritative, calculate_tee_times, t1_percent, t2_percent, boot_file_name, next_server, server_hostname from dhcp4_shared_network"
    run_statement "dhcp4_shared_network" "$qry"

    # table: dhcp4_shared_network_server
    qry="select shared_network_id, server_id, modification_ts from dhcp4_shared_network_server"
    run_statement "dhcp4_shared_network_server" "$qry"

    # table: dhcp4_subnet
    qry="select subnet_prefix, 4o6_interface, 4o6_interface_id, 4o6_subnet, boot_file_name, client_class, interface, match_client_id, modification_ts, next_server, rebind_timer, relay, renew_timer, require_client_classes, server_hostname, shared_network_name, subnet_id, user_context, valid_lifetime, authoritative, calculate_tee_times, t1_percent, t2_percent from dhcp4_subnet"
    run_statement "dhcp4_subnet" "$qry"

    # table: dhcp4_pool
    qry="select id, start_address, end_address, subnet_id, modification_ts from dhcp4_pool"
    run_statement "dhcp4_pool" "$qry"

    # table: dhcp4_subnet_server
    qry="select subnet_id, server_id, modification_ts from dhcp4_subnet_server"
    run_statement "dhcp4_subnet_server" "$qry"

    # table: dhcp4_options (should include three new columns)
    qry="select shared_network_name, pool_id, modification_ts from dhcp4_options"
    run_statement "dhcp4_options" "$qry"

    # table: dhcp4_options_server
    qry="select option_id, server_id, modification_ts from dhcp4_options_server"
    run_statement "dhcp4_options_server" "$qry"

    # table: dhcp6_server
    qry="select id, tag, description, modification_ts from dhcp6_server"
    run_statement "dhcp6_server" "$qry"

    # table: dhcp6_server - check if it contains default entry
    qry="select count(*) from dhcp6_server"
    run_statement "dhcp6_server" "$qry" 1

    # table: dhcp6_audit
    qry="select id, object_type, object_id, modification_type from dhcp6_audit"
    run_statement "dhcp6_audit" "$qry"

    # table: dhcp6_global_parameter
    qry="select id, name, value, parameter_type, modification_ts from dhcp6_global_parameter"
    run_statement "dhcp6_global_parameter" "$qry"

    # table: dhcp6_global_parameter_server
    qry="select parameter_id, server_id, modification_ts from dhcp6_global_parameter_server"
    run_statement "dhcp6_global_parameter_server" "$qry"

    # table: dhcp6_option_def
    qry="select id, code, name, space, type, modification_ts, is_array, encapsulate, record_types, user_context from dhcp6_option_def"
    run_statement "dhcp6_option_def" "$qry"

    # table: dhcp6_option_def_server
    qry="select option_def_id, server_id, modification_ts from dhcp6_option_def_server"
    run_statement "dhcp6_option_def_server" "$qry"

    # table: dhcp6_shared_network
    qry="select id, name, client_class, interface, modification_ts, preferred_lifetime, rapid_commit, rebind_timer, relay, renew_timer, require_client_classes, user_context, valid_lifetime, calculate_tee_times, t1_percent, t2_percent, interface_id from dhcp6_shared_network"
    run_statement "dhcp6_shared_network" "$qry"

    # table: dhcp6_shared_network_server
    qry="select shared_network_id, server_id, modification_ts from dhcp6_shared_network_server"
    run_statement "dhcp6_shared_network" "$qry"

    # table: dhcp6_subnet
    qry="select subnet_prefix, client_class, interface, modification_ts, preferred_lifetime, rapid_commit, rebind_timer, relay, renew_timer, require_client_classes, shared_network_name, subnet_id, user_context, valid_lifetime, calculate_tee_times, t1_percent, t2_percent, interface_id from dhcp6_subnet"
    run_statement "dhcp6_subnet" "$qry"

    # table: dhcp6_subnet_server
    qry="select subnet_id, server_id, modification_ts from dhcp6_subnet_server"
    run_statement "dhcp6_subnet_server" "$qry"

    # table: dhcp6_pd_pool
    qry="select id, prefix_length, delegated_prefix_length, subnet_id, modification_ts from dhcp6_pd_pool"
    run_statement "dhcp6_pd_pool" "$qry"

    # table: dhcp6_pool
    qry="select id, start_address, end_address, subnet_id, modification_ts from dhcp6_pool"
    run_statement "dhcp6_pool" "$qry"

    # table: dhcp6_options (should include four new columns)
    qry="select shared_network_name, pool_id, pd_pool_id, modification_ts from dhcp6_options"
    run_statement "dhcp6_options" "$qry"

    # table: dhcp6_options_server
    qry="select option_id, server_id, modification_ts from dhcp6_options_server"
    run_statement "dhcp6_options_server" "$qry"

    # Schema upgrade from 7.0 to 8.0

    # Test that createAuditRevisionDHCP4 exists and creates entry in
    # the dhcp4_audit_revision table.
    qry="CALL createAuditRevisionDHCP4('2019-01-28 23:59:11', 'all', 'some log message', 0)"
    run_statement "createAuditRevisionDHCP4" "$qry"

    qry="SELECT COUNT(*) from dhcp4_audit_revision"
    run_statement "dhcp4_audit_revision count" "$qry" 1

    qry="SELECT id, modification_ts, server_id, log_message FROM dhcp4_audit_revision"
    run_statement "dhcp4_audit_revision" "$qry"

    # Test that createAuditEntryDHCP4 exists and creates entry in
    # the dhcp4_audit table.
    qry="SET @audit_revision_id = (SELECT id FROM dhcp4_audit_revision LIMIT 1); CALL createAuditEntryDHCP4('dhcp4_subnet', 1, 'create')"
    run_statement "createAuditEntryDHCP4" "$qry"

    qry="SELECT COUNT(*) FROM dhcp4_audit"
    run_statement "dhcp4_audit count" "$qry" 1

    qry="SELECT id, object_type, object_id, modification_type, revision_id FROM dhcp4_audit"
    run_statement "dhcp4_audit" "$qry"

    # Test that createOptionAuditDHCP4 exists can create an audit
    # entry.

    # First set the cascade_transaction session variable to check that
    # the procedure won't create the audit entry for the option when
    # this flag is set.
    qry="SET @audit_revision_id = (SELECT id FROM dhcp4_audit_revision LIMIT 1); SET @cascade_transaction = 1; CALL createOptionAuditDHCP4('create', 0, 1024, NULL, NULL, NULL, NULL, now())"
    run_statement "createOptionAuditDHCP4 cascade update" "$qry"

    # The number of rows matching the audit entry should be 0.
    qry="SELECT COUNT(*) FROM dhcp4_audit WHERE object_type = 'dhcp4_options' AND object_id = 1024";
    run_statement "createOptionAuditDHCP4 cascade update, entry not inserted" "$qry" 0;

    # This time set the cascade_update to 0 and expect that the
    # audit entry will be created for the option.
    qry="SET @audit_revision_id = (SELECT id FROM dhcp4_audit_revision LIMIT 1); SET @cascade_transaction = 0; CALL createOptionAuditDHCP4('create', 0, 1024, NULL, NULL, NULL, NULL, now())"
    run_statement "createOptionAuditDHCP4 cascade update" "$qry"

    qry="SELECT COUNT(*) FROM dhcp4_audit WHERE object_type = 'dhcp4_options' AND object_id = 1024";
    run_statement "createOptionAuditDHCP4 cascade update, entry not inserted" "$qry" 1;

    # Test that createAuditRevisionDHCP6 exists and creates entry in
    # the dhcp6_audit_revision table.
    qry="CALL createAuditRevisionDHCP6('2019-01-28 23:59:11', 'all', 'some log message', 0)"
    run_statement "createAuditRevisionDHCP6" "$qry"

    qry="SELECT COUNT(*) from dhcp6_audit_revision"
    run_statement "dhcp6_audit_revision count" "$qry" 1

    qry="SELECT id, modification_ts, server_id, log_message FROM dhcp6_audit_revision"
    run_statement "dhcp6_audit_revision" "$qry"

    # Test that createAuditEntryDHCP6 exists and creates entry in
    # the dhcp6_audit table.
    qry="SET @audit_revision_id = (SELECT id FROM dhcp6_audit_revision LIMIT 1); CALL createAuditEntryDHCP6('dhcp6_subnet', 1, 'create')"
    run_statement "createAuditEntryDHCP6" "$qry"

    qry="SELECT COUNT(*) FROM dhcp6_audit"
    run_statement "dhcp6_audit count" "$qry" 1

    qry="SELECT id, object_type, object_id, modification_type, revision_id FROM dhcp6_audit"
    run_statement "dhcp6_audit" "$qry"

    # Test that createOptionAuditDHCP6 exists can create an audit
    # entry.

    # First set the cascade_transaction session variable to check that
    # the procedure won't create the audit entry for the option when
    # this flag is set.
    qry="SET @audit_revision_id = (SELECT id FROM dhcp6_audit_revision LIMIT 1); SET @cascade_transaction = 1; CALL createOptionAuditDHCP6('create', 0, 1024, NULL, NULL, NULL, NULL, NULL, now())"
    run_statement "createOptionAuditDHCP6 cascade update" "$qry"

    # The number of rows matching the audit entry should be 0.
    qry="SELECT COUNT(*) FROM dhcp6_audit WHERE object_type = 'dhcp6_options' AND object_id = 1024";
    run_statement "createOptionAuditDHCP6 cascade update, entry not inserted" "$qry" 0;

    # This time set the cascade_update to 0 and expect that the
    # audit entry will be created for the option.
    qry="SET @audit_revision_id = (SELECT id FROM dhcp6_audit_revision LIMIT 1); SET @cascade_transaction = 0; CALL createOptionAuditDHCP6('create', 0, 1024, NULL, NULL, NULL, NULL, NULL,now())"
    run_statement "createOptionAuditDHCP6 cascade update" "$qry"

    qry="SELECT COUNT(*) FROM dhcp6_audit WHERE object_type = 'dhcp6_options' AND object_id = 1024";
    run_statement "createOptionAuditDHCP6 cascade update, entry not inserted" "$qry" 1;

    # New triggers aren't tested here because the extensive tests are
    # provided with the backend implementations.

    # parameter_data_type must exist and must have 4 rows.
    qry="SELECT COUNT(*) FROM parameter_data_type";
    run_statement "parameter_data_type count" "$qry" 4;

    # Schema upgrade from 8.0 to 8.2

    # New lifetime bounds.

    # table: dhcp4_shared_network
    qry="select id, name, client_class, interface, match_client_id, modification_ts, rebind_timer, relay, renew_timer, require_client_classes, user_context, valid_lifetime, min_valid_lifetime, max_valid_lifetime, authoritative, calculate_tee_times, t1_percent, t2_percent, boot_file_name, next_server, server_hostname from dhcp4_shared_network"
    run_statement "dhcp4_shared_network" "$qry"

    # table: dhcp4_subnet
    qry="select subnet_prefix, 4o6_interface, 4o6_interface_id, 4o6_subnet, boot_file_name, client_class, interface, match_client_id, modification_ts, next_server, rebind_timer, relay, renew_timer, require_client_classes, server_hostname, shared_network_name, subnet_id, user_context, valid_lifetime, min_valid_lifetime, max_valid_lifetime, authoritative, calculate_tee_times, t1_percent, t2_percent from dhcp4_subnet"
    run_statement "dhcp4_subnet" "$qry"

    # table: dhcp6_shared_network
    qry="select id, name, client_class, interface, modification_ts, preferred_lifetime, min_preferred_lifetime, max_preferred_lifetime,rapid_commit, rebind_timer, relay, renew_timer, require_client_classes, user_context, valid_lifetime, min_valid_lifetime, max_valid_lifetime, calculate_tee_times, t1_percent, t2_percent from dhcp6_shared_network"
    run_statement "dhcp6_shared_network" "$qry"

    # table: dhcp6_subnet
    qry="select subnet_prefix, client_class, interface, modification_ts, preferred_lifetime, min_preferred_lifetime, max_preferred_lifetime, rapid_commit, rebind_timer, relay, renew_timer, require_client_classes, shared_network_name, subnet_id, user_context, valid_lifetime, min_valid_lifetime, max_valid_lifetime, calculate_tee_times, t1_percent, t2_percent from dhcp6_subnet"
    run_statement "dhcp6_subnet" "$qry"

    # table: dhcp4_pool (should include three new columns)
    qry="select client_class, require_client_classes, user_context from dhcp4_pool"
    run_statement "dhcp4_pool" "$qry"

    # table: dhcp6_pd_pool (should include five new columns)
    qry="select excluded_prefix, excluded_prefix_length, client_class, require_client_classes, user_context from dhcp6_pd_pool"
    run_statement "dhcp6_pd_pool" "$qry"

    # table: dhcp6_pool (should include three new columns)
    qry="select client_class, require_client_classes, user_context from dhcp6_pool"
    run_statement "dhcp6_pool" "$qry"

    # Verify that dhcp4_option_def column name is is_array
    qry="select is_array from dhcp4_option_def"
    run_statement "dhcp4_option_def verify is_array column" "$qry"

    # Verify that dhcp6_option_def column name is is_array
    qry="select is_array from dhcp6_option_def"
    run_statement "dhcp6_option_def verify is_array column" "$qry"

    # Schema upgrade from 8.2 to 9.3

    # New DDNS columns.

    # table: dhcp4_shared_network (should include six new columns)
    qry="select ddns_send_updates, ddns_override_no_update, ddns_override_client_update, ddns_replace_client_name, ddns_generated_prefix, ddns_qualifying_suffix from dhcp4_shared_network"
    run_statement "dhcp4_shared_network" "$qry"

    # table: dhcp6_shared_network (should include six new columns)
    qry="select ddns_send_updates, ddns_override_no_update, ddns_override_client_update, ddns_replace_client_name, ddns_generated_prefix, ddns_qualifying_suffix from dhcp6_shared_network"
    run_statement "dhcp6_shared_network" "$qry"

    # table: dhcp4_subnet (should include six new columns)
    qry="select ddns_send_updates, ddns_override_no_update, ddns_override_client_update, ddns_replace_client_name, ddns_generated_prefix, ddns_qualifying_suffix from dhcp4_subnet"
    run_statement "dhcp4_subnet" "$qry"

    # table: dhcp6_subnet (should include six new columns)
    qry="select ddns_send_updates, ddns_override_no_update, ddns_override_client_update, ddns_replace_client_name, ddns_generated_prefix, ddns_qualifying_suffix from dhcp6_subnet"
    run_statement "dhcp6_subnet" "$qry"

    # Schema upgrade from 9.3 to 9.4.

    # Non unique indexes on hosts allowing multiple reservation for the same IP.

    insert_sql="\
insert into hosts(dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, ipv4_address) values (hex('010101010101'), 0, 1, inet_aton('192.0.2.0'));\
insert into hosts(dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, ipv4_address) values (hex('010101010102'), 0, 1, inet_aton('192.0.2.0'));"
    run_command \
        mysql_execute "$insert_sql"
    assert_eq 0 "${EXIT_CODE}" "insert into hosts failed, expected exit code %d, actual %d"

    # Schema upgrade from 9.4 to 9.5.

    # table: dhcp4_shared_network (reservation_mode replaced by reservations flags)
    qry="select reservations_global, reservations_in_subnet, reservations_out_of_pool from dhcp4_shared_network"
    run_statement "dhcp4_shared_network" "$qry"

    qry="show columns from dhcp4_shared_network like 'reservation_mode'";
    run_command \
        mysql_execute "${qry}"
    assert_eq 0 "${EXIT_CODE}" "show columns from dhcp4_shared_network like 'reservation_mode' failed. (expected status code %d, returned %d)"
    count=$(echo "${OUTPUT}" | grep -Fci reservation) || true
    assert_eq 0 "${count}" "dhcp4_shared_network has still reservation_mode column. (returned count %d, expected %d)"

    # table: dhcp4_subnet (reservation_mode replaced by reservations flags)
    qry="select reservations_global, reservations_in_subnet, reservations_out_of_pool from dhcp4_subnet"
    run_statement "dhcp4_subnet" "$qry"

    qry="show columns from dhcp4_subnet like 'reservation_mode'";
    run_command \
        mysql_execute "${qry}"
    assert_eq 0 "${EXIT_CODE}" "show columns from dhcp4_subnet like 'reservation_mode' failed. (expected status code %d, returned %d)"
    count=$(echo "${OUTPUT}" | grep -Fci reservation) || true
    assert_eq 0 "${count}" "dhcp4_subnet has still reservation_mode column. (returned count %d, expected %d)"

    # table: dhcp6_shared_network (reservation_mode replaced by reservations flags)
    qry="select reservations_global, reservations_in_subnet, reservations_out_of_pool from dhcp6_shared_network"
    run_statement "dhcp6_shared_network" "$qry"

    qry="show columns from dhcp6_shared_network like 'reservation_mode'";
    run_command \
        mysql_execute "${qry}"
    assert_eq 0 "${EXIT_CODE}" "show columns from dhcp6_shared_network like 'reservation_mode' failed. (expected status code %d, returned %d)"
    count=$(echo "${OUTPUT}" | grep -Fci reservation) || true
    assert_eq 0 "${count}" "dhcp6_shared_network has still reservation_mode column. (returned count %d, expected %d)"

    # table: dhcp6_subnet (reservation_mode replaced by reservations flags)
    qry="select reservations_global, reservations_in_subnet, reservations_out_of_pool from dhcp6_subnet"
    run_statement "dhcp6_subnet" "$qry"

    qry="show columns from dhcp6_subnet like 'reservation_mode'";
    run_command \
        mysql_execute "${qry}"
    assert_eq 0 "${EXIT_CODE}" "show columns from dhcp6_subnet like 'reservation_mode' failed. (expected status code %d, returned %d)"
    count=$(echo "${OUTPUT}" | grep -Fci reservation) || true
    assert_eq 0 "${count}" "dhcp6_subnet has still reservation_mode column. (returned count %d, expected %d)"

    # Verify upgraded schema reports version 9.5
    version=$("${kea_admin}" db-version mysql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}")
    assert_str_eq "9.5" "${version}" "Expected kea-admin to return %s, returned value was %s"

    # Let's wipe the whole database
    mysql_wipe

    test_finish 0
}

mysql_lease4_dump_test() {
    test_start "mysql.lease4_dump_test"

    test_dir="@abs_top_srcdir@/src/bin/admin/tests"
    output_dir="@abs_top_builddir@/src/bin/admin/tests"

    output_file="$output_dir/data/mysql.lease4_dump_test.output.csv"
    tmp_file="$output_file.tmp"

    ref_file="$test_dir/data/mysql.lease4_dump_test.reference.csv"

    # wipe out any residuals from prior failed runs
    if [ -e $output_file ]
    then
        rm $output_file
    fi

    if [ -e $tmp_file ]
    then
        rm $tmp_file
    fi

    # Let's wipe the whole database
    mysql_wipe

    # Ok, now let's initialize the database
    run_command \
        "${kea_admin}" db-init mysql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
    assert_eq 0 "${EXIT_CODE}" "could not create database, expected exit code %d, actual %d"

    # Insert the reference record
    insert_sql="\
insert into lease4 values(10,20,30,40,'2015-01-01 01:15:30',50,1,1,'one.example.com', 0,NULL);\
insert into lease4 values(11,NULL,123,40,'2015-02-02 02:30:45',50,1,1,'', 1,NULL);\
insert into lease4 values(12,22,NULL,40,'2015-03-03 11:01:07',50,1,1,'three.example.com', 2,NULL);"

    run_command \
        mysql_execute "$insert_sql"
    assert_eq 0 "${EXIT_CODE}" "insert into lease4 failed, expected exit code %d, actual %d"

    # Dump lease4 to output_file
    run_command \
        "${kea_admin}" lease-dump mysql -4 -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}" -o $output_file
    assert_eq 0 "${EXIT_CODE}" "kea-admin lease-dump -4 failed, expected exit code %d, actual %d"

    # Compare the dump output to reference file, they should be identical
    run_command \
        cmp -s "${output_file}" "${ref_file}"
    assert_eq 0 "${EXIT_CODE}" "dump file does not match reference file, expected exit code %d, actual %d"

    # remove the output file
    rm $output_file

    # Let's wipe the whole database
    mysql_wipe

    test_finish 0
}

mysql_lease6_dump_test() {
    test_start "mysql.lease6_dump_test"

    test_dir="@abs_top_srcdir@/src/bin/admin/tests"
    output_dir="@abs_top_builddir@/src/bin/admin/tests"

    output_file="$output_dir/data/mysql.lease6_dump_test.output.csv"
    tmp_file="$output_file.tmp"

    ref_file="$test_dir/data/mysql.lease6_dump_test.reference.csv"

    # wipe out any residuals from prior failed runs
    if [ -e $output_file ]
    then
        rm $output_file
    fi

    if [ -e $tmp_file ]
    then
        rm $tmp_file
    fi

    # Let's wipe the whole database
    mysql_wipe

    # Ok, now let's initialize the database
    run_command \
        "${kea_admin}" db-init mysql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
    assert_eq 0 "${EXIT_CODE}" "could not create database, expected exit code %d, actual %d"

    # Insert the reference record
    insert_sql="\
insert into lease6 values(10,20,30,'2015-04-04 01:15:30',40,50,1,60,70,1,1,'one.example.com',80,90,16,0,NULL);\
insert into lease6 values(11,NULL,30,'2015-05-05 02:30:45',40,50,1,60,70,1,1,'',80,90,1,1,NULL);\
insert into lease6 values(12,21,30,'2015-06-06 11:01:07',40,50,1,60,70,1,1,'three.example.com',80,90,4,2,NULL);"

    run_command \
        mysql_execute "$insert_sql"
    assert_eq 0 "${EXIT_CODE}" "insert into lease6 failed, expected exit code %d, actual %d"

    # Dump lease4 to output_file
    run_command \
        "${kea_admin}" lease-dump mysql -6 -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}" -o $output_file
    assert_eq 0 "${EXIT_CODE}" "kea-admin lease-dump -6 failed, status code %d"

    # Compare the dump output to reference file, they should be identical
    run_command \
        cmp -s "${output_file}" "${ref_file}"
    assert_eq 0 "${EXIT_CODE}" "dump file does not match reference file, expected exit code %d, actual %d"

    # remove the output file
    rm $output_file

    # Let's wipe the whole database
    mysql_wipe

    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
    run_command \
        "${kea_admin}" db-init mysql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
    assert_eq 0 "${EXIT_CODE}" "kea-admin db-init mysql failed, expected %d, returned non-zero status code %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 = $addr;"
    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
    run_command \
        "${kea_admin}" db-init mysql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
    assert_eq 0 "${EXIT_CODE}" "kea-admin db-init mysql failed, expected %d, returned non-zero status code %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 "mysql.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 1.0.
    mysql -u"${db_user}" -p"${db_password}" "${db_name}" < "@abs_top_srcdir@/src/bin/admin/tests/dhcpdb_create_1.0.mysql"

    # Now upgrade to schema 4.0, this has lease_state in it
    mysql_upgrade_schema_to_version 4.0

    # 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 4.0 database with leases. Let's upgrade it to 6.0
    run_command \
        "${kea_admin}" db-upgrade mysql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"

    #
    # 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

    test_finish 0
}

mysql_lease_stat_recount_test() {
    test_start "mysql.lease_stat_recount_test"

    # Let's wipe the whole database
    mysql_wipe

    # Ok, now let's initialize the database
    run_command \
        "${kea_admin}" db-init mysql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
    assert_eq 0 "${EXIT_CODE}" "kea-admin db-init mysql failed, expected %d, returned non-zero status code %d"

    # Now we need insert some leases to "recount"
    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"

    # Now we change some counters.
    qry=\
"insert into lease4_stat (subnet_id, state, leases) values (20,0,1);\
 update lease4_stat set leases = 5 where subnet_id = 10 and state = 0;
 delete from lease4_stat where subnet_id = 10 and state = 2;"
    run_statement "change v4 stats" "$qry"

    qry=\
"insert into lease6_stat (subnet_id, lease_type, state, leases) values (20,1,0,1);
 update lease6_stat set leases = 5 where subnet_id = 40 and lease_type = 0 and state = 0;
 delete from lease6_stat where subnet_id = 40 and lease_type = 1 and state = 2;"
    run_statement "change v6 stats" "$qry"

    # Recount all statistics from scratch.
    run_command \
        "${kea_admin}" stats-recount mysql -u "${db_user}" -p "${db_password}" -n "${db_name}"
    assert_eq 0 "${EXIT_CODE}" "kea-admin stats-recount mysql failed, expected %d, returned non-zero status code %d"

    #
    # First we'll verify lease4_stats are correct after recount.
    #

    # 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

    #
    # Next we'll verify lease6_stats are correct after recount.
    #

    # 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

    # Let's wipe the whole database
    mysql_wipe

    test_finish 0
}

# Verifies that you can upgrade from an earlier version and
# that unused subnet ID values in hosts and options tables are
# converted to NULL.
mysql_unused_subnet_id_test() {
    test_start "mysql.unused_subnet_id_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 convert subnet id values
    #
    # Initialize database to scheme 1.0.
    mysql -u"${db_user}" -p"${db_password}" "${db_name}" < "@abs_top_srcdir@/src/bin/admin/tests/dhcpdb_create_1.0.mysql"

    # Now upgrade to schema 6.0, this has lease_state in it
    mysql_upgrade_schema_to_version 6.0

    # Now we need insert some hosts to "migrate" for both v4 and v6
    qry=\
"insert into hosts (dhcp_identifier_type, dhcp_identifier, dhcp4_subnet_id, dhcp6_subnet_id, hostname)\
   values (0, '0123456', 0, 0, 'both'); \
 insert into hosts (dhcp_identifier_type, dhcp_identifier, dhcp4_subnet_id, dhcp6_subnet_id, hostname)\
   values (0, '1123456', 4, 0, 'v4only');
 insert into hosts (dhcp_identifier_type, dhcp_identifier, dhcp4_subnet_id, dhcp6_subnet_id, hostname)\
   values (0, '2123456', 0, 6, 'v6only');\
 insert into hosts (dhcp_identifier_type, dhcp_identifier, dhcp4_subnet_id, dhcp6_subnet_id, hostname) \
   values (0, '3123456', 4, 6, 'neither');"

    run_statement "insert hosts" "$qry"

    # Now we need insert some options to "migrate" for both v4 and v6
    qry=\
"insert into dhcp4_options (code, dhcp4_subnet_id, scope_id) values (1, 4, 0);\
 insert into dhcp4_options (code, dhcp4_subnet_id, scope_id) values (2, 0, 0);\
 insert into dhcp6_options (code, dhcp6_subnet_id, scope_id) values (1, 6, 0);\
 insert into dhcp6_options (code, dhcp6_subnet_id, scope_id) values (2, 0, 0);"

    run_statement "insert options" "$qry"

    # Ok, we have a 6.0 database with hosts and options. Let's upgrade it to 7.0
    # For versions higher than 7.0 some new constraints fail to be added
    # with the not empty tables, for instance the 9.1 -> 9.2 upgrade script
    # can raise a MySQL error 1452 for fk_dhcp4_options_subnet constraint.
    mysql_upgrade_schema_to_version 7.0

    # Version should be new 7.0
    version=$("${kea_admin}" db-version mysql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}")
    assert_str_eq "7.0" "${version}" "Expected kea-admin to return %s, returned value was %s"

    # Two hosts should have null v4 subnet ids
    qry="select count(host_id) from hosts where dhcp4_subnet_id is null;"
    run_statement "#hosts.1" "$qry" 2

    # Two hosts should have v4 subnet ids = 4
    qry="select count(host_id) from hosts where dhcp4_subnet_id = 4;"
    run_statement "#hosts.2" "$qry" 2

    # Two hosts should have null v6 subnet ids
    qry="select count(host_id) from hosts where dhcp6_subnet_id is null;"
    run_statement "#hosts.3" "$qry" 2

    # Two hosts should should have v6 subnet ids = 6
    qry="select count(host_id) from hosts where dhcp6_subnet_id = 6;"
    run_statement "#hosts.4" "$qry" 2

    # One option should have null v4 subnet id
    qry="select count(option_id) from dhcp4_options where dhcp4_subnet_id is null;"
    run_statement "#options.1" "$qry" 1

    # One option should have v4 subnet id = 4
    qry="select count(option_id) from dhcp4_options where dhcp4_subnet_id = 4;"
    run_statement "#options.2" "$qry" 1

    # One option should have null v6 subnet id
    qry="select count(option_id) from dhcp6_options where dhcp6_subnet_id is null;"
    run_statement "#options.3" "$qry" 1

    # One option should have v4 subnet id = 6
    qry="select count(option_id) from dhcp6_options where dhcp6_subnet_id = 6;"
    run_statement "#options.4" "$qry" 1

    # Let's wipe the whole database
    mysql_wipe

    test_finish 0
}

# Verifies that you can upgrade from an earlier version and
# that reservation_mode values in subnet and shared network tables are
# converted to new reservations flags.
mysql_reservation_mode_upgrade_test() {
    test_start "mysql_reservation_mode_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 convert subnet id values
    #
    # Initialize database to scheme 1.0.
    mysql -u"${db_user}" -p"${db_password}" "${db_name}" < "@abs_top_srcdir@/src/bin/admin/tests/dhcpdb_create_1.0.mysql"

    # Now upgrade to schema 9.4, the last version with reservation_mode
    mysql_upgrade_schema_to_version 9.4

    # Now we need insert some subnets and shared networks.
    sql=\
"set @disable_audit = 1; \
 insert into dhcp4_shared_network (name, modification_ts, reservation_mode)\
    values ('test0', current_timestamp, 0);\
 insert into dhcp4_shared_network (name, modification_ts, reservation_mode)\
    values ('test1', current_timestamp, 1);\
 insert into dhcp4_shared_network (name, modification_ts, reservation_mode)\
    values ('test2', current_timestamp, 2);\
 insert into dhcp4_shared_network (name, modification_ts, reservation_mode)\
    values ('test3', current_timestamp, 3);\
 insert into dhcp4_subnet (subnet_id, subnet_prefix, modification_ts, reservation_mode)\
    values (1234, '192.0.0.0/24', current_timestamp, 0);\
 insert into dhcp4_subnet (subnet_id, subnet_prefix, modification_ts, reservation_mode)\
    values (2345, '192.0.1.0/24', current_timestamp, 1);\
 insert into dhcp4_subnet (subnet_id, subnet_prefix, modification_ts, reservation_mode)\
    values (3456, '192.0.2.0/24', current_timestamp, 2);\
 insert into dhcp4_subnet (subnet_id, subnet_prefix, modification_ts, reservation_mode)\
    values (4567, '192.0.3.0/24', current_timestamp, 3);\
 insert into dhcp6_shared_network (name, modification_ts, reservation_mode)\
    values ('test0', current_timestamp, 0);\
 insert into dhcp6_shared_network (name, modification_ts, reservation_mode)\
    values ('test1', current_timestamp, 1);\
 insert into dhcp6_shared_network (name, modification_ts, reservation_mode)\
    values ('test2', current_timestamp, 2);\
 insert into dhcp6_shared_network (name, modification_ts, reservation_mode)\
    values ('test3', current_timestamp, 3);\
 insert into dhcp6_subnet (subnet_id, subnet_prefix, modification_ts, reservation_mode)\
    values (1234, '2001:db8::/64', current_timestamp, 0);\
 insert into dhcp6_subnet (subnet_id, subnet_prefix, modification_ts, reservation_mode)\
    values (2345, '2001:db8:1::/64', current_timestamp, 1);\
 insert into dhcp6_subnet (subnet_id, subnet_prefix, modification_ts, reservation_mode)\
    values (3456, '2001:db8:2::/64', current_timestamp, 2);\
 insert into dhcp6_subnet (subnet_id, subnet_prefix, modification_ts, reservation_mode)\
    values (4567, '2001:db8:3::/64', current_timestamp, 3);\
 set @disable_audit = 0;"

    run_statement "insert reservation_mode" "$sql"

    qry="select count(*) from dhcp4_shared_network;"
    run_statement "#get 4_shared count before update" "$qry" 4

    qry="select count(*) from dhcp4_subnet;"
    run_statement "#get 4_subnet count before update" "$qry" 4

    qry="select count(*) from dhcp6_shared_network;"
    run_statement "#get 6_shared count before update" "$qry" 4

    qry="select count(*) from dhcp6_subnet;"
    run_statement "#get 6_subnet count before update" "$qry" 4

    # Upgrade to schema 9.5.
    mysql_upgrade_schema_to_version 9.5

    # Test DISABLED (0) -> false, false, null
    qry="select count(id) from dhcp4_shared_network where reservations_global = false and reservations_in_subnet = false and reservations_out_of_pool is null and name = 'test0';"
    run_statement "#4_shared_disabled" "$qry" 1

    # Test OUT_OF_POOL (1) -> false, true, true
    qry="select count(id) from dhcp4_shared_network where reservations_global = false and reservations_in_subnet = true and reservations_out_of_pool = true and name = 'test1';"
    run_statement "#4_shared_out_of_pool" "$qry" 1

    # Test GLOBAL (2) -> true, false, null
    qry="select count(id) from dhcp4_shared_network where reservations_global = true and reservations_in_subnet = false and reservations_out_of_pool is null and name = 'test2';"
    run_statement "#4_shared_global" "$qry" 1

    # Test ALL (3) -> false, true, false
    qry="select count(id) from dhcp4_shared_network where reservations_global = false and reservations_in_subnet = true and reservations_out_of_pool = false and name = 'test3';"
    run_statement "#4_shared_all" "$qry" 1

    # Test DISABLED (0) -> false, false, null
    qry="select count(subnet_id) from dhcp4_subnet where reservations_global = false and reservations_in_subnet = false and reservations_out_of_pool is null and subnet_prefix = '192.0.0.0/24'"
    run_statement "#4_subnet_disabled" "$qry" 1

    # Test OUT_OF_POOL (1) -> false, true, true
    qry="select count(subnet_id) from dhcp4_subnet where reservations_global = false and reservations_in_subnet = true and reservations_out_of_pool = true and subnet_prefix = '192.0.1.0/24'"
    run_statement "#4_subnet_out_of_pool" "$qry" 1

    # Test GLOBAL (2) -> true, false, null
    qry="select count(subnet_id) from dhcp4_subnet where reservations_global = true and reservations_in_subnet = false and reservations_out_of_pool is null and subnet_prefix = '192.0.2.0/24'"
    run_statement "#4_subnet_global" "$qry" 1

    # Test ALL (3) -> false, true, false
    qry="select count(subnet_id) from dhcp4_subnet where reservations_global = false and reservations_in_subnet = true and reservations_out_of_pool = false and subnet_prefix = '192.0.3.0/24'"
    run_statement "#4_subnet_all" "$qry" 1

    # Test DISABLED (0) -> false, false, null
    qry="select count(id) from dhcp6_shared_network where reservations_global = false and reservations_in_subnet = false and reservations_out_of_pool is null and name = 'test0';"
    run_statement "#6_shared_disabled" "$qry" 1

    # Test OUT_OF_POOL (1) -> false, true, true
    qry="select count(id) from dhcp6_shared_network where reservations_global = false and reservations_in_subnet = true and reservations_out_of_pool = true and name = 'test1';"
    run_statement "#6_shared_out_of_pool" "$qry" 1

    # Test GLOBAL (2) -> true, false, null
    qry="select count(id) from dhcp6_shared_network where reservations_global = true and reservations_in_subnet = false and reservations_out_of_pool is null and name = 'test2';"
    run_statement "#6_shared_global" "$qry" 1

    # Test ALL (3) -> false, true, false
    qry="select count(id) from dhcp6_shared_network where reservations_global = false and reservations_in_subnet = true and reservations_out_of_pool = false and name = 'test3';"
    run_statement "#6_shared_all" "$qry" 1

    # Test DISABLED (0) -> false, false, null
    qry="select count(subnet_id) from dhcp6_subnet where reservations_global = false and reservations_in_subnet = false and reservations_out_of_pool is null and subnet_prefix = '2001:db8::/64'"
    run_statement "#6_subnet_disabled" "$qry" 1

    # Test OUT_OF_POOL (1) -> false, true, true
    qry="select count(subnet_id) from dhcp6_subnet where reservations_global = false and reservations_in_subnet = true and reservations_out_of_pool = true and subnet_prefix = '2001:db8:1::/64'"
    run_statement "#6_subnet_out_of_pool" "$qry" 1

    # Test GLOBAL (2) -> true, false, null
    qry="select count(subnet_id) from dhcp6_subnet where reservations_global = true and reservations_in_subnet = false and reservations_out_of_pool is null and subnet_prefix = '2001:db8:2::/64'"
    run_statement "#6_subnet_global" "$qry" 1

    # Test ALL (3) -> false, true, false
    qry="select count(subnet_id) from dhcp6_subnet where reservations_global = false and reservations_in_subnet = true and reservations_out_of_pool = false and subnet_prefix = '2001:db8:3::/64'"
    run_statement "#6_subnet_all" "$qry" 1

    qry="select count(*) from dhcp4_shared_network;"
    run_statement "#get 4_shared count before update" "$qry" 4

    qry="select count(*) from dhcp4_subnet;"
    run_statement "#get 4_subnet count before update" "$qry" 4

    qry="select count(*) from dhcp6_shared_network;"
    run_statement "#get 6_shared count before update" "$qry" 4

    qry="select count(*) from dhcp6_subnet;"
    run_statement "#get 6_subnet count before update" "$qry" 4

    # Let's wipe the whole database
    mysql_wipe

    test_finish 0
}


# Run tests.
mysql_db_init_test
mysql_host_reservation_init_test
mysql_db_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
mysql_lease_stat_recount_test
mysql_unused_subnet_id_test
mysql_reservation_mode_upgrade_test