Troubleshoot JDBC V2 connector datasource job failures

 

The issue arises when running the JDBC v2 connector with lucidworks.jdbc 4.1.3 (lucidworks.connector.jdbc-2.3.0.zip). Although the datasource reports success, no documents are indexed (as none appear in Query Workbench), and the counters fail to increment. However, this problem does not exist in the older version of the JDBC connector. When clients upgraded their Fusion version that included lucidworks.jdbc 4.1.3 (lucidworks.connector.jdbc-2.3.0.zip) as the latest release (until the previous month), they encountered the following issues.

Logs from the connector backend and JDBC connector plugin pods identified the occurrence of the following error:

ERROR [fetch-input-receiver.opentext_gallery-95:com.lucidworks.connector.plugins.jdbc.fetcher.processor.PageProcessor@76] - 
Error while iterating results com.lucidworks.connector.plugins.jdbc.exception.JdbcRuntimeException: 
Cannot resolve Oracle CLOB data.
Caused by: java.lang.ClassNotFoundException: oracle.jdbc.internal.OracleClob
at java.base/java.net.URLClassLoader.findClass(URLClassLoader.java:476)

Few similar issues captured :

Explanation:

errorMessage: "The following components failed: [class com.lucidworks.connectors.service.components.job.processor.DefaultDataProcessor : 
Job terminated due to no plugin activity within 600 seconds]"

errorMessage: "The following components failed: [class com.lucidworks.connectors.service.components.job.processor.DefaultDataProcessor : 
Job terminated due to no plugin activity within 5000 seconds]"

“cannot obtain status from the job backend in 60000 ms”. 

 

 

An example of a successful scenario involved indexing 10 million or more documents with the following configurations:

 

Sometimes, infra-level configurations alone may not suffice, and tweaking the datasource  configuration becomes necessary.

For instance, indexing the aforementioned 10 million plus documents required the following datasource configurations:

The connectors-backend can sometimes fail in an unrecoverable state when a V2 remote datasource encounters an error. The UI continues to display "running" even when the remote connector is stopped on the external client.

 

To address this, restart the connector-backend services. Restarting both the connector-backend and connector-plugin may be necessary to resume crawling after a connector-backend crash.

 

The confusing state of the plugin or connectors-backend arises due to communication over Kafka (or Pulsar in earlier versions). Since communication is not direct, a crash on one side is not immediately detected on the other side. When the crashing process restarts, a state mismatch can cause various issues. In some cases, restarting the plugin or remote connector is sufficient, but for guaranteed recovery, restarting both the backend and the plugin is recommended.

 

 

Verifying the Query :

If the query returns a large number of rows, it may be worth trying to leverage the pagination of the results. The client can try using Fusion’s built-in offset and limit variables to manage that. The query would need to be modified to leverage those.

 

In the query description

A SQL SELECT statement to choose the records to be retrieved. 
For paginated queries, use the special variables ${limit} and ${offset}

 

For Example:

When dealing with a large dataset, such as approximately 4 million records, it becomes imperative to divide the task into 3-4 smaller jobs. For instance, by breaking down the query into chunks of 100,000 records, the process works seamlessly when data is limited.

Please note that pagination will not function correctly if the query lacks the ${limit} and ${offset} variables, as described in the field definition:

A SQL SELECT statement to choose the records to be retrieved. 
For paginated queries, use the special variables ${limit} and ${offset}. 
The specific syntax will be driver dependent. 
Examples: 
Mysql - SELECT * FROM test_table LIMIT ${limit} OFFSET ${offset}, 
Microsoft SQLServer - SELECT * FROM test_table ORDER BY primary_key OFFSET ${offset} FETCH NEXT ${limit} ROWS ONLY