TGAL-6990-Upgrade to 2021.4.2.47792 or 2022.1.1.3.42590 results in failed schema migration if a gallery data connection contains an empty or null password in the connection string

[TimothyR]

Environment Details

 

When upgrading or patching the Alteryx Server to 2021.4.2.47792 or 2022.1.1.3.42590, a failed gallery schema migration can occur on the dataConnections collection with a stored Gallery Data Connection that has an empty value in the PasswordSecured field.  These empty or null values are not abnormal and can be created by choosing “Other” in the Technology setting on a new Gallery Data Connection and leaving out a password value in the connection string.

Error when accessing Server:

image.png

Example error in alteryx-migration.csv file (located in Server/Gallery log directory):

15:44:09.311829,FATAL,1,AlteryxServerWebApiHost,migrationLogger,DoMigrateDatabase,Migration failed with error: Migration to version 34.03 failed: Renaming collection from 'dataConnections' to 'dataConnectionsMigrationInProcess_34.03'->Done renaming collection->Aborting migration->Removing collection: dataConnectionsMigrationInProcess_34.03->Done Aborting migration->, Migration 34.03

 

• Alteryx Server
  • 2021.4.2.47792
  • 2021.4.2 (Patch 5)
  • 2022.1.1.42590
  • 2022.1.1.3 (Patch 3)
  • 2022.3 LA

Cause

When the dataConnections migration executes, the script expects a value in the PasswordSecured  field to decrypt and will error and fail the migration if there is no value.  A failed migration will render the Gallery inaccessible via the browser even though the Alteryx Service will show “Running” in the Microsoft Services Console or Task Manager. This issue will be fixed in an upcoming patch.

Diagnosis

• If you are pre-upgrade:
  • If any records are returned from running the following query in your Mongo database (either in command line or Robo3T):
    db.getCollection('dataConnections').find({$or:[{"PasswordSecured": ""},{"PasswordSecured": null}]}) 
  • If you were referred to this article via the Pre-upgrade checks workflow
• If your upgrade is currently in a failed status and you are receiving the red migration error above

 

Resolution

Overview

In order to resolve this issue, you must temporarily add a valid encrypted value to the "PasswordSecured" field in each of the affected data connections in your Mongo database. Once you have completed a successful upgrade, these fields must be reverted back to their original value in order for the connections to function properly.
 

Scenario 1

Note: If Server has not been upgraded to the affected versions or was rolled back after failed migration, proceed to step 1, otherwise skip to Scenario 2.

1. Create a “dummy” Gallery Data Connection selecting Other for Connection Type, use the following information verbatim to create the connection:

Connection Name: temporary connection
Connection String: PWD=temporarypassword

image.png

2. Log in to the AlteryxGallery db via Robo3t or the through the command line and find the connection you created (either manually or by running the following query): db.getCollection('dataConnections').find({"ConnectionName": "temporary connection"})
Right-click the PasswordSecured field, select Copy Value, and save to a notepad for later use:
image.png

3. Skip to section: “Edit problematic data connections”

Scenario 2

Note: If your Server has already been updated and is resulting in the migration error within the Diagnosis section above, follow these steps.

1. Via Robo3T or command line, locate a data connection that has a non-empty or non-null PasswordSecured value (It may be easiest to do this from the table view on Robo3t):

image.png

2. If you do not have a connection available that has a valid PasswordSecured value, you can copy an encrypted value from the dCMECredentials collection:

image.png
If there are no encrypted values in the dCMECredentials collection or the collection itself is not there, you will need to rollback your server upgrade and refer back to the previous section.

3. Proceed to next section: “Editing problematic data connections”
 

Editing problematic data connections:
 

Run following query in AlteryxGallery to identify problematic records (same query listed in the Diagnosis above):
db.getCollection('dataConnections').find({$or:[{"PasswordSecured": ""},{"PasswordSecured": null}]})
Edit each document returned and paste the encrypted value you copied earlier into the PasswordSecured value and save:

Take note of the connection names and the original values in notepad to revert back after the migration.

image.png
 

Install/Post-install of patch
 

1. Install patch/build for desired version (2021.4.2.47792 or 2022.1.1.3.42590)
2. Start the Alteryx Service after the install and confirm that the migration completes within the “alteryx-migration.csv” logfile.  The logfile can be found in your Gallery log location.   Look for the following towards bottom of file:
image.png3. Using Robo3T or the command line, edit the connections you modified back to their original state.
 

*If you require any assistance on the above steps, please reach out to support@alteryx.com or better yet, open a case through the Case Portal and attach logs and screenshots.

Additional Resources

• How to Login to Mongo via Robo 3T
• How to log in to Mongo via command line
• Pre-upgrade checks