jira，wiki批量修改登录用户名

前言

jira对接统一单点登录后，因为cas的登录账号和jira原来的登录账号不一致，所以通过cas登录后，jira中根据登录账号获取用户信息时也会获取失败，而不会登录到jira系统中。所以需要保证jira账号和cas账号的一致，crowd后台可以直接修改登录账号，但没法批量修改，只能单条一一修改，工作量比较大，所以现在想直接从库里面统一修改jira的登录账号从英文名修改成邮箱。

1、修改jira日志级别

1.1 观察数据同步日志

首先我找到了crowd的用户表是cwd_user表，里面user_name，lower_user_name记录的就是登录账号，同时jira库中cwd_user表信息是crowd cwd_user表同步过来的用户信息。但我单独修改了crowd库中cwd_user表的user_name,lower_user字段信息后，手动登录jira后台执行同步程序后，jira cwd_user表的信息没有同步过来，在Jira后台也无法修改这个用户信息，所以我恢复了原始数据。
然后我打开jira的sql 日志，想通过观察日志来查看jira同步时都修改了哪些表，jira日志目录如下：
/opt/atlassian/jira/atlassian-jira/WEB-INF/classes/log4j.properties
设置LoggingSQLInterceptor = ON
LoggingSQLInterceptor =true

#####################################################
# SQL logs
#####################################################
#
# Beware of turning this log level on.  At INFO level it will log every SQL statement
# and at DEBUG level it will also log the calling stack trace.  Turning this on will DEGRADE your
# JIRA database throughput.
#
log4j.logger.com.atlassian.jira.ofbiz.LoggingSQLInterceptor = ON, sqllog
log4j.additivity.com.atlassian.jira.ofbiz.LoggingSQLInterceptor = true

打开日志后，我在crowd后台修改了登录名，然后 执行同步程序，观察如下：
中文部分是我自己的注释

开始扫描删除的用户， 扫描新增或更新的用户：

2019-04-03 16:30:05,868 atlassian-scheduler-quartz1.clustered_Worker-2 INFO      [atlassian.jira.ofbiz.LoggingSQLInterceptor] 1ms Connection returned. borrowed : 0
2019-04-03 16:30:05,869 atlassian-scheduler-quartz1.clustered_Worker-2 INFO      [atlassian.crowd.directory.DbCachingRemoteChangeOperations] deleted [ 13 ] users in [ 83ms ]
2019-04-03 16:30:05,869 atlassian-scheduler-quartz1.clustered_Worker-2 INFO      [atlassian.crowd.directory.DbCachingRemoteChangeOperations] scanned for deleted users in [ 101ms ]
2019-04-03 16:30:05,870 atlassian-scheduler-quartz1.clustered_Worker-2 INFO      [atlassian.crowd.directory.DbCachingRemoteChangeOperations] scanning [ 1066 ] users to add or update

#扫描并比较：
2019-04-03 16:30:05,872 atlassian-scheduler-quartz1.clustered_Worker-2 INFO      [atlassian.crowd.directory.DirectoryCacheImplUsingChangeOperations] scanned and compared [ 1066 ] users for update in DB cache in [ 3ms ]


#根据crowd  cwd_user表的external_id 查 jira crowd表里面的唯一数据： 修改前的值
SELECT ID, directory_id, user_name, active, created_date, updated_date, first_name, last_name, display_name, email_address, EXTERNAL_ID FROM cwd_user WHERE directory_id='10100' AND EXTERNAL_ID='11111:aed4a01f-we2wsw-w2-ddd-ee2e2'

//根据要修改的lower_user_name 查app_user
SELECT ID, user_key, lower_user_name FROM app_user WHERE lower_user_name='clock@company.com'

