Description: Pipelines in AFLAC would go to Blocked state as the MongoDB service on the IWX Edge node crashes because of the compatibility issues of MongoDB on the IWX Edge node OS.


The state of the running pipelines will be in the building state in the Infoworks metadata store and it will not be marked as failed as the pipelines were interrupted because of the MongoDB crash.


Impact: After restarting all the IWX services including the MongoDB service to bring them back online, if you trigger the pipelines, the below message would be displayed in the pipeline build UI page.





The screenshot says the previous build is still running. As MongoDB service crashed when the previous build was running, the pipeline state is not updated in the metadata. 

Note: The long-term solution for this issue is to upgrade Infoworks to v5.0 from v3.1.2 to avoid the MongoDB crash. As an interim solution/workaround, perform the below steps to resolve this issue.


Run the below Query1 to check the value for the key state of the pipeline. If the value for the below pipeline is building, then run query 2:

Query1:

a) Login to IWX Edge node as Infoworks user.
b) source /opt/infoworks/bin/env.sh
c) mongo -uinfoworks -pIN11**rk infoworks-new
d) db.pipelines.find({_id:ObjectId("provide the pipeline id")}).pretty()



For example: db.pipelines.find({_id:ObjectId("d509ffe28769bd4d7c432d33")}).pretty()


In the JSON out of the above command, check for the value of the key state and it will be building.



db.pipelines.find({_id:ObjectId("d509ffe28769bd4d7c432d33")}).pretty()
{
        "_id" : ObjectId("d509ffe28769bd4d7c432d33"),
        "name" : "p-s3-secure-to-snowflake-wyn-extract_partyphone-prod",
        "description" : "",
        "batch_engine" : "SPARK",
        "domainId" : ObjectId("c59bff550b57a81f3b33ebdb"),
        "deploy_mode" : "cluster",
        "metadata_version" : 3,
        "createdAt" : ISODate("2021-05-01T09:43:40.089Z"),
        "createdBy" : "Qbb7zL7kRBhbikxdk",
        "dummyJobId" : ObjectId("aaf77f1aacef13696e84bc8b"),
        "modifiedAt" : ISODate("2021-05-01T09:43:41.832Z"),
        "modifiedBy" : "Qbb7zL7kRBhbikxdk",
        "active_version_id" : ObjectId("951652b897144264bf1d4e78"),
        "versions_count" : 3,
        "state" : "building",
        "stateModifiedBy" : "31f850fd436333be2d4f042b"
}

Query2:

a) Login to IWX Edge node as Infoworks user.
b) source /opt/infoworks/bin/env.sh
c) mongo -uinfoworks -pIN11**rk infoworks-new
d) db.pipelines.update({_id:ObjectId("provide the pipeline Id")},{"$set":{"state":"ready"}})


For example: db.pipelines.update({_id:ObjectId("d509ffe28769bd4d7c432d33")},{"$set":{"state":"ready"}})


Run the pipeline build again, it would fail with a message "Cannot crawl table as it is already being crawled"




The above failure happens as the state of the pipeline target is not updated to ready state as the MongoDB crashed abruptly.



Run the below queries to confirm that the state of the pipeline target table is still crawling_from_ready and then update the state of the table accordingly.

Query3:


a) Login to Edge node as Infoworks user
b) source /opt/infoworks/bin/env.sh
c) mongo -uinfoworks -pIN11**rk infoworks-new
d) db.getCollection('tables').find({_id:ObjectId("608d22d0c6b7d363a6d3644c")})


> db.getCollection('tables').find({_id:ObjectId("608d22d0c6b7d363a6d3644c")})

