Licensing, Expressions: column 'lastUpdateDate' cannot be null

[t4k]

I'm posting the contents of my email to the coral-users list because I think it's a legitimate issue after investigating further. https://lists.coral-erm.org/pipermail/coral-user/2019-February/000506.html

We are getting an error when trying to save an Expression on a license.

There was a problem with the database: Column 'lastUpdateDate' cannot be null

From what I can tell in the code, this makes sense…

coral/licensing/install/protected/install.sql

Line 51 in 9752600

`lastUpdateDate` timestamp NOT NULL default CURRENT_TIMESTAMP,

declares that the column in the db cannot be NULL.

coral/licensing/admin/classes/common/DatabaseObject.php

Lines 223 to 224 in 9752600

	if ($value == '' || !isset($value)) {
	$value = "NULL";

sets the value of the attribute to NULL if it is sent with nothing.

coral/licensing/ajax_processing.php

Lines 259 to 303 in 9752600

	case 'submitExpression':
	//if expressionID is sent then this is an update
	if ((isset($_POST['expressionID'])) && ($_POST['expressionID'] != '')){
	$expressionID = $_POST['expressionID'];
	$expression = new Expression(new NamedArguments(array('primaryKey' => $expressionID)));
	}else{
	$expression = new Expression();
	//default production use (terms tool indicator) to off if this is an add, otherwise we leave it alone
	$expression->productionUseInd = 0;
	$expression->expressionID = '';
	}
	$expression->documentText = $_POST['documentText'];
	$expression->documentID = $_POST['documentID'];
	$expression->expressionTypeID = $_POST['expressionTypeID'];
	$expression->productionUseInd = '0';
	$expression->simplifiedText = '';
	try {
	$expression->save();
	if (!$expressionID){
	$expressionID=$expression->primaryKey;
	}
	//first remove all qualifiers, then we'll add them back
	$expression->removeQualifiers();
	foreach (explode(',', $_POST['qualifiers']) as $id){
	if ($id){
	$expressionQualifierProfile = new ExpressionQualifierProfile();
	$expressionQualifierProfile->expressionID = $expressionID;
	$expressionQualifierProfile->qualifierID = $id;
	$expressionQualifierProfile->save();
	}
	}
	} catch (Exception $e) {
	echo $e->getMessage();
	}
	break;

does not set lastUpdateDate anywhere.

I'm puzzled why I cannot reproduce the error on any test instances, only on our production instance.

The production instance is using:
PHP Version 7.0.33-0ubuntu0.16.04.1
MySQL Server version: 5.6.40-log Source distribution (AWS RDS)
Apache/2.4.18 (Ubuntu)

[t4k]

I have discovered what the problem is for our setup.

AWS RDS MySQL sets the explicit_defaults_for_timestamp option to TRUE in its default parameter group for MySQL 5.6. It can only be changed by someone going in and adjusting a custom parameter for that setting to FALSE and restarting the database service. (See: https://forums.aws.amazon.com/thread.jspa?threadID=132676)

Although this is an edge case, I believe the CORAL code can be improved to overcome it. It is very possible that someone may come along and attempt to use AWS RDS with MySQL 5.6 with default settings and hit the same problem as I did.