Troubleshooting MongoDB Integration Error in ClickHouse

Integrating different data storage solutions can sometimes present unexpected challenges. If you’ve encountered the below error while integrating MongoDB with ClickHouse, don’t worry – we’re here to help you navigate it.

The Error Message:

Code: 634. DB::Exception: Received from localhost:9000. DB::Exception: Got error from MongoDB: OP_QUERY is no longer supported. 
The client driver may require an upgrade. For more details, see https://dochub.mongodb.org/core/legacy-opcode-removal, code: 5739101: While executing MongoDB. (MONGODB_ERROR)

clickhouse1 :) SELECT * FROM mongodb(
                   '10.0.6.24:27027',
                   'sampleDB',
                   'my_collection',
                   'test_user',
                   'password',
                   'log_type String, host String, command String',
                   'connectTimeoutMS=10000'
               )

SELECT *
FROM mongodb('10.0.6.24:27027', 'sampleDB', 'my_collection', 'test_user', 'password', 'log_type String, host String, command String', 'connectTimeoutMS=10000')

Query id: e00b0e2a-13b5-4f0c-8f80-4014aefb4024


0 rows in set. Elapsed: 0.027 sec.

Received exception from server (version 23.3.2):
Code: 634. DB::Exception: Received from localhost:9000. DB::Exception: Got error from MongoDB: OP_QUERY is no longer supported. 
The client driver may require an upgrade. For more details see https://dochub.mongodb.org/core/legacy-opcode-removal, code: 5739101: While executing MongoDB. (MONGODB_ERROR)

clickhouse1 :)

Understanding the Issue: The error message indicates that you’re facing an issue related to the MongoDB integration with ClickHouse. Specifically, the error is due to the fact that the OP_QUERY operation is no longer supported in MongoDB after version 5. This incompatibility between MongoDB’s version and the operation ClickHouse is attempting to execute is causing the integration to fail.

Bug details  :

https://github.com/ClickHouse/ClickHouse/issues/45621

https://github.com/ClickHouse/ClickHouse/pull/50061

Solution: Thankfully, there’s a straightforward solution to address this problem. To resolve the error and successfully integrate MongoDB with ClickHouse, follow these steps:

1. Upgrade ClickHouse Version: To align with the changes in MongoDB’s capabilities, ensure that you’re using the latest version of ClickHouse. By upgrading to the latest version, you’ll benefit from bug fixes and improvements that have been made, including compatibility updates for MongoDB integration.
2. Verify Configuration: Double-check your integration configuration settings to ensure that they are accurate and up-to-date.

The bug has been fixed in ClickHouse release 23.6, 2023-06-29 link here

• Update MongoDB protocol to support MongoDB 5.1 version and newer. Support for the versions with the old protocol (<3.6) is preserved. Closes #45621, #49879. #50061 (Nikolay Degterinsky).

Example:

If you’re using a command similar to the one provided in this post, Ensure you’ve upgraded ClickHouse to the latest version before reattempting this command.

Success at Last: After upgrading ClickHouse to the latest version, your integration should work smoothly, and you’ll be able to retrieve data from MongoDB without any issues. You can then execute the command with confidence:

clickhouse1 :) SELECT * FROM mongodb(
                   '10.0.6.24:27027',
                   'sampleDB',
                   'my_collection',
                   'test_user',
                   'password',
                   'log_type String, host String, command String',
                   'connectTimeoutMS=10000'
               )

SELECT *
FROM mongodb('10.0.6.24:27027', 'sampleDB', 'my_collection', 'test_user', 'password', 'log_type String, host String, command String', 'connectTimeoutMS=10000')

Query id: 3a90815e-ab64-44a6-b4c8-d188dd3ebd27

┌─log_type─┬─host───────┬─command─────────────────────┐
│ event    │ 120.5.33.9 │ check-cpu-usage -w 75 -c 90 │
│ event    │ 120.5.33.4 │ system-check                │
└──────────┴────────────┴─────────────────────────────┘

2 rows in set. Elapsed: 0.029 sec.

If you’d like more detailed guidance on MongoDB integration with ClickHouse kinldy refer https://chistadata.com/knowledge-base/ingesting-data-from-mongodb/

Remember, integrations can sometimes be tricky, but with the right steps and resources, your MongoDB and ClickHouse will work together seamlessly in no time. Happy data integration!

Stay tuned to our blog for more insightful articles on data integration, troubleshooting, and making the most of your tech solutions. If you have any questions or feedback, please reach out in the comments below.