ERROR 4006 (HY000) at line 1: Operation CREATE USER failed

[0x29a]

This command from the MySQL initialization script has been failing since commit openedx/edx-platform@6c2c433:

ERROR 4006 (HY000) at line 1: Operation CREATE USER failed for 'pr_1758_49e51b_openedx'@'%' as it is referenced as a definer account in a view.

This function in MySQL raises the error.

The failure occurs because the commit upgraded edx-enterprise to version 5.13.1, which introduces this migration. That migration creates the view_enterprise_customer_support_users view.

CREATE USER does not fail on a local MySQL instance, because local users possess the SET_USER_ID permission. In DigitalOcean's managed MySQL (that we use), this dynamic privilege is intentionally disallowed by DigitalOcean and cannot be enabled, so the command fails there.

This error has not yet appeared on AWS RDS, but is very likely to surface soon. As of MySQL >= 8.4.3, the privilege was renamed to ALLOW_NONEXISTENT_DEFINER and is explicitly disallowed, as shown in this table.

This is not strictly a Tutor bug, but these privileges are likely to be disallowed by other managed MySQL providers as well. It would therefore be helpful to provide a workaround—​like, maybe, an ability to patch mysql.sh? Alternatively, we could check explicitly whether the user already exists and skip this command if it does, although that approach might be somewhat ugly.

cc @marlonkeating (author of the migration), @brian-smith-tcril, @kdmccormick and @feanil because this is the reason of this sandbox failure, @kaustavb12