[BUG REPORT] HRM 3.9.0 database update fails

[grzybeks]

Describe the bug Database update in HRM 3.9.0 fails when there are data in the shared_task_parameter table which need to be changed.

To Reproduce Have a pre HRM 3.9.0 database, e.g. HRM 3.8.0 with data in shared_task_parameter table for QualityChangeStoppingCriterion and / or NumberOfIterations.

Expected behavior Database update completes without error.

Environment Server-side Ubuntu 22.04.3, PHP 8.1, MySQL 5.7.43 (on remote server).

Client-side N/A

Additional context The dbupdate.php has bugs for updating to DB version 20 when data in the shared_task_parameter needs updating. Likely a cut-and-paste error of the code for the task_parameter, not accounting for the difference in table structure. See patch proposed below. However, it was not tested and necessary changes were done on SQL level.

It would likely be much more efficient and safer doing the desired change entirely on the database side. Safer, because you do delete + insert not as a single transaction. If the insert fails, the deleted data is already gone and cannot be reconstructed. More efficient, as the change can be handled in only one SQL update statement. Example for only one of the four update cases:

update shared_task_parameter set value=concat_ws('#',concat('#',value),value,value,value,value,value) WHERE name = 'NumberOfIterations' and substr(value,1,1) != '#';

Proposed patch (untested):

$ diff -C2 dbupdate.php{,.orig}
*** dbupdate.php        2023-11-15 16:37:28.000000000 +0100
--- dbupdate.php.orig   2023-07-14 12:26:15.000000000 +0200
***************
*** 6688,6692 ****

              # Transform "<val>" to "#<val>#<val>#<val>#<val>#<val>#<val>".
!             $quality = $row[5];

              # If the first character is a '#' the change has already been
--- 6688,6692 ----

              # Transform "<val>" to "#<val>#<val>#<val>#<val>#<val>#<val>".
!             $quality = $row[3];

              # If the first character is a '#' the change has already been
***************
*** 6698,6708 ****
              $qualityArray = array_fill(0, $maxCh, $quality);
              $qualityArray = array_merge(array(null), $qualityArray);
!             $row[5] = implode('#', $qualityArray);

              # Delete old entry.
              if (!$db->Execute("DELETE FROM " . $tabname .
!                               " WHERE owner='" . $row[2] .
!                               "' AND setting='" . $row[3] .
!                               "' AND name='" . $row[4] . "'")) {
                  $msg = "An error occurred while updating " .
                      "the database to revision " . $n . ".";
--- 6698,6708 ----
              $qualityArray = array_fill(0, $maxCh, $quality);
              $qualityArray = array_merge(array(null), $qualityArray);
!             $row[3] = implode('#', $qualityArray);

              # Delete old entry.
              if (!$db->Execute("DELETE FROM " . $tabname .
!                               " WHERE owner='" . $row[0] .
!                               "' AND setting='" . $row[1] .
!                               "' AND name='" . $row[2] . "'")) {
                  $msg = "An error occurred while updating " .
                      "the database to revision " . $n . ".";
***************
*** 6793,6797 ****

              # Transform "<val>" to "#<val>#<val>#<val>#<val>#<val>#<val>".
!             $iterations = $row[5];

              # If the first character is a '#' the change has already been
--- 6793,6797 ----

              # Transform "<val>" to "#<val>#<val>#<val>#<val>#<val>#<val>".
!             $iterations = $row[3];

              # If the first character is a '#' the change has already been
***************
*** 6803,6813 ****
              $iterationsArray = array_fill(0, $maxCh, $iterations);
              $iterationsArray = array_merge(array(null), $iterationsArray);
!             $row[5] = implode('#', $iterationsArray);

              # Delete old entry.
              if (!$db->Execute("DELETE FROM " . $tabname .
!                               " WHERE owner='" . $row[2] .
!                               "' AND setting='" . $row[3] .
!                               "' AND name='" . $row[4] . "'")) {
                  $msg = "An error occurred while updating " .
                      "the database to revision " . $n . ".";
--- 6803,6813 ----
              $iterationsArray = array_fill(0, $maxCh, $iterations);
              $iterationsArray = array_merge(array(null), $iterationsArray);
!             $row[3] = implode('#', $iterationsArray);

              # Delete old entry.
              if (!$db->Execute("DELETE FROM " . $tabname .
!                               " WHERE owner='" . $row[0] .
!                               "' AND setting='" . $row[1] .
!                               "' AND name='" . $row[2] . "'")) {
                  $msg = "An error occurred while updating " .
                      "the database to revision " . $n . ".";

[KevinNamink]

Hi,

Sorry for the late reply, I had a severe cold.

This issue is known, in commit github.com/aarpon/hrm/commit/436a5cf944ab0e4b50fb1cb3be108a0df8509278 it has been fixed. We took a chance by not widely announcing this fix.

I'm glad you figured it out on your own so soon.

[grzybeks]

Hello Kevin,

Thank you for the follow up. I worked with the hrm_3.9.0.zip installation file and not with the GitHub repository directly. The zip file does not include the fix. Maybe you should create a 3.9.1 release and update the documentation?

Thanks, Stefan

From: Kevin @.> Sent: Monday, November 20, 2023 09:37 To: aarpon/hrm @.> Cc: Grzybek, Stefan @.>; Author @.> Subject: Re: [aarpon/hrm] [BUG REPORT] HRM 3.9.0 database update fails (Issue #22)

Hi,

Sorry for the late reply, I had a severe cold.

This issue is known, in commit github.com/aarpon/hrm/commit/436a5cf944ab0e4b50fb1cb3be108a0df8509278 it has been fixed. We took a chance by not widely announcing this fix.

I'm glad you figured it out on your own so soon.

— Reply to this email directly, view it on GitHubhttps://github.com/aarpon/hrm/issues/22#issuecomment-1818457686, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ACUEAPKCJHCFRDGGRWFWLK3YFMJCBAVCNFSM6AAAAAA7OBSEC6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMJYGQ2TONRYGY. You are receiving this because you authored the thread.Message ID: @.**@.>>