JIRA migration: BugZilla multi-line desc/comments

BugZilla MySQL database

The longtext table contains each bug_id's associated multi-line desc and multi-line comments.

MySQL query to get bug_id and the text result

SELECT 

    bug_id
    , thetext
FROM
    bugs.longdescs as T1
where
    T1.bug_id in (SELECT 
           bug_id
        FROM
            bugs.bugs
        where
            product_id in (SELECT 
                    id
                FROM
                    bugs.products
                where
                    (name like 'MY_BUGZILLA_PROJECT')))
Order by T1.bug_id
For each of bug_ids associated multi-line thetext entries: 
First entry is the bug_id associated desc
Remaining entries (if existed) are the bug_id associated comments.

Migrate BugZilla multi-line desc & comments into JIRA

1. create a BugZilla query for JIRA migration without multi-line desc/comments
2. JIRA, Administration -> System -> Import & Export -> Import External projects -> CSV Import
3. create a MySQL query to only contains bug ids, and save the query result as XML format

SELECT 
    bug_id
FROM
    bugs.bugs
where
    product_id in (SELECT 
            id
        FROM
            bugs.products
        where
            (name like ' MY_BUGZILLA_PROJECT ')))
Order by bug_id
4. run a PERL script (i.e. xmlBugIdExportIntoJIRA.pl) through JIRA cli interface updating desc/comments on Linux platform from where BugZilla's desc/comments UTF-8 code issue will be solved)

NAME

    xmlBugIdExportIntoJira.pl

