Alteryx Server Knowledge Base

Procedures for backing up Built-in MongoDB

One of the three database options when setting up the Alteryx Server is to connect into a User-Managed MongoDB instance. Why would you want to set up your own implementation of MongoDB? The main benefits are to take advantage of the features of MongoDB that are not included with our embedded instance.

This error typically occurs when using an unsupported MongoDB version for instance MongoDB 4.2+.or using a Server Usage Report version not compatible with the Server version.

One of the three database options when setting up the Alteryx Server is to connect into a User-Managed

This KB outlines how to enable MongoDB logs for troubleshooting purposes

After having used MongoDB logging for troubleshooting purposes, you can now safely turn it off.

Alteryx Service throws corrupt record error when a MongoDB backup script is in progress

There are occasions where it is necessary to identify the ID of a Workflow or Schedule. These are easy to find in the URL.

How to determine which User deleted a Gallery application

How To: Shrink the size of Alteryx Server Embedded MongoDB It is common to see cases where the customer says that they are running out of space on their database. The customer goes into his Alteryx System Settings > Controller > Persistence > Persistence Options, decreases the number of days he wants to hold on to workflow results, completed schedules, or uploaded files in their database, they then notice that the workflow results, completed schedules, or uploaded files have less records, however the size of his database remains the same. When you change the persistence options by changing the number of days you want to deleteworkflow results, completed schedules, or uploaded files from the database, you are indeed getting rid of those files, however the space where those files were, are now empty spaces in the database. In order to get rid of those empty spaces you need to perform a database backup and restore, which will then eliminate all of those empty spaces and shrink the size of the database. This is explained in the MongoDB documentation, here, scrolling down to the section named "How do I reclaim disk space in WiredTiger?". Prerequisites Product - Alteryx Server Product - Embedded MongoDB Example Keep in mind that this procedure below is based on an existing sample I created and the description is based on the sample. 1. Verification Verify Size of Embedded MongoDB by navigating to the database folder in a File Explorer > right click > select properties > verify size. - Notice on the screenshot below thatin my case the MongoDB has a size of 913 MB. - To find out where your database folder open Alteryx System Settings > Controller > Persistence > Data Folder Open Alteryx Designer in the server and go to Options > View Schedules > Make sure that the Controller field is set to "My Computer" > go into the Results tab > verify the number of results in the bottom left of the View Schedules screen - Notice on the screenshot bellow that my scheduler contains 174 results 2. Implementation Open Alteryx System Settings > Controller > Persistence > Persistence Options If you do not have any of the Persistence Options Enabled then enable them by selecting the checkboxes and set it to the desired number of days. The Persistence options are: - Delete queue and results after (days) - Delete completed schedules after (days) - Delete uploaded files after (days) When you enable Persistence Options, which are pretty much self explanatory, queue and results, completed schedules, and uploaded files will be deleted after the number of days set by the user. In my example the Alteryx System Settings had the Persistence Options "Delete queue and results after (days)" and "Delete completed schedules after (days)" set to delete my results that are over 120 days old.I changed those settings to 30 days and then I kept clicking next through all the screens until it finished and the service automatically restarted. 3- After Implementation Back into my scheduler you can see that the total number of results went down from 174 (previously checked in the verification section above) to only 71 workflows. Also notice that the size of my database did not change. It is still 913 MB. The procedure above did delete multiple documents from the database, however (as mentioned on the introduction of this article above) those documents are now empty spaces and the size occupied by the database remains the same. Procedure In order to get rid of the empty spaces in the database it is necessary to do a database backup and restore. 1. Backup Create an empty folder to store the backup in. I named it MongoDB_backup. Open command prompt as administrator Navigate to path “Program Files\Alteryx\bin” Stop the service by using command "AlteryxService.exe stop" Then enter the emongodump command as seen below ( AlteryxService.exe emongodump=Z:\Path\MongoDB_backup) : After the backup finishes it is time to do a restore. 5. Restore As you can see on my screenshot below, first I checked if the service was still stopped. Rename the current database folder (i.e. MongoDB_4_0.old). I then created a new empty folder with the exact same name as what my Mongo database had: MongoDB_4_0 Then I ran the emongorestore command - ( alteryxservice emongorestore=,) Once the restore finished I started the service. ( AlteryxService.exe start) Then I verified the status of service to make sure it is running. 6- Verify if the size of the database has decreased In Windows File Explorer right-click over the MongoDB_4_0 folder and select Properties Notice that the size is now 693 MB, rather then 913 MB as it initially had. Additional Resources Alteryx Server Backup %26 Recovery Part 1: Best Practices Alteryx Server Backup %26 Recovery Part 2: Procedures

