MySQL triggers aren't supported on all hosting platforms

Hi Vlad! Unfortunately, to support config and content entities at the same time triggers are the only option. Is it possible to use DER 8.x-1.x? That branch only supports content entities.

If switching to 8.x-1.x is not possible then Acquia can configure it for you. @larowlan and I ran into this issue on Acquia but they fixed that for us upon client request.

Correct, version 2 doesn't work on Acquia if you have numeric IDs.

For my project I had string IDs so it didn't need the trigger in the end.

The triggers in 2.x are by design or are you planing to remove them to restore the compatibility with Acquia Cloud?

The triggers in 2.x are by design

Yes!

are you planing to remove them

No, because module works fine on Acquia until you allow a string ID entity so by default normal functionality works fine.

Whatever a string ID entity is... I do not know.

I'm using DER 2.x on Acquia with string ID entities.

i.e. My entities have ids like 'C10020' and C1342' instead of integers.

I think the triggers are designed to copy the ID into an integer column if it is an integer, and use that for queries (As integer columns are faster).

So I think the converse is the case, you can use DER 2.x on Acquia if your entities have string IDs, but not if they have integers.

I've been getting around it by importing it manually and removing the DEFINER clause, and I'll add that into an acquia hook. Seems to add the triggers back in again without issues... Not 100% sure this is a good way to go, will just have to test.

As this issue is linked on the module page I comment here, despite being closed.

Are the used triggers deterministic? If I see it right it does some updates but I didn't checked closely yet.

The problem with triggers are that it requires more privileges if you use mysql/mariadb with replication.
For more reasoning consult https://dev.mysql.com/doc/refman/5.5/en/stored-programs-logging.html

As in a live environment you normaly have a replication running, I would correct the statement to most of the hosters doesn't support this functionality. They maybe can turn it on (either with the log_bin_trust_function_creators setting or giving SUPER to the db user) but if I would be the hoster I would tell you at the same time that I do not guarantee the correctness of your backup anymore.

Does anybody using this module have experience with running mysql/mariadb with replication and using triggers from this module? As backups are created from the replication it needs to be ensured that these triggers are the same on master and slave in the DB.

@kfritsche
Regarding replication: I do have a cluster of 3 servers using MariaDB Galera replication, and they all have the same triggers created from this module. I have not experienced any issues in 1 year of uptime with dynamic_entity_reference fields involved.

I'm not familiar with this syntax, but it reads as though it is only casting an already-determined value, so not a risk of values changing.
Unless the NEW. portion means to generate a new auto increment id for the table (uh oh).
SET NEW.field_content_reference_target_id_int = IF(NEW.field_content_reference_target_id REGEXP '^[0-9]+$', CAST(NEW.field_content_reference_target_id AS UNSIGNED), NULL)

From some documentation (https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html):

In a BEFORE trigger, the NEW value for an AUTO_INCREMENT column is 0, not the sequence number that is generated automatically when the new row actually is inserted.

(And the Triggers I see from DER are BEFORE timing).

Hey @jibran @larowlan just having a discussion over on the Salesforce Suite project regarding this issue (#2975914).

What is the support status of the 1.x branch of DER? Will it be receiving long term support?

from my point of view, yes, but jibran might feel differently

What is the support status of the 1.x branch of DER? Will it be receiving long term support?

Both DER v1 and v2 will be supported long term. Other than the string entity ID support the codebase is same and backporting/forward porting is simple.

Because the Version 2 strategy of using triggers can cause problems in several hosting situations including CI Setups the issue title should reflect this. So I copied the warning of the project page as issue title. So it's easier to point in other module issues to this discussion.

Yes, I prefer v1 , would prefer avoiding triggers unless absolutely necessary. Triggers require additional permissions and configurations and not all hosting platforms support that option or make it difficult.

Is this addressed somewhere? I cannot find how to handle these problems. We cannot get our platforms to work because of these issues with the triggers and the creator, when the database is cloned from one environment to another. This is going to be an issue going forward for many people.

@Maintainers:
It looks like the points / questions in #18 and #19 are not answered yet. Should we perhaps reopen this for final discussion and documentation?

Three things I just saw "from outside", running into these issues:

1. The module page links this issue, but only for 8.x-2.x, while it seems like >= 2.x is affected? Could that perhaps be documented?
2. Should 1.x for that reason perhaps be continued and made Drupal 10 compatible to provide a solution for existing installations to be able to upgrade to Drupal 10 on hosts (like Acquia) not supporting triggers?
3. Should the functionality using triggers perhaps better be separated into a different module, as it seems it's something that can't be upgraded to everywhere? And the 1.x implementation might be "good enough" for many users? Just an idea...

Of course, we may put this into a separate issue / discussion, if it makes sense for you.

2.x is 4.x in D10 and 1.x is 3.x in D10. I think that is clear from the project page.

Folks who are using 1.x with D9 can upgrade to 3.x without any issue after D10 upgrade.

I also think that we made it very clear that if you don't want a DER field to support a string ID reference then you can keep on using 1.x. If you want 2.x to work without the triggers then we have #3204103: Use virtual index instead of triggers.

If you can't work on issue #3204103 then please consider supporting the dev work as that is the only blocker at the moment. If your hosting platform is interested in supporting string ID reference on their platform then please talk to them about supporting the dev work required to move away from triggers.

Reverting back to the original title.