0

Background

I attempted to add a new field "Audience" which is an entity reference field referencing an "Audience" taxonomy. After creating this, I attempted to edit it to allow the user to make multiple selections when I got the below error:

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_audience_target_id' in 'where clause': SELECT 1 AS "expression" FROM "node_revision__field_audience" "t" WHERE "field_audience_target_id" IS NOT NULL LIMIT 1 OFFSET 0; Array ( ) in Drupal\Core\Entity\Sql\SqlContentEntityStorage->countFieldData() (line 1794 of /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorage.php).

Backtrace:

#0 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/Database/Connection.php(883): Drupal\mysql\Driver\Database\mysql\ExceptionHandler->handleExecutionException(Object(PDOException), Object(Drupal\Core\Database\StatementWrapperIterator), Array, Array)
#1 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/Database/Query/Select.php(525): Drupal\Core\Database\Connection->query('SELECT 1 AS "ex...', Array, Array)
#2 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorage.php(1794): Drupal\Core\Database\Query\Select->execute()
#3 /Users/ciesinsg/Documents/Repositories/its/web/core/modules/field/src/Entity/FieldStorageConfig.php(744): Drupal\Core\Entity\Sql\SqlContentEntityStorage->countFieldData(Object(Drupal\field\Entity\FieldStorageConfig), true)
#4 /Users/ciesinsg/Documents/Repositories/its/web/core/modules/field_ui/src/Form/FieldStorageConfigEditForm.php(123): Drupal\field\Entity\FieldStorageConfig->hasData()
#5 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/Entity/EntityForm.php(107): Drupal\field_ui\Form\FieldStorageConfigEditForm->form(Array, Object(Drupal\Core\Form\SubformState))
#6 /Users/ciesinsg/Documents/Repositories/its/web/core/modules/field_ui/src/Form/FieldStorageConfigEditForm.php(85): Drupal\Core\Entity\EntityForm->buildForm(Array, Object(Drupal\Core\Form\SubformState))
#7 /Users/ciesinsg/Documents/Repositories/its/web/core/modules/field_ui/src/Form/FieldConfigEditForm.php(199): Drupal\field_ui\Form\FieldStorageConfigEditForm->buildForm(Array, Object(Drupal\Core\Form\SubformState), Object(Drupal\field\Entity\FieldConfig))
#8 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/Entity/EntityForm.php(107): Drupal\field_ui\Form\FieldConfigEditForm->form(Array, Object(Drupal\Core\Form\FormState))
#9 [internal function]: Drupal\Core\Entity\EntityForm->buildForm(Array, Object(Drupal\Core\Form\FormState))
#10 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/Form/FormBuilder.php(536): call_user_func_array(Array, Array)
#11 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/Form/FormBuilder.php(283): Drupal\Core\Form\FormBuilder->retrieveForm('field_config_ed...', Object(Drupal\Core\Form\FormState))
#12 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/Controller/FormController.php(73): Drupal\Core\Form\FormBuilder->buildForm(Object(Drupal\field_ui\Form\FieldConfigEditForm), Object(Drupal\Core\Form\FormState))
#13 [internal function]: Drupal\Core\Controller\FormController->getContentResult(Object(Symfony\Component\HttpFoundation\Request), Object(Drupal\Core\Routing\RouteMatch))
#14 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/EventSubscriber/EarlyRenderingControllerWrapperSubscriber.php(123): call_user_func_array(Array, Array)
#15 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/Render/Renderer.php(627): Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->Drupal\Core\EventSubscriber\{closure}()
#16 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/EventSubscriber/EarlyRenderingControllerWrapperSubscriber.php(121): Drupal\Core\Render\Renderer->executeInRenderContext(Object(Drupal\Core\Render\RenderContext), Object(Closure))
#17 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/EventSubscriber/EarlyRenderingControllerWrapperSubscriber.php(97): Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->wrapControllerExecutionInRenderContext(Array, Array)
#18 /Users/ciesinsg/Documents/Repositories/its/vendor/symfony/http-kernel/HttpKernel.php(181): Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->Drupal\Core\EventSubscriber\{closure}()
#19 /Users/ciesinsg/Documents/Repositories/its/vendor/symfony/http-kernel/HttpKernel.php(76): Symfony\Component\HttpKernel\HttpKernel->handleRaw(Object(Symfony\Component\HttpFoundation\Request), 1)
#20 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/StackMiddleware/Session.php(58): Symfony\Component\HttpKernel\HttpKernel->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#21 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/StackMiddleware/KernelPreHandle.php(48): Drupal\Core\StackMiddleware\Session->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#22 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/StackMiddleware/ContentLength.php(28): Drupal\Core\StackMiddleware\KernelPreHandle->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#23 /Users/ciesinsg/Documents/Repositories/its/web/core/modules/big_pipe/src/StackMiddleware/ContentLength.php(32): Drupal\Core\StackMiddleware\ContentLength->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#24 /Users/ciesinsg/Documents/Repositories/its/web/core/modules/page_cache/src/StackMiddleware/PageCache.php(106): Drupal\big_pipe\StackMiddleware\ContentLength->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#25 /Users/ciesinsg/Documents/Repositories/its/web/core/modules/page_cache/src/StackMiddleware/PageCache.php(85): Drupal\page_cache\StackMiddleware\PageCache->pass(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#26 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/StackMiddleware/ReverseProxyMiddleware.php(48): Drupal\page_cache\StackMiddleware\PageCache->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#27 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/StackMiddleware/NegotiationMiddleware.php(51): Drupal\Core\StackMiddleware\ReverseProxyMiddleware->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#28 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/StackMiddleware/AjaxPageState.php(36): Drupal\Core\StackMiddleware\NegotiationMiddleware->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#29 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/StackMiddleware/StackedHttpKernel.php(51): Drupal\Core\StackMiddleware\AjaxPageState->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#30 /Users/ciesinsg/Documents/Repositories/its/web/core/lib/Drupal/Core/DrupalKernel.php(704): Drupal\Core\StackMiddleware\StackedHttpKernel->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)
#31 /Users/ciesinsg/Documents/Repositories/its/web/index.php(19): Drupal\Core\DrupalKernel->handle(Object(Symfony\Component\HttpFoundation\Request))
#32 {main}

