I have the following Problem when using DER fields and syncing database between two different environments
When i create a new DER field on our integration environment, aftwards sync the database from there to my local machine and try to create a new entity with a dynamic entity reference and save it i receive the following error:
The website encountered an unexpected error. Please try again later.</br></br><em class="placeholder">Drupal\Core\Entity\EntityStorageException</em>: SQLSTATE[HY000]: General error: 1449 The user specified as a definer ('DBUSERINTEGRATION'@'IPINTEGRATION') does not exist: INSERT INTO {paragraph__field_teaser_dynamic} (entity_id, revision_id, bundle, delta, langcode, field_teaser_dynamic_target_id, field_teaser_dynamic_target_type) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6); Array
(
[:db_insert_placeholder_0] => 5741
[:db_insert_placeholder_1] => 19244
[:db_insert_placeholder_2] => teaser
[:db_insert_placeholder_3] => 0
[:db_insert_placeholder_4] => de
[:db_insert_placeholder_5] => 1
[:db_insert_placeholder_6] => commerce_product
)
The error is totally correct, since i use different db credentials for both environments.
When i dump my database on my local environment and search for the db user (DBUSERINTEGRATION) in the database dump i find the following occurences:
/*!50003 CREATE*/ /*!50017 DEFINER="DBUSERINTEGRATION"@"IPINTEGRATION"*/ /*!50003 TRIGGER paragraph__field_teaser_dynamic_der_insert BEFORE insert ON paragraph__field_teaser_dynamic FOR EACH ROW SET NEW.field_teaser_dynamic_target_id_int = IF(NEW.field_teaser_dynamic_target_id REGEXP '^[0-9]+$', CAST(NEW.field_teaser_dynamic_target_id AS UNSIGNED), NULL) */;;
/*!50003 CREATE*/ /*!50017 DEFINER="DBUSERINTEGRATION"@"IPINTEGRATION"*/ /*!50003 TRIGGER paragraph__field_teaser_dynamic_der_update BEFORE update ON paragraph__field_teaser_dynamic FOR EACH ROW SET NEW.field_teaser_dynamic_target_id_int = IF(NEW.field_teaser_dynamic_target_id REGEXP '^[0-9]+$', CAST(NEW.field_teaser_dynamic_target_id AS UNSIGNED), NULL) */;;
/*!50003 CREATE*/ /*!50017 DEFINER="DBUSERINTEGRATION"@"IPINTEGRATION"*/ /*!50003 TRIGGER paragraph_revision__field_teaser_dynamic_der_insert BEFORE insert ON paragraph_revision__field_teaser_dynamic FOR EACH ROW SET NEW.field_teaser_dynamic_target_id_int = IF(NEW.field_teaser_dynamic_target_id REGEXP '^[0-9]+$', CAST(NEW.field_teaser_dynamic_target_id AS UNSIGNED), NULL) */;;
/*!50003 CREATE*/ /*!50017 DEFINER="DBUSERINTEGRATION"@"IPINTEGRATION"*/ /*!50003 TRIGGER paragraph_revision__field_teaser_dynamic_der_update BEFORE update ON paragraph_revision__field_teaser_dynamic FOR EACH ROW SET NEW.field_teaser_dynamic_target_id_int = IF(NEW.field_teaser_dynamic_target_id REGEXP '^[0-9]+$', CAST(NEW.field_teaser_dynamic_target_id AS UNSIGNED), NULL) */;
When i check the modules code, i found the follwing function \Drupal\dynamic_entity_reference\Storage\IntColumnHandlerMySQL::createTrigger which seems to be responsible for those CREATE TRIGGER statements in the export.
My workaround for now is to
1. db sync
drush sql-sync --create-db @project.igr @self
2. dump database
drush sql-dump --result-file="dump.sql"
3. replace the definer statement in the database
sed -i ‘s/DEFINER=“DBUSERINTEGRATION"@"IPINTEGRATION"/DEFINER=“DBUSERLOCAL"@"127.0.0.1"/g’ dump.sql
4. reimport database
drush sql-drop && drush sql-cli < dump.sql
After that procedure, i can save entites with DER fields on my local machien again.
How can this be solved? Do i need a different SQL setting or something to prevent the TRIGGER statement to not write the DEFINEr of the current environment into the statement?