[Bug 27253] New: borrowers.updated_on cannot be null on fresh install, but can be null with upgrade
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Bug ID: 27253 Summary: borrowers.updated_on cannot be null on fresh install, but can be null with upgrade Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Architecture, internals, and plumbing Assignee: koha-bugs@lists.koha-community.org Reporter: kyle@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org Simply put, the borrowers table column updated_on in kohastructure.sql specifies that it cannot be null, but the updatedatabase.pl it can. It appears the NOT got lost when the column as renamed 'updated_on' from 'timestamp' in a followup on bug 10459. Fixing it is easy, the question is, what do we do with currently NULL updated_on's? My though is to set them to the unix epoch ( 1970-01-01 ). -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |10459 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=10459 [Bug 10459] borrowers should have a timestamp -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |kyle@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@bugs.koha-c | |ommunity.org --- Comment #1 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- What about MAX(date_renewed, dateenrolled, last_seen)? -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 --- Comment #2 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to Jonathan Druart from comment #1)
What about MAX(date_renewed, dateenrolled, last_seen)?
+1 -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Julian Maurice <julian.maurice@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |julian.maurice@biblibre.com |ity.org | CC| |julian.maurice@biblibre.com Status|NEW |ASSIGNED -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 --- Comment #3 from Julian Maurice <julian.maurice@biblibre.com> --- Created attachment 132819 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=132819&action=edit Bug 27253: Fix definition of updated_on in borrowers and deletedborrowers Test plan: 1. First you have to be in a state where updated_on is NULL-able. You can do that by either: a) do a fresh install of Koha 16.05 and update to master, or b) execute the following SQL queries: ALTER TABLE borrowers MODIFY updated_on timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'time of last change could be useful for synchronization with external systems (among others)' ALTER TABLE deletedborrowers MODIFY updated_on timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'time of last change could be useful for synchronization with external systems (among others)' 2. Create two borrowers (let's name them X and Y) 3. Delete borrower Y 4. Set the column updated_on to NULL for both borrowers by executing the following SQL query: UPDATE borrowers SET updated_on = NULL WHERE borrowernumber = <borrowernumber of X> UPDATE deletedborrowers SET updated_on = NULL WHERE borrowernumber = <borrowernumber of Y> 5. Apply patch and run updatedatabase 6. Verify that borrowers.updated_on and deletedborrowers.updated_on are not NULL-able. Verify that updated_on for X and Y have taken the value of dateenrolled. 7. Repeat step 2 to 6 but this time renew the patron and/or log in with its account in order to set the columns borrowers.date_renewed and borrowers.lastseen before executing updatedatabase borrowers.updated_on should take the greatest value among dateenrolled, date_renewed, and lastseen -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Julian Maurice <julian.maurice@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Owen Leonard <oleonard@myacpl.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Owen Leonard <oleonard@myacpl.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #132819|0 |1 is obsolete| | --- Comment #4 from Owen Leonard <oleonard@myacpl.org> --- Created attachment 132934 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=132934&action=edit Bug 27253: Fix definition of updated_on in borrowers and deletedborrowers Test plan: 1. First you have to be in a state where updated_on is NULL-able. You can do that by either: a) do a fresh install of Koha 16.05 and update to master, or b) execute the following SQL queries: ALTER TABLE borrowers MODIFY updated_on timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'time of last change could be useful for synchronization with external systems (among others)' ALTER TABLE deletedborrowers MODIFY updated_on timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'time of last change could be useful for synchronization with external systems (among others)' 2. Create two borrowers (let's name them X and Y) 3. Delete borrower Y 4. Set the column updated_on to NULL for both borrowers by executing the following SQL query: UPDATE borrowers SET updated_on = NULL WHERE borrowernumber = <borrowernumber of X> UPDATE deletedborrowers SET updated_on = NULL WHERE borrowernumber = <borrowernumber of Y> 5. Apply patch and run updatedatabase 6. Verify that borrowers.updated_on and deletedborrowers.updated_on are not NULL-able. Verify that updated_on for X and Y have taken the value of dateenrolled. 7. Repeat step 2 to 6 but this time renew the patron and/or log in with its account in order to set the columns borrowers.date_renewed and borrowers.lastseen before executing updatedatabase borrowers.updated_on should take the greatest value among dateenrolled, date_renewed, and lastseen Signed-off-by: Owen Leonard <oleonard@myacpl.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |m.de.rooy@rijksmuseum.nl QA Contact|testopia@bugs.koha-communit |m.de.rooy@rijksmuseum.nl |y.org | --- Comment #5 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- QA: Looking here -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 --- Comment #6 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Kyle M Hall from comment #0)
Simply put, the borrowers table column updated_on in kohastructure.sql specifies that it cannot be null, but the updatedatabase.pl it can. It appears the NOT got lost when the column as renamed 'updated_on' from 'timestamp' in a followup on bug 10459.
Fixing it is easy, the question is, what do we do with currently NULL updated_on's? My though is to set them to the unix epoch ( 1970-01-01 ).
(In reply to Jonathan Druart from comment #1)
What about MAX(date_renewed, dateenrolled, last_seen)?
I think it is an arbitrary choice. For my part NOW() would have been good either (and much easier). After all, we are updating those borrowers .. now, right? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 --- Comment #7 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- $DBversion = "16.06.00.002"; if ( CheckVersion($DBversion) ) { unless ( column_exists('borrowers', 'updated_on') ) { $dbh->do(q{ ALTER TABLE borrowers ADD COLUMN updated_on timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER privacy_guarantor_checkouts; }); $dbh->do(q{ ALTER TABLE deletedborrowers ADD COLUMN updated_on timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER privacy_guarantor_checkouts; -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Patch complexity|--- |Small patch Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #132934|0 |1 is obsolete| | --- Comment #8 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Created attachment 133342 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=133342&action=edit Bug 27253: Fix definition of updated_on in borrowers and deletedborrowers Test plan: 1. First you have to be in a state where updated_on is NULL-able. You can do that by either: a) do a fresh install of Koha 16.05 and update to master, or b) execute the following SQL queries: ALTER TABLE borrowers MODIFY updated_on timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'time of last change could be useful for synchronization with external systems (among others)' ALTER TABLE deletedborrowers MODIFY updated_on timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'time of last change could be useful for synchronization with external systems (among others)' 2. Create two borrowers (let's name them X and Y) 3. Delete borrower Y 4. Set the column updated_on to NULL for both borrowers by executing the following SQL query: UPDATE borrowers SET updated_on = NULL WHERE borrowernumber = <borrowernumber of X> UPDATE deletedborrowers SET updated_on = NULL WHERE borrowernumber = <borrowernumber of Y> 5. Apply patch and run updatedatabase 6. Verify that borrowers.updated_on and deletedborrowers.updated_on are not NULL-able. Verify that updated_on for X and Y have taken the value of dateenrolled. 7. Repeat step 2 to 6 but this time renew the patron and/or log in with its account in order to set the columns borrowers.date_renewed and borrowers.lastseen before executing updatedatabase borrowers.updated_on should take the greatest value among dateenrolled, date_renewed, and lastseen Signed-off-by: Owen Leonard <oleonard@myacpl.org> Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |30486 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30486 [Bug 30486] [OMNIBUS] Synchronize database schema with (older) database revisions -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |tomascohen@gmail.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 --- Comment #9 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- FROM_UNIXTIME(0) does not seem to work MariaDB [koha_kohadev]> update borrowers set updated_on="1970-01-01 00:00:01" where borrowernumber=1; Query OK, 1 row affected (0.011 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [koha_kohadev]> update borrowers set updated_on="1970-01-01 00:00:00" where borrowernumber=1; Query OK, 1 row affected, 1 warning (0.012 sec) Rows matched: 1 Changed: 1 Warnings: 1 MariaDB [koha_kohadev]> select updated_on from borrowers where borrowernumber=1; +---------------------+ | updated_on | +---------------------+ | 0000-00-00 00:00:00 | +---------------------+ 1 row in set (0.001 sec) MariaDB [koha_kohadev]> show warnings; +---------+------+-----------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------+ | Warning | 1264 | Out of range value for column 'updated_on' at row 1 | +---------+------+-----------------------------------------------------+ 1 row in set (0.000 sec) MariaDB [koha_kohadev]> update borrowers set updated_on="1970-01-01 00:00:01" where borrowernumber=1; Query OK, 1 row affected (0.011 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 --- Comment #10 from Owen Leonard <oleonard@myacpl.org> --- I get this error: ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Incorrect datetime value: '1970-01-01 00:00:00' for column `koha_kohadev`.`borrowers`.`updated_on` at row 4739 at /kohadevbox/koha/C4/Installer.pm line 738 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to master Version(s)| |22.05.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 --- Comment #11 from Fridolin Somers <fridolin.somers@biblibre.com> --- Pushed to master for 22.05, thanks to everybody involved 🦄 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |fridolin.somers@biblibre.co | |m --- Comment #12 from Fridolin Somers <fridolin.somers@biblibre.com> --- (In reply to Fridolin Somers from comment #11)
Pushed to master for 22.05, thanks to everybody involved 🦄
Sorry I forgot yesterday to update status -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 --- Comment #13 from Fridolin Somers <fridolin.somers@biblibre.com> --- May I revert ? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 --- Comment #14 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Hi, we recently had some issues with updated timestamps on updates. Can you confirm that this will only change records where the updated_on was NULL before? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 --- Comment #15 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Katrin Fischer from comment #14)
Hi,
we recently had some issues with updated timestamps on updates. Can you confirm that this will only change records where the updated_on was NULL before?
+ WHERE updated_on IS NULL -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 --- Comment #16 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Reading helps - thx Marcel. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 --- Comment #17 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- MariaDB [koha_myclone]> update borrowers set updated_on=COALESCE( NULL, FROM_UNIXTIME(0) ) where borrowernumber=51; ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:00:00' for column `koha_myclone`.`borrowers`.`updated_on` at row 1 MariaDB [koha_myclone]> update borrowers set updated_on=COALESCE( NULL, FROM_UNIXTIME(1) ) where borrowernumber=51; Query OK, 1 row affected (0.008 sec) Rows matched: 1 Changed: 1 Warnings: 0 So I would propose to switch to UNIXTIME(1). Note that I would consider this a bug in SQL. Will add a follow-up -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 --- Comment #18 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Created attachment 133735 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=133735&action=edit Bug 27253: (follow-up) Fix UNIXTIME(0) in db_revs/211200037.pl MariaDB [koha_myclone]> update borrowers set updated_on=COALESCE( NULL, FROM_UNIXTIME(0) ) where borrowernumber=51; ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:00:00' for column `koha_myclone`.`borrowers`.`updated_on` at row 1 MariaDB [koha_myclone]> update borrowers set updated_on=COALESCE( NULL, FROM_UNIXTIME(1) ) where borrowernumber=51; Query OK, 1 row affected (0.008 sec) Rows matched: 1 Changed: 1 Warnings: 0 Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Tested by switching updated_on to datetime. Remove NOT NULL, etc. Copied dbrev to atomicupdate folder. Resulted in: Updated all NULL values of borrowers.updated_on to GREATEST(date_renewed, dateenrolled, lastseen): 51 rows updated -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to master |ASSIGNED -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 --- Comment #19 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Fridolin Somers from comment #13)
May I revert ?
Push the follow-up please. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 --- Comment #20 from Julian Maurice <julian.maurice@biblibre.com> --- (In reply to Marcel de Rooy from comment #17)
So I would propose to switch to UNIXTIME(1). Note that I would consider this a bug in SQL.
It's weird that '1970-01-01 00:00:00' is not a valid timestamp but it's not really a bug IMO. MySQL documentation says that: The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. https://dev.mysql.com/doc/refman/8.0/en/datetime.html -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 --- Comment #21 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Julian Maurice from comment #20)
(In reply to Marcel de Rooy from comment #17)
So I would propose to switch to UNIXTIME(1). Note that I would consider this a bug in SQL.
It's weird that '1970-01-01 00:00:00' is not a valid timestamp but it's not really a bug IMO. MySQL documentation says that:
The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
Ok no bug, inconsistency then :) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 --- Comment #22 from Fridolin Somers <fridolin.somers@biblibre.com> --- (In reply to Marcel de Rooy from comment #19)
(In reply to Fridolin Somers from comment #13)
May I revert ?
Push the follow-up please.
Done -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to master -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to master |Pushed to stable Version(s)|22.05.00 |22.05.00,21.11.06 released in| | --- Comment #23 from Kyle M Hall <kyle@bywatersolutions.com> --- Pushed to 21.11.x for 21.11.06 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27253 Victor Grousset/tuxayo <victor@tuxayo.net> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to stable |RESOLVED Resolution|--- |FIXED CC| |victor@tuxayo.net --- Comment #24 from Victor Grousset/tuxayo <victor@tuxayo.net> --- Not backported to oldoldstable (21.05.x). Feel free to ask if it's needed. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org