Hi there,
I'm in the process of testing an external database for synchronising a subset of our users to Moodle and have run into an issue with a field that I'd like to sync back to the database when changed in Moodle.
The field in the database is a foreign key to another table to restrict the values. If you try updating the table via SQL with a bad value, the update fails with:
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_account_type". The conflict occurred in database "MoodleOtherUsers", table "dbo.account_type", column 'accounttypeid'.
The issue is that if you update via Moodle with a bad value, Moodle actually saves the bad value and presents no warning, even though the update fails for the external database. This leaves the values between Moodle and the external database out-of-sync (not to mention allowing an invalid value in Moodle).
I was expecting Moodle to fail to save the change and present an error, similar to the error you get if you mistyped the field name when setting up the mappings in the first place:
Error
Failed to update user data on external auth: db. See the server logs for more details.
I also tested removing UPDATE rights to the sql user running the external database sync and found the same - Moodle will save a change even when it's not able to update the value in the database.
Is this there a setting I'm missing that will allow me to set Moodle to fail to save a change if it's not able to update the external database when configured to do so? If not, shouldn't this be considered a bug?
The reason I'd like to manage the change in Moodle and have it feed back to the external db is because changes made in the external db don't feed into Moodle until the user logs in, when I need these to be kept in sync at least as often as the sync job runs. I thought allowing Moodle to update the external db would be ideal, but not if it allows updating Moodle even when the update to the external db fails.
I aware of picklists in Moodle, but these aren't going to do for us for a few reasons (e.g., they require using a numerical ID for the sync, which is contrary to how we store this in the database, and even when set to be optional you can't leave it 'blank' when editing a profile as far as I can tell).