Generic DB adapter collation conflict issue

Hello,

I'm using integration generic DB adapter for pull data from external MSSQL DB (SQL_Latin1_General_CP1250_CI_AS collation). UCMDB is running on Windows and using MSSQL DB (Czech_CI_AS).

Integration works fine long time on UCMDB 2019.11 version, but last week I did upgrade on 2021.11 and from that time I'm getting integration "error com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "Czech_CI_AS" and "SQL_Latin1_General_CP1250_CI_AS" in the equal to operation."

There was no change on external database, could be there some parameter change on UCMDB during upgrade that now causing mentioned issue? Thanks for any help.

Also strange is that if I use filter in the query to select less records, integration finished as success without error. Here are two examples one select with filter that returns 7 records and second select without filter that returns 136 records

<2021-12-21 12:34:35,784> [INFO ] [JobExecuterWorker-1:DS_CA CMDB Population_012_Unix_servers] (?:?) - DS_CA CMDB Population_012_Unix_servers: Last Successfull run time NEVER
<2021-12-21 12:34:37,016> [INFO ] [JobExecuterWorker-1:DS_CA CMDB Population_012_Unix_servers] (?:?) - DS[CA CMDB Population]: query[UnixServersFromCACMDB] Topology For add[7]  For update[0]  For remove[0]
<2021-12-21 12:34:37,469> [INFO ] [JobExecuterWorker-1:DS_CA CMDB Population_012_Unix_servers] (?:?) - Result redundant filter (previous executions) done for trigger CI: 02d22d02bcf64cc8ca6f59546bf1d303 -  Total Objects number: 7-->7 DBStore true :7(7)->0(0)+7+0+0(0-0)+0(0-0) ( unix:7->7 DBStore:7->0+0(0)+0(0)  )Run way : full syn

<2021-12-21 12:55:23,517> [INFO ] [JobExecuterWorker-2:DS_CA CMDB Population_012_Unix_servers] (?:?) - DS_CA CMDB Population_012_Unix_servers: Last Successfull run time NEVER
<2021-12-21 12:55:24,487> [INFO ] [JobExecuterWorker-2:DS_CA CMDB Population_012_Unix_servers] (?:?) - DS[CA CMDB Population]: query[UnixServersFromCACMDB] Topology For add[136]  For update[0]  For remove[0]
<2021-12-21 12:55:24,581> [ERROR] [JobExecuterWorker-2:DS_CA CMDB Population_012_Unix_servers] (?:?) - Failed running Datastore: CA CMDB Population
com.mercury.topaz.cmdb.shared.base.CmdbException: [ErrorCode [802] General Integration Error{CA CMDB Population}]
appilog.framework.shared.manage.impl.MamResponseException: [ErrorCode [802] General Integration Error{CA CMDB Population}]
CMDB Operation Internal Error: class com.mercury.topaz.cmdb.shared.fcmdb.dataAccess.exception.AdapterAccessGeneralException : Error performing command: [FcmdbDalTransactionComplexCommand]. Command ID [1536606562], due to exception: com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.exception.FcmdbDalException: [ErrorCode [-2147483648] undefined error code]
com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.exception.FcmdbDalException: [ErrorCode [-2147483648] undefined error code]
Error performing command: [FcmdbDalGetObjectsLayoutWithTempTableComplexCommand]. Command ID [864362708], due to exception: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query : operation DataAccess Query: Retrieve Layout From Changes Source Adapter operation
    at com.mercury.topaz.cmdb.shared.manage.operation.impl.AbstractCommonOperation.execute(AbstractCommonOperation.java:161)
    at com.hp.ucmdb.dataAccess.manager.DataAccessAdapterManagerProbeImpl.executeOperation(DataAccessAdapterManagerProbeImpl.java:50)
    at com.hp.ucmdb.discovery.probe.agents.probemgr.adapters.DataAccessAdaptersFacade.invokeOperation(DataAccessAdaptersFacade.java:396)
    at com.hp.ucmdb.discovery.probe.services.dynamic.core.AdapterService.runQueriesFullOnChangesAdapter(AdapterService.java:1155)
    at com.hp.ucmdb.discovery.probe.services.dynamic.core.AdapterService.runQueries(AdapterService.java:416)
    at com.hp.ucmdb.discovery.probe.services.dynamic.core.AdapterService.runDiscovery(AdapterService.java:224)
    at com.hp.ucmdb.discovery.probe.services.dynamic.core.AdapterService.discover(AdapterService.java:168)
    at com.hp.ucmdb.discovery.probe.agents.probemgr.taskexecuter.JobExecuter.launchTask(JobExecuter.java:1338)
    at com.hp.ucmdb.discovery.probe.agents.probemgr.taskexecuter.JobExecuter$JobExecuterWorker.getInvocationStatus(JobExecuter.java:1085)
    at com.hp.ucmdb.discovery.probe.agents.probemgr.taskexecuter.JobExecuter$JobExecuterWorker.launchAndIsProbeRestarting(JobExecuter.java:1037)
    at com.hp.ucmdb.discovery.probe.agents.probemgr.taskexecuter.JobExecuter$JobExecuterWorker.executeTask(JobExecuter.java:952)
    at com.hp.ucmdb.discovery.probe.agents.probemgr.taskexecuter.JobExecuter$JobExecuterWorker.run(JobExecuter.java:797)
