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 (&#039;DBUSERINTEGRATION&#039;@&#039;IPINTEGRATION&#039;) 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] =&gt; 5741
    [:db_insert_placeholder_1] =&gt; 19244
    [:db_insert_placeholder_2] =&gt; teaser
    [:db_insert_placeholder_3] =&gt; 0
    [:db_insert_placeholder_4] =&gt; de
    [:db_insert_placeholder_5] =&gt; 1
    [:db_insert_placeholder_6] =&gt; 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

megadesk3000 created an issue. See original summary.

jibran’s picture

Version: 8.x-2.0-alpha8 » 8.x-2.x-dev

Can you please share SQL version and settings?

megadesk3000’s picture

Hi @jibran

mysql --version
mysql  Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using  EditLine wrapper
cat /etc/my.cnf
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
bind-address = 0.0.0.0
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid

# Logging configuration.
log-error = /var/log/mysqld/mysqld.log



# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links = 0

# User is ignored when systemd is used (fedora >= 15).
user = mysql

# http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html
;performance_schema

# Memory settings.
key_buffer_size = 256M
max_allowed_packet = 64M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_type = 0
query_cache_size = 16M
query_cache_limit = 1M
max_connections = 151
tmp_table_size = 16M
max_heap_table_size = 16M
group_concat_max_len = 1024
join_buffer_size = 262144

# Other settings.
wait_timeout = 28800
lower_case_table_names = 0
event_scheduler = OFF

# InnoDB settings.
innodb_large_prefix = 1
innodb_file_format = barracuda
innodb_file_per_table = 1
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 64M

[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
jibran’s picture

Category: Bug report » Support request
Status: Active » Fixed

Thanks, for sharing the info. Maybe add it to your build process something like https://stackoverflow.com/a/29007140/308851

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

bob.hinrichs’s picture

This 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.

banoodle’s picture

This 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.

jibran’s picture

There is a way forward without using triggers #3204103: Use virtual index instead of triggers. It just needs some interest from the community.

caesius’s picture

As 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)"

paulsheldrake’s picture

For folks that are working in a local environment you can import your DB as the root user as a work around

MYSQL_CONNECTION_DETAILS="-h db -proot -u root default"
zcat $DBFILE | mysql $MYSQL_CONNECTION_DETAILS
coconnor’s picture

If 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.

lawxen’s picture

I 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

danharper’s picture

Having 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.

lawxen’s picture

I don't understand the solutions above.

Resaving the DER field config

rishi.kulshreshtha’s picture

Thank you for the tip, @lawxen. Resaving the DER field helped me out!