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?
Comments
Comment #2
jibranCan you please share SQL version and settings?
Comment #3
megadesk3000 commentedHi @jibran
Comment #4
jibranThanks, for sharing the info. Maybe add it to your build process something like https://stackoverflow.com/a/29007140/308851
Comment #6
bob.hinrichs commentedThis is still a problem and will affect all users of pantheon that are using this module. It is not fixed, I am experiencing it now and the stackoverflow reference looks very helpful, but is advanced database work, not something that all users of this module will be able to do easily.
Comment #7
banoodle commentedThis seems like a critical issue to me and I also would be interested in a solution that doesn't require messing with DB settings as part of our automated build process.
Comment #8
jibranThere is a way forward without using triggers #3204103: Use virtual index instead of triggers. It just needs some interest from the community.
Comment #9
caesius commentedAs stated above, this issue isn't fixed. The issue status is misleading/wrong; anyone who comes here with this issue and doesn't fully read the comment thread would assume that downloading the latest dev version would resolve it. If the maintainer isn't interested in resolving the issue in the module or in leaving it open for contribution/discussion then the proper status is "Closed (won't fix)"
Comment #10
paulsheldrake commentedFor folks that are working in a local environment you can import your DB as the root user as a work around
Comment #11
coconnor commentedIf I explicitly designate the 3.x version in composer.json, the issue goes away.
composer require 'drupal/dynamic_entity_reference:^3.2'I just experienced this issue on many of my Pantheon instances, and I was extremely concerned about how I'd rectify the issue on local/multidev/dev/test environments over the course of our project. AFAIK, the issue seems to be endemic to the 4.x version. I didn't seem to see any explicit notes re: the 4.x build hosting this issue, so I'm hoping this note helps someone.
Hat tip to this post for pointing me in the right place.
Comment #12
lawxen commentedI facing the problem of "The user specified as a definer *** does not exist"
Solving it by resaving the DER field, it seems related with https://www.drupal.org/project/dynamic_entity_reference/issues/3099176
Comment #13
danharper commentedHaving the same issue with version 4.
I can't test locally and I don't understand the solutions above. I'm in a position where I don't want to push any changes in case it breaks production.
I'm dumping from platform.sh and importing into lando.
Comment #14
lawxen commentedResaving the DER field config
Comment #15
rishi.kulshreshthaThank you for the tip, @lawxen. Resaving the DER field helped me out!