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

>>>>> On Fri, 15 Jan 2021 14:18:32 -0500, Phil Stracchino said:
> 
> The proper way to fix this is as follows:
> 
> INSERT IGNORE into the table.
> If mysql_affected_rows() returns 1, i.e we inserted exactly one row;
> return mysql_insert_id(), which will be correct.
> If mysql_affected_rows() returns 0, then the INSERT was ignored because
> the Path already exists.  In this case mysql_insert_id() will be wrong,
> so return SELECT PathId FROM Path WHERE Path = 'the path we just tried
> to insert but found it already existed'.
> 
> This should return the correct PathId in all cases, whether it is a
> newly inserted row or the previously existing row, UNLESS the INSERT
> failed for some *other* reason than the Path already existing, in which
> case we *should legitimately* fail.  (Possibly after retrying.)

Looks reasonable to me.  (*)

If the INSERT failed for some other reason then the original mysql_query()
should have failed rather than mysql_affected_rows() returning 0.  I think if
a subsequent SELECT returns a PathId then it would be safe to assume it is
correct, regardless of any error in the INSERT.

(*) Except that there is the still an outstanding mystery about why this only
seems to happen repeatably for you and I think someone asked for more
debugging info about why you have concurrent updates to the db at all.

__Martin