JIRA: Check workflow entry states are correct

Symptom: You see no transition buttons on the ticket, but when you edit it, they come back.
Cause: The workflow entry associated with the issues in screwed up. Its state is "created"(zero) or null instead of "activated"(one).
How to check:

SELECT PROJECT.PKEY || '-' || JIRAISSUE.ISSUENUM AS PKEY, JIRAISSUE.WORKFLOW_ID, OS_WFENTRY.STATE
FROM JIRAISSUE
INNER JOIN OS_WFENTRY ON JIRAISSUE.WORKFLOW_ID = OS_WFENTRY.ID
INNER JOIN PROJECT ON PROJECT.ID = JIRAISSUE.PROJECT
WHERE OS_WFENTRY.STATE IS NULL OR OS_WFENTRY.STATE = 0;

These should return all the issues you have the problem with.

How to fix it in Oracle:

UPDATE OS_WFENTRY SET STATE = 1
WHERE ID IN ( SELECT OS_WFENTRY.ID FROM OS_WFENTRY
INNER JOIN JIRAISSUE ON JIRAISSUE.WORKFLOW_ID = OS_WFENTRY.ID
WHERE OS_WFENTRY.STATE IS NULL OR OS_WFENTRY.STATE = 0);

There are other workflow state codes, but the official correction in the integrity checker is to set it to 1 (activated).

Oracle: JIRA: index for the pagination query

I found a select that executes with a cost over 16k, and I wondered, how could I speed this up.
(Sometimes it ran for 10.5 seconds!)

SELECT SQ_.ID FROM
  ( 
    SELECT ID FROM JIRAISSUE
    WHERE PROJECT IN(:1, :2)
    ORDER BY UPDATED DESC
  ) SQ_
WHERE ROWNUM <= 10;

It seems, only the IN-s contents are changing over the course, so we could fit an index for that. Lets see, what we have:

• ORDER BY UPDATED DESC - this will be the leading column in descending order,
• PROJECT - filter, this should be in,
• ID - as we projected this column, if we put this in the index, it can be an index-only query.

CREATE INDEX IX_JIRAISSUE_UPDATED_PROJ_ID ON JIRAISSUE (UPDATED DESC, PROJECT ASC, ID ASC);

Results in an index full scan and a count-with-stopkey. Yaay, the cost is 3 (three), in a table with ~2 million rows.

Oracle: scan types

• Full Table Scan - Highest cost. This happens, if:
• No index exists.
• There is an index, but
• Without columns that the query is using, or
• Aggregate function is used, AND index allows nulls, or
• Multi-column index, and you're not using the first column in it.
• Resultset expected to be so big, that it is cheaper to read through the table than use the index. (Fewer large IO calls are cheaper than lot of tiny ones.)
• The table is too small. (Smaller than DB_FILE_MULTIBLOCK_READ_COUNT)
• Table parallelism too high. (Small partitions are easier to scan - see previous item)
• Table Access by rowid - Getting rowids through an index.
• An index returns with a set of rowid-s.
• There are columns specified that are not in the index.
• Table Access Sample - When using block sampling.
• Table Access Inmemory - Since 12cR1, on tables using inmemory parameter.
• Index Unique Scan - unique means, at most one rowid exists for a given index entry, so the search can stop after the first match.
• Unique Index exists and can be used, and
• Equality operator being in place.
• Index Range Scan (Descending) - ordered scan of values, even for ranges.
• Index leading columns with <,>,= operators, and
• AND combination of those (at least for leading columns).
• Can be forced by using BETWEEN.
• Descending: Order By has Desc parameter in it.
• Index Full Scan - Uses index order for Order By.
• Referencing an indexed column,
• Order By present, in a notnull column,
• All columns in Order By are indexed,
• Order of columns in Order By match the order of the leading index columns.
• Index Fast Full Scan - Full index scan, not accessing the table.
•  All columns required are in the index(es).
• Index Skip Scan
• Leading column in a composite index is not used,
• Leading column has only a few distinct values,
• Non-leading, referenced column has many distinct values.
• Hash Join (Index Join Scan)
• Hash join of multiple indexes returns all the required columns (without table access),
• Table access is more expensive than an index join.
• Table Access Cluster (Cluster Scan)
• Using an indexed cluster.
• Needs a simple Cluster used by all the tables.
• Table Access Hash (Hash Scan)
• In a hash cluster, rows are grouped into the same block by their hash value.
• Needs a Hash Cluster used by all the tables.

Leading column is the first column in a composite index.

CREATE INDEX ix_thisisanindex ON table42(leadingcolumn, notleading, stillnotleading);

