Note to self #1

Holt-Winters-Brown

Some Hungarian laws

A "2010. évi CXXX. törvény a jogalkotásról" alapján a szolgáltató nem jogalkotó és nem jogforrás. Következményként az ÁSZF/EULA törvényt nem írhat felül.

javascript: What is the type of this thing?!?!

So you wonder, what the hell is the type of a something? Stack Overflow to the rescue!

Object.prototype.toString.call('abc') // [object String]
Object.prototype.toString.call(/abc/) // [object RegExp]
Object.prototype.toString.call([1,2,3]) // [object Array]

The full explanation and other ideas are here: http://stackoverflow.com/a/332429/357403

java: Field Type Descriptors

A field descriptor represents the type of a class, instance, or local variable.

FieldType term	Type	Interpretation
B	byte	signed byte
C	char	Unicode character code point in the Basic Multilingual Plane, encoded with UTF-16
D	double	double-precision floating-point value
F	float	single-precision floating-point value
I	int	integer
J	long	long integer
LClassName;	reference	an instance of class ClassName eg. for Object ⇒ Ljava/lang/Object;
S	short	signed short
Z	boolean	true or false
[	reference	one array dimension eg. for double[][][] ⇒ [[[D

Oracle: JIRA issue activity breakdown

So you need how many issues get created, changed or commented per hour, each day.

SELECT * FROM (
   SELECT * FROM (
      SELECT
         ID                             AS "ISSUEID",
         TO_CHAR(CREATED, 'YYYY-MM-DD') AS CREATED  ,
         TO_CHAR(CREATED, 'HH24')       AS HOURS    ,
         TO_CHAR(CREATED, 'DAY')        AS DOW      ,
         'NEW'                          AS "TYPE"
      FROM JIRAISSUE
      WHERE CREATED >= TO_DATE('2015-01-01', 'YYYY-MM-DD')
      UNION ALL
      SELECT
         ISSUEID                                  ,
         TO_CHAR(CREATED, 'YYYY-MM-DD') AS CREATED,
         TO_CHAR(CREATED, 'HH24')       AS HOURS  ,
         TO_CHAR(CREATED, 'DAY')        AS DOW    ,
         'CHANGE'                       AS "TYPE"
      FROM CHANGEGROUP
      WHERE CREATED >= TO_DATE('2015-01-01', 'YYYY-MM-DD')
      UNION ALL
      SELECT
         ISSUEID                                  ,
         TO_CHAR(CREATED, 'YYYY-MM-DD') AS CREATED,
         TO_CHAR(CREATED, 'HH24')       AS HOURS  ,
         TO_CHAR(CREATED, 'DAY')        AS DOW    ,
         'COMMENT'                      AS "TYPE"
      FROM JIRAACTION
      WHERE CREATED >= TO_DATE('2015-01-01', 'YYYY-MM-DD')
   )
   PIVOT (
      COUNT(ISSUEID) FOR (HOURS)
      IN ('00', '01', '02', '03', '04', '05', '06', '07',
         '08', '09', '10', '11', '12', '13', '14', '15',
         '16', '17', '18', '19', '20', '21', '22', '23')
   )
) ORDER BY CREATED DESC, "TYPE" DESC;

Not my best, but what do you expect in ten minutes?

java: How to bulk load the Java Exception Site List

This guide comes handy if you have a big site list that utilizes a Java applet as the primary entry point (Netscaler, Agile, HP BPM/PPM, etc), those you want to allow in browsers but latest Java blocks them, because they are not containing a security manifest.

Good to know

• No wildcards at all in the exception file.
• Specify the port if needed (like :8080), as the default is the protcol's default port (80 for http, 443 for https).
• End URL-s with / to also allow subdirectories.
• Your own (default) exception.sites file is at: C:\Users\<user>\AppData\LocalLow\Sun\Java\Deployment\security\exception.sites, this is what the Java Control Panel uses by default. You have to copy your existing stuff from here.
• No comments are allowed in the exception.sites file... :-( If you try to have one, the exception file gets corrupted and will be skipped.

Step-by-step guide

1. Create the file: C:\Users\<user>\AppData\LocalLow\Sun\Java\Deployment\deployment.config
2. Set it's contents to be:

   deployment.system.config=file\:C\:/Windows/Sun/Java/Deployment/deployment.properties

3. Create the file: C:\Windows\Sun\Java\Deployment\deployment.properties
4. Set it's contents to be:

   #C:\Windows\Sun\Java\Deployment\deployment.properties
   deployment.webjava.enabled=true
   deployment.security.level=MEDIUM
   deployment.security.level.locked
   deployment.user.security.exception.sites=C\:/Windows/Sun/Java/Deployment/exception.sites
   install.disable.sponsor.offers=true

5. Create the file: C:\Windows\Sun\Java\Deployment\exception.sites
6. Now, you can copy your previous site list. You can even populate it by a login script.

C: fun on the 404 page of stackoverflow

I found this fun on the SO's 404 page:

Of course, it is a pretty lightly obfuscated C code printing out "404".

This is what will get effectively compiled and executed (you can verify with gcc -E -P):

main(){putchar(4+putchar(putchar(52)-4));return 0;};exit();

As putc returns the character it printed out, the magic is easy:

1. Print the character dec 52  (which is the digit four)
2. Print the character dec 52-4 = dec 48 (which is the digit zero)
3. Print the character dec 48+4 = dec 52 again

Shell: Get threaddump directly from the java process

Inspecting the Java source, I found a pretty easy way to skip java in the process of extracting info from another java process :-)

PID=`pgrep java`
SCKT=/tmp/.java_pid$PID
SGNL=/tmp/.attach_pid$PID
CMD='1\0threaddump\0\0\0\0'

if [ ! -r $SCKT ]; then
 touch $SGNL || exit 2
 kill -s SIGQUIT $PID
 sleep 5
 rm $SGNL
 if [ ! -r $SCKT ]; then
  echo Cannot read $SCKT ... either you are not the correct user for this, or the java process does not 'see' our attach request.
  exit 1
 fi
 echo Done
fi

echo -ne "$CMD" | nc -U "$SCKT"

Possible options and variations I know about:

• 1\0threaddump\0-l\0\0\0 small L for the jstack -L option
• 1\0inspectheap\0\0\0\0
• 1\0inspectheap\0-live\0\0\0

For others see the attachListener.cpp (JDK7, JDK8)

Oracle: ANSI JOIN recommended since 9.2?

Look at this gem I found in the Oracle 9i (!!!) [released in 2001] documentation.

http://docs.oracle.com/cd/B10501_01/server.920/a96540/queries7.htm#2054065

Oracle Corporation recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions(...)

Also, http://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_103a.htm#2091953

Oracle Corporation strongly recommends that you use the more flexible Oracle9i FROM clause join syntax shown in the former example.

What is this "FROM clause join syntax"?

It is The ANSI JOIN syntax.

Lol.

Nagios: Run query as a service check

Sometimes it is a good idea to check things directly in the database. A few months (years?) ago I ran into an issue with JIRA, that the data integrity is absolutely not enforced in any way, and user-detected issues are repaired with ad-hoc features, like the dreaded Integrity Checker. I have two major issues with Atlassians standpoint with this:

1. The Integrity Checker is a reaction, after the user reported the problem. The latter can take weeks(!!!). You cannot solve an issue before the user even detects it if you have no tools to... detect it.
2. A multi-$10k software not using foreign keys and other (not so) advanced RDBMS features? Come on...

So we are already using Nagios, let's see if I can put together a command and a service that reports things to me.

Now, we have 2 important tasks:

1. Run a SELECT on the Oracle server from a script, and return as a standard Nagios check script.
2. Run this command from a Nagios service and make it accept arguments.

Let's see, what info does our script need:

• We have a tnsnames.ora, so we only need the service name. We can assemble this based on the application name and the tier code.
• If we hardcode a serviced user for this, we'll only need it's password (as it may vary depending on the tier).
• It is easier to host functions on the server and just call them. So we need a parameter for the currently desired function. Why function? We'll see it later.

Now, our check script looks like this:

#!/bin/sh

export ORACLE_HOME=/usr/local/oracle/product/11.2.0/client

OK=0
WARN=1
CRIT=2
UNKN=3

usage()
{
cat << EOF
SCRIPT PROBLEM|Called as $0 $@
usage: $0 options

This script run the db_check() on the specified Oracle service.

OPTIONS:
   -a           application (eg. jira)
   -t           tier (eg. d1, t1)
   -p           nagiossvc password (default: welcome)
   -f           function name (default: DB_CHECK)

These will be combined to be <tier><application>, and apps.nagios_<application> inside.
EOF
}

APP=
TIER=
PASSWD=
FUNCTION=
OBJECT=

while getopts "ht:a:p:f:o:" OPTION
do
     case $OPTION in
         h)
             usage
             exit $UNKN
             ;;
         t)
             TIER=$OPTARG
             ;;
         a)
             APP=$OPTARG
             ;;
         p)
             PASSWD=$OPTARG
             ;;
         f)
             FUNCTION=$OPTARG
             ;;
         o)
            OBJECT=$OPTARG
            ;;
         ?)
             usage
             exit $UNKN
             ;;
     esac