This KB gives you fundamental understanding on how to extract a list of users and their permissions on the Gallery. Only relevant for versions 2020.1+ with Windows Authentication.

This KB provides you with steps on connecting to a User-Managed MongoDB using Robo 3T

When migrating a MongoDB to a new machine all the relevant steps in https://help.alteryx.com/20201/server/server-host-recovery-guide (use document for your Server version) should be followed. Skipping steps can cause issue occurring later.

Alteryx Service Fails to Start when Mongo Fails with Error - “Input string was not in a correct format”    When trying to start the Alteryx Service, the following error can be seen in the Alteryx Service Logs:   FATAL,1,,RegisterClasses,,,,Axx-xxxxx100,,,,,,Input string was not in a correct format.,   Environment   Alteryx Server Windows Operating System   Diagnosis   This error message is coming directly from MongoDB and while very generic, it is telling us that the driver is unable to handle the data that is being sent to MongoDB. The Alteryx Service will fail to start and you will most likely get some sort of popup error when trying to start the service from the Windows Services menu.   Is FIPS enabled on your machine? If so, See Solution A.   Are you in a multi-node environment? If you are unable to start the Alteryx Service on a node that is not the Controller, see Solution B.   Cause A   FIPS is enabled on the Machine   Solution A   This error occurs because the encryption Alteryx Server uses for inter-service communication won't happen if FIPS is enabled because .NET will block the requests.   Per this article : “FIPS Compliance Encryption Issues In the .NET framework, three different versions of the SHA256 hashing algorithm, each having different security levels and speed are available. The fastest one among them has not yet been submitted for validation. However, it is believed that it is as secure. Enabling FIPS mode in systems with Microsoft OS will break the .NET applications as they probably use latest cryptography algorithms that are more efficient. And, if the .NET applications must necessarily work then a slower, much less efficient cryptography algorithm must be used.”   The Alteryx Gallery uses .NET completely for communication with MongoDB, there is not another option for communication.   Resolutions\Workarounds: There is currently no workaround to get the Alteryx Service to start with FIPS enabled. FIPS must be disabled completely on the server machine to get the Alteryx Gallery to communicate with Mongo correctly and get the service running. The impacted policy is named System cryptography: Use FIPS compliant algorithms for encryption, hashing, and signing.   To get to this setting:   1. Open the Local Security Policy window from the Windows Start menu 2. Navigate to Local Policies > Security Options 3. Select the correct policy   Make sure that group policy does not enforce FIPS after reboot. To verify that FIPS has been disabled, make sure that the “Enabled” registry key located here HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\FipsAlgorithmPolicy is set to “0”:     Cause B   Generic failed Connection to MongoDB (Not FIPS).   Solution B   The error states that for whatever reason, MongoDB is not able to understand the information that is being sent. A failed connection to MongoDB can also cause this error, though it can be difficult to pinpoint the root cause. There are a few things that you will want to check to see if they are causing the issue:   1. If you have a multi-node environment and are configuring the Gallery, confirm the following in the Alteryx System Settings under Gallery > Persistence. 1. Confirm the password is correct. This password should be the user (not-admin) password from the System Settings on the Controller. 2. Confirm you have the port appended to the hostname (default port for embedded MongoDB is 27018) hostname:port   2. Enable Mongo logging and see if MongoDB gives more details on why the error is occurring  3. Test connecting to MongoDB through another source (such as Robo 3t) and see if this gives more detail on why the error is occurring.          

If you are upgrading your Alteryx Server from an older version to 2018.1 or greater, your MongoDB will be migrated from Mongo 3.0 to Mongo 3.4 as part of the process. This article details how to troubleshoot a potentially failed migration. 