C: FizzBuzzWizz

#include <stdio.h>
int main(int argc, char *argv){
  int i;
  for(i=0; i < 101; i++) {
 switch((i%7 == 0) << 2 | (i%5 == 0) << 1 | (i%3 == 0)) {
 case 0: printf("%d\n", i); break;
 case 1: printf("Fizz\n"); break;
 case 2: printf("Buzz\n"); break;
 case 3: printf("FizzBuzz\n"); break;
 case 4: printf("Wizz\n"); break;
 case 5: printf("FizzWizz\n"); break;
 case 6: printf("BuzzWizz\n"); break;
 case 7: printf("FizzBuzzWizz\n"); break;
 }
  }
}

C: FizzBuzz

#include <stdio.h>
int main(int argc, char *argv) {
  int i;
  for(i=0; i < 101; i++) {
    switch((i%5 == 0) << 1 | (i%3 == 0)) {
      case 0: printf("%d\n", i); break;
      case 1: printf("Fizz\n"); break;
      case 2: printf("Buzz\n"); break;
      case 3: printf("FizzBuzz\n"); break;
    }
  }
  return 0;
}

Attenuation and Signal To Noise Ratio and ADSL and PPPoE things

Attenuation

Attenuation is the signal loss made by the transmitting medium. In short, the longer your cable is, the bigger the reduction of your signal is. It is sometimes referred as "noise", as it makes the signal weaker.

Lower is better (as the signal is stronger).

Signal to Noise Ratio (SNR)

This measures the external noise's rate compared to the signal's strength. It is like normalizing the nose to 0 dB, and comparing the signal strength. The decibel as a unit is just invented for this.

Higher is better (as the signal is more stronger than the noise).

Rates for copper ADSL

SNR Levels

• Over 20dB, it is good
• 10dB - 20dB, it is satisfactory
• 7dB - 10dB, it is bad, you'll disconnect often
• Below 7dB, don't bother to try, the noise is so strong, that interferes with the signal.

7dB means the ratio between the two power values is around 2.

If you see somewhere dBm, then it means, that the dB value was calculated from milliwatts.

Attenuation Levels

• Below 50dB, it is good
• Over 50dB, it is poor
• Over 60dB, the signal is not strong enough to detect it.

Some Terms

• "ATU-R" (ADSL Terminal Unit - Remote) - This is your ADSL Modem
• "ATU-C" (ADSL Termination Unit - Central Office) - This is the other endpoint of your DSL-enabled phone line

Why do ISP-s use PPPoE over DSL?

The answer is easy: They used to provide PPP over POTS, and PPPoE authentication can be integrated into their old, PPP authentication directories. They won't spend money if it isn't really necessary... Also, PPPoE can be encrypted and compressed before putting the packet onto the wire.

This means, if someone cuts your wire, can't use your DSL subscription, as they don't know your user/pass.

What your SOHO router does to initiate a PPPoE connection

Discovery

1. Client initiates a search for servers
1. Sends a PADI packet (PPPoE Active Discovery Initiation), which is an Ethernet broadcast packet, looking for an Access Concentrator (AC, later we'll talk to this).
2. Here, you can specify a Service Name, if multiple ISP-s use the same network, or your ISP is a dick, and requires additional configuration.
2. Server responds
1. PADO Offer returned (PPPoE Active Discovery Offer), which states the MAC of the AC
3. Client requests a session
1. Sends a PADR packet ( PPPoE active discovery request)
2. This in fact acknowledges that the session should be created (like in the TCP handshake)
4. Server confirms
1. PADS confirmation packet returned (PPPoE Active Discovery Session-confirmation)
2. This sends back the Session Identifier to use later.

After the Discovery is done, and the session is created, the PPP layer (above PPPoE) initiates the authentication, just like in the old dial-up times.

Problems

• "Err, my router says she sends the PADI, but no response..."
• It wouldn't try to connect, if it would not see a live medium, so there is a problem somewhere else. I had overheating issues XD
• To be sure, check the cable, and the connector at the end of the cable. If you have a cheap RJ45 plug crimped, it might be undersized (trollolo)...

An oldtimer - Error 1722 or 2755 from Wise installer

Here it is for future reference if anyone ends up here on their endeavor of searching to solve this encountering problem.

Internal Error 2755. 1632 setup fails

This problem occurs when the following conditions are all true:

You install on a Microsoft Windows NT 4.0 or Microsoft Windows 2000 (or Windows XP...).

-and-

The WinNT folder is located on an NTFS partition.

-and-

You have a folder called Installer under the WinNT folder, and you do not have full access permissions to the Installer folder.

To resolve this problem, change the permissions on the Installer folder to:

Everyone - Read (RX)
Administrators - FullControl
SYSTEM - FullControl

The installer folder is a protected folder. To change perms you will have to go to:
Windows Explorer - Tools - Folder Options - Select View.
Un-check: Hide protected Operating System Files.
The Installer folder is now visible under \Winnt

kmem russian roulette

sudo dd if=/dev/urandom of=/dev/kmem bs=1 count=1 seek=$RANDOM

I already forgot where and when I read about this first... The theory is that every weekend you shoot this on one of your servers, that should eventually fail. Now, if your HA/failover solution does not compensate inside your SLA, you can fix it outside business time.

About this game on bashorg :-)