done

if [ -z $APP ]
then
     usage
     exit $UNKN
fi

if [ -z $TIER ]
then
    export TNS_ADMIN=/etc/tnsnames/prod
else
    export TNS_ADMIN=/etc/tnsnames/dev
fi

if [ -z $FUNCTION ]
then
    FUNCTION=DB_CHECK
fi

if [ -z $OBJECT ]
then
    OBJECT=NAGIOS_$APP
fi

if [ -z $PASSWD ]
then
        if [ -z $TIER ]
        then
            PASSWD=nagiossvc_passwd
        else
            PASSWD=welcome
        fi
fi

START=$(date +%s)
RESULTSET="$(${ORACLE_HOME}/bin/sqlplus -S -R 3 -L nagiossvc/${PASSWD}@${TIER}${APP} <<OURQUERY
set colsep ,
set pagesize 0
set linesize 10240
set trimspool on
set longchunksize 2000000 long 2000000 pages 0
SELECT ${OBJECT}.${FUNCTION}() AS ERRORS FROM DUAL;
OURQUERY
)"
END=$(date +%s)
TIMESPAN=$((END-START))

if [[ $RESULTSET == *ORA-* ]]
then
    echo "Script error!|${TIMESPAN}sec"
    echo "$RESULTSET"
    exit $CRIT