Issue   As documented in the Server release notes (Server 2019.3), Alteryx Server version 2019.3 includes a new version of the embedded MongoDB (MongoDB functions as the server's  persistence layer ) . If you are upgrading your Alteryx Server from 2018.1 or greater*, your MongoDB will be migrated from Mongo 3.4 to Mongo 4.0 as part of the process.   *If upgrading from pre-2018.1, you will need to upgrade to a version between 2018.1-2019.2 before upgrading to 2019.3. Some users have reported the following error after the migration :   Inconsistencies between the original data and the new data may indicate a migration error. Check the C:\ProgramData\Alteryx\Service\Persistence\MongoDB_4_0\migration.log for any errors and contact Alteryx Customer Support for assistance.   While the server may still run on the MongoDB 3.4, this may cause unexpected behavior in the long term. Should you encounter this error, it is important to troubleshoot it and make sure that your MongoDB is able to successfully migrate.   Environment   Alteryx Server Upgrading from a version  ≥ 2018.1 to version 2019.3   Diagnosis   If this error is encountered, the server itself will upgrade, but fail to start the service.   To determine which troubleshooting steps to take, we first need to check if the migration was actually successful or not :   Confirm there a MongoDB_4_0 folder under the path in the Alteryx System Settings under Controller > Persistence > Data Folder. If there is a MongoDB_4_0 folder, check the migration.log file found under the Data Folder path.   Check to see if it matches the successful migration shown here in the Spoiler:   A "successful" migration should have the following: First, you see the original 3.4 database start with the 3.4 executable. Then, getstats.js is run - it will show a list of collections and a count of items in them.   --Starting: C:\Program Files\Alteryx\bin\mongod3_4.exe --dbpath "C:\ProgramData\Alteryx\Service\Persistence\MongoDB" --port 27018 --Starting: mongo3_4.exe localhost:27018 "C:\ProgramData\Alteryx\Service\Persistence\MongoDB\getstats.js" {"databases": [ {"name": "AlteryxService", "collections": [ {"name": "AS_App_Chunks", "count": 290} , {"name": "AS_App_Chunks.Files", "count": 27} ... Next, Mongo will dump out to the PreUpgrade folder specified in the migration utility. Once the backup completes the MongoDB service is stopped with the shutdown.js. You will see MongoDB is not responding because it was shut down successfully - any errors referencing the server being down, or the connection being forcibly closed is expected. --Starting: mongodump3_4.exe --host localhost --port 27018 --username user --password xxxxxxxxxxxxxxxxxxxxxxxxxxx --out "C:\ProgramData\Alteryx\Service\Persistence\MongoDB_PreUpgrade" 2019-09-04T11:21:45.557+0100 writing admin.system.users to 2019-09-04T11:21:45.559+0100 done dumping admin.system.users (4 documents) 2019-09-04T11:21:45.559+0100 writing admin.system.version to 2019-09-04T11:21:45.560+0100 done dumping admin.system.version (2 documents) ... --Stopping MongoDb --Starting: mongo3_4.exe localhost:27018 "C:\ProgramData\Alteryx\Service\Persistence\MongoDB\shutdown.js" ... 2019-09-04T11:21:48.413+0100 I NETWORK [thread1] Socket recv() An existing connection was forcibly closed by the remote host. 127.0.0.1:27018 2019-09-04T11:21:48.413+0100 I NETWORK [thread1] SocketException: remote: (NONE):0 error: 9001 socket exception [RECV_ERROR] server [127.0.0.1:27018] server should be down... Then Mongo is started again, and restored to the new MongoDB_4_0 directory from the previous backup that was just taken. Again, we should not see any errors in here. Then, the createUsers.js re-creates all the user accounts for each database. The MongoDB is then shut down via the shutdown.js again. Again - any errors referencing the server being down, or the connection being forcibly closed is expected.   --Starting: C:\Program Files\Alteryx\bin\mongod.exe --dbpath "C:\ProgramData\Alteryx\Service\Persistence\MongoDB_4_0" --port 27018 --Starting: mongorestore.exe --host localhost --port 27018 "C:\ProgramData\Alteryx\Service\Persistence\MongoDB_PreUpgrade" ... {restore happens} 2019-09-04T11:21:59.059+0100 done --Starting: mongo.exe localhost:27019 "C:\ProgramData\Alteryx\Service\Persistence\MongoDB_34\createUsers.js" MongoDB shell version v4.0.10 connecting to: mongodb://localhost:27018/test?gssapiServiceName=mongodb Implicit session: session { "id" : UUID("b355d9b1-d9b0-4b6f-816f-158da213dcaa") } MongoDB server version: 4.0.10 Successfully added user: { "user" : "user", "roles" : [ "dbOwner", "hostManager", "dbAdminAnyDatabase", "userAdminAnyDatabase", "backup", "restore" ] } Successfully added user: { "user" : "user", "roles" : [ "readWrite", "userAdmin" ] } Successfully added user: { "user" : "user", "roles" : [ "readWrite", "userAdmin" ] } Successfully added user: { "user" : "user", "roles" : [ "readWrite", "userAdmin" ] } --Stopping MongoDb --Starting: mongo.exe localhost:27019 "C:\ProgramData\Alteryx\Service\Persistence\MongoDB\shutdown.js" ... server should be down... 2019-09-04T11:22:01.726+0100 I NETWORK [js] trying reconnect to localhost:27018 failed 2019-09-04T11:22:02.728+0100 I QUERY [js] Failed to end session { id: UUID("129ef6b5-c6a8-40d0-8801-5987cb980e00") } due to SocketException: socket exception [CONNECT_ERROR] server [couldn't connect to server localhost:27018, connection attempt failed: SocketException: Error connecting to localhost:27018 (127.0.0.1:27018) :: caused by :: No connection could be made because the target machine actively refused it.]   The new MongoDB_4_0 is then started up again with Mongo 4.0, and we run getstats.js again, giving us a list of the collections and the count of the documents, similar to the first step. --Starting: C:\Program Files\Alteryx\bin\mongod.exe --dbpath "C:\ProgramData\Alteryx\Service\Persistence\MongoDB_4_0" --port 27018 --Starting: mongo.exe localhost:27018 "C:\ProgramData\Alteryx\Service\Persistence\MongoDB\getstats.js" If everything looks good up to this point, the migration has likely been successful - continue to check the points below. If not, please see Solution B. Are the Alteryx System Settings under Controller > Persistence > Data Folder pointing to the new MongoDB folder (MongoDB_4_0)? If everything looks good in the logs, but the service didn't start or something happened after this point, we should be able to manually change the persistence directory to point at the new MongoDB_4_0 directory, and in theory, the service should start up fine. See Solution A for steps.   What version of Mongo is running after the attempt? In the persistence directory of the target database, there's an ASMongoDBVersion.bin file that seems to match up with the version of Mongo. If this does not show version 4.0, see Solution B.     Solution A   If the log contains all the information mentioned previously and the migration seems successful, but there’s an error starting the service:   Check that the Data Folder under Controller > Persistence is set to the MongoDB_4_0 folder. If is it not, edit it to point to this new folder. Restart the Alteryx Service.   Solution B   If the migration log doesn’t match the description above, give the migration another attempt:   1. Delete the folders MongoDB_PreUpgrade and MongoDB_4_0 2. Run the MongoDbUpgradeTo30.exe found here: %ALTERYX_INSTALL_DIRECTORY%\bin\MongoDbUpgradeTo30.exe     If the MongoDB_4_0 folder is still empty with the exception of the migration.log, then check the MongoDB_PreUpgrade folder for the ASMongoDBVersion.bin file. If it is not there: Copy the file from your old MongoDB folder and paste it into the MongoDB_PreUpgrade folder. Open the file and change the version number to 4.0.10. Close the file and re-attempt the upgrade.   As always, don't hesitate to contact us over at Customer Support if you run into any trouble.

