This page constitutes random notes from my work day as an Atlassian product consultant, put up in the vague hope they might benefit others. Expect rambling, reference to unsolved problems, and plenty of stacktraces. Check the date as any information given is likely to be stale.
I spend plenty of time poking around in Jira databases. Something that has always mildy annoyed me is the proliferation of the AO_ tables that plugins create:
redradish_jira=#
List of relations
┌────────┬─────────────────────────────────────────────┬──────────┬────────────────┐
│ Schema │ Name │ Type │ Owner │
├────────┼─────────────────────────────────────────────┼──────────┼────────────────┤
│ public │ AO_013613_ACTIVITY_SOURCE │ table │ redradish_jira │
│ public │ AO_013613_ACTIVITY_SOURCE_ID_seq │ sequence │ redradish_jira │
│ public │ AO_013613_EXPENSE │ table │ redradish_jira │
│ public │ AO_013613_EXPENSE_ID_seq │ sequence │ redradish_jira │
│ public │ AO_013613_EXP_CATEGORY │ table │ redradish_jira │
│ public │ AO_013613_EXP_CATEGORY_ID_seq │ sequence │ redradish_jira │
....
└────────┴─────────────────────────────────────────────┴──────────┴────────────────┘
(1318 rows)
Of those 1318 tables (!?), 1116 begin with AO_.
I know these AO_ tables are associated with plugins, but I have no idea (short of Google searching) which plugin generated which tables.
Furthermore every search involving these tables requires quoting the table name and column names, because they inexplicably needed to be uppercase.
To make life easier, I've create a project that automatically creates nicely named views on top of the AO tables:
https://github.com/redradishtech/activeobject_views
I can now see my Jira database contains tables from 56 plugins:
redradish_jira=# \dn
List of schemas
┌────────────────────┬────────────────┐
│ Name │ Owner │
├────────────────────┼────────────────┤
│ agile │ redradish_jira │
│ agilepoker │ redradish_jira │
│ api │ redradish_jira │
│ atlnotifications │ redradish_jira │
│ automation │ redradish_jira │
│ backbonesync │ redradish_jira │
│ betterpdf │ redradish_jira │
│ configmanagercore │ redradish_jira │
│ dvcs │ redradish_jira │
│ dynaforms │ redradish_jira │
│ groovy │ redradish_jira │
│ hipchat │ redradish_jira │
│ inform │ redradish_jira │
│ issueactionstodo │ redradish_jira │
│ jeditor │ redradish_jira │
│ jeti │ redradish_jira │
│ jiradevint │ redradish_jira │
│ jiradiagnostics │ redradish_jira │
│ jiraemailprocessor │ redradish_jira │
│ jirainvite │ redradish_jira │
│ jiramail │ redradish_jira │
│ jiramobile │ redradish_jira │
│ jiraoptimizer │ redradish_jira │
│ jiraprojects │ redradish_jira │
│ jiratranstrigger │ redradish_jira │
│ jirawebhooks │ redradish_jira │
│ jmcf │ redradish_jira │
│ jqlt │ redradish_jira │
│ jsd │ redradish_jira │
│ jsu │ redradish_jira │
│ kepler │ redradish_jira │
│ labelmanager │ redradish_jira │
│ navlinks │ redradish_jira │
│ portfolio │ redradish_jira │
│ portfolioteam │ redradish_jira │
│ projtemplates │ redradish_jira │
│ public │ postgres │
│ queries │ redradish_jira │
│ saml │ redradish_jira │
│ securelogin │ redradish_jira │
│ servicerocket │ redradish_jira │
│ sil │ redradish_jira │
│ simpletasklists │ redradish_jira │
│ simplifiedplanner │ redradish_jira │
│ startwork │ redradish_jira │
│ streams │ redradish_jira │
│ structure │ redradish_jira │
│ support │ redradish_jira │
│ tempo │ redradish_jira │
│ tempo2 │ redradish_jira │
│ tempoplanner │ redradish_jira │
│ timedpromise │ redradish_jira │
│ webhooks │ redradish_jira │
│ whitelist │ redradish_jira │
│ workhours │ redradish_jira │
│ xporter │ redradish_jira │
└────────────────────┴────────────────┘
(56 rows)
If I want to see tables for a specific plugin, I can limit psql to just the plugin's schema:
redradish_jira=# set search_path=tempo;
SET
redradish_jira=# \d
List of relations
┌────────┬─────────────────────┬──────┬────────────────┐
│ Schema │ Name │ Type │ Owner │
├────────┼─────────────────────┼──────┼────────────────┤
│ tempo │ account_v1 │ view │ redradish_jira │
│ tempo │ activity_source │ view │ redradish_jira │
│ tempo │ budget │ view │ redradish_jira │
│ tempo │ category_type │ view │ redradish_jira │
│ tempo │ category_v1 │ view │ redradish_jira │
│ tempo │ customer_permission │ view │ redradish_jira │
│ tempo │ customer_v1 │ view │ redradish_jira │
│ tempo │ exp_category │ view │ redradish_jira │
│ tempo │ expense │ view │ redradish_jira │
│ tempo │ favorites │ view │ redradish_jira │
│ tempo │ hd_scheme │ view │ redradish_jira │
│ tempo │ hd_scheme_day │ view │ redradish_jira │
│ tempo │ hd_scheme_member │ view │ redradish_jira │
│ tempo │ internal_issue │ view │ redradish_jira │
│ tempo │ link_v1 │ view │ redradish_jira │
│ tempo │ location │ view │ redradish_jira │
│ tempo │ membership │ view │ redradish_jira │
│ tempo │ permission_group │ view │ redradish_jira │
│ tempo │ pgp_group │ view │ redradish_jira │
│ tempo │ pgp_group_to_team │ view │ redradish_jira │
│ tempo │ pgp_group_v2 │ view │ redradish_jira │
│ tempo │ pgp_member │ view │ redradish_jira │
│ tempo │ pgp_member_v2 │ view │ redradish_jira │
│ tempo │ pgp_permission │ view │ redradish_jira │
│ tempo │ pgp_permission_v2 │ view │ redradish_jira │
│ tempo │ program │ view │ redradish_jira │
│ tempo │ project_config │ view │ redradish_jira │
│ tempo │ rate │ view │ redradish_jira │
│ tempo │ rate_table │ view │ redradish_jira │
│ tempo │ saved_report │ view │ redradish_jira │
│ tempo │ saved_report_v2 │ view │ redradish_jira │
│ tempo │ team │ view │ redradish_jira │
│ tempo │ team_link │ view │ redradish_jira │
│ tempo │ team_member │ view │ redradish_jira │
│ tempo │ team_member_v2 │ view │ redradish_jira │
│ tempo │ team_permission │ view │ redradish_jira │
│ tempo │ team_role │ view │ redradish_jira │
│ tempo │ team_to_member │ view │ redradish_jira │
│ tempo │ team_v2 │ view │ redradish_jira │
│ tempo │ user_index │ view │ redradish_jira │
│ tempo │ user_location │ view │ redradish_jira │
│ tempo │ wa_sl_value │ view │ redradish_jira │
│ tempo │ wa_value │ view │ redradish_jira │
│ tempo │ wl_scheme │ view │ redradish_jira │
│ tempo │ wl_scheme_day │ view │ redradish_jira │
│ tempo │ wl_scheme_member │ view │ redradish_jira │
│ tempo │ work_attribute │ view │ redradish_jira │
└────────┴─────────────────────┴──────┴────────────────┘
(47 rows)
I hope this helps fellow SQL hackers out there!
This page constitutes random notes from my work day as an Atlassian product consultant, put up in the vague hope they might benefit others. Expect rambling, reference to unsolved problems, and plenty of stacktraces. Check the date as any information given is likely to be stale.
TLSv1.3 is relatively new and shiny, as fundamental web protocols go, and TLS stacks are still working out the bugs. In my experience:
- Back in April I had a client whose Jira connections to Slack would hang soon after server start.
This turned out to be caused by a TLS 1.3 bug in Java 11. Jira loses track of the TCP connection state (leaving the TCP connection in CLOSE-WAIT forever) and blocks, thereby blocking the whole webhook thread pool:https://jira.atlassian.com/browse/JRASERVER-70780
https://jira.atlassian.com/browse/JRASERVER-70189
This was triggered by some change on Slack's end. Perhaps Slack switched their default from TLS 1.2 to 1.3 when they removed older TLS versions the previous month. Same client, 4 months later, suddenly experienced complete failure of Crowd to authenticate users against their LDAP.
The relevant line in the stacktrace is:Caused by: org.springframework.ldap.CommunicationException: ldap.phx7.<redacted>.com:636; nested exception is javax.naming.CommunicationException: ldap.phx7.<redacted>.com:636 [Root exception is javax.net.ssl.SSLHandshakeException: extension (5) should not be presented in certificate_request]
Once again some hurried Googling suggested this is a TLSv1.3 problem.
In both cases the solution was to avoid TLSv1.3 for now, by setting the Java flag:
-Djdk.tls.client.protocols=TLSv1.2
The first bug might also be avoidable by upgrading to Java 11.0.8 or higher. I haven't tested that yet though.
For now, I'm setting that flag in all instances I administer, until TLS servers and clients get their act together.