Missing Created/Timestamp Columns Causing Problems

1 Scenario

Scenario

Timestamp fields in JEMH (other apps?) database tables contain Created / Updated fields, if null/empty this can cause problems, since since: JEMH 4.1.33 the following can be found in the log. The log and the JEMH UI will refer this page.

Likely Causes

A failed database migration or upgrade.

Example Logs

Exception: Timestamp was null:
java.lang.Exception: Timestamp was null
	at com.javahollic.jira.emh.engine.support.EMHDateUtils.formatForUserLocale(EMHDateUtils.java:314)
	at com.javahollic.jira.emh.ui.ao.bean.UIProfileBean.<init>(UIProfileBean.java:43)
	at com.javahollic.jira.emh.engine.ProfileGroupHelper$ProfileEntityGroupData.getSingleProfileBeans(ProfileGroupHelper.java:53)
	at com.javahollic.jira.emh.ui.action.JEMHProfiles.getSingleProfiles(JEMHProfiles.java:1106)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.apache.velocity.util.introspection.UberspectImpl$VelMethodImpl.doInvoke(UberspectImpl.java:385)
	at org.apache.velocity.util.introspection.UberspectImpl$VelMethodImpl.invoke(UberspectImpl.java:374)
	at com.atlassian.velocity.htmlsafe.introspection.UnboxingMethod.invoke(UnboxingMethod.java:28)
	at org.apache.velocity.runtime.parser.node.ASTMethod.execute(ASTMethod.java:270)
	at org.apache.velocity.runtime.parser.node.ASTReference.execute(ASTReference.java:262)
	at org.apache.velocity.runtime.parser.node.ASTReference.value(ASTReference.java:507)
	at org.apache.velocity.runtime.parser.node.ASTExpression.value(ASTExpression.java:71)
	at org.apache.velocity.runtime.parser.node.ASTSetDirective.render(ASTSetDirective.java:142)
	at org.apache.velocity.runtime.parser.node.SimpleNode.render(SimpleNode.java:336)
	at org.apache.velocity.runtime.directive.Parse.render(Parse.java:263)
	at org.apache.velocity.runtime.parser.node.ASTDirective.render(ASTDirective.java:175)

Fix for: Failed Database Migrations

A failed migration should be re-run to correctly migrate all the data. A broken database will cause problems with the app, is not an ‘app bug’ and will not be fixed ‘in the app’. Applying per table fixes to correct this is possible, but we wonder how many other Timestamp fields in your Jira instance are null !

Script to output the amount of null values in the “CREATED” column in JEMH tables:

Example query:

SELECT count(*) FROM public."AO_78C957_PROFILE" WHERE "CREATED" is null

Note: the most common examples of null values we have seen are in the table “AO_78C957_PROFILE”, but you can apply the above script to any table by replacing “AO_78C957_PROFILE“ with any of the values in the list below.

List of tables:

"AO_78C957_ALIASES"
"AO_78C957_AUDITCONF"
"AO_78C957_AUDITEVENTS"
"AO_78C957_AUDITMETA"
"AO_78C957_AUDITMETA_DOMAIN"
"AO_78C957_AUDITMETA_PROJECT"
"AO_78C957_AUDIT_SUMMARY"
"AO_78C957_AUTOLABELCONF"
"AO_78C957_AUTOLABELIGNORE"
"AO_78C957_AUTOLABELSUBS"
"AO_78C957_BLACKLISTING"
"AO_78C957_CATCH_ADDRESS"
"AO_78C957_CFDEFAULT"
"AO_78C957_COLONSUFXFPCONF"
"AO_78C957_CONFIG"
"AO_78C957_CSS_THEME"
"AO_78C957_DIRSET"
"AO_78C957_DIRSETLINK"
"AO_78C957_EML_ADDR_TRCKR"
"AO_78C957_ENABLED_FEATURES"
"AO_78C957_ENABLED_MSG_FILTERS"
"AO_78C957_FIELDPROC"
"AO_78C957_HEALTHCHECK"
"AO_78C957_INSTALL_HISTORY"
"AO_78C957_LDAPCONF"
"AO_78C957_LIMIT"
"AO_78C957_LSNR_CONF"
"AO_78C957_LSNR_JPROJ_TMPL"
"AO_78C957_LSNR_JSDPROJ_TMPL"
"AO_78C957_LSNR_PROJ"
"AO_78C957_LSNR_PROJ_TMPL"
"AO_78C957_LSNR_TRANPRJTMPL"
"AO_78C957_LSNR_TRANSPRT"
"AO_78C957_NAGCONFIG"
"AO_78C957_NAGPHRASE"
"AO_78C957_NAGPHRASESET"
"AO_78C957_NOTIF_RECIPIENT"
"AO_78C957_PM_CLN_REGEXP"
"AO_78C957_PM_DLM_REGEXP"
"AO_78C957_PM_EMAIL_CONFIG"
"AO_78C957_PRE_PROC_TASK"
"AO_78C957_PRJCFDEFAULT"
"AO_78C957_PRJDOMMAP"
"AO_78C957_PRJGRPMAP"
"AO_78C957_PRJKYWRDMAP"
"AO_78C957_PRJSCRIPTMAP"
"AO_78C957_PROFILE"
"AO_78C957_PROFILE_AUDIT"
"AO_78C957_PROJCONFIG"
"AO_78C957_PROPERTY"
"AO_78C957_REGEXPPROCCNF"
"AO_78C957_REGEXPPROCMAP"
"AO_78C957_SCRIPTFPCONF"
"AO_78C957_STATIC_RESOURCE"
"AO_78C957_STATUS_NTFYCNF"
"AO_78C957_SUPPORT"
"AO_78C957_TEMPLATESET"
"AO_78C957_TEMPLATESET_ORDER"
"AO_78C957_TEMPLATE_HISTORY"
"AO_78C957_TEMPLATE_SET_PRJLNK"
"AO_78C957_TESTCASE"
"AO_78C957_TRANSPORT_ITEM"
"AO_78C957_USEREVENTCNF"
"AO_78C957_USERSIGNUP"
"AO_78C957_USG_HST_CURR"
"AO_78C957_USG_HST_DAY"
"AO_78C957_USG_HST_HOUR"
"AO_78C957_USG_HST_MONTH"
"AO_78C957_WFRULE"