How to migrate from MMAPv1 to the WiredTiger Storage Engine   In earlier versions of MongoDB (3.0 and earlier), the default storage engine used was MMAPv1. The WiredTiger storage engine was introduced in MongoDB 3.0 and is the default storage engine in MongoDB 3.2 and later. Two major benefits of the WiredTiger storage engine are:   Document-level concurrency control for write operations which allows multiple documents in a collection to be updated at the same time Native compression which allows for more efficient use of storage space and less disk I/O   Earlier versions of Alteryx Server shipped with MongoDB 2.6 and MongoDB 3.0 which defaults to using the MMAPv1 storage engine. If you installed Alteryx Server prior to the 2018.1 release when Alteryx Server was upgraded to MongoDB 3.4, it is highly likely that your Alteryx Server is running MongoDB using the MMAPv1 storage engine. To take advantage of the performance benefits of the WiredTiger storage engine you can migrate to the WiredTiger storage engine. This article documents the steps required to migrate to the WiredTiger storage engine by performing a backup and restore of the embedded MongoDB database.   NOTE: The steps below only apply when using Embedded MongoDB with Alteryx Server 2018.1 and newer. If you are running Alteryx Server with a user-managed MongoDB, refer to the MongoDB help documentation.   Prerequisites   Product - Alteryx Server 2018.1 or newer with embedded MongoDB   Procedure   The first step is to identify which storage engine you are using. You can easily identify which storage engine is being used by reviewing the file system. Use the following Instructions to verify whether you are using the MMAPv1 or WiredTiger storage engine.   Open the Alteryx System Settings Navigate to the Controller -> Persistence page Copy the "Data Folder" path Open Windows Explorer (File Browser) Paste the "Data Folder" path copied in Step 3 in the Address Bar of Windows Explorer If you see a series of "NS File" and "0 File" file types, as shown below, your Embedded MongoDB is running using the MMAPv1 storage engine If you see a series of "WT File" file types and a file named "WiredTiger", as shown below, your Embedded MongoDB is running using the WiredTiger storage engine and no further action is necessary Create an Embedded MongoDB Backup   The steps below walk you through creating an Embedded MongoDB backup and restoring the backup to a new directory. For additional information on backup and restore best practices, refer to the additional resources at the end of this article.   Log in to the server hosting the Alteryx Server Controller and embedded MongoDB database Open an elevated command prompt (Right-click -> Run as Administrator) Change directories to the Alteryx Server installation directory cmd: cd %ProgramFiles%\Alteryx\bin Alteryx Server installs to %ProgramFiles%\Alteryx\bin by default. If you installed to a non-default directory, update the paths accordingly as shown in the screenshots Stop the AlteryxService cmd: AlteryxService.exe stop The AlteryxService should report it stopped successfully, as shown in the below screenshot Perform a backup of the embedded MongoDB cmd: AlteryxService.exe emongodump=<Path to Backup Location> Proceed to "Restoring an Embedded MongoDB Backup"    Restoring an Embedded MongoDB Backup   Restore the embedded MongoDB backup to a new directory cmd: AlteryxService.exe emongorestore="<Path to MongoDB Backup>","<Path to Restore to>" Update the Data Folder in the Alteryx System Settings Browse to or enter the path MongoDB was restored to in the Data Folder field on the Controller -> Persistence section In the Alteryx System Settings console, click "Next" until reaching the "Finalize Your Configuration" screen Click the "Finish" button on the "Finalize Your Configuration" screen to apply the changes and start the Alteryx Service Verify the Alteryx Service started cmd: sc query AlteryxService Confirm the state returned is "Running" as shown in the screenshot below   Verify MongoDB is running using the WiredTiger Storage Engine   Open Windows Explorer (File Browser) Navigate to the new "Data Folder" path in Windows Explorer You should see a series of "WT File" file types and a file named "WiredTiger," as shown below. If you see "NS File" file types, confirm you completed the steps outlined in the article     Now that you have completed the steps, your Alteryx Server is running embedded MongoDB using the WiredTiger storage engine - YAY! If you experience any issues with this process, please contact the Alteryx Customer Support Team for further assistance.    Additional Resources   For additional information on managing embedded MongoDB, refer to the Alteryx Server Help topic on MongoDB Management For Alteryx Server Backup and Recovery best practices, refer to the following Community articles Alteryx Server Backup & Recovery Part 1: Best Practices Alteryx Server Backup & Recovery Part 2: Procedures For user-managed MongoDB deployments running MongoDB 4.0, refer to the following MongoDB documentation MongoDB Production Notes Change Standalone to WiredTiger Change Replica Set to WiredTiger Change Sharded Cluster to WiredTiger

This is a very common error that can occur if the AlteryxService shuts down unexpectedly. Most commonly the error is caused by MongoDB not shutting down properly and the lock file does not get released. This prevents MongoDB from starting the next time you try to start the AlteryxService and returns an error message.

Alteryx Server provides a fully scalable architecture that allows an organization to scale Alteryx to automate data analytics, tackle bigger projects, process larger datasets and put self-service data analytics into the hands of more decision makers. From scaling Worker nodes to Gallery nodes to the MongoDB persistence layer, Alteryx Server allows organizations to efficiently manage their automated and self-service data analytics needs.

One common reason why the Alteryx Service appears stuck in the 'Stopping' state is when the service is trying to stop but the AlteryxEngineCmd.exe process is running. In other words, a workflow is running. The Alteryx Service cannot be stopped when a workflow is running due to a schedule or a Gallery run.

Now, find all your Server and Gallery questions and answers in one place!  The new Gallery Admin Help Page has your Server Installation Guide, Configuration instructions, and the much-requested Administer Gallery management features - Subscriptions and Studios defined!  Manage your user permissions!  Edit user accounts!