//根据修改前值 查cwd_user
SELECT ID, directory_id, user_name, lower_user_name, active, created_date, updated_date, first_name, lower_first_name, last_name, lower_last_name, display_name, lower_display_name, email_address, lower_email_address, CREDENTIAL, deleted_externally, EXTERNAL_ID FROM cwd_user WHERE directory_id='10100' AND lower_user_name='clock'


更新crw_user lower_user user_name 
UPDATE cwd_user SET directory_id='10100', user_name='clock@company.com', lower_user_name='clock@company.com', active='1', created_date='2016-12-02 17:49:10.0', updated_date='2019-04-03 16:30:05.874', first_name='clock', lower_first_name='clock', last_name='clock', lower_last_name='clock', display_name='clock clock', lower_display_name='clock clock', email_address='clock@company.com', lower_email_address='clock@company.com', CREDENTIAL='X', deleted_externally='null', EXTERNAL_ID='11111:aed4a01f-we2wsw-w2-ddd-ee2e2' WHERE ID='10821'



查询cwd_membership 的值，根据旧值查
SELECT ID, parent_id, child_id, membership_type, group_type, parent_name, lower_parent_name, child_name, lower_child_name, directory_id FROM cwd_membership WHERE membership_type='GROUP_USER' AND lower_child_name='clock' AND directory_id='10100'

删除旧值
"DELETE FROM cwd_membership WHERE membership_type='GROUP_USER' AND child_name='clock' AND directory_id='10100'

//验证旧值
SELECT lower_parent_name FROM cwd_membership WHERE (directory_id =  '10100' ) AND (lower_child_name =  'clock' ) AND (membership_type =  'GROUP_USER' )


验证新值

SELECT lower_parent_name FROM cwd_membership WHERE (directory_id =  '10100' ) AND (lower_child_name =  'clock@company.com' ) AND (membership_type =  'GROUP_USER' )

插入新lower_user 的membership值
INSERT INTO cwd_membership (ID, parent_id, child_id, membership_type, group_type, parent_name, lower_parent_name, child_name, lower_child_name, directory_id) VALUES ('15304', '10414', '10821', 'GROUP_USER', 'null', 'crowd-users', 'crowd-users', 'clock@company.com', 'clock@company.com', '10100')




SELECT lower_parent_name FROM cwd_membership WHERE (directory_id =  '10100' ) AND (lower_child_name =  'clock@company.com' ) AND (membership_type =  'GROUP_USER' )



INSERT INTO cwd_membership (ID, parent_id, child_id, membership_type, group_type, parent_name, lower_parent_name, child_name, lower_child_name, directory_id) VALUES ('15305', '10444', '10821', 'GROUP_USER', 'null', 'test-研发组', 'test-研发组', 'click@compayn.com', 'clock@company.com', '10100')





SELECT user_key FROM app_user WHERE lower_user_name='clock'



UPDATE app_user SET  lower_user_name = 'clock@company.com'  WHERE user_key='clock'


SELECT COUNT(*) FROM cwd_user WHERE lower_user_name='clock'


SELECT ID, directory_id, user_name, lower_user_name, active, created_date, updated_date, first_name, lower_first_name, last_name, lower_last_name, display_name, lower_display_name, email_address, lower_email_address, CREDENTIAL, deleted_externally, EXTERNAL_ID FROM cwd_user WHERE directory_id='10100' AND lower_user_name='clock@company.com'



UPDATE cwd_user SET directory_id='10100', user_name='clock@company.com', lower_user_name='clock@company.com', active='1', created_date='2016-12-02 17:49:10.0', updated_date='2019-04-03 16:30:05.874', first_name='clock', lower_first_name='clock', last_name='clock', lower_last_name='clock', display_name='clock clock', lower_display_name='clock clock', email_address='clock@company.com', lower_email_address='clock@company.com', CREDENTIAL='X', deleted_externally='null', EXTERNAL_ID='11111:aed4a01f-we2wsw-w2-ddd-ee2e2' WHERE ID='10821'


SELECT ID, user_key, lower_user_name FROM app_user WHERE lower_user_name='clock@company.com'