elif [ -n "$RESULTSET" ]
then
    echo "Issues were found.|${TIMESPAN}sec"
    echo "${RESULTSET}"
    exit $WARN
else
    echo "OK|${TIMESPAN}sec"
    exit $OK
fi

exit $UNKN

That's it for the first part, now we need a package that hosts this function for us. Why a package? Our DBA-s are crazy for packages. So let's create a package!

CREATE OR REPLACE PACKAGE nagios_jira AUTHID DEFINER AS
   FUNCTION db_check RETURN CLOB;
   -- Application specific functions:
   FUNCTION check_workflow_entry_states RETURN CLOB;
   FUNCTION check_issue_summary_not_null RETURN CLOB;
   FUNCTION check_invalid_issuelink RETURN CLOB;
   FUNCTION check_deleted_is_watcher RETURN CLOB;
   FUNCTION check_fileattachment_nulled RETURN CLOB;
END nagios_jira;
/
CREATE OR REPLACE PACKAGE BODY nagios_jira AS
FUNCTION db_check RETURN CLOB IS
   v_results CLOB := '';
BEGIN
   v_results := v_results || check_workflow_entry_states;
   v_results := v_results || check_issue_summary_not_null;
   v_results := v_results || check_invalid_issuelink;
   v_results := v_results || check_deleted_is_watcher;
   v_results := v_results || check_fileattachment_nulled;
   -- repeat
   RETURN v_results;
END db_check;

FUNCTION check_workflow_entry_states RETURN CLOB IS
   v_errorcount NUMBER(6);
BEGIN
   SELECT COUNT(*) INTO v_errorcount FROM JIRAUSER.JIRAISSUE  
   INNER JOIN JIRAUSER.OS_WFENTRY ON JIRAISSUE.WORKFLOW_ID = OS_WFENTRY.ID
   WHERE OS_WFENTRY.STATE IS NULL OR OS_WFENTRY.STATE = 0;
   IF (v_errorcount > 0) THEN
      RETURN 'CHECK_WORKFLOW_ENTRY_STATES('||v_errorcount||')' || CHR(10);
   ELSE
      RETURN '';
   END IF;
END check_workflow_entry_states;

