[#2909] Addressed additional review comments

3 files changed, 33 insertions, 18 deletions

diff --git a/src/bin/admin/tests/mysql_tests.sh.in b/src/bin/admin/tests/mysql_tests.sh.in
index 56be81c778..e83075e458 100644
--- a/src/bin/admin/tests/mysql_tests.sh.in
+++ b/src/bin/admin/tests/mysql_tests.sh.in
@@ -2789,8 +2789,8 @@ mysql_update_empty_duid_test() {
     mysql_upgrade_schema_to_version 16.0
 
     sql=\
-"insert into lease6 values(10,203,30,(SELECT FROM_UNIXTIME(1642000000)),40,50,1,60,70,1,1,'one.example.com',80,90,16,0,NULL);\
- insert into lease6 values(11,UNHEX('00'),30,(SELECT FROM_UNIXTIME(1643210000)),40,50,1,60,70,1,1,'',80,90,1,1,'{  }')"
+"insert into lease6 values('::10',203,30,(SELECT FROM_UNIXTIME(1642000000)),40,50,1,60,70,1,1,'one.example.com',80,90,16,0,NULL);\
+ insert into lease6 values('::11',UNHEX('00'),30,(SELECT FROM_UNIXTIME(1643210000)),40,50,1,60,70,1,1,'',80,90,1,1,'{  }')"
 
     run_statement "insert v6 leases" "$sql"
 
@@ -2799,11 +2799,11 @@ mysql_update_empty_duid_test() {
         "${kea_admin}" db-upgrade mysql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
 
     # leases count for declined state should be 1 with DUID updated (0x000000)
-    qry="select count(*) from lease6 where address = 11 and duid = 0x000000 and state = 1"
+    qry="select count(*) from lease6 where address = inet6_aton('::11') and duid = 0x000000 and state = 1"
     run_statement "#2" "$qry" 1
 
     # leases count for non declined state should be 1 with DUID unchanged (0x323033)
-    qry="select count(*) from lease6 where address = 10 and duid = 0x323033 and state = 0"
+    qry="select count(*) from lease6 where address = inet6_aton('::10') and duid = 0x323033 and state = 0"
     run_statement "#3" "$qry" 1
 
     # Let's wipe the whole database
diff --git a/src/share/database/scripts/mysql/dhcpdb_create.mysql b/src/share/database/scripts/mysql/dhcpdb_create.mysql
index a606011f05..67640acad3 100644
--- a/src/share/database/scripts/mysql/dhcpdb_create.mysql
+++ b/src/share/database/scripts/mysql/dhcpdb_create.mysql
@@ -13,8 +13,7 @@
 # To create the schema, either type the command:
 #
 # mysql -u <user> -p <password> <database> < dhcpdb_create.mysql
-#
-# ... at the command prompt, or log in to the MySQL database and at the 'mysql>'
+# # ... at the command prompt, or log in to the MySQL database and at the 'mysql>'
 # prompt, issue the command:
 #
 # source dhcpdb_create.mysql
@@ -5681,22 +5680,30 @@ UPDATE schema_version
 -- We have to play some games to make lease address
 -- binary, primary key and retain its place as first
 -- column.
--- Move data and primary key to binaddr
+-- Store binary values for address in binaddr column
 DROP INDEX lease6_by_binaddr ON lease6;
 UPDATE lease6 set binaddr = inet6_aton(address);
 ALTER TABLE lease6 DROP PRIMARY KEY, ADD PRIMARY KEY (binaddr);
--- Copy binary data back to address column
-UPDATE lease6 set address = binaddr;
--- Make address column binary and primary key again
+-- Wipe existing address column contents so we can change data type
+UPDATE lease6 set address = '::';
+-- Change address data type
 ALTER TABLE lease6 MODIFY COLUMN address BINARY(16);
+-- Copy the binary values back to address
+UPDATE lease6 set address = binaddr;
+-- Restore address as primary key
 ALTER TABLE lease6 DROP PRIMARY KEY, ADD PRIMARY KEY (address);
 -- Drop binaddr column
 ALTER TABLE lease6 DROP COLUMN binaddr;
 
 -- Change data type of ipv6_reservations.address column.
--- First we replace it's contents with network bytes value.
-UPDATE ipv6_reservations set address = inet6_aton(address);
+-- Convert existing data via a temporary binary address column.
+ALTER TABLE ipv6_reservations ADD COLUMN binaddr  BINARY(16);
+UPDATE ipv6_reservations set binaddr = inet6_aton(address);
+-- Wipe existing address column contents so we can change data type
+UPDATE ipv6_reservations set address = '::';
 ALTER TABLE ipv6_reservations MODIFY COLUMN address BINARY(16);
+UPDATE ipv6_reservations set address = binaddr;
+ALTER TABLE ipv6_reservations DROP COLUMN binaddr;
 
 -- Convert binary lease6 address to text
 DROP PROCEDURE IF EXISTS lease6DumpData;
diff --git a/src/share/database/scripts/mysql/upgrade_018_to_019.sh.in b/src/share/database/scripts/mysql/upgrade_018_to_019.sh.in
index 0c65a72050..831bcbfd61 100644
--- a/src/share/database/scripts/mysql/upgrade_018_to_019.sh.in
+++ b/src/share/database/scripts/mysql/upgrade_018_to_019.sh.in
@@ -56,22 +56,30 @@ mysql "$@" <<EOF
 -- We have to play some games to make lease address
 -- binary, primary key and retain its place as first
 -- column.
--- Move data and primary key to binaddr
+-- Store binary values for address in binaddr column
 DROP INDEX lease6_by_binaddr ON lease6;
 UPDATE lease6 set binaddr = inet6_aton(address);
 ALTER TABLE lease6 DROP PRIMARY KEY, ADD PRIMARY KEY (binaddr);
--- Copy binary data back to address column
-UPDATE lease6 set address = binaddr;
--- Make address column binary and primary key again
+-- Wipe existing address column contents so we can change data type
+UPDATE lease6 set address = '::';
+-- Change address data type
 ALTER TABLE lease6 MODIFY COLUMN address BINARY(16);
+-- Copy the binary values back to address
+UPDATE lease6 set address = binaddr;
+-- Restore address as primary key
 ALTER TABLE lease6 DROP PRIMARY KEY, ADD PRIMARY KEY (address);
 -- Drop binaddr column
 ALTER TABLE lease6 DROP COLUMN binaddr;
 
 -- Change data type of ipv6_reservations.address column.
--- First we replace it's contents with network bytes value.
-UPDATE ipv6_reservations set address = inet6_aton(address);
+-- Convert existing data via a temporary binary address column.
+ALTER TABLE ipv6_reservations ADD COLUMN binaddr  BINARY(16);
+UPDATE ipv6_reservations set binaddr = inet6_aton(address);
+-- Wipe existing address column contents so we can change data type
+UPDATE ipv6_reservations set address = '::';
 ALTER TABLE ipv6_reservations MODIFY COLUMN address BINARY(16);
+UPDATE ipv6_reservations set address = binaddr;
+ALTER TABLE ipv6_reservations DROP COLUMN binaddr;
 
 -- Convert binary lease6 address to text
 DROP PROCEDURE IF EXISTS lease6DumpData;