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.