Bug 21219

Summary:	NULL values in updated_on field break the Patrons REST API
Product:	Koha	Reporter:	Pete Edwards <koha>
Component:	REST API	Assignee:	Tomás Cohen Arazi (tcohen) <tomascohen>
Status:	CLOSED INVALID	QA Contact:
Severity:	major
Priority:	P5 - low	CC:	jonathan.druart, m.de.rooy, tomascohen
Version:	Main
Hardware:	All
OS:	All
See Also:	https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16330 https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17651 https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21402
Change sponsored?:	---	Patch complexity:	---
Documentation contact:		Documentation submission:
Text to go in the release notes:		Version(s) released in:
Circulation function:

Description Pete Edwards 2018-08-14 14:28:19 UTC

The swagger definition does not allow for updated_on to be NULL but the database does allow this. If a patron has a null value then the REST service call fails.

The history of this is as follows:
Bug #10459 (released in 16.05.01) - updated_on field added to borrowers table, NULL allowed

Bug #17651 (released in 16.11) - fix swagger definition to allow for NULL 
    "updated_on": {
-      "type": "string",
+      "type": ["string", "null"],
      "description": "time of last change could be useful for synchronization with external systems (among others)"
    },

Bug #16330 (released in 18.05) - removes the null option from the definition
  "updated_on": {
-      "type": ["string", "null"],
+      "type": "string",
      "format": "date-time",
      "description": "time of last change could be useful for synchronization with external systems (among others)"
    },

You can see that the last push undid the previous bug fix.

Comment 1 Tomás Cohen Arazi (tcohen) 2018-08-16 13:22:50 UTC

I looked at all the branches down to 16.05.x and they all seem to have NOT NULL on the table definition. Maybe this is an update process bug.

Comment 2 Pete Edwards 2018-09-01 16:36:20 UTC

I did wonder whether it could be an update issue but I couldn't find any evidence of a script that would have set a value on the column for existing records when the column was added. Does simply changing the column definition to "NOT NULL" cause the default timestamp to be set - if so I really don't understand why it was created with "NULL".

Obviously, this leaves me concerned that there could be other other discrepancies - is there an easy way to compare my actual schema to the one the should be installed - I installed v18.05 but my database has the column defined as:

  `updated_on` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

Thanks

Comment 3 Jonathan Druart 2018-12-07 14:30:41 UTC

(In reply to Pete Edwards from comment #2)
> I installed v18.05 but my database has the column
> defined as:
> 
>   `updated_on` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
> CURRENT_TIMESTAMP,

borrowers.updated_on has been added by bug 10459, and it was already NOT NULL.

Did you mean you did a fresh 18.05(.00?) install and you got this table definition?