用于HCL连接6.5的TDI由于“错误的SQL语法[]；"而失败.错误 [英] TDI for HCL Connections 6.5 synchronization fails with "bad SQL grammar [];" error

问题描述

我正在使用Tivoli Directory Integrator(TDI)将用户从Domino LDAP同步到HCL Connections的本地DB2人员数据库.在测试安装中，尝试最初同步用户时出现以下错误:

I'm using Tivoli Directory Integrator (TDI) to sync users from Domino LDAP to the local DB2 people database of HCL Connections. On a test installation, I got the following error when trying to initially sync the users:

[root@cnx65 tdisol]# LANG=en_US.utf8 ./sync_all_dns.sh 
create synchronization lock
log4j:WARN No appenders could be found for logger (server).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
**********
CLFRN1275I: Begin to hash records in database.
CLFRN1269I: Finish hash records in database.
**********
"message": "CLFRN1254E: An error occurred while performing findEntry: {0}."
"exception": "com.ibm.lconn.profiles.api.tdi.service.TDIException: CLFRN1254E: An error occurred while performing findEntry: {0}."
Synchronize of Database Repository failed

HCL文档建议检查日志，以防万一 CLFRN1254E 的代码.文件 logs/SyncUpdates.log 包含以下异常:

HCLs documentation recommend to check the logs in case of CLFRN1254E. The file logs/SyncUpdates.log contains the following exception:

2020-01-21 07:50:03,803 INFO  [org.apache.log4j.DailyRollingFileAppender.7431103d-4d0a-4d63-bdb7-61e274f23ed4] - CTGDIS092I Use entry provided at runtime as work entry (first pass only).
2020-01-21 07:50:11,723 ERROR [org.apache.log4j.DailyRollingFileAppender.7431103d-4d0a-4d63-bdb7-61e274f23ed4] - [hash_db_entries] CTGDIS181E Error while evaluating the hook 'Function error' in the component 'hash_db_entries (hash_db_entries.functioncall_fail).
com.ibm.lconn.profiles.api.tdi.service.TDIException: CLFRN1254E: An error occurred while executing findEntry: {0}.
        at com.ibm.lconn.profiles.api.tdi.connectors.ProfileConnector$ProfileCodeBlock.handleRecoverable(ProfileConnector.java:1063)
        at com.ibm.lconn.profiles.api.tdi.connectors.Util.TDICodeRunner.run(TDICodeRunner.java:41)
        at com.ibm.lconn.profiles.api.tdi.connectors.ProfileConnector.getNextEntry(ProfileConnector.java:155)
        at com.ibm.di.server.AssemblyLineComponent.executeOperation(AssemblyLineComponent.java:3370)
        at com.ibm.di.server.AssemblyLineComponent.getnext(AssemblyLineComponent.java:932)
        at com.ibm.di.server.AssemblyLine.msGetNextIteratorEntry(AssemblyLine.java:3689)
        at com.ibm.di.server.AssemblyLine.executeMainStep(AssemblyLine.java:3388)
        at com.ibm.di.server.AssemblyLine.executeMainLoop(AssemblyLine.java:3000)
        at com.ibm.di.server.AssemblyLine.executeMainLoop(AssemblyLine.java:2983)
        at com.ibm.di.server.AssemblyLine.executeAL(AssemblyLine.java:2952)
        at com.ibm.di.server.AssemblyLine.run(AssemblyLine.java:1319)
Caused by: org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the TDIProfile.get-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -551, SQLSTATE: 42501, SQLERRMC: LCUSER;SELECT;EMPINST.EMPLOYEE
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:212)
        at org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult(SqlMapClientTemplate.java:249)
        at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapClientTemplate.java:296)
        at com.ibm.lconn.profiles.internal.service.store.sqlmapdao.TDIProfileSqlMapDao.get(TDIProfileSqlMapDao.java:50)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:88)

可能是什么问题?我如何找到更多信息为什么会发生此错误?

在 profiles_tdi.properties 中，我为每个组件启用了调试日志:

In profiles_tdi.properties I enabled debug logs for every component:

debug_collect=true
debug_draft=true
debug_fill_codes=true
debug_managers=true
debug_photos=true
debug_pronounce=true
debug_special=true
debug_update_profile=true
trace_profile_tdi_javascript=on

因为这没有效果，所以我在 etc/log4j.properties 中将log4j级别设置为调试整个应用程序:

Since this had no effect, I set the log4j level to debug for the entire application in etc/log4j.properties:

log3j.rootCategory=DEBUG, Default

还尝试了 ALL 而不是 DEBUG .但是，输出没有变化.我希望看到导致异常的SQL查询.

Also tried ALL instead of DEBUG. However, there is no change in the output. I expected to see the SQL query, which caused the exception.

根据该帖子， mode 属性将用于确定用户是内部用户还是外部用户.由于示例配置显示

According to this post, the mode attribute will be used to decide if an user is internal or external. Since the example config says

Actually, any string other than "external" is interpreted as employee.

它设置为 mode = memberType .还尝试了 mode = uid 和 mode = mail .两者都是包含不等于"external"的字符串的字段，因此这将导致所有成员作为内部用户导入.

it is set to mode=memberType. Also tried mode=uid and mode=mail. Both are fields containing a string not equal to "external", so this should result in all members imported as internal users.

由于我的LDAP过滤器适用于大约60个用户，因此我成功运行了 ./collect_dns.sh ，并从我自己的 collect.dns 文件中删除了所有用户.然后将dn文件中的用户与 ./populate_from_dn_file.sh 同步.这是针对其他两个用户执行的，始终导致相同的错误:

Since my LDAP filter applies to around 60 users, I ran ./collect_dns.sh successfully and removed all users from collect.dns file except my own. Then sync the user from the dn file with ./populate_from_dn_file.sh. This was done for two other users, resulting always in the same error:

CLFRN0027I: After operation, success records is 0, duplicate records 0, failure records is 1, and last successful entry is null.
CLFRN1280I: 20200121105123 Iterations total number: 1.

唯一的区别是 logs/PopulateDBFromDNFile.log 包含有关获取的属性，映射等的更多详细信息.不幸的是，它在错误方面并没有真正帮助我，因为它会产生类似的消息:

The only difference is that logs/PopulateDBFromDNFile.log contains more detailled information about the fetched attributes, mappings and so on. Unfortunately, it doesn't really help me in terms of the error, since it produces a similiar message:

2020-01-21 10:55:27,530 INFO  [com.ibm.di.log.FileRollerAppender.268b5e1d-d0fc-4a7c-9e12-4d742c44faa5] - [callSyncDB_mod] [add_manager_data] [setup_if_lookup] CTGDIS126I Return false.
2020-01-21 10:55:27,530 INFO  [com.ibm.di.log.FileRollerAppender.268b5e1d-d0fc-4a7c-9e12-4d742c44faa5] - [callSyncDB_mod] [add_manager_data] [setup_if_lookup] CTGDIS123I Returned object class java.lang.Boolean.
2020-01-21 10:55:27,530 INFO  [com.ibm.di.log.FileRollerAppender.268b5e1d-d0fc-4a7c-9e12-4d742c44faa5] - [callSyncDB_mod] [add_manager_data] CTGDIS075I Trying to exit TaskCallBlock.
2020-01-21 10:55:27,531 INFO  [com.ibm.di.log.FileRollerAppender.268b5e1d-d0fc-4a7c-9e12-4d742c44faa5] - [callSyncDB_mod] [add_manager_data] CTGDIS076I Succeeded exiting TaskCallBlock.
2020-01-21 10:55:27,531 INFO  [com.ibm.di.log.FileRollerAppender.268b5e1d-d0fc-4a7c-9e12-4d742c44faa5] - [callSyncDB_mod] [add_manager_data] CTGDIS057I Hook after_functioncall not enabled.
2020-01-21 10:55:27,531 INFO  [com.ibm.di.log.FileRollerAppender.268b5e1d-d0fc-4a7c-9e12-4d742c44faa5] - [callSyncDB_mod] CTGDIS352I Use null Behavior for $_already_lookup_manager.
2020-01-21 10:55:27,531 INFO  [com.ibm.di.log.FileRollerAppender.268b5e1d-d0fc-4a7c-9e12-4d742c44faa5] - [callSyncDB_mod] CTGDIS351I Map Attribute $manager_uid [1].
2020-01-21 10:55:27,531 INFO  [com.ibm.di.log.FileRollerAppender.268b5e1d-d0fc-4a7c-9e12-4d742c44faa5] - [callSyncDB_mod] CTGDIS353I Script is: conn["$manager_uid"]
2020-01-21 10:55:27,531 INFO  [com.ibm.di.log.FileRollerAppender.268b5e1d-d0fc-4a7c-9e12-4d742c44faa5] - [callSyncDB_mod] CTGDIS352I Use null Behavior for $manager_uid.
2020-01-21 10:55:27,531 INFO  [com.ibm.di.log.FileRollerAppender.268b5e1d-d0fc-4a7c-9e12-4d742c44faa5] - [callSyncDB_mod] [add_manager_data] CTGDIS057I Hook functioncall_ok not enabled.
2020-01-21 10:55:27,531 INFO  [com.ibm.di.log.FileRollerAppender.268b5e1d-d0fc-4a7c-9e12-4d742c44faa5] - [callSyncDB_mod] [add_manager_data] CTGDIS057I Hook default_ok not enabled.
2020-01-21 10:55:27,538 INFO  [com.ibm.di.log.FileRollerAppender.268b5e1d-d0fc-4a7c-9e12-4d742c44faa5] - [callSyncDB_mod] Result: <My Name of the User in dn file>
2020-01-21 10:55:27,591 ERROR [com.ibm.di.log.FileRollerAppender.268b5e1d-d0fc-4a7c-9e12-4d742c44faa5] - [callSyncDB_mod] [ProfileConnector] SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the TDIProfile.get-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -551, SQLSTATE: 42501, SQLERRMC: LCUSER;SELECT;EMPINST.EMPLOYEE