INSERT INTO audit_log (ID, REMOTE_ADDRESS, CREATED, AUTHOR_KEY, SUMMARY, CATEGORY, OBJECT_TYPE, OBJECT_ID, OBJECT_NAME, OBJECT_PARENT_ID, OBJECT_PARENT_NAME, AUTHOR_TYPE, EVENT_SOURCE_NAME, SEARCH_FIELD) VALUES ('24654', 'null', '2019-04-03 16:30:05.889', 'null', 'User updated', 'user management', 'USER', 'clock', 'clock@company.com', '10100', 'jira', '0', '', 'user updated management clock@company.com jira clock')



INSERT INTO audit_changed_value (ID, LOG_ID, NAME, DELTA_FROM, DELTA_TO) VALUES ('22202', '24654', 'Username', 'clock', 'clock@company.com')

从观察出的日志，大概可以看到jira从crowd库中同步时主要修改了3张表
cwd_user,cwd_membership,app_user。
其中cwd_membership是删除旧的关系，然后再新增新的关系。cwd_user,app_user是直接修改了user_name字段。

其中app_user可以看一下表结构，user_key,lower_user_name都是唯一索引，我们修改了lower_user_name，但user_key是不会更改的，可以推出jira其它工单信息表可能跟这个user_key是关联的。新增用户数据的时候user_key默认和lower_user_name一致，但如果我们手动修改一个user_key为将要新增的lower_user_name，那么user_key会随机生一个字符编码。

我也照上面那样修改了这3张表的数据,刷新jira后台用户管理页面后，任然不起作用，登出重新登录后也不生效。

1.1 观察数据查询日志

在修改库不生效后，我又观察jira后台根据关键字查询用户的日志，查询输入的关键字（test_search）去查找，并没有查找到内容，也没有执行sql，如果用有的用户名 关键字可以看到会执行sql，下面是没有匹配到内容的日志：

        ...
        at com.atlassian.jira.user.OfBizUserHistoryStore.getSortedHistory(OfBizUserHistoryStore.java:234)
        at com.atlassian.jira.user.OfBizUserHistoryStore.getHistory(OfBizUserHistoryStore.java:220)
        at com.atlassian.jira.user.CachingUserHistoryStore$DelegatingStoreCacheLoader.load(CachingUserHistoryStore.java:302)
        at com.atlassian.jira.user.CachingUserHistoryStore$DelegatingStoreCacheLoader.load(CachingUserHistoryStore.java:298)
        at com.atlassian.cache.memory.MemoryCacheManager$3$1.load(MemoryCacheManager.java:132)
        at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3573)
        at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2350)
        at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2313)
        at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2228)
        at com.google.common.cache.LocalCache.get(LocalCache.java:3970)
        at com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3974)
        at com.google.common.cache.LocalCache$LocalManualCache.get(LocalCache.java:4834)
        at com.atlassian.cache.memory.DelegatingCache$DelegatingLoadingCache.get(DelegatingCache.java:264)
        at com.atlassian.jira.user.CachingUserHistoryStore.getHistory(CachingUserHistoryStore.java:222)
        at com.atlassian.jira.user.CachingUserHistoryStore.getHistory(CachingUserHistoryStore.java:245)
        at com.atlassian.jira.user.SessionBasedAnonymousUserHistoryStore.getHistory(SessionBasedAnonymousUserHistoryStore.java:235)
        at com.atlassian.jira.user.DefaultUserHistoryManager.getHistory(DefaultUserHistoryManager.java:90)
        at com.atlassian.jira.user.DefaultUserProjectHistoryManager.getCurrentProject(DefaultUserProjectHistoryManager.java:58)
        at com.atlassian.jira.plugin.navigation.TopNavigationModuleDescriptorImpl.getTopNavigationContext(TopNavigationModuleDescriptorImpl.java:139)
        at com.atlassian.jira.plugin.navigation.TopNavigationModuleDescriptorImpl.getTopNavigationHtml(TopNavigationModuleDescriptorImpl.java:94)
        at com.atlassian.jira.tzdetect.HeadsUp.getHtml(HeadsUp.java:61)
        at com.atlassian.jira.plugin.navigation.HeaderFooterRendering$8.visit(HeaderFooterRendering.java:313)
        at com.atlassian.jira.plugin.navigation.HeaderFooterRendering$8.visit(HeaderFooterRendering.java:303)
        at com.atlassian.ozymandias.SafePluginPointAccess.invokeModule(SafePluginPointAccess.java:452)
        at com.atlassian.ozymandias.SafePluginPointAccess.descriptors(SafePluginPointAccess.java:150)
        at com.atlassian.jira.plugin.navigation.HeaderFooterRendering.includeTopNavigation(HeaderFooterRendering.java:302)
        at com.atlassian.jira.plugin.navigation.HeaderFooterRendering$1.run(HeaderFooterRendering.java:105)
        at com.atlassian.ozymandias.SafePluginPointAccess.runnable(SafePluginPointAccess.java:201)
        at com.atlassian.jira.plugin.navigation.HeaderFooterRendering.includeTopNavigation(HeaderFooterRendering.java:100)
        at org.apache.jsp.decorators.admin_jsp._jspService(admin_jsp.java:666)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)

