Sync fails with error "java.sql.SQLSyntaxErrorException: ORA-01745: invalid host/bind variable name".

[former_member664073]

Issue: Full Sync fails with error "java.sql.SQLSyntaxErrorException: ORA-01745: invalid host/bind variable name".

When are we facing this issue ? - We are encountering this error when we trigger a full sync on our content catalog.

Flexiquery which is failing during Sync configuration:

 SELECT {p:pk} FROM {CMSItem AS p} WHERE {p:catalogVersion}=?tgtVer AND NOT EXISTS ( {{SELECT {ts:pk} FROM {ItemSyncTimestamp AS ts} WHERE {ts:syncJob}=0 AND {ts:targetVersion}=?tgtVer AND {ts:sourceVersion}=?srcVer AND {ts:targetItem}={p:pk} }} ) AND NOT EXISTS( {{  SELECT {pp:pk} FROM {CMSItem as pp LEFT JOIN ItemSyncTimestamp AS ts2       ON {ts2:sourceItem}={pp:pk} AND {ts2:syncJob}=0 AND {ts2:targetVersion}=?tgtVer AND {ts2:sourceVersion}=?srcVer }       WHERE {pp:uid}={p:uid} AND {pp:catalogVersion}=?srcVer AND {ts2:pk} IS NULL }} ) ORDER BY {p:creationtime} ASC, {p:pk} ASC) with arguments (srcVer,tgtVer)

Details around our Component Modeling :

• Our component models are designed to be grouped based on functionality and each component group has its own table. Currently we have 36 such component groups.

• CatalogVersionSyncJobs are configured to have roottypes as CMSItem and CMSRelation.

• Thus we have 56 subtypes of CMSItem which have their own deployment table to be joined/unioned during sql query execution.

When we look for the cause for the ORA-01745, we understand that this might happen if the bind variables used in the sql queries are db specific keywords or column names. But considering sqls are built based on flexi queries by the hybris platform, we were not able understand WHY IS SYNC FAILING DUE TO SQLSyntaxErrorException.

Any ideas on troubleshooting this ?

Note: I have the transformed SQL with me, which I am not posting owing to its size. I can definitely share this if it helps in analysis.