FUNCTION check_issue_summary_not_null RETURN CLOB IS
   v_errorcount NUMBER(6);
BEGIN
   SELECT COUNT(*) INTO v_errorcount FROM JIRAUSER.JIRAISSUE WHERE SUMMARY IS NULL;
   IF (v_errorcount > 0) THEN
      RETURN 'CHECK_ISSUE_SUMMARY_NOT_NULL('||v_errorcount||')' || CHR(10);
   ELSE
      RETURN '';
   END IF;
END check_issue_summary_not_null;

FUNCTION check_invalid_issuelink RETURN CLOB IS
   v_errorcount NUMBER(6);
BEGIN
   SELECT COUNT(*) INTO v_errorcount FROM JIRAUSER.ISSUELINK L, JIRAUSER.JIRAISSUE I1, JIRAUSER.JIRAISSUE I2 WHERE I1.ID(+) = L.SOURCE AND I2.ID(+) = L.DESTINATION AND (I1.ID IS NULL OR I2.ID IS NULL);
   IF (v_errorcount > 0) THEN
      RETURN 'CHECK_INVALID_ISSUELINK('||v_errorcount||')' || CHR(10);
   ELSE
      RETURN '';
   END IF;
END check_invalid_issuelink;

FUNCTION check_deleted_is_watcher RETURN CLOB IS
   v_errorcount NUMBER(6);
BEGIN
   SELECT COUNT(*) INTO v_errorcount FROM (SELECT DISTINCT LOWER(SOURCE_NAME) FROM JIRAUSER.USERASSOCIATION MINUS SELECT DISTINCT LOWER_USER_NAME FROM JIRAUSER.CWD_USER);
   IF (v_errorcount > 0) THEN
      RETURN 'CHECK_DELETED_USERS_IN_WATCHERS('||v_errorcount||')' || CHR(10);
   ELSE
      RETURN '';
   END IF;
END check_deleted_is_watcher;

FUNCTION check_fileattachment_nulled RETURN CLOB IS
   v_errorcount NUMBER(6);
BEGIN
   SELECT COUNT(*) INTO v_errorcount FROM JIRAUSER.FILEATTACHMENT WHERE FILENAME IS NULL;
   IF (v_errorcount > 0) THEN
      RETURN 'CHECK_FILEATTACHMENT_WITHOUT_FILENAME('||v_errorcount||')' || CHR(10);
   ELSE
      RETURN '';
   END IF;
END check_fileattachment_nulled;

END nagios_jira;
/
GRANT EXECUTE ON nagios_jira to nagiossvc;
CREATE SYNONYM nagiossvc.nagios_jira FOR APPS.nagios_jira;

Ugh, those joins are ugly, but our architect is stuck in pre-9i times...

So if we get the return value from this function, it will contain only one cell for us. If that's not empty, then we found issues, and the details are listed.

Now, wire it into Nagios, have a Nagios command definition for this script:

# Run the check on the specified db
# ARG1 - application
# ARG2 - tier (defaults to prod)
# ARG3 - password of nagiossvc - optional (defaults in script)
# ARG4 - function name - optional (defaults to DB_CHECK)
# ARG5 - plsql package object name - optional (defaults to ARG1)
define command {
    command_name check_jira_integrity
    command_line /usr/local/whatever/bin/check_db.sh -a "$ARG1$" -t "$ARG2$" -p "$ARG3$" -f "$ARG4$" -o "$ARG5$"
}

Call this command from a Nagios service:

define service {
        service_description jira_prod_check_integrity
        host_name myjira
        check_command check_jira_integrity!jira
        check_interval 15
        notification_interval 15
        retry_interval 5
}

(Check every 15 minutes. When problems detected, retry every 5 minutes.)

Now we are playing.

JIRA: Additional indexes for performance

Some indices for purrformance, that piled up during the years:

-- Fix Kepler idiocies:
ALTER INDEX PK_KISSUESTATE RENAME TO ISSUE_STATE_IDX;
ALTER INDEX PK_KPLUGINSCFG RENAME TO PLUGIN_CFG_IDX;
ALTER INDEX PK_KSTATEVALUES RENAME TO STATE_VALUES_IDX;