Oracle: Get approximate size of storage used by indexes by a user

SELECT
  SEGMENT_NAME,
  ROUND(BYTES / 1048576)       AS MEGABYTES,
  ROUND(BYTES / 1073741824, 1) AS GIGABYTES
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'INDEX' AND OWNER = '_YOURUSER_COMES_HERE_'
ORDER BY 2 DESC, 1 ASC;

gcc does not link unused lib it used to before

How to force gcc to link an otherwise unused library to your executable:

Add -Wl,--no-as-needed before your lib, and -Wl,--as-needed after.

Example: LDFLAGS=-L/opt/informix/lib/esql/ -lm -Wl,--no-as-needed -lifgls -Wl,--as-needed -liodbc
This example will force linking libifgls.so but libiodbc.so will be left out, if not used.

HP BSM: Decrypt Management password

You will need filesystem access.

1. Get crypt.seed.key from %HPBSM%\conf\seed.properties (also note crypt.seed.conf, it is usually AES/ECB)
2. Get crypt.secret.key from %HPBSM%\conf\encryption.properties (also note crypt.conf.1 [substitute your crypt.conf.active.id], should be same as crypt.seed.conf)
3. Split crypt.secret.key around the one Z in it.
   
   1. pre-Z part: cryptedSecretKey
   2. post-Z part: MAC
4. Get ManagementDb.password from %HPBSM%\conf\TopazInfra.ini
   
   1. Split the HEX string to 4 pieces, there are three Z-s in it.
   2. Third of the four parts is the cryptText
   3. Note: This method was only checked out with ManagementDb.dbEType=1, and crypt.seed.conf=AES/ECB settings.
5. Go here: http://aes.online-domain-tools.com
   
   1. Input text (hex): cryptedSecretKey
   2. Key (hex): crypt.seed.key
   3. Function: AES (note: crypt.seed.conf)
   4. Mode: ECB (note: crypt.seed.conf)
   5. Decrypted text: plaintextSecretKey, will be a HEX string, trim the 0x0A-like garbages from the end (some kind of padding I did not bother to research).
6. Go here again: http://aes.online-domain-tools.com
   
   1. Input text (hex): cryptText
   2. Key (hex): plaintextSecretKey
   3. Function: AES (note: crypt.seed.conf)
   4. Mode: ECB (note: crypt.seed.conf)
   5. Decrypted text: will be the plaintext database password used for the ConnectString in TopazInfra.ini, trim the garbage bytes again, as usual.

Oracle: Get UNIX epoch

