JIRA常见配置与问题处理


Atlassian介绍

http://www.fangwai.net/software/

jira数据库创建库

CREATE DATABASE jiradb CHARACTER SET utf8 COLLATE utf8_bin;

==For MySQL 5.5, MySQL 5.6, and MySQL 5.7.0 to MySQL 5.7.5:==

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on <JIRADB>.* TO '<USERNAME>'@'<JIRA_SERVER_HOSTNAME>' IDENTIFIED BY '<PASSWORD>';
flush privileges;

==For MySQL 5.7.6 and above, you must also include the REFERENCES permission:==

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,ALTER,INDEX on <JIRADB>.* TO '<USERNAME>'@'<JIRA_SERVER_HOSTNAME>' IDENTIFIED BY '<PASSWORD>';
flush privileges;
SHOW GRANTS FOR <USERNAME>@<JIRA_SERVER_HOSTNAME>;

==my.cnf==

default-storage-engine=INNODB
max_allowed_packet=256M
innodb_log_file_size=256M
sql_mode = NO_AUTO_VALUE_ON_ZERO

==字符集检查==

Database
Collation(s)
How to Verify Table Collations
How to Verify Database Collations

MySQL 
* utf8_bin

SELECT DISTINCT C.collation_name, T.table_name
FROM   information_schema.tables AS T,
information_schema.`collation_character_set_applicability` AS C
WHERE  C.collation_name = T.table_collation
AND T.table_schema = DATABASE();

SELECT default_collation_name
FROM   information_schema.schemata S
WHERE  schema_name = (SELECT DATABASE()
FROM   DUAL);

==字符集修改==

Changing the Database Collation
Change yourDB to suit your database name:
ALTER DATABASE yourDB CHARACTER SET utf8 COLLATE utf8_bin
Changing Table Collation
The following query will produce a series of ALTER TABLE statements, which you must then run against your database. Change yourDB to suit your database name:


SELECT CONCAT('ALTER TABLE ',  table_name, ' CHARACTER SET utf8 COLLATE utf8_bin;')

FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C

WHERE C.collation_name = T.table_collation

AND T.table_schema = 'yourDB'

AND

(

    C.CHARACTER_SET_NAME != 'utf8'

    OR

    C.COLLATION_NAME != 'utf8_bin'

);
Changing Column Collation
The following queries (one for varchar columns, and one for non-varchar columns) will produce a series of ALTER TABLE statements, which you must then run against your database. ChangeyourDB to suit your database name:


SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET UTF8 COLLATE utf8_bin', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')

FROM information_schema.COLUMNS

WHERE TABLE_SCHEMA = 'yourDB'

AND DATA_TYPE = 'varchar'

AND

(

    CHARACTER_SET_NAME != 'utf8'

    OR

    COLLATION_NAME != 'utf8_bin'

);


SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, ' CHARACTER SET UTF8 COLLATE utf8_bin', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')

FROM information_schema.COLUMNS

WHERE TABLE_SCHEMA = 'yourDB'

AND DATA_TYPE != 'varchar'

AND

(

    CHARACTER_SET_NAME != 'utf8'

    OR

    COLLATION_NAME != 'utf8_bin'

);
Dealing with Foreign Key Constraints
It may be necessary to ignore foreign key constraints when making changes to a large number of columns. You can use the SET FOREIGN_KEY_CHECKS command to ignore foreign key constraints while you update the database.


SET FOREIGN_KEY_CHECKS=0;



-- Insert your other SQL Queries here...



SET FOREIGN_KEY_CHECKS=1;

==Proxying Atlassian server applications with Apache HTTP Server (modproxyhttp)==

###tomcat
server.xml
     proxyName="<subdomain>.<domain>.com"
proxyPort="80"
scheme="http"/>

###apache
<VirtualHost *:80>
    ServerName <subdomain>.<domain>.com

    ProxyRequests Off
    ProxyVia Off

    <Proxy *>
         Require all granted
    </Proxy>

    ProxyPass /<contextpath> http://<internal_domain>:<port>/<contextpath>
    ProxyPassReverse /<contextpath> http://<internal_domain>:<port>/<contextpath>
</VirtualHost>
RemoteIPHeader X-Forwarded-For

==邮件模板配置例子==

cd /opt/atlassian/jira/atlassian-jira/WEB-INF/classes/templates/email/html
vi issueclosed.vm
#disable_html_escaping()

#set ($resolution = "<strong>$textutils.htmlEncode(${issue.getResolutionObject().getNameTranslation($i18n)}, false)</strong>")
#defaultMailHeaderWithParam("email.event.activity.closed.issue", $changelogauthor, $resolution)

#parse('templates/email/html/includes/patterns/comment-top.vm')

#set ($issueTitleBody="#parse('templates/email/html/includes/patterns/issue-title.vm')")
#rowWrapperNormal($issueTitleBody)

#if ($changelog)
    #set ($changelogBody="#parse('templates/email/html/includes/fields/changelog.vm')")
    #rowWrapperNormal($changelogBody)
#end

#if ($issue.getCustomFieldValue("customfield_11001"))
$stringUtils.leftPad($issue.getCustomField("customfield_11001").name, $padSize): $issue.getCustomFieldValue("customfield_11001")
#end

#set ($commentActionBody="#parse('templates/email/html/includes/patterns/comment-action.vm')")
#rowWrapperNormal($commentActionBody)

#parse("templates/email/html/includes/footer.vm")



md5-7472e7c200643c966f1a786224e8b1bb



vi issueresolved.vm 
#disable_html_escaping()

#set ($resolution = "<strong>$textutils.htmlEncode(${issue.getResolutionObject().getNameTranslation($i18n)}, false)</strong>")
#defaultMailHeaderWithParam("jira.email.title.issue.resolved", $changelogauthor, $resolution)
#if ($comment)
    #parse('templates/email/html/includes/patterns/comment-top.vm')
#end

#rowWrapperNormal("#parse('templates/email/html/includes/patterns/issue-title.vm')")


#rowWrapperNormalBegin('' 'wrapper-special-margin')


md5-223bc6c188ad976816e7eeabd015e823


#rowWrapperNormalEnd()

#if ($changelog)
    #rowWrapperNormal("#parse('templates/email/html/includes/fields/changelog.vm')")
#end



#rowWrapperNormalBegin('' 'wrapper-special-margin')


md5-588599449e84ad2ef5b47be89a362f07


#rowWrapperNormalEnd()


#if ($issue.description)
    #set($textParagraph = $issue.htmlDescription)
    #rowWrapperNormal("#parse('templates/email/html/includes/patterns/text-paragraph.vm')", '', 'issue-description-container')
#end

#if ($issue.getCustomFieldValue("customfield_11001"))
$stringUtils.leftPad($issue.getCustomField("customfield_11001").name, $padSize): $issue.getCustomFieldValue("customfield_11001")
#end

#set ($commentActionBody="#parse('templates/email/html/includes/patterns/comment-action.vm')")
#rowWrapperNormal($commentActionBody)

#parse("templates/email/html/includes/footer.vm")