-- Standard additions
CREATE INDEX IX_NOTIF_INST_MSGID ON NOTIFICATIONINSTANCE (MESSAGEID ASC);
CREATE INDEX IX_CHANGEGROUP_AUTHOR ON CHANGEGROUP (AUTHOR ASC); -- can be big
CREATE INDEX IX_CHANGEGROUP_AUTHOR_CREATED ON CHANGEGROUP (AUTHOR ASC, CREATED ASC); -- can be big
CREATE INDEX IX_CHANGEGROUP_AUTH_CREA_ISSUE ON CHANGEGROUP (AUTHOR ASC, CREATED DESC, ISSUEID ASC);
CREATE INDEX IX_FILEATTACHMENT_FILENAME ON FILEATTACHMENT (FILENAME ASC, '1'); -- See Note
CREATE INDEX IX_JIRAACTION_AUTHOR ON JIRAACTION (AUTHOR ASC);
CREATE INDEX IX_JIRAACTION_AUTHOR_CREATED ON JIRAACTION (AUTHOR ASC, CREATED ASC); 
CREATE INDEX IX_JIRAACTION_AUTH_CREAT_ISSUE ON JIRAACTION (AUTHOR ASC, CREATED DESC, ISSUEID ASC);
CREATE INDEX IX_JIRAACTION_TYPE_LEVEL ON JIRAACTION (ID ASC, ACTIONTYPE ASC, ACTIONLEVEL ASC); 
CREATE INDEX IX_JIRAISSUE_ISSUESTATUS ON JIRAISSUE (ISSUESTATUS ASC, '1');
CREATE INDEX IX_JIRAISSUE_PROJ_ASSIGNEE ON JIRAISSUE (PROJECT ASC, ASSIGNEE ASC);
CREATE INDEX IX_JIRAISSUE_PROJECT ON JIRAISSUE (PROJECT ASC, '1');
CREATE INDEX IX_JIRAISSUE_PROJECT_UPDATED ON JIRAISSUE (PROJECT ASC, UPDATED ASC);
CREATE INDEX issue_reporter ON JIRAISSUE (REPORTER ASC); -- lately introduced by Atlassian in JIRA 6.3
CREATE INDEX IX_JIRAISSUE_SUMMARY ON JIRAISSUE (SUMMARY ASC, '1'); -- See Note
CREATE INDEX IX_JIRAISSUE_UPDATED_PROJ_ID ON JIRAISSUE (UPDATED DESC, PROJECT ASC, ID ASC); -- for idiotic pagination queries
CREATE INDEX IX_JIRAISSUE_WF_PKEY_1 ON JIRAISSUE (WORKFLOW_ID ASC, PKEY ASC, '1');
CREATE INDEX IX_OS_WFENTRY_ID_NAME ON OS_WFENTRY (ID ASC, NAME ASC);

-- customfieldvalue is large. we need it to be created quickly, then turn off parallelism.
CREATE INDEX IX_CUSTOMFIELDVALUE_ISSUE ON CUSTOMFIELDVALUE (ISSUE ASC) PARALLEL 12; ALTER INDEX IX_CUSTOMFIELDVALUE_ISSUE NOPARALLEL; -- can be _really_ big depending on your CF usage

-- bitmap indexes:
CREATE BITMAP INDEX IX_OS_WFENTRY_STATE ON OS_WFENTRY (STATE ASC); -- degree of cardinality is typically below 1 ppm.

-- recreate these indexes:
DROP INDEX PRMSSN_SCHEME; CREATE BITMAP INDEX PRMSSN_SCHEME ON SCHEMEPERMISSIONS(SCHEME ASC);
DROP INDEX WF_ENTRYID; CREATE INDEX WF_ENTRYID ON OS_CURRENTSTEP (ENTRY_ID, STEP_ID);
DROP INDEX ISSUE_WORKFLOW; CREATE INDEX ISSUE_WORKFLOW ON JIRAISSUE (WORKFLOW_ID, ISSUESTATUS);

Note: On multi column indices containing a '1': The '1' here is actually a static char(1) column, always containing '1', and even if the first (the real) column's value is null, it will be stored in the index, and thus will be searchable.

JIRA: When were the watchers added to an issue?

SELECT
   SOURCE_NAME AS WATCHER,
   A.CREATED             ,
   TO_CHAR(A.CREATED, 'yyyy-mm-dd hh24:mi:ss') FANCY_CREATED