SYNOPSIS

  DESCRIPTION

    This script is going to migrate exported XML format from BugZilla MySQL with bug_id, thetext file into Jira.

  VERSION

    Version 1.0

  USAGE

       perl xmlBugIdExportIntoJira.pl --jira_project_key "[jira_project_key]" --jira_field_for_bug_id "[jira_field_for_bug_id]" --jira_field_for_bugzilla_thetext "[jira_field_for_bugzilla_thetext]" --exported_xml_format_bugzilla_mysql_id_description_file "[exported_xml_format_bugzilla_mysql_id_description_file]" --jira_cli "[jira_cli]"
    *   --jira_project_key "[jira_project_key]" - Jira Project Key. *Required*
    *   --jira_field_for_bug_id "[jira_field_for_bug_id]" - Jira field maps to bug_id field. *Required*
    *   --jira_field_for_bugzilla_thetext="[jira_field_for_bugzilla_thetext]" - Jira field maps to bug_id Description field. *Required*
    *   --exported_xml_format_bugzilla_mysql_id_description_file "[exported_xml_format_bugzilla_mysql_id_description_file]" - Fullpath of exported xml format BugZilla MySQL bug_id file. *Required*
    *   execute MySQL query SELECT bug_id FROM bugs.bugs where product_id=(SELECT id FROM bugs.products where name = "MY_BUGZILLA_PROJECT" and save the results into xml
        SELECT bug_id, thetext FROM bugs.longdescs as T1 where T1.bug_id = id Order By bug_when
    *   --jira_cli "[jira_cli]" - Full path of atlassian-cli. *Required*

  EXAMPLE

       1. Migrate exported XML format BugZilla bug_id, thetext file into Jira.
       perl /tmp/xmlBugIdExportIntoJira.pl --jira_project_key "MY_JIRA_PROJECT_KEY" --jira_field_for_bug_id "bug_id" --jira_field_for_bugzilla_thetext "description" --exported_xml_format_bugzilla_mysql_id_description_file /tmp/BugZilla_MySQL_id_longdescs.xml --jira_cli /opt/atlassian-cli-2.4.0/jira.sh

Enterprise Linux: CollabNet Subversion Edge

# get CollabNetSubversionEdge

Download CollabNetSubversionEdge-2.2.1_linux-x86_64.tar.gz from http://www.open.collab.net/downloads/subversion/

CollabNet Subversion Edge 2.2.1 (for Linux 64 bit)

   License: GNU Affero General Public License 3.0 (AGPLv3)

   Certified platforms: Red Hat Enterprise Linux 5.x

                        Red Hat Enterprise Linux 6.x

                        CentOS 5.x

                        SuSE Linux Enterprise 11.x

   NOTE: These are the platforms we formally certify when testing.  CollabNet

   Subversion Edge is known to work on virtually all Linux distributions and is

   informally tested on others such as Ubuntu and Fedora.

  

   Requirements

   * Java 1.6 JRE/JDK must be installed. We recommend the JRE provided by

     Sun/Oracle.  When testing on 64-bit Linux we have used the 64-bit JVM.

   * Python 2.4 to 2.6 must be installed.

# Java 1.6

[root@linux64-svn-server ~]# rpm -Uvh /tmp/ jdk-6u23-linux-amd64.rpm

[root@linux64-svn-server ~]# echo "export JAVA_HOME=/usr/java/jdk1.6.0_23" >> /etc/profile

[root@linux64-svn-server ~]# source /etc/profile

# Python version

[root@linux64-svn-server ~]# python –V
Python 2.4.3

# extract CollabNetSubversionEdge tar.gz using none root, sudo user

[root@linux64-svn-server ~]# useradd csvn

[root@linux64-svn-server ~]# su - csvn

[csvn@linux64-svn-server ~]$ chmod a+x /tmp/CollabNetSubversionEdge-2.2.1_linux-x86_64.tar.gz

[csvn@linux64-svn-server ~]$ tar zxf /tmp/CollabNetSubversionEdge-2.2.1_linux-x86_64.tar.gz

[csvn@linux64-svn-server ~]$ exit # as root now

[root@linux64-svn-server ~]# cp –pr /tmp/csvn /opt

[root@linux64-svn-server ~]# cd /opt/csvn/bin

[root@linux64-svn-server bin]# ./csvn install

Detected RHEL or Fedora:

Installing the CSVN Console daemon..

Setting RUN_AS_USER to: 'root'. Please edit '../data/conf/csvn.conf' if this needs to be adjusted

Setting JAVA_HOME to: '/usr/java/jdk1.6.0_23'. Please edit '../data/conf/csvn.conf' if this needs to be adjusted.

./csvn start

Starting CSVN Console......

CSVN Console started

Waiting for application to initialize (this may take a minute).................

CSVN Console is ready at http://localhost:3343/csvn

./csvn-httpd install

Detected RHEL or Fedora:

Installing the Subversion Edge Apache Server daemon..

./csvn-httpd start

Starting Subversion Edge Apache Server:                    [  OK  ]

# change port from 18080 to 443 or 80

[root@linux64-svn-server ~]# chown root:csvn /opt/csvn/lib/httpd_bind/httpd_bind

[root@linux64-svn-server ~]# chmod u+s /opt/csvn/lib/httpd_bind/httpd_bind

# make sure csvn has access permission for repositories

[root@linux64-svn-server ~]# chown –R csvn /opt/csvn/data/repositories

# http://localhost:4434/csvn

JIRA migration: ClearQuest multi-line description

Migrate ClearQuest defects multi-line description field into JIRA

1. Using ClearQuest client or ClearQuest web to create a ClearQuest query for those ClearQuest defects without desc field
2. From ClearQuest web exporting ClearQuest query results into excel format, and save as a csv file
3. JIRA, Administration -> System -> Import & Export -> Import External projects -> CSV Import
4. Duplicate the above ClearQuest query, and only display id and description fields
5. From ClearQuest Eclispe Client exporting ClearQuest query results as XML format

Use ClearQuest Client the one built on Eclipse

Within Eclipse ClearQuest Client workspace, select a query, which contains the display fields: id and multiline field (such as, Description),right click the query -> select Export Records… -> in Select fields diagram, click Next -> in Export tool settings diagram, select XML as Format, click Next -> in Specify file names diagram, click Finish

6. Run a PERL script (i.e. xmlClearQuestExportIntoJira.pl) through JIRA cli interface updating desc on Linux from where ClearQuest UTF-8 code issue will be eliminated)

NAME

    xmlClearQuestExportIntoJira.pl

