The design mentioned in comment 3 features a solid proposal for the MySQL database schema. MySQL schema is updated using kea-admin. See src/bin/admin and src/bin/admin/scripts/mysql for previous updates. We do have upgrade 1.0 to 2.0 that did insert extra columns in lease6 columns.

You may also want to read Section 5 of Kea User's guide that explains how to use kea-admin (it's currently part of #3644 ticket, but it should be merged in the next couple days).

I received a patch over mail from Adam and committed it on a branch trac3567 (commit e9fc3ede80d833c0b186c1ec4cb4a32a8ebb46ed).

The changes look mostly good, but there are a couple of things that has to be changed.

The tables are created in a separate database called kea_host_reservation. They should
be created in the usual database, not in a separate one. Please fix this in the database
initialization script, but also in the HostReservationDesign page. A bit of explanation:
during the design phase, ISC engineers had a discussion and decided that it should be
possible to keep host reservation in a separate database. The design as of today
not only makes it possible, but actually enforces it. That's too much. While we should
keep in mind that some people may want to keep reservation details separated, most
users will simply keep everything DHCP-related in a single database.

There are a couple of directives:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

I'm not MySQL expect, so I don't understand them. In particular, allow_invalid_dates look suspicious.
Unless there's a good reason for keeping them, please remove them from both the script and the design.

The initialization script is used in new installations. We also need an
upgrade script for people who run old Kea installation and are migrating
to the new one. Note that kea-admin loads scripts from src/bin/admin/scripts/mysql/upgrade-*.sh
in alphabetic order. Please name your script appropriately (upgrade_2.0_to_3.0.sh).

Feel free to take a look at upgrade_1.0_to_2.0.sh as an example.

Once those scripts are in place, make sure you also update test tests.
They are in mysql_tests.sh.in. To run them:

./configure --with-dhcp-mysql
make
make check (or cd src/bin/admin; make check)

Please propose ChangeLog entry. (See ChangeLog? file) Make sure the entry is
clear that this ticket updates the schema only and the tables are not used yet.
Otherwise users will complain that they put some data there and nothing
happened.

Finally, as this change introduces new files, make sure that it will not
break down the release. In particular, the release involves (among many
other steps) making tar.gz file. This is done with:

make dist

To check whether this tar.gz contains all necessary files (including those
you just added), you can run:

make distcheck

It may require updating Makefile.am in the appropriate directory.

Reassigning this ticket back to Adam. Once you deal with those comments,
please reassign it back to me.

I'm returning ticket after sending new patch which includes changes as follows:

-New tables are now created in the same database as those associated with leases.

-Removed directives mentioned earlier, for what I understand they could speed up the mysql server import process, but at the cost of skipping some validations. Yet, I'm not a MySQL expert either...

-Added upgrade_2.0_to_3.0.sh script

-Added some tests for new host reservation tables and upgrade script.
Test errors quoted earlier are most likely results of missing keatest user, keatest database and/or necessary privileges, which, according to User's Manual chapter "4.3.2.1. First Time Creation of Kea Database", should be created manually before executing tests.

-Finally, Makefile.am and configure.ac has been updated to include new upgrade file.

Thanks for this guidelines and hope this time everything will work fine.