仔细查看com.atlassian.cache.memory.MemoryCacheManager$3$1.load(MemoryCacheManager.java:132)这一段，执行的是缓存方法，有部分用户数据是加载到缓存里的。

2、crowd库修改

1. 修改crowd库中cwd_user表的user_name,lower_user_name和cwd_membership中child_name,lower_child_name为新的登录用户名。
2. 重启jira服务，wiki服务器

重新登录jira后台，刷新页面，发现用户名修改成功了。
登录wiki后台，刷新页面，可以观察到用户登录名修改成功了。
后面就可以通过修改数据库来批量修改登录用户名了。

3、总结

3.1

jira的用户名是缓存到内存中的，所以直接修改库中用户名，然后刷新页面发现没有变化。修改完crowd库中cwd_user,cwd_membership数据后，需要重启jira。
其实在数据库同步的日志中也可以观察到，同步程序是描DB cache数据来同步数据的，所以直接修改库，无法将修改值同步到DB cache中，所以同步程序也无法取到修改值

[atlassian.jira.ofbiz.LoggingSQLInterceptor] 1ms Connection returned. borrowed : 0
[atlassian.crowd.directory.DbCachingRemoteChangeOperations] deleted [ 13 ] users in [ 83ms ]
[atlassian.crowd.directory.DbCachingRemoteChangeOperations] scanned for deleted users in [ 101ms ]
[atlassian.crowd.directory.DbCachingRemoteChangeOperations] scanning [ 1066 ] users to add or update

#扫描并比较：
[atlassian.crowd.directory.DirectoryCacheImplUsingChangeOperations] scanned and compared [ 1066 ] users for update in DB cache in [ 3ms ]

3.2 ticket not match supplied service或ticket not recognized

问题描述如下：
在jira和wiki对接完单点登录后，我尝试从crowd库中可以批量修改登录账号。更新完crowd表后，重启了jira，可以正常登录，但wiki登录不上了报错：
ticket not match supplied service或
ticket not recognized，
有部分人可以登录，有部分人不能登录报上面的错误
原因是统一单点登录的账号在wiki中不匹配，需要保证统一单点登录的账号在wiki中存在。
我的conflence对接cas时就出现了如下错误：

原始服务器地址和提供的服务地址只差了一个‘/’，刚开始以为是cas client本身的错误，后来检查发现，原来是之前手动改了crwod库账号，而wiki账号没同步，注意在修改crwod中的登录账号后，wiki系统也需要重启，账号才能同步。