SYNOPSIS

  DESCRIPTION

    This script is going to migrate exported XML format ClearQuest id, Description file into Jira.

  VERSION

    Version 1.0

  USAGE

       perl xmlClearQuestExportIntoJira.pl --jira_project_key "[jira_project_key]" --jira_field_for_clearquest_id "[jira_field_for_clearquest_id]" --jira_field_for_clearquest_description "[jira_field_for_clearquest_description]" --exported_xml_format_clearquest_id_description_file "[exported_xml_format_clearquest_id_description_file]" --jira_cli "[jira_cli]"
    *   --jira_project_key "[jira_project_key]" - Jira Project Key. *Required*
    *   --jira_field_for_clearquest_id "[jira_field_for_clearquest_id]" - Jira field maps to ClearQuest id field. *Required*
    *   --jira_field_for_clearquest_description "[jira_field_for_clearquest_description]" - Jira field maps to ClearQuest Description field. *Required*
    *   --exported_xml_format_clearquest_id_description_file "[exported_xml_format_clearquest_id_description_file]" - Full path of exported xml format ClearQuest id, Description file. *Required*
    *   --jira_cli "[jira_cli]" - Full path of atlassian-cli. *Required*

  EXAMPLE

       1. Migrate exported XML format ClearQuest id, Description file into Jira.
       perl /tmp/xmlClearQuestExportIntoJira.pl --jira_project_key "MY_JIRA_PROJECT_KEY" --jira_field_for_clearquest_id "id" --jira_field_for_clearquest_description "description" --exported_xml_format_clearquest_id_description_file /tmp/CQ_desciption_export.xml --jira_cli /opt/atlassian-cli-2.4.0/jira.sh

JIRA security: JIRA server runs over HTTPS

In order to encrypt JIRA login username & passsword instead of plain text in HTTP, configure JIRA Tomcat server (6.0) to run over HTTPS

1. Generate a private key

# login as root on JIRA Linux server.
[root@linux64-jira-server ~]#  id
uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel)
# su to jira running user
[jira@linux64-jira-server ~]$ su - jira
[jira@linux64-jira-server ~]$ $JAVA_HOME/bin/keytool -genkey -alias tomcat -keyalg RSA
Enter keystore password:
Re-enter new password:
What is your first and last name?
  [Unknown]:  linux64-jira-server.domain.com
What is the name of your organizational unit?
  [Unknown]:  my_unit
What is the name of your organization?
  [Unknown]:  my_org
What is the name of your City or Locality?
  [Unknown]:  my_city
What is the name of your State or Province?
  [Unknown]:  my_state
What is the two-letter country code for this unit?
  [Unknown]:  my_country
Is CN=linux64-jira-server.domain.com, OU=my_unit, O=my_org, L=my_city, ST=my_state, C=my_country correct?
  [no]:  y
Enter key password for <tomcat>
        (RETURN if same as keystore password):
# Make sure .keystore existence under JIRA running user (jira) home
[jira@linux64-jira-server ~]$ id
uid=501(jira) gid=501(jira) groups=501(jira) 
[jira@linux64-jira-server ~]$ ls -al ~/.keystore
-rw-rw-r--  1 jira jira 1362 Jun  5  2012 .keystore

2. Generate a CSR (Certificate Signing Request)

[jira@linux64-jira-server ~]$ $JAVA_HOME/bin/keytool -certreq -keyalg RSA -alias tomcat -file certreq.csr -keystore /home/jira/.keystore

3. Submit certreq.csr to a certificate authority

Submit the generated certreq.csr to Certificate Authority for a certificate chain (DER encoded or Base 64 encoded), save it as certnew.p7b

4. Import the signed certificate into local keystore

[jira@linux64-jira-server ~]$ $JAVA_HOME/bin/keytool -importcert -alias tomcat -keystore /home/jira/.keystore -file /home/jira/certnew.p7b

5. Configure HTTPS in Tomcat

Insert below session into server.xml (Default is /opt/atlassian/jira/conf/server.xml) before </service> tag
<Connector port="8443" maxHttpHeaderSize="8192" SSLEnabled="true"
maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
enableLookups="false" disableUploadTimeout="true" useBodyEncodingForURI="true"
acceptCount="100" scheme="https" secure="true"
clientAuth="false" sslProtocol="TLS" />

6. Enable 8443 port from JIRA server firewall

system-config-securitylevel

7. Redirecting certain pages to HTTPS