FROM
   USERASSOCIATION A
INNER JOIN JIRAISSUE I
ON
   I.ID = A.SINK_NODE_ID
INNER JOIN PROJECT P
ON
   P.ID = I.PROJECT
WHERE
   P.PKEY     = 'TICKT' AND
   I.ISSUENUM = '1114';

Java: Why is Object.wait() always Runnable?

In the Java thread dumps you see some parking threads that say: "java.lang.Thread.State: RUNNABLE", but obviously they are waiting for a monitor, so it should be "timed_wait" or "waiting".
Also, it says they are in a native frame.
How?

1. Basically, all frames that are in native code, are Runnable. The jvm just doesn't care, and also can't check what the state is. So it will always be Runnable if the frame is native.
2. When you call the Object.wait() through any kind of consequences, it will simply call Object.wait(0).
3. However, Object.wait(timeout) is a native call to a JVM-internal function. (Hence the default threadstate.)
4. Upon checking the source in the OpenJDK site, we find it simply routes the call to a function called JVM_MonitorWait, which is defined in jvm.cpp.
5. This function eventually calls ObjectSynchronizer::wait([obj], [timeout], [traps]). This wait creates an object monitor based on the passed object reference. Then, the ObjectMonitor's wait method is called. (Note: sources linked for Linux target)
6. This ObjectMonitor::wait manages the waiting queue. Calls park() on the current thread.
7. In fact, park() is a member of os::PlatformEvent, so we need to go further.
8. os::PlatformEvent:park() does an old-fashioned looped lock checking with pthread_cond_wait.

Java To Trust GoDaddy Class2 G2 Certs

Sadly, GoDaddy's "Class 2" and "Class 2 - G2" root cacerts are not included in the Java7 packages, so they are not trusted by default.

So we'll have to add the CA certificates to the Java TrustStore.

I use Apache Tomcat, so I'll need another step: configuring where to look for the truststore file, which I'll add to the CATALINA_OPTS, but you should use setenv.sh or whatever your deployment process forces you to. For some reason I haven't got the urge to look up, Tomcat does NOT pick up the truststore by default. Maybe it's just some PEBKAC in the setup somewhere else.

Align the cacerts file location to your needs.

-Djavax.net.ssl.trustStore=/opt/apps/jira/jdk/jre/lib/security/cacerts -Djavax.net.ssl.trustStorePassword=changeit

Firstly, fetch the certificates we want to trust:

1. https://certs.godaddy.com/repository/gd_bundle.crt
2. https://certs.godaddy.com/repository/gdroot-g2.crt
3. https://certs.godaddy.com/repository/gd-class2-root.crt
4. https://certs.godaddy.com/repository/gdig2.crt

Sometimes they interleave (eg. gdig2 is an intermediate cert, connecting Class2 and G2), if it throws an error about already being imported, ignore the error.

Secondly, import the certs to trust them (do it on all four):

keytool -import -trustcacerts -alias gd_bundle -file gd_bundle.crt -keystore /opt/apps/jira/jdk/jre/lib/security/cacerts

Here, I use the same alias as the filename. Again, align the cacerts's file location to your needs.
The keytool binary will ask for the password, which is changeit by default. If you change it, remember to change the tomcat parameter trustStorePassword also.

Lastly, restart Tomcat.

JIRA: Check JIRA issues with null status

So you see errors like: "java.lang.RuntimeException: Could not find workflow status for issue SOMEPRJ-123456.".
It suggests, the jiraissue.issuestatus column on that issue is null.
How to check:

SELECT ID FROM JIRAISSUE WHERE ISSUESTATUS IS NULL;

How to fix it in Oracle:
Prerequisites: For the TEMP_JIRAWORKFLOWS, see here: http://kozelljozsef.blogspot.hu/2014/11/jira-check-workflow-current-step-entries.html

