Welcome to the Alteryx Knowledge Base

AlteryxService and Gallery not starting after upgrading the server to 2021.2+. The following error message appears in the Gallery logs, preventing the schema migration and AlteryxServerHost from starting.

FATAL,1,Registrations,RegisterDataLayer,,,,WIN-D4T4I281SK4,,,,,,Command createIndexes failed: E11000 duplicate key error collection: AlteryxGallery.users index: Email_1 dup key: { : null }

• Alteryx Server 
• • Version 2021.2 +

Starting from version 2021.2, any users with duplicate emails found in MongoDB will prevent the migration from taking place and put the Alteryx Server in maintenance mode. The migration will not start as AlteryxGallery.users collection has duplicate records/documents.

The error message verbosely identifies which of the Emails is a duplicate. In this example, Email_1 dupe key: { : null} means there are multiple users with the Email "null". If you had two users with the email address "user@email.com", it would show Email_1 dupe key: { : users@email.com}

We believe any duplicate values on any of the indexes will cause this issue. 

 
 

Diagnosis

1. Locate the Gallery logs from when the customer upgraded (Click here to find the location).
2. Search for the following in the logs: 

E11000 Duplicate Key

Solution A: Using Robo 3T

If Robo 3T is not available, go to Solution B.
 

Part 1: Identify which users have duplicate Emails

1. Start Mongo from the Command Prompt by following the steps in this Knowledge Article.
2. Log into AlteryxGallery Database using Robo3t (Click here for guide).
3. Open the users collection.
4. Run these queries to search for duplicate/null values:
   1. Null values

      db.getCollection('users').find({"Email" : null})

   2. Duplicate values - YOU MUST RUN THIS QUERY, even if you have a clearly identified email address. This is required because the current logging only returns the 1st duplicate email, and there may be multiple!

      db.getCollection("users").aggregate([{ "$group" : { "_id" : "$Email","count" : {"$sum" : 1.0}}},{ "$match" : { "count" : { "$gt" : 1.0}}}],{"allowDiskUse" : false})

5. The second query above will return an aggregated list of values, showing you the Email address that is duplicated, and how many occurrences there are:

Part 2: Identify which of the users is the duplicate

 Double-click on the users collection, and search for each of the email addresses:

db.getCollection("users").find({"Email" : "myemail@company.com"})

At this point, you will have at least 2 records returned. Now, you need to identify which of the users is the duplicate.

Options:

• Check in appInfos if the user has uploaded any workflows (using the _id from users)

db.getCollection("appInfos").find({ "CreatedBy" : "5ec60be4e7906319cca455c0"})

• Check in sessions to find which of the users has multiple sessions attached to them. The duplicate user is likely to only have 1 session attached, while the "main" user record should have many sessions.

db.getCollection("sessions").find({ "UserId" : "5ec60be4e7906319cca455c0"}).count()

Part 3: Update the duplicate records

1. Right-click the identified duplicate record and select Edit Document. 
2. Under Email attribute, either:
   1. Determine which of the two emails is the duplicate, and edit the email address of the duplicate
      1. Right-click > Edit on the identified duplicate record.
      2. Change the Email, for example, by appending 1, i.e., "myemailaddress1@company.com"
   2. Replace the null value with a new email address in Quotes
      1. Right-click > Edit on the null records.
      2. Enter "Email": "correct_user_email@company.com" in the JSON.
3. Save the document and Restart the service.

Solution B: Using Command Prompt

Part 1: Identify which users have duplicate Emails

1. Start Mongo from Command Prompt by following the steps in this Knowledge Article.
2. Open a new MongoDB shell instance in Command Prompt by following the Procedure for using embedded MongoDB in this Knowledge Article . Note that we only need to set up the connection to the "AlteryxGallery" database.
3. Run these queries to search for duplicate/null values:
   1. Null values

      db.getCollection('users').find({"Email" : null})

   2. Duplicate values - YOU MUST RUN THIS QUERY, even if you have a clearly identified email address. This is required because the current logging only returns the 1st duplicate email, and there may be multiple!

      db.getCollection("users").aggregate([{ "$group" : { "_id" : "$Email","count" : {"$sum" : 1.0}}},{ "$match" : { "count" : { "$gt" : 1.0}}}],{"allowDiskUse" : false})

4. The second query above will return an aggregated list of values, showing you the Email address that is duplicated, and how many occurrences there are.
5. Save the above search results, as we will come back to them later.

Part 2: Identify which of the users is the duplicate

1. In the MongoDB shell instance in Command Prompt, run this command: 

   db.getCollection("users").find({"Email" : "myemail@company.com"}).pretty()

2. At this point, you will have at least 2 records returned in JSON format. You can distinguish between the records by their unique MongoDB user ID, which appears as the first row in each JSON record. Now, you need to identify which user record represents the duplicate. There are two ways to check:
   • Check in appInfos if the user has uploaded any workflows (using the _id from users). For each MongoDB user ID, run this command in the MongoDB shell: 

     db.getCollection("appInfos").find({ "CreatedBy" : "5ec60be4e7906319cca455c0"})

   • Check in sessions to find which of the users has multiple sessions attached to them. The duplicate user is likely to only have 1 session attached, while the "main" user record should have many sessions. For each MongoDB user ID, run this command in the MongoDB shell: 

     db.getCollection("sessions").find({ "UserId" : "5ec60be4e7906319cca455c0"}).count()

Part 3: Update the duplicate records

1. Run the following command to update the duplicate user record's email to a unique value, for example, by appending "1" to the name in the email: 

   db.getCollection('users').update({"_id":ObjectId("DUPLICATE_USER_ID")}, { $set: { "Email": "myemailaddress1@company.com" } })

2. For each user with a null value in the "Email" attribute identified previously, run the same command to update the "Email" attribute with a valid value: 

   db.getCollection('users').update({"_id":ObjectId("NULL_USER_ID")}, { $set: { "Email": "correct_user_email@company.com" } })

3. Start AlteryxService.

As a final check, confirm that the MongoDB database migration was successful by checking the Gallery logs. Open the Gallery logs and check if the schema migration is successful:​​​​ 

2021-04-07 09:50:30.887184,INFO,1,Runner,MigrateDatabase,,,,POD-620132,,,,,,Database migration successful.,

If, for any reason, you can update from the GUI directly (requires the server to be stable and the Gallery to be accessible), this article walks you through how to do this: How to Identify and Fix Users with Duplicate Email... - Alteryx Community

• How to perform an manual schema migration
• How to manually migrate MongoDB (Version migration)
• How to Identify and Fix Users with Duplicate Email... - Alteryx Community