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

>>>>> On Fri, 15 Jan 2021 11:11:15 -0500, Phil Stracchino said:
> 
> 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.

Will sql_insert_autokey_record (i.e. mysql_insert_id) still return the correct
id after this?  If not, then you will get a subtle mess.

__Martin