MERGE INTO jiraissue USING
(
  WITH W_JIRAISSUE AS
  ( SELECT
      I.ID AS ISSUEID,
      I.WORKFLOW_ID,
      I.ISSUESTATUS,
      W.NAME AS WFNAME,
      C.STEP_ID AS CURRENT_STEP_ID
    FROM JIRAISSUE I
    INNER JOIN OS_WFENTRY W
    ON I.WORKFLOW_ID = W.ID
    INNER JOIN OS_CURRENTSTEP C
    ON I.WORKFLOW_ID = C.ENTRY_ID
    WHERE I.ISSUESTATUS IS NULL
  )
SELECT
  I.ISSUEID,
  I.ISSUESTATUS,
  WF.LINKEDSTATUSID AS LINKED_STATUS_ID
FROM W_JIRAISSUE I
INNER JOIN TEMP_JIRAWORKFLOWS WF
ON WF.WORKFLOWNAME = I.WFNAME AND WF.STEPID = I.CURRENT_STEP_ID
) GOODSTATUS ON (GOODSTATUS.issueid = JIRAISSUE.ID)
WHEN MATCHED THEN UPDATE
    SET JIRAISSUE.ISSUESTATUS = GOODSTATUS.LINKED_STATUS_ID;

This essentially looks up the workflow step associated with the failing issue, reads out the linked status id that should be written back to the issuestatus column.

JIRA: Check workflow current step entries

An issue can have an invalid status (one that does not exist for the workflow) if a Workflow Migration or Bulk Move died half way through.

An issue must have exactly 1 (one) currentstep. It tells, how did we got here.
If it has zero, we should insert one for the current wfentry based on the workflow.
If it has more than one, we should only keep the latest. (Not covered by this blog entry.)

How to check:

SELECT COUNT(*) AS PROBLEM_CNT
FROM JIRAISSUE I    
LEFT JOIN OS_CURRENTSTEP C ON I.WORKFLOW_ID = C.ENTRY_ID
INNER JOIN TEMP_JIRAWORKFLOWS WF ON I.ISSUESTATUS = WF.LINKEDSTATUSID 
INNER JOIN OS_WFENTRY W ON I.WORKFLOW_ID = W.ID AND WF.WORKFLOWNAME = W.NAME
WHERE C.STEP_ID != WF.STEPID OR C.ENTRY_ID IS NULL;

Essentially, this check is an antijoin, looking for issues without a corresponding currentstep (however the condition also gives us the multiple ones too) OR with an incorrect step.

How to fix:

UPDATE: Now, as converting the XML-s to a table is pretty expensive, we'll create a temporary table, and fill it in with the workflow-step-status tuples.

-- Create me once.
CREATE GLOBAL TEMPORARY TABLE TEMP_JIRAWORKFLOWS
(WORKFLOWNAME VARCHAR2(255 CHAR),
STEPID NUMBER,
LINKEDSTATUSID VARCHAR2(16))
ON COMMIT PRESERVE ROWS;

Every time we connect, we'll have to fill it out.
Note: you can create a real table with triggers to reflect the changes on the original, or have a materialized view. I choose this cumbersome way, because our DBA-s are afraid of triggers (wtf?!), and also they does not let me create materialized views.

INSERT INTO TEMP_JIRAWORKFLOWS
WITH W_JIRAWORKFLOWS AS
  ( SELECT
        WORKFLOWNAME,
        XMLTYPE.CREATEXML(REPLACE(DESCRIPTOR,
        '<!DOCTYPE workflow PUBLIC "-//OpenSymphony Group//DTD OSWorkflow 2.8//EN" "http://www.opensymphony.com/osworkflow/workflow_2_8.dtd">'
        )) AS DESCRIPTOR
     FROM JIRAWORKFLOWS)
SELECT
  W_JIRAWORKFLOWS.WORKFLOWNAME,
  WF.STEPID                   ,
  WF.LINKEDSTATUSID
FROM
  W_JIRAWORKFLOWS,
  XMLTABLE('/workflow/steps/step[@id]' PASSING W_JIRAWORKFLOWS.DESCRIPTOR
     COLUMNS
        "STEPID" NUMBER PATH '@id',
        "LINKEDSTATUSID" VARCHAR2(16) PATH 'meta[@name="jira.status.id"]/text()'
     ) WF;

COMMIT;
 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED NAME 'Check workflow current step entries';
 
