ILIAS
ILIAS copied to clipboard
ILIAS-eLearning
Memberships: Prevent integrity constraint issues
If you monitor the log of ILIAS installations with many concurrent users, you often find dozens of integrity constraint violations for the object_members table.
These duplicate key issues are caused by race conditons. This commit makes the state modifying SQL statements more robust and prevents that exceptions or WSOD (white screen of death) are shown to the user. I added a ON DUPLICATE KEY UPDATE clause (possible, because we do not support Oracle or Postgres anymore) which updates only the relevant field(s) if the tuple already exists.
Important:
The use of VALUES() to refer to the new row and columns is deprecated beginning with MySQL 8.0.20, and is subject to removal in a future version of MySQL. Instead, use row and column aliases, as described in the next few paragraphs of this section.
Beginning with MySQL 8.0.19, it is possible to use an alias for the row, with, optionally, one or more of its columns to be inserted, following the VALUES or SET clause, and preceded by the AS keyword. Using the row alias new, the statement shown previously using VALUES() to access the new column values can be written in the form shown here:
See: https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
@alex40724 There is a difference. A REPLACE is equivalent to a DELETE/INSERT.
The main problem is:
Values for all columns are taken from the values specified in the [REPLACE] statement. Any missing columns are set to their default values, just as happens for [INSERT].
@mjansenDatabay Yes, I know, but we are selecting from obj_members before anyway. So we could provide these or null values.
If we use INSERT... ON DUPLICATE... shouldn't the SELECTS and UPDATES be removed completely?
So we could provide these or null values.
You are right. I personally would try to use the most "atomic" approach.
If we use INSERT... ON DUPLICATE... shouldn't the SELECTS and UPDATES be removed completely?
Yes, I could change this.
Hi @smeyer-ilias
As Technical Board, we regularly check for pull requests that have been open for a long time. Any Updates on this? Note, that you can also close this, if you are not able or if you not have the resources to look into it in detail.
Best regards! @kergomard
