On 1/15/21 1:31 PM, Martin Simmons wrote:
> 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.
That is a good question. Per official documentation,
"If you use INSERT IGNORE and the row is ignored, the AUTO_INCREMENT
counter is not incremented and LAST_INSERT_ID() returns 0, which
reflects that no row was inserted."
I'm looking at sql_insert_autokey_record right now. Here's what we
currently have:
uint64_t BDB_MYSQL::sql_insert_autokey_record(const char *query, const
char *table_name)
{
BDB_MYSQL *mdb = this;
/*
* First execute the insert query and then retrieve the currval.
*/
if (mysql_query(mdb->m_db_handle, query) != 0) {
return 0;
}
mdb->m_num_rows = mysql_affected_rows(mdb->m_db_handle);
if (mdb->m_num_rows != 1) {
return 0;
}
mdb->changes++;
return mysql_insert_id(mdb->m_db_handle);
}
So basically if mysql_affected_rows() returns anything but 1,
sql_insert_autokey_record() returns 0. If it returns 1, then we call
mysql_insert_id() and return that.
This looks like we're in a bit of a quandary. If we do a direct INSERT,
and we have a duplicate because of a race condition, the INSERT fails
and the job unnecessarily fails. (And there's the issue of the separate
bug where the failed job is not terminating. I have no idea where to
even start looking for that.)
If we INSERT IGNORE or REPLACE INTO and there is no existing record,
then everything works as expected. So far, so good.
If we INSERT IGNORE and there IS an existing matching record, then the
record is NOT inserted, no error is thrown, but mysql_affected_rows()
and mysql_insert_id() both return 0, and so does
sql_insert_autokey_record().
If we REPLACE INTO and there is an existing record, then the existing
record is deleted and REPLACED mysql_affected_rows() returns 1,
mysql_insert_id() returns the new ID, and sql_insert_autokey_record()
succeeds, *BUT* the *original* PathId for that Path is now dangling.
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.)
--
Phil Stracchino
Babylon Communications
ph...@caerllewys.net
p...@co.ordinate.org
Landline: +1.603.293.8485
Mobile: +1.603.998.6958
_______________________________________________
Bacula-devel mailing list
Bacula-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-devel
