Welcome to the Alteryx Knowledge Base

Performing deletion using Simba Mongo ODBC Driver in the Output Data tool may result in the following error if many documents (> 10,000) are in the collection.

For version < 2022.1.1

The Designer x64 reported: InboundNamedPipe GetOverlappedResult:  The pipe has been ended.

For version >= 2022.1.1

The Designer x64 reported: You have found a bug.  The pipe has been ended.

If you have enabled logging from the ODBC DSN, you may see the last logging stops after the bulk operation execution with no response.

Example of the log:

Feb 11 01:44:45.979 DEBUG 4720 Simba::MongoDBODBC::MDWriteContext::RemoveRow: bulk_operation_remove_one: { "_id" : { "$oid" : "67a9eedbdb7900006e004931" } }
Feb 11 01:44:45.979 DEBUG 4720 Simba::MongoDBODBC::MDWriteContext::RemoveRow: bulk_operation_remove_one: { "_id" : { "$oid" : "67a9eedbdb7900006e004932" } }
Feb 11 01:44:45.979 DEBUG 4720 Simba::MongoDBODBC::MDWriteContext::RemoveRow: bulk_operation_remove_one: { "_id" : { "$oid" : "67a9eedcdb7900006e004933" } }
Feb 11 01:44:45.979 DEBUG 4720 Simba::MongoDBODBC::MDWriteContext::RemoveRow: bulk_operation_remove_one: { "_id" : { "$oid" : "67a9eedcdb7900006e004934" } }
Feb 11 01:44:45.979 DEBUG 4720 Simba::MongoDBODBC::MDWriteContext::RemoveRow: bulk_operation_remove_one: { "_id" : { "$oid" : "67a9eedcdb7900006e004935" } }
Feb 11 01:44:45.979 DEBUG 4720 Simba::MongoDBODBC::MDWriteContext::ExecuteCurrentBatch: Executing Bulk Operation with size 500

In a successful bulk operation, there should be a return response like the example below:

...
Feb 11 01:44:42.886 DEBUG 4720 Simba::MongoDBODBC::MDWriteContext::ExecuteCurrentBatch: Executing Bulk Operation with size 500
Feb 11 01:44:43.274 DEBUG 4720 Simba::MongoDBODBC::MDWriteContext::ExecuteCurrentBatch: bulk_operation_execute response: { "nInserted" : 0, "nMatched" : 0, "nModified" : 0, "nRemoved" : 500, "nUpserted" : 0, "writeErrors" : [  ] }
...

Configuration example of the Output Data tool:

Using the DELETE statement in either the Pre Create SQL Statement property or the Post Create SQL Statement property results in the same error.

NOTE: The Simba MongoDB ODBC Driver supports Data Manipulation Language (DML) statements such as INSERT, UPDATE, and DELETE. The driver does not support SQL subqueries or ODBC transactions and does not support CREATE, ALTER, and DROP statements for tables. Please refer to the driver documentation from Simba for details.

• Alteryx Designer
  • Versions: All
• Simba MongoDB ODBC Driver
  • Version: 2.3.22.1024
• MongoDB
  • Version >= 3.0

A defect (GCSE-2994/TDCE-727) has been logged to address this issue. Please note that this issue may not be addressed if deemed a driver limitation.

NOTE: It has been confirmed that this is a driver issue and we are not able to address it in Alteryx Designer.

Please consider one or more of the following suggestions to address the error:

Solution A: Increase the Batch Size for Writeback

The batch size setting in the Writeback option is the maximum number of documents the driver can handle at one time during a write operation. Increasing this value from the default value of 500 may help in some cases. Please follow the instructions listed below to change the value.

For DSN connection

1. Open the DSN in ODBC Data Sources (64-bit) and click the Advanced Options button

2. Click the Writeback Options in the Advanced Options window.

3. Change the Batch Size (1 - 1000) parameter to 1000.

4. Click Ok and exit the DSN configuration.

For DSN-less connection

Add DmlBatchSize to the connect string and add a value of 1000.

For example:

Driver=Simba MongoDB ODBC Driver;Server=<DB_SERVER>;Port=<PORT>;DmlBatchSize=1000;Database=<DATABASE>;authMechanism=<AUTH_TYPE>;AuthSource=<DATABASE>;UID=<USER>;PWD=<PWD>

NOTE: If the error still happens but intermittently, please review Solution B. You can use this setting together with Solution B.

Solution B: Turn off Write Concern for Writeback

The value in the Write Concern parameter can be 0, 1 or more for requesting acknowledgement of a write operation in order for the driver to report a successful write operation Setting "0" indicates no request for acknowledgement. Please review the MongoDB documentation for details on this setting and check if it is fine to set "0" for your use case.

For DSN connection

1. Open the DSN in ODBC Data Sources (64-bit) and click the Advanced Options button
2. Click the Writeback Options in the Advanced Options window.
3. Change the Write Concern parameter to 0.
4. Click Ok and exit the DSN configuration.

For DSN-less connection

Add WriteConcern to the connect string and add a value of 0.

For example:

Driver=Simba MongoDB ODBC Driver;Server=<DB_SERVER>;Port=<PORT>;WriteConcern=0;Database=<DATABASE>;authMechanism=<AUTH_TYPE>;AuthSource=<DATABASE>;UID=<USER>;PWD=<PWD>

NOTE: Turning the acknowledgement off should work if the experienced issue is due to the Writeback response. However, if the error still happens after this, please consider Solution C or D instead.

Solution C: Use MongoDB tools

Although the tools are deprecated from version 2023.1.1 onwards, you can still use them if they work for you. Please consider the use of the MongoDB Output tool if necessary and applicable.

Solution D: Use Python to connect to MongoDB

Consider using Python with MongoDB driver for Python (PyMongo) if all the above do not meet the business needs. Please review the PyMongo documentation here.

NOTE: The use of this driver is beyond the scope of Alteryx Support.

Solution E: Increase Documents to fetch per block

If you know the number of documents in the collection to be removed, then increasing the Documents to fetch per block parameter in the ODBC DSN can be used. This parameter determines the number of documents that a query returns at a time. Setting the value of this parameter to be the same or greater than the documents in the collection addresses the error based on our testing. For example, if you have 30,000 documents in collection A and you need to delete these, the value to set in the Documents to fetch per block will then be 30,000 or higher.

NOTE: The maximum value of this parameter is 2,147,483,647. The more documents to return reduces the roundtrip to fetch the documents. However, this will consume more memory and may lead to timeout or excessive memory usage if not set appropriately.

This solution may not be ideal if you have a collection that is dynamic and the number of documents may become more than the initial value you have set as you need to keep changing the value before you use the tool. As such, please review this solution or consider Solution B to D instead.

Please find the instructions below to set the value:

For DSN connection

1. Open the DSN in ODBC Data Sources (64-bit) and click the Advanced Options button

2. Change the Documents to fefch per block parameter to the number of documents in the collection.

3. Click Ok and exit the DSN configuration.

For DSN-less connection

Add BatchSize to the connect string and add the document count in the collection.

For example:

Driver=Simba MongoDB ODBC Driver;Server=<DB_SERVER>;Port=<PORT>;BatchSize=30000;Database=<DATABASE>;authMechanism=<AUTH_TYPE>;AuthSource=<DATABASE>;UID=<USER>;PWD=<PWD>

• MongoDB Output Tool
• How to enable Simba ODBC Driver Log