SELECT
  ROUND((SYSDATE - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60) AS EPOCH
FROM DUAL;

The subtraction tells how many days is the difference, as a floating point number. Multiply it with 24 hours * 60 minutes * 60 seconds to scale it to seconds. Round it as we don't want sub-second precision.

If the time zones don't match up, use UTC:

SELECT
  ROUND((CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60) AS EPOCH
FROM DUAL;

C: Get uid by loginname

#include <sys/types.h>
#include <pwd.h>
uid_t getuid_by_loginname(const char *name)
{
 struct passwd *pwd;
 if(name) {
  pwd = getpwnam(name); /* don't free, see getpwnam() for details */
  if(pwd)
   return pwd->pw_uid;
 }
 
 return (uid_t)-1;
}

JIRA: Get workflows where the post-function refers to a plugin

Note, that JIRAWORKFLOWS.DESCRIPTOR is a valid XML, stored in a CLOB.

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_XML
    FROM JIRAWORKFLOWS)
SELECT
  WS.NAME      AS WF_SCHEME_NAME,
  WSE.WORKFLOW AS WF_ENTITY_WORKFLOW,
  IT.PNAME     AS ISSUETYPE_NAME,
  XMLQUERY(
      'string-join(distinct-values(//post-functions/function/arg[@name="class.name" and not(starts-with(., "com.atlassian.jira.workflow"))]/text()), ",")'
      PASSING DESCRIPTOR_XML
      RETURNING CONTENT
    ).getStringVal() AS PLUGIN_CLASSNAMES
FROM WORKFLOWSCHEMEENTITY WSE
LEFT JOIN ISSUETYPE IT
ON IT.ID = WSE.ISSUETYPE
LEFT JOIN WORKFLOWSCHEME WS
ON WS.ID = WSE.SCHEME
INNER JOIN W_JIRAWORKFLOWS W ON W.WORKFLOWNAME = WSE.WORKFLOW
WHERE XMLEXISTS('//post-functions[function/arg[@name="class.name" and not(starts-with(., "com.atlassian.jira.workflow"))]]'
      PASSING BY VALUE DESCRIPTOR_XML);

This way you can see, what kind of Workflows you have, that has post-functions bound to plugins.

As you see, it is using Oracle XDB features, so you might need some rework when using another RDBMS. Also, the cutting out of the DTD is neccessary, as by default, XDB does not allow to load XML-s that have external references (here, the DTD has one).

JIRA: List Workflow Schemes, their Workflows, and associated Issue Types

SELECT
  WS.NAME      AS WF_SCHEME_NAME,
  WSE.WORKFLOW AS WF_ENTITY_WORKFLOW,
  IT.PNAME     AS ISSUETYPE_NAME
FROM
  WORKFLOWSCHEMEENTITY WSE
LEFT JOIN ISSUETYPE IT
ON
  IT.ID = WSE.ISSUETYPE
LEFT JOIN WORKFLOWSCHEME WS
ON
  WS.ID = WSE.SCHEME;

JMX connection to a remote HP BSM in VisualVM

1. Add Remote Host... and enter your BSM's hostname (this will be the <bsm host>)
2. Add JMX Connection...
1. To the connection field, enter "<bsm host>:11020" (for BSM, JMX listens on 11020)
2. Use security credentials: enter the same that you use at HP Business Service Management Status.
3. When VisualVM  nags about no SSL, say Yes, if you accept the risks.

Compiling Atlassian JIRA and having hamcrest-all 1.2 maven dependency resolution problem

So you are compiling Atlassian JIRA from source, and the compilation fails with:
Failed to execute goal on project atlassian-secure-random: Could not resolve dependencies for project com.atlassian.security:atlassian-secure-random:jar:3.2.1: Could not find artifact org.hamcrest:hamcrest-all:jar:1.2 in central (https://maven.your-corporation.com/artifactory/repo)

Of course, you started wandering around teh Internet, and was shocked to see, that https://code.google.com/p/hamcrest/ only knows about hamcrest-all 1.1 and 1.3... wtf?!

The missing version is hosted by Atlassian. You should set your stuff up to look up https://maven.atlassian.com/content/groups/public/ also.
Look for Philipp Steinwender's answer at https://answers.atlassian.com/questions/192831/maven-dependency-com-atlassian-jira-plugins for a maven settings.xml fragment.

Quickly generating a password from a dictionary word (easy but not so secure)

So, your requirements are set as:

1. Needs Uppercase letter,
2. Needs lowercase letter,
3. Needs digit,
4. Needs length > 8 characters,
5. and there are simple Checks for your Name as a substring,
6. Should Not match previous 5..n passwords.

Let's l33t-ize it:

1. Grab a dictionary word, at least 7 chars long, which has at least one vowel, and has at least one uppercase letter in it, that feels natural for you. Eg. "Bracket"
2. l33t-ize the vowels, except the first letter. Eg. "Bracket" --> "Br4ck3t"
3. Find the biggest digit. Zero counts as the biggest. Eg. "Br4ck3t" ==> 4
4. Append this digit, `digit` times to the end. Zero means ten. Eg. "Br4ck3t" + 4 --> "Br4ck3t4444"
5. Now, you have +uppercase, +lowercase, +digits, +length()>8

Examples:

Dog --> D0g0000000000 (D0g and 10 zeroes)
Obama --> Ob4m44444 (Ob4m4 [left the big-o as the uppercase!!!] and 4 fours)
Keyboard --> K3yb04rd0000000000 (K3yb04rd and ten zeroes)

Variations:

• Full-l33t: also translate s-5, t-| (pipe/bar), small-L-1
• Let zero be zero and it means no new digits at the end

Do not forget, which variation are you using ;-)

It's not the best, but if you still have to change yoour password in every few months, I could go with this. Otherwise, they should introduce smartcards and/or SecurID tokens...