推荐答案

发现这是我的不幸的逻辑错误.数据库是使用连接安装向导附带的sql文件创建的.我自动将它们循环导入.由于速度非常慢(所有脚本大约需要30分钟)，因此我尝试通过在命令末尾添加& 并最终在末尾 wait 对其进行并行化确保所有脚本均已执行.

Found out that this was a unlucky logical mistake from me. The database is created using sql files, shipped with the Connections Installation Wizard. I automatically import them in a loop. Since it was very slow (about 30 min for all scripts), I tried to parallelize them by adding a & at the end of the command and finally wait at the end to make sure all scripts were executed.

- name: Check and create non existing DBs for CNX
  become: yes
  become_user: "{{ db2.instance.name }}"
  shell: |
    db={{ item.name }}
    scripts=({{ item.files | join(' ') }})
    existing_dbs=$(echo -e '{{ existing_dbs.stdout }}')
    echo "Check db ${db}"

    if ! echo ${existing_dbs} | grep -q ${db}; then
      echo "DB ${db} doesn't exist, execute scripts"
      for script in "${scripts[@]}"
      do
        echo "${db}: Execute script ${script}"
        {{ db2.target }}/bin/db2 -td@ -f {{ cnx_sql_dir }}/${script} &
      done
      wait
    fi
  register: db_check
  changed_when: "'execute scripts' in db_check.stdout"
  loop: "{{ cnx.db_scripts }}"

cnx.db_scripts 是数据库名称到SQL文件的映射:

cnx.db_scripts is a mapping of database names to SQL files:

  db_scripts:
    - name: PEOPLEDB
      files:
        - profiles/db2/createDb.sql
        - profiles/db2/appGrants.sql
    - name: FORUM
      files: 
         # - ...

回想起来，这是一个可怕的逻辑错误，因为我错过了那些脚本相互依赖的事实:当 profiles/db2/appGrants.sql 在 profiles/db2/之前执行时createDb.sql 已完成，由于该数据库不存在，因此无法正常工作.

In retrospect, this was a terrible logical mistake because I missed the fact that those scripts rely on each other: When profiles/db2/appGrants.sql is executed before profiles/db2/createDb.sql was finished, it wouldn't work because the db doesn't exists.

结果，TDI查询失败，因为仅部分创建了数据库和表.我没有立即注意到这一点，因为在Ansible剧本开发过程中多次重新部署了该机器.奇怪的是，TDI仅在10个部署中的2个失败.好像DB2排队一样，并根据时间安排，在某些运行中成功创建了TDI所需的人员数据库.

As a result, TDIs queries failed because the database and tables were only partly created. I didn't notice this immediately, since the machine was several re-deployed during of the Ansible playbook development. Strangely, TDI only failed in 2 of 10 deployments. Seems like DB2 make some kind of queue and depending on the timing, the people database required for TDI is created successfully on some runs.

这篇关于用于HCL连接6.5的TDI由于“错误的SQL语法[]；"而失败.错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！