• State Not Answered
  • Replies replies
    4
  • Subscribers subscribers
    179
  • Views views
    378
  • Users 0 members are here
Related
Options

Generic DB adapter collation conflict issue

cavoki1

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

  • 0  

    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,
  • 0 in reply to GabiMedan1

    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. 

  • 0   in reply to cavoki1

    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,

  • 0   in reply to cavoki1

    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,

Resources

Support
Documentation
Learning Services
CyberRes Academy
Partner Programs
Contact us
Compliance
Help
Company
Privacy Policy
Terms of Use
Accessibility
Anti-Slavery Statement
Support
Contact Us
Careers
Code of Conduct
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.