Insert below session into web.xml (Default is /opt/atlassian/jira/atlassian-jira/WEB-INF/web.xml) before </web-app> tag <security-constraint>
<security-constraint>
   <web-resource-collection>
      <web-resource-name>all-except-attachments</web-resource-name>
      <url-pattern>*.jsp</url-pattern>
      <url-pattern>*.jspa</url-pattern>
      <url-pattern>/browse/*</url-pattern>
   </web-resource-collection>
   <user-data-constraint>
      <transport-guarantee>CONFIDENTIAL</transport-guarantee>
   </user-data-constraint>
</security-constraint>

8. Debug JIRA Tomcat Server runs over HTTPS

[jira@linux64-jira-server ~]$ /opt/atlassian/jira/bin/start-jira.sh -fg

9. Restart JIRA Tomcat Server runs over HTTPS

[jira@linux64-jira-server ~]$  id
uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel)
[jira@linux64-jira-server ~]$ service jira stop
[jira@linux64-jira-server ~]$ service jira start

JIRA customization: email subject, description

Email notification subject with Status and Priority:

cd /opt/atlassian/jira/atlassian-jira/WEB-INF/classes/templates/email/subject

cat issueassigned.vm

$issue.statusObject.name $issue.priorityObject.name ($issue.key) $issue.summary

Customized Description:

Description field with template as below:

Symptoms :

--------------

Procedures :

--------------

               

Details:

---------

Steps:

cd /opt/atlassian/jira/atlassian-jira/WEB-INF/classes/templates/jira/issue/field

cp description-edit.vm org_bk_description-edit.vm

cat description-edit.vm

#customControlHeader ($action $field.id $i18n.getText($field.nameKey) $fieldLayoutItem.required $displayParameters $auiparams)

## setup some additional parameters

$!rendererParams.put("class", "long-field")

$!rendererParams.put("rows", "12")

$!rendererParams.put("wrap", "virtual")

#macro( setDescription )

Symptoms :

--------------

Procedures :

--------------

               

Details:

---------

#end

## let the renderer display the edit component

#if ($description=="" || !$description)

#set ($description = "#setDescription()")

#end

$rendererDescriptor.getEditVM($!description, $!issue.key, $!fieldLayoutItem.rendererType, $!field.id, $field.name, $rendererParams, false)

#customControlFooter ($action $field.id $fieldLayoutItem.getFieldDescription() $displayParameters $auiparams)

Restart JIRA service

service jira stop; service jira start:

JIRA: cli interface

Creating JIRA Issue using JIRA cli interface – a minimal effort solution, easy use and maintain

1.       JDK1.6 installed

2.       Download atlassian-cli-2.4.0-distribution.zip from atlassian website

3.       Unzip atlassian-cli-2.4.0-distribution.zip, such as C:\atlassian-cli-2.4.0

4.       Edit C:\atlassian-cli-2.4.0\jira.bat

from

      java -jar "%dirPath%"/lib/jira-cli-2.4.0.jar %*

               to

      java -jar "%dirPath%"/lib/jira-cli-2.4.0.jar --server http://<my_jira_server>:8080 --user <submitter_username> --password <submitter_password> %*

Example creating an issue in JIRA

Under Dos Prompt executing command as below :

C:\atlassian-cli-2.4.0\jira.bat --action createIssue --project "MY_JIRA_PROJECT_KEY" --type "defect" --summary "Created from cli" --priority "Priority 2" --affectsVersions "3.0.1" -–components "application"

Issue JIRATST-17 created with id 10705. URL: http://<my_jira_server>:8080/browse/MY_JIRA_PROJECT_KEY -17

Note:

Priority, affectsVersdions are mandatory fields

JIRA: duplicate JIRA database

# JIRA's XML backup utility to backup JIRA database in XML format

Administrator -> System -> Import & Export -> Backup System

# create a new latin1 database on a new database server to house JIRA's data

[root@linux64-jira-server ~]# mysql -u root -h linux64-jira-server
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.5.25-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE latin1jiradb CHARACTER SET 'latin1';
Query OK, 1 row affected (0.00 sec)
mysql> CREATE USER 'latin1jira'@'linux64-jira-server' IDENTIFIED BY '********';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'latin1jira'@'linux64-jira-server';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@linux64-jira-server ~]# mysql --user=latin1jira --password=******** --database=latin1jiradb --host=linux64-jira-server
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.5.25-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye

# shutdown JIRA server

[root@linux64-jira-server ~]# service jira stop

# make a backup of JIRA HOME Directory (/var/atlassian/application-data/jira)

[root@linux64-jira-server jira]# cp -prf /var/atlassian/application-data/jira /var/atlassian/application-data/jira_backup

# remove previous dbconfig.xml from JIRA HOME Directory

[root@linux64-jira-server jira]# rm -f /var/atlassian/application-data/jira/dbconfig.xml

# restart JIRA server

[root@linux64-jira-server jira]# service jira start

# restart JIRA with JIRA Setup Wizard for configuring database connection

# click "import your existing data" link and restore you data from the XML backup created previously