Contrib.social

SQLSTATE[HY000]: General error: 1449 The user specified as a definer does not exist

Open on Drupal.org →
Created on 9 April 2024, about 1 year ago

Problem/Motivation

Some hosting providers change the database user name on deploys or rebuilds of containers.

Preview link is using a db trigger and when this gets called SQL exception occurs:

Drupal\Core\Entity\EntityStorageException: SQLSTATE[HY000]: General error: 1449 The user specified as a definer ('9980214569b54b02ada9c0674849c3bc'@'%') does not exist: INSERT INTO "preview_link__entities" ("entity_id", "revision_id", "bundle", "delta", "langcode", "entities_target_id", "entities_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] => 1441 [:db_insert_placeholder_1] => 1441 [:db_insert_placeholder_2] => preview_link [:db_insert_placeholder_3] => 0 [:db_insert_placeholder_4] => und [:db_insert_placeholder_5] => 387474 [:db_insert_placeholder_6] => node ) in Drupal\Core\Entity\Sql\SqlContentEntityStorage->save() (line 817 of core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorage.php).
Drupal\Core\Database\StatementWrapperIterator->execute(Array, Array) (Line: 44)
Drupal\mysql\Driver\Database\mysql\Insert->execute() (Line: 1403)
Drupal\Core\Entity\Sql\SqlContentEntityStorage->saveToDedicatedTables(Object, , Array) (Line: 971)
Drupal\Core\Entity\Sql\SqlContentEntityStorage->doSaveFieldItems(Object) (Line: 718)
Drupal\Core\Entity\ContentEntityStorageBase->doSave(NULL, Object) (Line: 486)
Drupal\Core\Entity\EntityStorageBase->save(Object) (Line: 806)
Drupal\Core\Entity\Sql\SqlContentEntityStorage->save(Object) (Line: 82)
Drupal\preview_link\PreviewLinkStorage->save(Object) (Line: 339)
Drupal\Core\Entity\EntityBase->save() (Line: 83)
Drupal\preview_link\Form\PreviewLinkForm->getEntityFromRouteMatch(Object, 'preview_link') (Line: 73)
Drupal\Core\Entity\HtmlEntityFormController->getFormObject(Object, 'preview_link.preview_link.default') (Line: 58)
Drupal\Core\Controller\FormController->getContentResult(Object, Object) (Line: 39)
Drupal\layout_builder\Controller\LayoutBuilderHtmlEntityFormController->getContentResult(Object, Object)
call_user_func_array(Array, Array) (Line: 123)
Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->Drupal\Core\EventSubscriber\{closure}() (Line: 592)
Drupal\Core\Render\Renderer->executeInRenderContext(Object, Object) (Line: 124)
Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->wrapControllerExecutionInRenderContext(Array, Array) (Line: 97)
Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->Drupal\Core\EventSubscriber\{closure}() (Line: 181)
Symfony\Component\HttpKernel\HttpKernel->handleRaw(Object, 1) (Line: 76)
Symfony\Component\HttpKernel\HttpKernel->handle(Object, 1, 1) (Line: 58)
Drupal\Core\StackMiddleware\Session->handle(Object, 1, 1) (Line: 48)
Drupal\Core\StackMiddleware\KernelPreHandle->handle(Object, 1, 1) (Line: 53)
Asm89\Stack\Cors->handle(Object, 1, 1) (Line: 48)
Drupal\Core\StackMiddleware\ReverseProxyMiddleware->handle(Object, 1, 1) (Line: 51)
Drupal\Core\StackMiddleware\NegotiationMiddleware->handle(Object, 1, 1) (Line: 51)
Drupal\Core\StackMiddleware\StackedHttpKernel->handle(Object, 1, 1) (Line: 704)
Drupal\Core\DrupalKernel->handle(Object) (Line: 19)

Steps to reproduce

* create a preview link entity
* change db user
* try to access the preview link tab

Proposed resolution

* decouple trigger from db user

🐛 Bug report
Status

Active

Version

2.1

Component

Code

Created by

miiimooo Europe

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

  • Issue created by @miiimooo
  • Comment about 1 year ago →
    miiimooo Europe
  • Comment 6 months ago →
    🇺🇸United States kreynen

    @miiimooo thanks for linking to the DER issue. It would have taken me a long time to figure out that in 2024 a module would attempt to store username for a SQL connection in a database. The idea that database connection information never changes when most Drupal hosts now provide a dev->test->live deployment with database connection information populated in the settings.php files from environmental variables BECAUSE THEY CAN CHANGE AT ANYTIME is just a fundamentally flawed approach.

    I understand that this is only an alpha, but the steps to reproduce this error on a host like Pantheon are very simple.

    1. In the live environment, create a Preview Link with a URL like node/123/generate-preview-link
    2. Clone the live environment's database to test
    3. Attempt to access node/123/generate-preview-link

    I haven't looked into why the module is stashing the db credentials in a table and to determine if it is worth fixing that, but my solution was to simply to uninstall the module and reinstall it.

  • Comment 5 months ago →
    🇺🇸United States damienmckenna NH, USA

    Just hit this problem on a site after some recent codebase updates. FWIW the site is hosted at Pantheon the Preview Link configuration was not changed recently.

  • Comment 5 months ago →
    🇺🇸United States damienmckenna NH, USA
  • Comment 4 months ago →
    🇺🇸United States damienmckenna NH, USA

    The problem isn't limited to 2.2.0-alpha1, I tested all 2.1.0 and 2.2.0 alpha releases and they all have the error.

  • Comment 4 months ago →
    🇺🇸United States damienmckenna NH, USA

    I wonder if this is a problem from Dynamic Entity Reference?

  • Comment 4 months ago →
    🇺🇸United States damienmckenna NH, USA

    The recommendation in the DER module is to resave the field, but that's a little harder to do with Preview Link because the field was created programmatically, rather than within the UI.

  • Comment 4 months ago →
    🇺🇸United States damienmckenna NH, USA

    I think it would be useful to have a Drush command to resave the fields, to work around these problems.

  • Comment 4 months ago →
    🇺🇸United States damienmckenna NH, USA

    I tried this:

      $field_specs = \Drupal::service('entity_field.manager')->getFieldStorageDefinitions('preview_link');
  if (!empty($field_specs['entities'])) {
    \Drupal::entityDefinitionUpdateManager()->updateFieldStorageDefinition($field_specs['entities']);
  }

    That seems to resolve the problem.

  • Comment 4 months ago →
    🇺🇸United States damienmckenna NH, USA

    BTW before I updated the field specs I had to truncate the tables:

      $db = \Drupal::database();
  $db->query("TRUNCATE {preview_link}")->execute();
  $db->query("TRUNCATE {preview_link__entities}")->execute();
  • Comment 4 months ago →
    🇺🇸United States jmcgehee

    @damienmckenna, I'm new to this issue. How did you run this code fix?

  • Comment 4 months ago →
    🇺🇸United States damienmckenna NH, USA

    I added them to a hook_update_NAME() script in a custom module.

contrib.social Blog FAQ Discussions
Production build 0.71.5 2024