So now I have a field on this content-type that cannot be edited or deleted. I should also mention that the field was created using the Admin UI and not using custom PHP code.

I opened the database to look at node__field_audience and sure enough there was no field_audience_target_id column. Instead, it had field_audience_value.

Steps to Replicate Issue

I am not sure that you will be able to replicate the issue, but the steps I followed are:

  1. Open Quick Actions content-type and go to fields
  2. Created a new entity reference field "Support type" that references a taxonomy
  3. Realized the taxonomy hasn't been created yet and created an Audience taxonomy in a separate tab
  4. Added Staff and Student terms to the taxonomy
  5. Went back to Quick Actions and created the entity reference field "Support type" once again
  6. Decided to rename this field Audience and saved. I noticed that the machine name also changed
  7. After this I got the error and was no longer able to update or delete this field

Fix Attempts

  1. Recreated Audience entity reference field: First, I tried to create this field again (as Audience 2) to see if it would cause the same issue, but this time it created the database table and had the correct field_audience_2_target_id column instead of value. Additionally, I can now edit this field. I am not sure why, but the second time I created the issue it did not result in the error I got the first time. Also, the original Audience field still errors out when you try to edit or delete it.

  2. Remove the referenced vocabulary: I thought this might have something to do with the vocabulary I created to reference. I was able to delete and recreate the vocabulary without issue, but I still could not delete the field.

  3. Ran update.php but there were no database updates.

  4. Updated core and dependencies including drush.

  5. Ran drush updb but once again, there were no updates so nothing happened.

  6. Searched the internet for this issue but could only find similar issues that upon further reading were not applicable to me as they occurred in different parts of the site (not fields) or were caused by modules.

Conclusion

Has anybody experienced this or know a workaround to remove this field? I am able to do basic changes to the database as well if needed, but I wanted to check here before I caused more damage. I also have a week old backup but I was hoping not to revert progress unless there was no other choice.

1 Answer 1

0

I found a solution after many attempts:

Solution

  1. The first thing I did was actually Fix attempt 1 from my question. In short, I recreated the field again, but this time it created successfully as field_audience_2. This is important for the next step.
  2. Next, I went into MySQL and ran Describe table_name on both node_revision__field_audience and node_revision__field_audience_2. I found that audience had a column called field_audience_value instead of field_audience_target_id, which caused the error I posted here.
  3. I used two table alter statements to first add the column, and then add the multiple index:
-- Add the column
ALTER TABLE node_revision__field_audience ADD COLUMN field_audience_target_id INT UNSIGNED NOT NULL AFTER field_audience_value;

-- Add the multiple index
ALTER TABLE node_revision__field_audience ADD INDEX idx_field_audience_target_id (field_audience_target_id);
  1. After modifying the table, I went back into the Drupal UI, opened my Quick Action content-type and was able to delete the table normally.

More Info

I also asked this question on the Drupal Forum where I have included more information including failed attempts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.