Configuration for testing with minikube:
Clone policy-docker and navigate to policy-db-migrator folder
Download db.yaml to the folder
Create a db-migrator-test.sh file under /git/docker/policy-db-migrator/src/main/docker folder with the script provided at the end of this page
Considering minikube is installed, on a terminal do the following:
Test scenarios:
- Fresh install Honolulu
DB migrator should configure the database on Honolulu scripts.
Schema shouldn't exist
Command: /opt/app/policy/bin/db-migrator -s policyadmin -o upgrade -t 0800
Expected result: schema is created and populated with tables and indexes.
- Fresh install Istanbul
DB migrator should configure the database on Istanbul scripts.
Command: /opt/app/policy/bin/db-migrator -s policyadmin -o upgrade
Expected result: schema is created and populated with tables and indexes.
- Downgrade Istanbul to Honolulu
DB migrator should change tables and indexes based on Istanbul downgrade scripts.
Command: /opt/app/policy/bin/db-migrator -s policyadmin -o downgrade -f 0900 -t 0800
Expected result: tables and indexes altered, but no data loss.
- Upgrade Honolulu to Istanbul
DB migrator should change tables and indexes based on Istanbul upgrade scripts.
Command: /opt/app/policy/bin/db-migrator -s policyadmin -o upgrade -f 0800 -t 0900
Expected result: tables and indexes altered, but no data loss.
- Upgrade Istanbul to Honolulu (fail scenario) (need to confirm if target is by number)
Command: /opt/app/policy/bin/db-migrator -s policyadmin -o upgrade -t 0800 -f 0900
DB migrator should fail when upgrading to a down version.
Expected result: no changes on database, script has error result.
- Downgrade Honolulu to Istanbul (fail scenario)
DB migrator should fail when downgrading to an upper version.
Command: /opt/app/policy/bin/db-migrator -s policyadmin -o downgrade -t 0900 -f 0800
Expected result: no changes on database, script has error result.
- Downgrade Honolulu
DB migrator should change tables and indexes based on Honolulu downgrade scripts.
Command: /opt/app/policy/bin/db-migrator -s policyadmin -o downgrade -t 0800
Expected result: tables and indexes are all dropped.
- Partial upgrade
- Upgrade to Honolulu
- Drop a table that is to be altered in Istanbul
- Upgrade to Istanbul
Command: /opt/app/policy/bin/db-migrator -s policyadmin -o upgrade -t 0800; drop table pdpdstatistics; db-migrator -s policyadmin -o upgrade -f 0800 -t 0900
Expected result: script halts at the given table's upgrade script and updates the metadata table to indicate that it failed
- Downgrade to Honolulu
Command: /opt/app/policy/bin/db-migrator -s policyadmin -o downgrade -f 0900 -t 0800
Expected result: script skips the missing table and completes successfully (though it doesn't re-create the table)
- Upgrade to Istanbul
Command: /opt/app/policy/bin/db-migrator -s policyadmin -o upgrade -f 0800 -t 0900
Expected result: script halts at the given table's upgrade script and updates the metadata table to indicate that it failed
- Manually recreate the table
- Upgrade to Istanbul
Expected result: success
- Partial downgrade
- Upgrade to Istanbul
- Drop/Alter a table that is to be changed in Istanbul
- Downgrade to Honolulu
Expected result: script halts at the given table's downgrade script and updates the metadata table to indicate that it failed
- Upgrade to Istanbul
Expected result: script skips the missing table and completes successfully (though it doesn't re-create the table)
- Downgrade to Honolulu
Expected result: script halts at the given table's upgrade script and updates the metadata table to indicate that it failed
- Manually recreate the table
- Downgrade to Honolulu
Expected result: success
- Metadata database fails (No new functionality being tested)
When DB migrator starts, a metadata for historical changes is created/updated.
If metadata schema verification fails, script should return error.
Expected result: no changes on database, script has error result.
- Database information check fails (No new functionality being tested)
Username and password for accessing/creating schemas are invalid.
Expected result: no changes on database, script has error result.
- Database cannot connect
Server is unavailable.
Expected result: no changes on database, script has error result.
- Current schema already exists
If operation is valid, scripts should run with no issues.
Expected result: schema is populated with tables and indexes or tables and indexes are altered.
#!/bin/sh # ============LICENSE_START==================================================== # Copyright (C) 2021 Nordix Foundation. # ============================================================================= # Licensed under the Apache License, Version 2.0 (the "License"); # you may not use this file except in compliance with the License. # You may obtain a copy of the License at # # http://www.apache.org/licenses/LICENSE-2.0 # # Unless required by applicable law or agreed to in writing, software # distributed under the License is distributed on an "AS IS" BASIS, # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. # See the License for the specific language governing permissions and # limitations under the License. # # SPDX-License-Identifier: Apache-2.0 # ============LICENSE_END====================================================== export POLICY_HOME=/opt/app/policy export SQL_USER=policy_user export SQL_PASSWORD=policy_user export SQL_HOST=mariadb export MYSQL_ROOT_PASSWORD=secret export SCHEMA=policyadmin # Test variables TEST_TS=$(date +%F-%T) TOTAL_COUNT=0 HONOLULU_UPGRADE_COUNT=$(ls /home/policy/sql/0800/upgrade/*.sql | wc -l) HONOLULU_DOWNGRADE_COUNT=$(ls /home/policy/sql/0800/downgrade/*.sql | wc -l) ISTANBUL_UPGRADE_COUNT=$(ls /home/policy/sql/0900/upgrade/*.sql | wc -l) ISTANBUL_DOWNGRADE_COUNT=$(ls /home/policy/sql/0900/downgrade/*.sql | wc -l) NEW_RECORDS=0 START_VERSION="" START_RECORDS=0 END_VERSION="" END_RECORDS=0 END_STATUS=0 TEST_STATUS="FAIL" TEST_MSG="" TESTS=0 PASSED=0 FAILED=0 # SQL statements PDPSTATISTICS="CREATE TABLE IF NOT EXISTS pdpstatistics (" PDPSTATISTICS=${PDPSTATISTICS}"PDPGROUPNAME VARCHAR(120) NULL, " PDPSTATISTICS=${PDPSTATISTICS}"PDPSUBGROUPNAME VARCHAR(120) NULL, " PDPSTATISTICS=${PDPSTATISTICS}"POLICYDEPLOYCOUNT BIGINT DEFAULT NULL NULL, " PDPSTATISTICS=${PDPSTATISTICS}"POLICYDEPLOYFAILCOUNT BIGINT DEFAULT NULL NULL, " PDPSTATISTICS=${PDPSTATISTICS}"POLICYDEPLOYSUCCESSCOUNT BIGINT DEFAULT NULL NULL, " PDPSTATISTICS=${PDPSTATISTICS}"POLICYEXECUTEDCOUNT BIGINT DEFAULT NULL NULL, " PDPSTATISTICS=${PDPSTATISTICS}"POLICYEXECUTEDFAILCOUNT BIGINT DEFAULT NULL NULL, " PDPSTATISTICS=${PDPSTATISTICS}"POLICYEXECUTEDSUCCESSCOUNT BIGINT DEFAULT NULL NULL, " PDPSTATISTICS2=${PDPSTATISTICS}"POLICYUNDEPLOYCOUNT BIGINT DEFAULT NULL NULL, " PDPSTATISTICS2=${PDPSTATISTICS2}"POLICYUNDEPLOYFAILCOUNT BIGINT DEFAULT NULL NULL, " PDPSTATISTICS2=${PDPSTATISTICS2}"POLICYUNDEPLOYSUCCESSCOUNT BIGINT DEFAULT NULL NULL, " PDPSTATISTICS=${PDPSTATISTICS}"timeStamp datetime NOT NULL, " PDPSTATISTICS2=${PDPSTATISTICS2}"timeStamp datetime DEFAULT NULL NULL, " PDPSTATISTICS2=${PDPSTATISTICS2}"ID BIGINT NOT NULL, " PDPSTATISTICS=${PDPSTATISTICS}"name VARCHAR(120) NOT NULL, " PDPSTATISTICS2=${PDPSTATISTICS2}"name VARCHAR(120) NOT NULL, " PDPSTATISTICS=${PDPSTATISTICS}"version VARCHAR(20) NOT NULL," PDPSTATISTICS2=${PDPSTATISTICS2}"version VARCHAR(20) NOT NULL," PDPSTATISTICS=${PDPSTATISTICS}"CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (timeStamp, name, version));" PDPSTATISTICS2=${PDPSTATISTICS2}"CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (ID, name, version));" IDX_TSIDX1="CREATE INDEX IDX_TSIDX1 ON pdpstatistics(timeStamp, name, version);" INSERT="INSERT INTO pdpstatistics(PDPGROUPNAME,PDPSUBGROUPNAME,POLICYDEPLOYCOUNT,POLICYDEPLOYFAILCOUNT,POLICYDEPLOYSUCCESSCOUNT," INSERT1="${INSERT}""POLICYEXECUTEDCOUNT,POLICYEXECUTEDFAILCOUNT,POLICYEXECUTEDSUCCESSCOUNT,timeStamp,name,version)" INSERT2="${INSERT}""POLICYEXECUTEDCOUNT,POLICYEXECUTEDFAILCOUNT,POLICYEXECUTEDSUCCESSCOUNT,POLICYUNDEPLOYCOUNT," INSERT2="${INSERT2}""POLICYUNDEPLOYFAILCOUNT,POLICYUNDEPLOYSUCCESSCOUNT,timeStamp,ID,name,version)" SQL1="${INSERT1}"" values('groupname', 'subgroup',1,1,1,1,1,1,now(),'test1', '1.0')" SQL2="${INSERT1}"" values('groupname', 'subgroup',1,1,1,1,1,1,now(),'test1', '1.0')" SQL3="${INSERT2}"" values('groupname', 'subgroup',1,1,1,1,1,1,1,1,1,now(),3,'test1', '1.0')" SQL4="${INSERT2}"" values('groupname', 'subgroup',1,1,1,1,1,1,1,1,1,now(),4,'test1', '1.0')" SQL5="${INSERT1}"" values('groupname', 'subgroup',1,1,1,1,1,1,now(),'test2', '1.0')" SQL6="${INSERT1}"" values('groupname', 'subgroup',1,1,1,1,1,1,now(),'test2', '1.0')" function run_sql { local user="${1}" password="${2}" schema="${3}" sql="${4}" MYSQL="mysql -u${user} -p${password} -h ${SQL_HOST} ${schema}"; ${MYSQL} --execute "${sql}" return $? } function start_test { let TESTS=$TESTS+1 reportStatus=$(/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o report | tail -2) START_VERSION=$(echo $reportStatus | cut -f13 -d' ') START_RECORDS=$(echo $reportStatus | cut -f1 -d' ') if [ "${START_VERSION}" == "" ]; then START_VERSION="0" START_RECORDS=0 fi } function end_test { reportStatus=$(/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o report | tail -2) END_VERSION=$(echo $reportStatus | cut -f13 -d' ') END_RECORDS=$(echo $reportStatus | cut -f1 -d' ') END_STATUS=$(echo $reportStatus | cut -f7 -d' ') } function check_results { local status="${1}" operation="${2}" startVer="${3}" endVer="${4}" newRecords="${5}" filesRan="${6}" echo "" echo "check_results: status:$status, operation:$operation, from:$startVer, to:$endVer, new records:$newRecords, sqlFiles:$filesRan" # Convert to number startVer=$(echo $startVer | awk '{$0=int($0)}1') endVer=$(echo $endVer | awk '{$0=int($0)}1') if [ ${startVer} -eq ${endVer} ] && [ $newRecords -eq $filesRan ] && [ $newRecords -eq 0 ]; then TEST_MSG="No ${operation} required" TEST_STATUS="PASS" let PASSED=$PASSED+1 elif [ $status -eq 1 ] && [ "${operation}" == "upgrade" ] && [ ${startVer} -le ${endVer} ] && [ $newRecords -eq $filesRan ]; then TEST_MSG="Successful ${operation}" TEST_STATUS="PASS" let PASSED=$PASSED+1 elif [ $status -eq 1 ] && [ "${operation}" == "downgrade" ] && [ ${startVer} -ge ${endVer} ] && [ $newRecords -eq $filesRan ]; then TEST_MSG="Successful ${operation}" TEST_STATUS="PASS" let PASSED=$PASSED+1 else TEST_MSG="Errors occurred during ${operation}" TEST_STATUS="FAIL" let FAILED=$FAILED+1 fi echo "*** Test $TESTS: $TEST_STATUS , $TEST_MSG, current version: $END_VERSION ***" } # Test 1 - Full upgrade start_test /opt/app/policy/bin/prepare_upgrade.sh ${SQL_DB} /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o upgrade end_test let TOTAL_COUNT=$HONOLULU_UPGRADE_COUNT+$ISTANBUL_UPGRADE_COUNT check_results $END_STATUS 'upgrade' "${START_VERSION}" "${END_VERSION}" "${END_RECORDS}" $TOTAL_COUNT sleep 5 # Test 2 - downgrade to 0800 start_test /opt/app/policy/bin/prepare_downgrade.sh ${SQL_DB} /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o downgrade -f 0900 -t 0800 end_test let NEW_RECORDS=$END_RECORDS-$START_RECORDS check_results $END_STATUS 'downgrade' "${START_VERSION}" "${END_VERSION}" "${NEW_RECORDS}" $ISTANBUL_DOWNGRADE_COUNT sleep 5 # Test 3 - upgrade to 0900 start_test /opt/app/policy/bin/prepare_upgrade.sh ${SQL_DB} /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o upgrade -f 0800 -t 0900 end_test let NEW_RECORDS=$END_RECORDS-$START_RECORDS check_results $END_STATUS 'upgrade' "${START_VERSION}" "${END_VERSION}" "${NEW_RECORDS}" $ISTANBUL_UPGRADE_COUNT sleep 5 # Test4 - run upgrade on db where tables already exist and migration schema is empty start_test /opt/app/policy/bin/prepare_downgrade.sh ${SQL_DB} /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o downgrade -f 0900 -t 0800 run_sql "root" "${MYSQL_ROOT_PASSWORD}" "${SCHEMA}" "DROP DATABASE IF EXISTS migration;" /opt/app/policy/bin/prepare_upgrade.sh ${SQL_DB} /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o upgrade end_test let NEW_RECORDS=$END_RECORDS check_results $END_STATUS 'upgrade' "0800" "${END_VERSION}" "${NEW_RECORDS}" $ISTANBUL_UPGRADE_COUNT sleep 5 # Test5 - upgrade after failed downgrade start_test /opt/app/policy/bin/prepare_downgrade.sh ${SQL_DB} run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "DROP table pdpstatistics;" /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o downgrade -f 0900 -t 0800 run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${PDPSTATISTICS2}" run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${IDX_TSIDX1}" /opt/app/policy/bin/prepare_upgrade.sh ${SQL_DB} /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o upgrade -f 0900 -t 0900 end_test let NEW_RECORDS=$END_RECORDS-$START_RECORDS # (files run before error * 2) + 1 to run the file again let TOTAL_COUNT=9 check_results $END_STATUS 'upgrade' "${START_VERSION}" "${END_VERSION}" "${NEW_RECORDS}" $TOTAL_COUNT sleep 5 # Test6 - Downgrade after failed downgrade start_test /opt/app/policy/bin/prepare_downgrade.sh ${SQL_DB} run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "DROP table pdpstatistics;" /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o downgrade -f 0900 -t 0800 run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${PDPSTATISTICS2}" run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${IDX_TSIDX1}" /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o downgrade -f 0900 -t 0800 end_test let NEW_RECORDS=$END_RECORDS-$START_RECORDS # +1 to run the file again let TOTAL_COUNT=$ISTANBUL_DOWNGRADE_COUNT+1 check_results $END_STATUS 'downgrade' "${START_VERSION}" "${END_VERSION}" "${NEW_RECORDS}" $TOTAL_COUNT sleep 5 # Test7 - downgrade after failed upgrade start_test /opt/app/policy/bin/prepare_upgrade.sh ${SQL_DB} run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "DROP table pdpstatistics;" /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o upgrade -f 0800 -t 0900 run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${PDPSTATISTICS}" /opt/app/policy/bin/prepare_downgrade.sh ${SQL_DB} /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o downgrade -f 0900 -t 0800 end_test let NEW_RECORDS=$END_RECORDS-$START_RECORDS # (files run before error * 2) + 1 to run the file again let TOTAL_COUNT=7 check_results $END_STATUS 'downgrade' "${START_VERSION}" "${END_VERSION}" "${NEW_RECORDS}" $TOTAL_COUNT sleep 5 # Test 8 - Upgrade after failed upgrade start_test /opt/app/policy/bin/prepare_upgrade.sh ${SQL_DB} run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "DROP table pdpstatistics;" /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o upgrade -f 0800 -t 0900 run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${PDPSTATISTICS}" /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o upgrade -f 0900 -t 0900 end_test let NEW_RECORDS=$END_RECORDS-$START_RECORDS # +1 to run the file again let TOTAL_COUNT=$ISTANBUL_UPGRADE_COUNT+1 check_results $END_STATUS 'upgrade' "${START_VERSION}" "${END_VERSION}" "${NEW_RECORDS}" $TOTAL_COUNT sleep 5 # Test 9 - Upgrade when pdpstatistics contains data /opt/app/policy/bin/prepare_downgrade.sh ${SQL_DB} /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o downgrade -f 0900 -t 0800 start_test run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${SQL1}" sleep 1 run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${SQL2}" sleep 1 /opt/app/policy/bin/prepare_upgrade.sh ${SQL_DB} /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o upgrade -f 0800 -t 0900 run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${SQL3}" sleep 1 run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${SQL4}" end_test let NEW_RECORDS=$END_RECORDS-$START_RECORDS # +1 to run the file again let TOTAL_COUNT=$ISTANBUL_UPGRADE_COUNT check_results $END_STATUS 'upgrade' "${START_VERSION}" "${END_VERSION}" "${NEW_RECORDS}" $TOTAL_COUNT sleep 5 # Test 10 - downgrade to 0800 with records in pdpstatistics start_test /opt/app/policy/bin/prepare_downgrade.sh ${SQL_DB} /opt/app/policy/bin/db-migrator -s ${SQL_DB} -o downgrade -f 0900 -t 0800 run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${SQL5}" sleep 1 run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${SQL6}" end_test let NEW_RECORDS=$END_RECORDS-$START_RECORDS check_results $END_STATUS 'downgrade' "${START_VERSION}" "${END_VERSION}" "${NEW_RECORDS}" $ISTANBUL_DOWNGRADE_COUNT echo echo "-----------------------------------------------------------------------" echo "Number of Tests: $TESTS, Tests Passed: $PASSED, Tests Failed: $FAILED" echo "Date: $TEST_TS" echo "-----------------------------------------------------------------------" nc -lk -p 6824 exit 0
Tests results: tests_results.txt
Testing jpapdpstatistics_enginestats
There is a dependency between pdpstatistics and jpapdpstatistics_enginestats
Here is some sample table data extracted during the apex-pdp test:
MariaDB [policyadmin]> select * from pdpstatistics;
+--------------+-----------------+-------------------+-----------------------+--------------------------+---------------------+-------------------------+----------------------------+---------------------+---------------------+-------------------------------------------+---------+-------------------------+----------------------------+-----+
| PDPGROUPNAME | PDPSUBGROUPNAME | POLICYDEPLOYCOUNT | POLICYDEPLOYFAILCOUNT | POLICYDEPLOYSUCCESSCOUNT | POLICYEXECUTEDCOUNT | POLICYEXECUTEDFAILCOUNT | POLICYEXECUTEDSUCCESSCOUNT | POLICYUNDEPLOYCOUNT | timeStamp | name | version | POLICYUNDEPLOYFAILCOUNT | POLICYUNDEPLOYSUCCESSCOUNT | ID |
+--------------+-----------------+-------------------+-----------------------+--------------------------+---------------------+-------------------------+----------------------------+---------------------+---------------------+-------------------------------------------+---------+-------------------------+----------------------------+-----+
| defaultGroup | apex | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2021-08-25 09:38:15 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 | 0 | 0 | 1 |
| defaultGroup | apex | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 2021-08-25 09:38:31 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 | 0 | 0 | 101 |
| defaultGroup | apex | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 2021-08-25 09:38:33 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 | 0 | 0 | 151 |
| defaultGroup | apex | 1 | 0 | 1 | 1 | 0 | 1 | 0 | 2021-08-25 09:38:53 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 | 0 | 0 | 201 |
| defaultGroup | apex | 2 | 0 | 2 | 1 | 0 | 1 | 0 | 2021-08-25 09:39:00 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 | 0 | 0 | 301 |
+--------------+-----------------+-------------------+-----------------------+--------------------------+---------------------+-------------------------+----------------------------+---------------------+---------------------+-------------------------------------------+---------+-------------------------+----------------------------+-----+
5 rows in set (0.000 sec)
MariaDB [policyadmin]> select * from jpapdpstatistics_enginestats;
+----------------------+-----------------------+-----------------+-------------------+------------+---------------+-------------------+---------------+--------+------+-------------------------------------------+---------+
| AVERAGEEXECUTIONTIME | ENGINEID | ENGINETIMESTAMP | ENGINEWORKERSTATE | EVENTCOUNT | LASTENTERTIME | LASTEXECUTIONTIME | LASTSTART | UPTIME | ID | name | version |
+----------------------+-----------------------+-----------------+-------------------+------------+---------------+-------------------+---------------+--------+------+-------------------------------------------+---------+
| 0 | MyApexEngine-0:0.0.1 | 1629884311356 | 2 | 0 | 0 | 0 | 1629884311356 | 0 | 101 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
| 0 | MyApexEngine-1:0.0.1 | 1629884311585 | 2 | 0 | 0 | 0 | 1629884311585 | 0 | 101 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
| 0 | MyApexEngine-2:0.0.1 | 1629884311734 | 2 | 0 | 0 | 0 | 1629884311734 | 0 | 101 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
| 0 | MyApexEngine-3:0.0.1 | 1629884311849 | 2 | 0 | 0 | 0 | 1629884311849 | 0 | 101 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
| 0 | MyApexEngine-0:0.0.1 | 1629884311356 | 2 | 0 | 0 | 0 | 1629884311356 | 0 | 151 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
| 0 | MyApexEngine-1:0.0.1 | 1629884311585 | 2 | 0 | 0 | 0 | 1629884311585 | 0 | 151 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
| 0 | MyApexEngine-2:0.0.1 | 1629884311734 | 2 | 0 | 0 | 0 | 1629884311734 | 0 | 151 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
| 0 | MyApexEngine-3:0.0.1 | 1629884311849 | 2 | 0 | 0 | 0 | 1629884311849 | 0 | 151 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
| 313 | MyApexEngine-0:0.0.1 | 1629884319361 | 2 | 1 | 0 | 313 | 1629884311356 | 8005 | 201 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
| 0 | MyApexEngine-1:0.0.1 | 1629884311585 | 2 | 0 | 0 | 0 | 1629884311585 | 0 | 201 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
| 0 | MyApexEngine-2:0.0.1 | 1629884311734 | 2 | 0 | 0 | 0 | 1629884311734 | 0 | 201 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
| 0 | MyApexEngine-3:0.0.1 | 1629884311849 | 2 | 0 | 0 | 0 | 1629884311849 | 0 | 201 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
| 313 | MyApexEngine-0:0.0.1 | 1629884319361 | 2 | 1 | 0 | 313 | 1629884311356 | 8005 | 301 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
| 0 | MyApexEngine-1:0.0.1 | 1629884311585 | 2 | 0 | 0 | 0 | 1629884311585 | 0 | 301 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
| 0 | MyApexEngine-2:0.0.1 | 1629884311734 | 2 | 0 | 0 | 0 | 1629884311734 | 0 | 301 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
| 0 | MyApexEngine-3:0.0.1 | 1629884311849 | 2 | 0 | 0 | 0 | 1629884311849 | 0 | 301 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
| 0 | NSOApexEngine-0:0.0.1 | 1629884339193 | 2 | 0 | 0 | 0 | 1629884339193 | 0 | 301 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
| 0 | NSOApexEngine-1:0.0.1 | 1629884339229 | 2 | 0 | 0 | 0 | 1629884339229 | 0 | 301 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
| 0 | NSOApexEngine-2:0.0.1 | 1629884339263 | 2 | 0 | 0 | 0 | 1629884339263 | 0 | 301 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
| 0 | NSOApexEngine-3:0.0.1 | 1629884339304 | 2 | 0 | 0 | 0 | 1629884339304 | 0 | 301 | apex-86512ba9-afc9-4a0f-bb45-0aba0d330ffb | 0.0.0 |
+----------------------+-----------------------+-----------------+-------------------+------------+---------------+-------------------+---------------+--------+------+-------------------------------------------+---------+
20 rows in set (0.000 sec)
jpapdpstatistics_enginestats records need to be updated whenever there is a change to the pdpstatistics PK.
Here is the data extracted during our tests (9 +10 above):
MariaDB [policyadmin]> select * from jpapdpstatistics_enginestats;
+----------------------+-------------+-----------------+-------------------+------------+---------------+-------------------+-----------+--------+---------------------+-------+---------+
| AVERAGEEXECUTIONTIME | ENGINEID | ENGINETIMESTAMP | ENGINEWORKERSTATE | EVENTCOUNT | LASTENTERTIME | LASTEXECUTIONTIME | LASTSTART | UPTIME | timeStamp | name | version |
+----------------------+-------------+-----------------+-------------------+------------+---------------+-------------------+-----------+--------+---------------------+-------+---------+
| 100 | Test-Engine | 20210825120846 | 2 | 5 | 0 | 0 | 9999 | 8888 | 2021-08-25 12:08:46 | test1 | 1.0 |
| 100 | Test-Engine | 20210825120847 | 2 | 5 | 0 | 0 | 9999 | 8888 | 2021-08-25 12:08:47 | test1 | 1.0 |
+----------------------+-------------+-----------------+-------------------+------------+---------------+-------------------+-----------+--------+---------------------+-------+---------+
2 rows in set (0.000 sec)
MariaDB [policyadmin]> select * from jpapdpstatistics_enginestats;
+----------------------+-------------+-----------------+-------------------+------------+---------------+-------------------+-----------+--------+------+-------+---------+
| AVERAGEEXECUTIONTIME | ENGINEID | ENGINETIMESTAMP | ENGINEWORKERSTATE | EVENTCOUNT | LASTENTERTIME | LASTEXECUTIONTIME | LASTSTART | UPTIME | ID | name | version |
+----------------------+-------------+-----------------+-------------------+------------+---------------+-------------------+-----------+--------+------+-------+---------+
| 100 | Test-Engine | 20210825120846 | 2 | 5 | 0 | 0 | 9999 | 8888 | 1 | test1 | 1.0 |
| 100 | Test-Engine | 20210825120847 | 2 | 5 | 0 | 0 | 9999 | 8888 | 2 | test1 | 1.0 |
+----------------------+-------------+-----------------+-------------------+------------+---------------+-------------------+-----------+--------+------+-------+---------+
2 rows in set (0.001 sec)
MariaDB [policyadmin]> select * from jpapdpstatistics_enginestats;
+----------------------+-------------+-----------------+-------------------+------------+---------------+-------------------+-----------+--------+---------------------+-------+---------+
| AVERAGEEXECUTIONTIME | ENGINEID | ENGINETIMESTAMP | ENGINEWORKERSTATE | EVENTCOUNT | LASTENTERTIME | LASTEXECUTIONTIME | LASTSTART | UPTIME | timeStamp | name | version |
+----------------------+-------------+-----------------+-------------------+------------+---------------+-------------------+-----------+--------+---------------------+-------+---------+
| 100 | Test-Engine | 20210825120846 | 2 | 5 | 0 | 0 | 9999 | 8888 | 2021-08-25 12:08:46 | test1 | 1.0 |
| 100 | Test-Engine | 20210825120847 | 2 | 5 | 0 | 0 | 9999 | 8888 | 2021-08-25 12:08:47 | test1 | 1.0 |
+----------------------+-------------+-----------------+-------------------+------------+---------------+-------------------+-----------+--------+---------------------+-------+---------+
2 rows in set (0.001 sec)
PK is converted from name,version,timeStamp to name,version,id and back again.
3 Comments
Jim Hahn
For the partial upgrade case, could do this (partial downgrade would be similar):
Upgrade to Honolulu
Drop a table that is to be altered in Istanbul
Upgrade to Istanbul
Expected result: script halts at the given table's upgrade script and updates the metadata table to indicate that it failed
Downgrade to Honolulu
Expected result: script skips the missing table and completes successfully (though it doesn't re-create the table)
Upgrade to Istanbul
Expected result: same error as before
Manually recreate the table
Upgrade to Istanbul
Expected result: success
Adheli Tavares
For PdpStatistics upgrade the issue is when changing PK to ID, name, version.
All IDs get set to 0, so if nameX, version1 happens more than once, statment for altering PK (ALTER TABLE PdpStatistics ADD CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (ID, name, version); ) fails with duplication error.
Suggestion 1: have an sql inserting sequential IDs. - solution integrated by POLICY-3546 - Getting issue details... STATUS
Suggestiong 2: move all the data to an archive table.
Adheli Tavares
upgrade istanbul
pdpstatistics have new data using id/name/version as pk (not timestamp)
downgrade to honolulu
what's supposed to happen here: should we populate the timestamp field? should java code cover this part and always set value for timestamp?
with null timestamp or maybe even current_timestamp() we could have the duplicate error when going back to pk with timestamp
models/models-pdp/src/main/java/org/onap/policy/models/pdp/persistence/concepts/JpaPdpStatistics.java
@ElementCollection
private List<PdpEngineWorkerStatistics> engineStats;
https://wiki.eclipse.org/EclipseLink/Examples/JPA/2.0/ElementCollections
jpapdpsta..enginestats is dependent on pdpstatistics -- dropping timestamp instead of just pushing aside.
should we not delete it?