Caused by: appilog.framework.shared.manage.impl.MamResponseException: [ErrorCode [802] General Integration Error{CA CMDB Population}]
CMDB Operation Internal Error: class com.mercury.topaz.cmdb.shared.fcmdb.dataAccess.exception.AdapterAccessGeneralException : Error performing command: [FcmdbDalTransactionComplexCommand]. Command ID [1536606562], due to exception: com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.exception.FcmdbDalException: [ErrorCode [-2147483648] undefined error code]
com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.exception.FcmdbDalException: [ErrorCode [-2147483648] undefined error code]
Error performing command: [FcmdbDalGetObjectsLayoutWithTempTableComplexCommand]. Command ID [864362708], due to exception: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query : operation DataAccess Query: Retrieve Layout From Changes Source Adapter operation
    at com.mercury.topaz.cmdb.shared.manage.operation.impl.AbstractCommonOperation$OperationExecuteFlowTrackingCommand.execute(AbstractCommonOperation.java:115)
    at com.mercury.topaz.cmdb.shared.manage.operation.impl.AbstractCommonOperation$OperationExecuteFlowTrackingCommand.execute(AbstractCommonOperation.java:61)
    at com.mercury.topaz.cmdb.shared.manage.flowmanagement.api.FlowManager.execute(FlowManager.java:227)
    at com.mercury.topaz.cmdb.shared.manage.operation.flow.OperationInFlowDefaultExecutor.execute(OperationInFlowDefaultExecutor.java:23)
    at com.mercury.topaz.cmdb.shared.manage.operation.impl.AbstractCommonOperation.execute(AbstractCommonOperation.java:159)
    ... 11 more
Caused by: com.mercury.topaz.cmdb.shared.fcmdb.dataAccess.exception.AdapterAccessGeneralException: [ErrorCode [802] General Integration Error{CA CMDB Population}]
Error performing command: [FcmdbDalTransactionComplexCommand]. Command ID [1536606562], due to exception: com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.exception.FcmdbDalException: [ErrorCode [-2147483648] undefined error code]
com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.exception.FcmdbDalException: [ErrorCode [-2147483648] undefined error code]
Error performing command: [FcmdbDalGetObjectsLayoutWithTempTableComplexCommand]. Command ID [864362708], due to exception: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
    at com.hp.ucmdb.dataAccess.exceptions.ExceptionConverter.createAdapterAccessGeneralException(ExceptionConverter.java:341)
    at com.hp.ucmdb.dataAccess.exceptions.ExceptionConverter.createAdapterAccessException(ExceptionConverter.java:175)
    at com.hp.ucmdb.dataAccess.exceptions.ExceptionConverter.createAdapterAccessException(ExceptionConverter.java:67)
    at com.hp.ucmdb.dataAccess.operations.AbstractDataAccessAdapterOperation.createAdapterAccessException(AbstractDataAccessAdapterOperation.java:54)
    at com.hp.ucmdb.dataAccess.operations.AbstractDataAccessAdapterOperation.doExecute(AbstractDataAccessAdapterOperation.java:39)
    at com.mercury.topaz.cmdb.shared.manage.operation.impl.AbstractFrameworkOperation.commonExecute(AbstractFrameworkOperation.java:17)
    at com.mercury.topaz.cmdb.shared.manage.operation.impl.AbstractCommonOperation$OperationExecuteFlowTrackingCommand.execute(AbstractCommonOperation.java:88)
    ... 15 more
