Troubleshooting: Remediation Process - Issues with Forecast or Deploy

Most forecast or deploy issues are related to issues with SQL scripts which contain typos or reference non-existent objects. Other issues could be related to Datical DB rules which enforce certain behavior and breaching those behaviors will trigger failures during forecast or deploy operations.

Issues with Deploying SQL Scripts – "Errors Indicated in Deploy Report (HTML)"

When forecasting or deploying changesets to a database, you could run into any number of issues. It is important to perform Forecast operation prior to Deploy in order to let Datical DB simulate a deploy and catch any errors which escape human review process.

Forecast and Deploy reports are available in Jenkins node or Octopus Deploy tentacles.

Forecast report will manifest errors and point to the source of those errors:

 
In this example, the changeset identified by "U002" ran into issues with SQL statement "Drop Table USERSLIST" and the forecast report indicates that there is no such table called "USERSLIST". This is an example where there could be a typo in the name of the table.

Issues with Deploying SQL Scripts – "Liquibase Exceptions" or "SQLPlus Errors"

A common use case is that SQL scripts are passed down from Application Developers. Those SQL scripts first need to run through Deployment Packager in order to package those scripts into one or more Datical DB changesets. Let's say the Deployment Packager successfully packages those changes into changesets. Then during the Deploy process to a higher environment an error could occur.
Examine the Deploy report

1. Report is available in Datical DB project at: Reports\2016\<month>\<deploy_step>\<timestamped_folder>\deployReport.html
2.  
3. If errors point to "liquibase exceptions" then there would be a DaticalLiquibase.log file created as an artifact

In this case, since the error points to "liquibase exceptions" then examine the DaticalLiquibase.log file:
 

The log shows SQL session where each line of the SQL script was executed. As you can see, the error occurred at line 1, with the message "table or view does not exist". Also note that this occurred while executing "update tables.sql" script.

The example illustrated above is when automatic rollback is not available by Datical DB because the SQL script is executed by SQLPlus tool. This is also the case when Deployment Packager is unable to compare before and after snapshots of the database. Additionally, forecast process will be unable to simulate database changes when passing SQL scripts via SQLPlus.

Note: In certain cases, when dealing with extremely large databases, Deployment Packager may take multiple hours to package changesets. In these cases, it is quicker to package SQL scripts into SQLPlus and deploy without forecasting. The remediation path is more complicated in this scenario because part of your scripts got deployed successfully but others did not, so your database may end up in a partially deployed state.

The screenshot above shows an error that occurred on line 1 using SQLPlus. What if this error occurred somewhere in the middle of the SQL script? If, for example, error occurred on line 25 of the SQL script, this means that lines 1-24 got executed and deployed successfully, however, nothing got deployed from line 25 onwards. The DaticalLiquibase.log file will indicate which line in the SQL script caused issues.

YOU NEED TO PROCEED WITH CAUTION HERE. Because simply fixing the script and re-deploying will cause further complications – remember, lines 1-24 already got deployed so your database is in a state of flux with partial deployment.

At this point, follow this workflow:

• Review Deploy Report
  • Reports\2016\<month>\<deploy_step>\<timestamped_folder>\deployReport.html
• Review sql log output or DaticalLiquibase.log
  • Reports\2016\<month>\<deploy_step>\<timestamped_folder>\DaticalLiquibase.log
  • Reports\2016\<month>\<deploy_step>\<timestamped_folder>*.sql.log
• Fix the SQL script:
  • Located in Resource\sql\folder\<SQL_script.sql>

  • Comment out section of the SQL script which got deployed successfully such that this section of the SQL script does not deploy again after the fix is implemented:

    Usage	Sample
    /* COMMENTED OUT LINE COMMENTED OUT LINE */	/* DROP TABLE USERSLIST; */

  • Fix the problems with the script and save
  • Re deploy changeset
  • Verify that changes are successfully deployed using the database client tool (e.g., SQL Developer for Oracle)
  • It is important to note that re-deploying the changeset only deployed the portion of the SQL script which previously errored out (e.g., lines 25 onwards).
• Deploy to higher environment:
  • Remove comments from the SQL script
    • Resource\sql\folder\<SQL_script.sql>
    • Save the SQL script
  • You need to clear changelog checksums and re-sync changelog such that all changesets are marked as "EXECUTED". This is a necessary step because you performed multiple deploy operations to fix SQL script errors. You need to indicate that the entire changeset is now deployed into REF.
    • Clear checksums on REF database:

      • Via Command Line: Log into the server where the Jenkins job ran and cd into Datical DB project directory (in Jenkins workspace). Issue the following command:

        Usage	Sample
        > hammer clearCheckSums <dbref>	> hammer clearCheckSums GIVREF

      • Via Automation: If you already have a specific job to clear checksums, run that job with user provided parameter <dbref>
      • Via Datical DB GUI: Expand the Datical DB project, expand Deployment Pipelines, right-click on REF database and select "Clear Check Sums"

• • • • • Click OK
      • Sync Change Log sync:

        • Via Command Line: Log into the server where the Jenkins job ran and cd into Datical DB project directory (in Jenkins workspace). Issue the following command:

          Usage	Sample
          > hammer changelogSync <dbref>	> hammer changelogSync GIVREF

        • Via Automation: If you already have a specific job to changelogSync, run that job with user provided parameter <dbref>
        • Via Datical DB GUI: Expand the Datical DB project, expand Deployment Pipelines, right-click on REF database and select "Clear Check Sums"

• Select Filter Change Sets and ensure that the correct label is selected

• • • • • Click Finish. Datical DB will take a few moments to complete the sync
    • Status REF:

      • Via Command Line: Log into the server where the Jenkins job ran and cd into Datical DB project directory (in Jenkins workspace). Issue the following command:

        Usage	Sample
        > hammer status <dbref>	> hammer status GIVREF

      • Via Automation: If you already have a specific job to Status database, run that job with user provided parameter <dbref>
      • Via Datical DB GUI: Click the Status button on the REF database

• • Deploy to the environment: Run the Jenkins job to deploy to the next database environment.
    • Via Datical DB GUI: Click Deploy on the next database environment where you want these changes to be deployed. Make sure to specify the correct label
  • Review sql log output
    • Reports\2016\<month>\<deploy_step>\<timestamped_folder>*.sql.log
  • Verify that changes are successfully deployed using the database client tool (e.g., SQL Developer for Oracle)