{ "_id" : ObjectId("608d22d0c6b7d363a6d3644c"), "table" : "EXTRACT_PARTYPHONE", "type" : "table", "parent_entity_type" : "pipeline", "pipeline_id" : ObjectId("aaf77f1aacef13696e84bc8b"), "target_node_id" : "TARGET_1", "batch_engine" : "SPARK", "scd_type" : null, "hdfs_path" : null, "current_data_subdir" : null, "last_merged_watermark" : "2021-08-21 09:25:31", "last_merged_timestamp" : null, "columns" : [ { "name" : "row_id", "at" : 1, "type" : "bigint", "sqlType" : -5, "targetSqlType" : -5, "precision" : 0, "role" : "o", "size" : 0, "isAuditColumn" : false, "isPredictionColumn" : null }, { "name" : "currentrecordindicator", "at" : 2, "type" : "boolean", "sqlType" : 16, "targetSqlType" : 16, "precision" : 0, "role" : "o", "size" : 0, "isAuditColumn" : false, "isPredictionColumn" : null }, { "name" : "sourcesystemcode", "at" : 3, "type" : "string", "sqlType" : 12, "targetSqlType" : 12, "precision" : 2147483647, "role" : "o", "size" : 2147483647, "isAuditColumn" : false, "isPredictionColumn" : null }, { "name" : "logicaldeleteindicator", "at" : 4, "type" : "boolean", "sqlType" : 16, "targetSqlType" : 16, "precision" : 0, "role" : "o", "size" : 0, "isAuditColumn" : false, "isPredictionColumn" : null }, { "name" : "party_wynnsid", "at" : 5, "type" : "int", "sqlType" : 4, "targetSqlType" : 4, "precision" : 23, "role" : "o", "size" : 23, "isAuditColumn" : false, "isPredictionColumn" : null }, { "name" : "party_wynid", "at" : 6, "type" : "bigint", "sqlType" : -5, "targetSqlType" : -5, "precision" : 0, "role" : "o", "size" : 0, "isAuditColumn" : false, "isPredictionColumn" : null }, { "name" : "party_wynversion", "at" : 7, "type" : "int", "sqlType" : 4, "targetSqlType" : 4, "precision" : 23, "role" : "o", "size" : 23, "isAuditColumn" : false, "isPredictionColumn" : null }, { "name" : "etl_updatedatetime", "at" : 8, "type" : "timestamp", "sqlType" : 93, "targetSqlType" : 93, "precision" : 29, "role" : "o", "size" : 29, "isAuditColumn" : false, "isPredictionColumn" : null }, { "name" : "partyaddresscategorycode", "at" : 9, "type" : "string", "sqlType" : 12, "targetSqlType" : 12, "precision" : 2147483647, "role" : "o", "size" : 2147483647, "isAuditColumn" : false, "isPredictionColumn" : null }, { "name" : "lastuser_nsid", "at" : 10, "type" : "int", "sqlType" : 4, "targetSqlType" : 4, "precision" : 23, "role" : "o", "size" : 23, "isAuditColumn" : false, "isPredictionColumn" : null }, { "name" : "lastuser_id", "at" : 11, "type" : "bigint", "sqlType" : -5, "targetSqlType" : -5, "precision" : 0, "role" : "o", "size" : 0, "isAuditColumn" : false, "isPredictionColumn" : null }, { "name" : "vbd_nsid", "at" : 12, "type" : "int", "sqlType" : 4, "targetSqlType" : 4, "precision" : 23, "role" : "o", "size" : 23, "isAuditColumn" : false, "isPredictionColumn" : null }, { "name" : "vbd_id", "at" : 13, "type" : "bigint", "sqlType" : -5, "targetSqlType" : -5, "precision" : 0, "role" : "o", "size" : 0, "isAuditColumn" : false, "isPredictionColumn" : null }, { "name" : "listorder", "at" : 14, "type" : "int", "sqlType" : 4, "targetSqlType" : 4, "precision" : 23, "role" : "o", "size" : 23, "isAuditColumn" : false, "isPredictionColumn" : null }, { "name" : "ver_etl_updatedatetime", "at" : 15, "type" : "timestamp", "sqlType" : 93, "targetSqlType" : 93, "precision" : 29, "role" : "o", "size" : 29, "isAuditColumn" : false, "isPredictionColumn" : null }, { "name" : "effectivedate", "at" : 16, "type" : "timestamp", "sqlType" : 93, "targetSqlType" : 93, "precision" : 29, "role" : "o", "size" : 29, "isAuditColumn" : false, "isPredictionColumn" : null }, { "name" : "enddate", "at" : 17, "type" : "timestamp", "sqlType" : 93, "targetSqlType" : 93, "precision" : 29, "role" : "o", "size" : 29, "isAuditColumn" : false, "isPredictionColumn" : null }, { "name" : "phonetypecode", "at" : 18, "type" : "string", "sqlType" : 12, "targetSqlType" : 12, "precision" : 2147483647, "role" : "o", "size" : 2147483647, "isAuditColumn" : false, "isPredictionColumn" : null }, { "name" : "phonenumber", "at" : 19, "type" : "string", "sqlType" : 12, "targetSqlType" : 12, "precision" : 2147483647, "role" : "o", "size" : 2147483647, "isAuditColumn" : false, "isPredictionColumn" : null }, { "name" : "phoneextension", "at" : 20, "type" : "string", "sqlType" : 12, "targetSqlType" : 12, "precision" : 2147483647, "role" : "o", "size" : 2147483647, "isAuditColumn" : false, "isPredictionColumn" : null } ], "configuration" : { "build_mode" : "OVERWRITE", "schema_name" : "STAGE_WYN", "table_name" : "EXTRACT_PARTYPHONE", "database_name" : "EODS_PROD", "natural_key" : [ "row_id", "party_wynnsid", "party_wynid", "party_wynversion", "vbd_nsid", "vbd_id", "effectivedate" ], "read_supported_engines" : [ ], "write_supported_engines" : [ ], "table_type" : "SNOWFLAKE" }, "cache_properties" : null, "state" : "crawling_from_ready", "stateModifiedBy" : "31f850fd436333be2d4f042b", "createdAt" : ISODate("2021-08-21T09:25:31.387Z"), "createdBy" : "uHf8yDTBCWSTBHC5H", "rowCount" : NumberLong(87909132) }



The state of the above pipeline table still shows as crawling_from_ready in the above JSON output. Run the below query to update the state of the table to ready and then trigger the pipeline build job again.


e) db.tables.update({"_id":ObjectId("provide the table id")},{"$set":{"state":"ready"}})


For example: db.tables.update({"_id":ObjectId("608d22d0c6b7d363a6d3644c")},{"$set":{"state":"ready"}})



Applicable IWX versions:

v3.1.2-emr