Caused by: com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.exception.FcmdbDalException: [ErrorCode [-2147483648] undefined error code]
Error performing command: [FcmdbDalTransactionComplexCommand]. Command ID [1536606562], due to exception: com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.exception.FcmdbDalException: [ErrorCode [-2147483648] undefined error code]
com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.exception.FcmdbDalException: [ErrorCode [-2147483648] undefined error code]
Error performing command: [FcmdbDalGetObjectsLayoutWithTempTableComplexCommand]. Command ID [864362708], due to exception: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.DBAdapter.getLayout(DBAdapter.java:1238)
    at com.hp.ucmdb.dataAccess.operations.DataAccessAdapterQueryRetrieveLayoutFromChangesSource.doDataAccessQueryExecute(DataAccessAdapterQueryRetrieveLayoutFromChangesSource.java:36)
    at com.hp.ucmdb.dataAccess.operations.AbstractDataAccessLifeCycleAdapterQuery.doLifeCycleExecute(AbstractDataAccessLifeCycleAdapterQuery.java:34)
    at com.hp.ucmdb.dataAccess.operations.AbstractDataAccessLifeCycleAdapterOperation.doDataAccessExecute(AbstractDataAccessLifeCycleAdapterOperation.java:58)
    at com.hp.ucmdb.dataAccess.operations.AbstractDataAccessAdapterOperation.dataAccessExecute(AbstractDataAccessAdapterOperation.java:59)
    at com.hp.ucmdb.dataAccess.operations.AbstractDataAccessAdapterOperation.doExecute(AbstractDataAccessAdapterOperation.java:37)
    ... 17 more
Caused by: com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.exception.FcmdbDalException: [ErrorCode [-2147483648] undefined error code]
com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.exception.FcmdbDalException: [ErrorCode [-2147483648] undefined error code]
Error performing command: [FcmdbDalGetObjectsLayoutWithTempTableComplexCommand]. Command ID [864362708], due to exception: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.impl.FcmdbDalTransactionComplexCommand.perform(FcmdbDalTransactionComplexCommand.java:60)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.impl.FcmdbDalAbstractCommand.execute(FcmdbDalAbstractCommand.java:101)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.FcmdbDalAbstractDAO$FCmdbDalRetriable.performAction(FcmdbDalAbstractDAO.java:151)
    at com.mercury.topaz.cmdb.shared.util.retry.impl.DefaultRetriableExecutorImpl.doExecute(DefaultRetriableExecutorImpl.java:37)
    at com.mercury.topaz.cmdb.shared.util.retry.impl.AbstractRetriableExecutor.execute(AbstractRetriableExecutor.java:37)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.FcmdbDalAbstractDAO.executeQuery(FcmdbDalAbstractDAO.java:73)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.FcmdbDalAbstractDAO.executeQuery(FcmdbDalAbstractDAO.java:65)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.FcmdbDalJPADAO.getCisLayout(FcmdbDalJPADAO.java:244)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.DBAdapter.getInternalCisLayout(DBAdapter.java:819)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.DBAdapter.getCisLayout(DBAdapter.java:785)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.DBAdapter.getLayout(DBAdapter.java:843)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.DBAdapter$InternalReplicationPlugin.getLayout(DBAdapter.java:1443)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.DBAdapter.getLayout(DBAdapter.java:1231)
    ... 22 more
