Re: Something amiss in bacula-9.6.7: Possible race condition

On 1/15/21 9:54 AM, Phil Stracchino wrote:
> I will TRY to find time to look at the code and see whether I can patch
> it myself and test.  I might get time today or this weekend.

Actually, I managed to fit in a look right now.  I've now rebuilt with
the following patch:

_____

--- src/cats/sql_create.c.orig  2020-12-10 08:26:39.000000000 -0500
+++ src/cats/sql_create.c       2021-01-15 10:31:47.537191261 -0500
@@ -581,11 +581,15 @@
          return 1;
       }
       sql_free_result();
    }

-   Mmsg(cmd, "INSERT INTO Path (Path) VALUES ('%s')", esc_name);
+/**
+ * Use INSERT IGNORE or REPLACE INTO here to avoid failure in the
+ * event of a race condition between jobs
+ */
+   Mmsg(cmd, "INSERT IGNORE INTO Path (Path) VALUES ('%s')", esc_name);

    if ((ar->PathId = sql_insert_autokey_record(cmd, NT_("Path"))) == 0) {
       Mmsg2(&errmsg, _("Create db Path record %s failed. ERR=%s\n"),
          cmd, sql_strerror());
       Jmsg(jcr, M_FATAL, 0, "%s", errmsg);
@@ -1006,11 +1010,15 @@
          return ar->FilenameId > 0;
       }
       sql_free_result();
    }

-   Mmsg(cmd, "INSERT INTO Filename (Name) VALUES ('%s')", esc_name);
+/**
+ * Use INSERT IGNORE or REPLACE INTO here to avoid failure in the
+ * event of a race condition between jobs
+ */
+   Mmsg(cmd, "INSERT IGNORE INTO Filename (Name) VALUES ('%s')", esc_name);

    ar->FilenameId = sql_insert_autokey_record(cmd, NT_("Filename"));
    if (ar->FilenameId == 0) {
       Mmsg2(&errmsg, _("Create db Filename record %s failed. ERR=%s\n"),
             cmd, sql_strerror());


_____

Manual jobs are running fine.  I may have to let it run for a week or
two before I have good confidence that this works around the race condition.


Either INSERT IGNORE or REPLACE should have the same result here, but
their semantics are slightly different.

INSERT IGNORE:  If the key value already exists, do not insert the row,
do nothing.

REPLACE INTO:  If the key value already exists, delete the row and
re-insert.

When the row consists ONLY of the key value and the ID, the two are
largely functionally equivalent, but I believe that INSERT IGNORE is the
correct action here, in addition to being slightly faster.



-- 
  Phil Stracchino
  Babylon Communications
  [email protected]
  [email protected]
  Landline: +1.603.293.8485
  Mobile:   +1.603.998.6958