Join Log in
Score:0
pazitos10 avatar Drupal

SQL Error when trying to remove content type field

np flag
pazitos10

I was trying to create a new Content Type for my site and I chose a field name ("description") that already used for other content types.

The problem occurred when saving changes as the machine name for the field already exists for the fields in other Content Types but with a different field type. In other Content types I used the default "Long Text" field type but in this case I chose "Read More Field" as it suited better my use case.

I noticed the problem and went back to change the machine name only to discover I had created two fields and one of them had a machine name with the problem I mentioned before.

When trying to remove such field, I went to admin/structure/types/manage/my_custom_content_type/fields and using the UI options selected "Delete".

Then, the admin UI showed me a screen where it displays the changes I'm about to make and when I chose "Accept", my site takes me to a blank page with the message: "The website encountered an unexpected error. Try it again later."

When I check the logs, this is the error message I see:

[06-Nov-2021 03:07:49] WARNING: [pool www] child 2014 said into stderr: "NOTICE: PHP message: Uncaught PHP Exception Drupal\Core\Database\DatabaseExceptionWrapper: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_description_teaser_value' in 'where clause': SELECT 1 AS expression"
[06-Nov-2021 03:07:49] WARNING: [pool www] child 2014 said into stderr: "FROM"
[06-Nov-2021 03:07:49] WARNING: [pool www] child 2014 said into stderr: "{node_revision__field_description} t"
[06-Nov-2021 03:07:49] WARNING: [pool www] child 2014 said into stderr: "WHERE (field_description_teaser_value IS NOT NULL) OR (field_description_teaser_format IS NOT NULL) OR (field_description_hidden_value IS NOT NULL) OR (field_description_hidden_format IS NOT NULL)"
[06-Nov-2021 03:07:49] WARNING: [pool www] child 2014 said into stderr: "LIMIT 1 OFFSET 0; Array"
[06-Nov-2021 03:07:49] WARNING: [pool www] child 2014 said into stderr: "("
[06-Nov-2021 03:07:49] WARNING: [pool www] child 2014 said into stderr: ")"
[06-Nov-2021 03:07:49] WARNING: [pool www] child 2014 said into stderr: "" at /var/www/html/web/core/lib/Drupal/Core/Database/Connection.php line 701"
2021/11/06 03:07:49 [error] 2010#2010: *21776 FastCGI sent in stderr: "PHP message: Uncaught PHP Exception Drupal\Core\Database\DatabaseExceptionWrapper: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_description_teaser_value' in 'where clause': SELECT 1 AS expression
FROM
{node_revision__field_description} t
WHERE (field_description_teaser_value IS NOT NULL) OR (field_description_teaser_format IS NOT NULL) OR (field_description_hidden_value IS NOT NULL) OR (field_description_hidden_format IS NOT NULL)
LIMIT 1 OFFSET 0; Array
(
)
" at /var/www/html/web/core/lib/Drupal/Core/Database/Connection.php line 701" while reading response header from upstream, client: 172.18.0.6, server: , request: "GET /admin/structure/types/manage/my_custom_content_type/fields/node.my_custom_content_type.field_description/storage HTTP/1.1", upstream: "fastcgi://unix:/run/php-fpm.sock:", host: "custom-site.ddev.site", referrer: "https://custom-site.ddev.site/admin/structure/types/manage/my_custom_content_type/fields"

When launching phpmyadmin, none of the column names listed in the where clause exist in node_revision__field_description table as those columns are created for a different field type.

At this point, I couldn't create any content using this Content Type and I would like to remove it and start from scratch by creating a new one with better machine names and trying to avoid those errors.

My question is: is there a safe way to remove the content type by using SQL or drush? I tried to remove it via the admin interface but it resulted with the same error as when I try to remove the field.

37
0 + 0
8
cn flag
Patrick Kenny
How did you initially try to remove it? You should always use the admin UI/config management first.
0
Reply
pazitos10 avatar
np flag
pazitos10
@PatrickKenny, thanks for the comment. I updated my question adding some clarifications but the tl;dr would be: to try to remove it, I used the admin UI/config management.
0
Reply
Score:0
pazitos10 avatar Drupal
np flag
pazitos10

I managed to solve my problem by manually adding the missing columns to the tables involved using phpmyadmin.

First:

  • Created a db backup (just in case).

Second, I created a copy of the tables involved and moved such copies to another db maintaining both data and structure:

  • node_revision__field_description
  • node__field_description

Once the backup was done, I proceeded creating the missing columns and copied their attributes from similar columns in other tables (created for other Content Types).

Then, I removed the Content Type via the admin UI without any problem.

Lastly, I restored the original copies of the tables to the db, replacing the modified versions of them.

I know this is probably not the best solution for everyone but it worked for me.

0 + 0
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.