MERGE INTO OS_CURRENTSTEP USING
( SELECT
       ROWNUM+(SELECT SEQ_ID FROM SEQUENCE_VALUE_ITEM WHERE SEQ_NAME = 'OSCurrentStep') AS N,
       I.WORKFLOW_ID     AS ENTRY_ID,
       WF.STEPID         AS STEP_ID ,
       WF.LINKEDSTATUSID AS STATUS
  FROM JIRAISSUE I
  LEFT JOIN OS_CURRENTSTEP C ON I.WORKFLOW_ID = C.ENTRY_ID
  INNER JOIN TEMP_JIRAWORKFLOWS WF ON I.ISSUESTATUS = WF.LINKEDSTATUSID
  INNER JOIN OS_WFENTRY W ON I.WORKFLOW_ID = W.ID AND WF.WORKFLOWNAME = W.NAME
  WHERE C.STEP_ID != WF.STEPID OR C.ENTRY_ID IS NULL
) GOODSTEP ON (GOODSTEP.ENTRY_ID = OS_CURRENTSTEP.ENTRY_ID)
WHEN MATCHED THEN UPDATE
    SET OS_CURRENTSTEP.STEP_ID = GOODSTEP.STEP_ID
WHEN NOT MATCHED THEN INSERT
VALUES (GOODSTEP.N, GOODSTEP.ENTRY_ID, GOODSTEP.STEP_ID, 0, NULL, SYSDATE, NULL, NULL, GOODSTEP.STATUS, NULL);
 
UPDATE SEQUENCE_VALUE_ITEM 
   SET SEQ_ID = (1 + (SELECT MAX(ID) FROM OS_CURRENTSTEP)) 
   WHERE SEQ_NAME = 'OSCurrentStep';
 
COMMIT;

The fix is more complex than the others. We need an ID range taken from the SEQUENCE_VALUE_ITEM table, so later nothing tries to use our id-s. I wonder, why Atlassian does not utilize things like autoincrement fields or sequences...
So we can fetch the step_id and status_id that took us to the current step, essentially everything we need in the currentstep. Then, we increment the sequence.

Oracle: quick notes on ojdbc6.jar

(If not specified, package is "oracle.jdbc.driver". Our current driver type is thin.)
(Need more details? JD-GUI and ojdbc6_g.jar is all what you need.)

1. oracle.jdbc.OracleDriver --> OracleDriver
1. connect()
1. oci: T2CDriverExtension (driverextension id: 2) [T2 means "type 2", aka using native-API]
2. thin: T4CDriverExtension (driverextension id: 0) [T4 means "type 4", aka pure java implementation]
2. Instatiates a T4CDriverExtension
1. getConnection() --> T4CConnection
3. Casts back the T4CConnection to PhysicalConnection, then returns.
2. PhysicalConnection.createStatement(CURSOR_TYPE, CONCURRENCY_TYPE, IGNORED)
1. return new OracleStatementWrapper( T4CDriverExtension.allocateStatement() --> T4CStatement)
3. OracleStatementWrapper.executeQuery() [To have a ResultSet]
1. OracleStatement.executeQuery()
1. OracleStatement.doExecuteWithTimeout()
1. T4CStatement.executeForRows()
1. T4CStatement.doOall8(PARSE, EXEC, FETCH, DESCR, DEF, 1stRow)
1. T4C8Oall.doOALL(PARSE, EXEC, FETCH, DESCR, DEF, kind, cursorid, sqlObject.getSqlBytes() [teh query], ...)
1. ...
2. return new OracleResultSetImpl(this.[pysical]connection, this[OracleStatement]);
2. ret
4. [while] OracleResultSetImpl.next()
1. OracleResultSetImpl.close_or_fetch_from_next()
1. T4CStatement.fetch()
1. T4CStatement.doOall8(f,f,t,f,f)
1. T4C8Oall.doOALL()
1. T4CTTIfun.doRPC() [TTI: Two-Task Interface]
1. marshalFunHeader()
1. T4CTTIMsg.marshalTTCcode()
1. T4CMAREngine.marshalUB1()
1. [T4CSocketOutputStreamWrapper.write(aByte)]
2. marshal() -- abstract: TODO
3. receive()
1. T4C8Oall.readDCB()
2. T4C8TTILob.readLOBD()
5. OracleResultSetImpl.getLong(i)
1. OracleStatement,accessors[i-1]
1. LongAccessor
1. CharCommonAccessor.getLong(i-1)
1. Long.parseLong(CharCommonAccessor.getString(i-1))
1. cuts the string out of an array of bytes received as a LOB.