Please find the error stack-trace below.

 ERROR [Thread-67] [SyncSchedulerCallableBase] Exception ORA-01745: invalid host/bind variable name
  occurred during executing query
 (SELECT {p:pk} FROM {CMSItem AS p} WHERE {p:catalogVersion}=?tgtVer AND NOT EXISTS ( {{SELECT {ts:pk} FROM {ItemSyncTimestamp AS ts} WHERE {ts:syncJob}=0 AND {ts:targetVersion}=?tgtVer AND {ts:sourceVersion}=?srcVer AND {ts:targetItem}={p:pk} }} ) AND NOT EXISTS( {{  SELECT {pp:pk} FROM {CMSItem as pp LEFT JOIN ItemSyncTimestamp AS ts2       ON {ts2:sourceItem}={pp:pk} AND {ts2:syncJob}=0 AND {ts2:targetVersion}=?tgtVer AND {ts2:sourceVersion}=?srcVer }       WHERE {pp:uid}={p:uid} AND {pp:catalogVersion}=?srcVer AND {ts2:pk} IS NULL }} ) ORDER BY {p:creationtime} ASC, {p:pk} ASC) with arguments (srcVer,tgtVer),
 values(th_content/Staged(8796093252185),th_content/Online(8796093284953))
 
 de.hybris.platform.jalo.JaloSystemException: ORA-01745: invalid host/bind variable name
 
         at de.hybris.platform.catalog.jalo.synchronization.JDBCQuery.handleError(JDBCQuery.java:123) ~[catalogserver.jar:?]
         at de.hybris.platform.catalog.jalo.synchronization.JDBCQuery.execute(JDBCQuery.java:71) ~[catalogserver.jar:?]
         at de.hybris.platform.catalog.jalo.synchronization.SyncSchedulerCallableBase.callImpl(SyncSchedulerCallableBase.java:117) [catalogserver.jar:?]
         at de.hybris.platform.catalog.jalo.synchronization.SyncSchedulerCallableBase.call(SyncSchedulerCallableBase.java:93) [catalogserver.jar:?]
         at de.hybris.platform.catalog.jalo.synchronization.SyncSchedulerCallableBase.call(SyncSchedulerCallableBase.java:1) [catalogserver.jar:?]
         at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_201]
         at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_201]
         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_201]
         at de.hybris.platform.core.TenantAwareThreadFactory$1.internalRun(TenantAwareThreadFactory.java:159) [coreserver.jar:?]
         at de.hybris.platform.core.threadregistry.RegistrableThread.run(RegistrableThread.java:135) [coreserver.jar:?]
 Caused by: java.sql.SQLSyntaxErrorException: ORA-01745: invalid host/bind variable name
 
         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at de.hybris.platform.jdbcwrapper.PreparedStatementImpl.executeQuery(PreparedStatementImpl.java:195) ~[coreserver.jar:?]
         at de.hybris.platform.catalog.jalo.synchronization.JDBCQuery.execute(JDBCQuery.java:67) ~[catalogserver.jar:?]
         ... 8 more
 ERROR [00017MZN::de.hybris.platform.catalog.jalo.synchronization.CatalogVersionSyncJob] (00017MZN) [Job] Caught throwable de.hybris.platform.jalo.JaloSystemException: ORA-01745: invalid host/bind variable name
 [HY--1]
 de.hybris.platform.jalo.JaloSystemException: de.hybris.platform.jalo.JaloSystemException: ORA-01745: invalid host/bind variable name
 [HY--1][HY--1]
         at de.hybris.platform.catalog.jalo.synchronization.CatalogVersionSyncJob.configureFullVersionSync(CatalogVersionSyncJob.java:500)
         at de.hybris.platform.catalog.hmc.SynchronizeCatalogVersionWizard$FullSyncConfigurator.configureCronjob(SynchronizeCatalogVersionWizard.java:864)
         at de.hybris.platform.catalog.jalo.synchronization.CatalogVersionSyncJob.performCronJob(CatalogVersionSyncJob.java:816)
         at de.hybris.platform.cronjob.jalo.Job.execute(Job.java:1390)
         at de.hybris.platform.cronjob.jalo.Job.performImpl(Job.java:814)
         at de.hybris.platform.cronjob.jalo.Job.access$1(Job.java:767)
         at de.hybris.platform.cronjob.jalo.Job$JobRunable.run(Job.java:686)
         at de.hybris.platform.util.threadpool.PoolableThread.internalRun(PoolableThread.java:208)
         at de.hybris.platform.core.threadregistry.RegistrableThread.run(RegistrableThread.java:135)
 Caused by: java.util.concurrent.ExecutionException: de.hybris.platform.jalo.JaloSystemException: ORA-01745: invalid host/bind variable name
 [HY--1]
         at java.util.concurrent.FutureTask.report(FutureTask.java:122)
         at java.util.concurrent.FutureTask.get(FutureTask.java:192)
         at de.hybris.platform.catalog.jalo.synchronization.CatalogVersionSyncJob.scheduleRemovalOfMissingItems(CatalogVersionSyncJob.java:704)
         at de.hybris.platform.catalog.jalo.synchronization.CatalogVersionSyncJob.configureFullVersionSync(CatalogVersionSyncJob.java:462)
         ... 8 more
 Caused by: de.hybris.platform.jalo.JaloSystemException: ORA-01745: invalid host/bind variable name
 [HY--1]
         at de.hybris.platform.catalog.jalo.synchronization.JDBCQuery.handleError(JDBCQuery.java:123)
         at de.hybris.platform.catalog.jalo.synchronization.JDBCQuery.execute(JDBCQuery.java:71)
         at de.hybris.platform.catalog.jalo.synchronization.SyncSchedulerCallableBase.callImpl(SyncSchedulerCallableBase.java:117)
         at de.hybris.platform.catalog.jalo.synchronization.SyncSchedulerCallableBase.call(SyncSchedulerCallableBase.java:93)
         at de.hybris.platform.catalog.jalo.synchronization.SyncSchedulerCallableBase.call(SyncSchedulerCallableBase.java:1)
         at java.util.concurrent.FutureTask.run(FutureTask.java:266)
         at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
         at de.hybris.platform.core.TenantAwareThreadFactory$1.internalRun(TenantAwareThreadFactory.java:159)
         ... 1 more
 Caused by: java.sql.SQLSyntaxErrorException: ORA-01745: invalid host/bind variable name
 
         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
         at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
         at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
         at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
         at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
         at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
         at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
         at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
         at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
         at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
         at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
         at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
         at de.hybris.platform.jdbcwrapper.PreparedStatementImpl.executeQuery(PreparedStatementImpl.java:195)
         at de.hybris.platform.catalog.jalo.synchronization.JDBCQuery.execute(JDBCQuery.java:67)
         ... 8 more
  

[former_member620692]

There is no problem with your query as per the Flexible Search Query specification. In order to analyze further, I will need the generated SQL. Please put it in a text file and attach it with the question.

The purpose for which I need the generated SQL is to check the value of the bind variables and ensure that they are not Oracle keywords or column names in the database.

Apart from this, I also recommend trying it with a different database (e.g. the default HSQLDB) and if it works, try upgrading/downgrading your Oracle database driver.

[Former Member]

It should be due to a malformed SQL statement, where substitution is failing. Can you match the brackets, substitutes and their values ??