Caused by: com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.exception.FcmdbDalException: [ErrorCode [-2147483648] undefined error code]
Error performing command: [FcmdbDalGetObjectsLayoutWithTempTableComplexCommand]. Command ID [864362708], due to exception: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.impl.FcmdbDalAbstractCommand.execute(FcmdbDalAbstractCommand.java:112)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.impl.FcmdbDalTransactionComplexCommand.executeCommands(FcmdbDalTransactionComplexCommand.java:93)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.impl.FcmdbDalTransactionComplexCommand.perform(FcmdbDalTransactionComplexCommand.java:53)
    ... 34 more
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1214)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1147)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:255)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.impl.FcmdbDalAbstractCommand.executeQuery(FcmdbDalAbstractCommand.java:207)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.datamodel.impl.FcmdbDalGetObjectsLayoutWithTempTableComplexCommand.executeQuery(FcmdbDalGetObjectsLayoutWithTempTableComplexCommand.java:91)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.datamodel.impl.FcmdbDalGetLayoutWithTempTableComplexCommand.access$400(FcmdbDalGetLayoutWithTempTableComplexCommand.java:64)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.datamodel.impl.FcmdbDalGetLayoutWithTempTableComplexCommand$HqlToSqlWork.retrieveLayout(FcmdbDalGetLayoutWithTempTableComplexCommand.java:276)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.datamodel.impl.FcmdbDalGetLayoutWithTempTableComplexCommand$HqlToSqlWork.execute(FcmdbDalGetLayoutWithTempTableComplexCommand.java:254)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.util.DBAdapterAlternateHibernateExecuter.executerWork(DBAdapterAlternateHibernateExecuter.java:31)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.datamodel.impl.FcmdbDalGetLayoutWithTempTableComplexCommand.createTempTableAndUpdateSqlString(FcmdbDalGetLayoutWithTempTableComplexCommand.java:212)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.datamodel.impl.FcmdbDalGetLayoutWithTempTableComplexCommand.getLayoutUsingTempTable(FcmdbDalGetLayoutWithTempTableComplexCommand.java:200)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.datamodel.impl.FcmdbDalGetLayoutWithTempTableComplexCommand.getLayout(FcmdbDalGetLayoutWithTempTableComplexCommand.java:173)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.datamodel.impl.FcmdbDalGetLayoutWithTempTableComplexCommand.getTypeLayout(FcmdbDalGetLayoutWithTempTableComplexCommand.java:162)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.datamodel.impl.FcmdbDalGetLayoutWithTempTableComplexCommand.getElementsLayout(FcmdbDalGetLayoutWithTempTableComplexCommand.java:121)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.datamodel.impl.FcmdbDalGetLayoutWithTempTableComplexCommand.perform(FcmdbDalGetLayoutWithTempTableComplexCommand.java:102)
    at com.mercury.topaz.fcmdb.adapters.dbAdapter.dal.command.impl.FcmdbDalAbstractCommand.execute(FcmdbDalAbstractCommand.java:101)
    ... 36 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.loader.Loader.doList(Loader.java:2536)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
    at org.hibernate.loader.Loader.list(Loader.java:2271)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
    at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)
    at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
    at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:246)
    ... 49 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "Czech_CI_AS" and "SQL_Latin1_General_CP1250_CI_AS" in the equal to operation.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:600)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7225)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3053)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:444)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
    at org.hibernate.loader.Loader.doQuery(Loader.java:802)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
    at org.hibernate.loader.Loader.doList(Loader.java:2533)
    ... 56 more

Parents
  • Hello,

     

    If the collations on DB side were the same when the 2019 version was running  than it could be related the database driver was change in between the mentioned versions.

    I would suggest to submit a support ticket.

    Additionally you can check if the tables used have multiple collations:

    SELECT
        col.name, col.collation_name
    FROM 
        sys.columns col
    WHERE
        object_id = OBJECT_ID('YourTableName')

    The quick workaround would be to change your collation in external DB.
    Hope it helps.

    Best Regards,
  • Yes you're true, looks like database driver issue. External DB from that we are pull data has different collation then is default collation of the their MSSQL instance. I have already opened the case but with no solution at the moment. 

Reply Children
  • Hello,

    There can be more solutions. For the final and long term solution you will have to wait for the resolution of your ticket.

    If I recall correctly, in GDBA orm.xml file user can add the select statement as well instead of the table name.

    As specified here you could add the following condition:

    "WHERE fieldname COLLATE DATABASE_DEFAULT = otherfieldname COLLATE DATABASE_DEFAULT

    "

    Hope it helps.

    Best Regards,

  • Hello,

    There can be more solutions. For the final and long term solution you will have to wait for the resolution of your ticket.

    If I recall correctly, in GDBA orm.xml file user can add the select statement as well instead of the table name.

    As specified here you could add the following condition:

    "WHERE fieldname COLLATE DATABASE_DEFAULT = otherfieldname COLLATE DATABASE_DEFAULT

    "

    Hope it helps.

    Best Regards,