Problem/Motivation

I just upgraded my site from drupal 9.5.2 to 9.5.4 (I also tried 9.5.3 with the same results). After the upgrade, one of my views throws this error:

[01-Mar-2023 20:08:53] WARNING: [pool www] child 70544 said into stderr: "NOTICE: PHP message: Uncaught PHP Exception Drupal\Core\Database\DatabaseExceptionWrapper: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'nametid' in 'group statement': SELECT "td"."tid" AS "tid", "td"."name" AS "name""
[01-Mar-2023 20:08:53] WARNING: [pool www] child 70544 said into stderr: "FROM"
[01-Mar-2023 20:08:53] WARNING: [pool www] child 70544 said into stderr: ""taxonomy_term_field_data" "td""
[01-Mar-2023 20:08:53] WARNING: [pool www] child 70544 said into stderr: "INNER JOIN "node__field_anonymized" "p" ON p.field_anonymized_target_id=td.tid"
[01-Mar-2023 20:08:53] WARNING: [pool www] child 70544 said into stderr: "WHERE ("td"."vid" = :db_condition_placeholder_0) AND ("p"."bundle" = :db_condition_placeholder_1)"
[01-Mar-2023 20:08:53] WARNING: [pool www] child 70544 said into stderr: "GROUP BY "nametid""
[01-Mar-2023 20:08:53] WARNING: [pool www] child 70544 said into stderr: "ORDER BY "name" ASC; Array"
[01-Mar-2023 20:08:53] WARNING: [pool www] child 70544 said into stderr: "("
[01-Mar-2023 20:08:53] WARNING: [pool www] child 70544 said into stderr: "    [:db_condition_placeholder_0] => anonymized"
[01-Mar-2023 20:08:53] WARNING: [pool www] child 70544 said into stderr: "    [:db_condition_placeholder_1] => anonymized_bundle"
[01-Mar-2023 20:08:53] WARNING: [pool www] child 70544 said into stderr: ")"
[01-Mar-2023 20:08:53] WARNING: [pool www] child 70544 said into stderr: "" at /var/www/html/docroot/core/modules/mysql/src/Driver/Database/mysql/ExceptionHandler.php line 53"
2023/03/01 20:08:53 [error] 2165#2165: *78352 FastCGI sent in stderr: "PHP message: Uncaught PHP Exception Drupal\Core\Database\DatabaseExceptionWrapper: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'nametid' in 'group statement': SELECT "td"."tid" AS "tid", "td"."name" AS "name"
FROM
"taxonomy_term_field_data" "td"
INNER JOIN "node__field_anonymized" "p" ON p.field_anonymized_target_id=td.tid
WHERE ("td"."vid" = :db_condition_placeholder_0) AND ("p"."bundle" = :db_condition_placeholder_1)
GROUP BY "nametid"
ORDER BY "name" ASC; Array
(
    [:db_condition_placeholder_0] => anonymized
    [:db_condition_placeholder_1] => anonymized_bundle
)
" at /var/www/html/docroot/core/modules/mysql/src/Driver/Database/mysql/ExceptionHandler.php line 53" while reading response header from upstream, client: 172.24.0.6, server: , request: "GET /anonymized/path/here HTTP/1.1", upstream: "fastcgi://unix:/run/php-fpm.sock:", host: "prod.local.ddev.site", referrer: "https://prod.local.ddev.site/admin/content?status=All&type=landing_page&title=anonymized&langcode=All"

[N.B. some details edited for anonymity]

The site is running on linux/apache with PHP v 8.1.

The problem shows itself in (at least) two ways. First, I have a page that shows this view as a block. That page now gives a WSOD. If I examine the view in the admin interface, the preview at the bottom doesn't appear...no visible SQL gets generated and there are no results.

Interestingly, I my a duplicate of the view and it works fine, with no changes whatsoever. So in a pinch, I can move forward by duplicating the view. However, it seems that some instability is at work here, and I'm hoping to resolve it more fully.

Steps to reproduce

Unfortunately, I couldn't find a way to reproduce the error. I tried to by duplicating the view, but that led to a working result, while the original, identical view is non-functioning. This view has operated without issue for years prior to this.

Despite this limitation, I wonder if anyone might have thoughts on diagnosing the matter further or solving it. Thanks.

Proposed resolution

Remaining tasks

User interface changes

API changes

Data model changes

Release notes snippet

CommentFileSizeAuthor
#10 views_broken.txt25.73 KBmefron
#10 views_working.txt25.01 KBmefron

Comments

mefron created an issue. See original summary.

cilefen’s picture

cilefen commented
Issue tags: +Needs steps to reproduce
  • Are there any contributed views-related modules installed? Did you change or not change their versions at the same time as upgrading Drupal core?
  • A stack trace would be super-helpful.
  • Have you exported the configurations of the broken original view and of the working cloned view and compared those?
  • The 9.5.3 release notes contain all changes. Do any of those seem a likely cause?
cilefen’s picture

cilefen commented
Title: upgrade to 9.5.4 breaks some views » After 9.5.3 upgrade: DatabaseExceptionWrapper: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'nametid' in 'group statement'

The release notes: https://www.drupal.org/project/drupal/releases/9.5.3

longwave’s picture

longwave
he/him
Primary language English
Location UK
commented
Status: Active » Postponed (maintainer needs more info)

Agree with #2 that we need more info about the view itself and the site configuration to be able to help diagnose this.

I checked the changes in 9.5.3 and 9.5.4 and don't immediately see anything that was changed in Views that would affect this.

longwave’s picture

longwave
he/him
Primary language English
Location UK
commented

#3191623: Select queries do not escape the GROUP BY fields did add escaping to GROUP BY, but as far as I can see that wouldn't have caused the syntax error in the query here. Somehow the name and tid columns have been merged together into a non-existent nametid column, but I don't understand how that could happen.

mefron’s picture

mefron commented

No other modules have changed during this update; core is the only variable. There are a number of contrib and custom views-related modules installed. But none have changed at this time.

I exported the configurations of the working and non-working versions of the view and here is the result:

diff views_working.yaml views_broken.yaml 
1c1
< uuid: 00ee2f69-33e9-4795-a0c0-784c1c5b6dda
---
> uuid: 9489b3b0-8df3-473b-8776-5bb36a41eecb
22,23c22,23
< id: duplicate_of_presidential_recording
< label: 'Duplicate of Presidential Recording'
---
> id: presidential_recording
> label: 'Presidential Recording'
606a607,609
>           relationship: none
>           group_type: group
>           admin_label: ''
609a613
>           operator: '='
611a616
>           exposed: false
612a618,621
>             operator_id: ''
>             label: ''
>             description: ''
>             use_operator: false
615a625,642
>             identifier: ''
>             required: false
>             remember: false
>             multiple: false
>             remember_roles:
>               authenticated: authenticated
>           is_grouped: false
>           group_info:
>             label: ''
>             description: ''
>             identifier: ''
>             optional: true
>             widget: select
>             multiple: false
>             remember: false
>             default_group: All
>             default_group_multiple: {  }
>             group_items: {  }

I'm not familiar enough with views to make much sense of this. But in case it's helpful, I built the working copy simply by using the `duplicate` function on the original, broken one.

mefron’s picture

mefron commented

As a follow-up to my last post, I tried an experiment and got interesting results.

I wanted to see if I could import the working config to rebuild the broken view, and in the process, fix the break.

So I deleted the original, broken view and then imported the configuration from the working copy, replacing the UUID, id, and label fields with the data from the original. However, when I did the import, the resulting view still had the same SQL problem. So I tried again several times, keeping ALL fields constant except one at a time.

What I found was that the "id" field in the configuration seems to be the one that causes the sensitivity. That is, the original view had the id `presidential_recording`. If I change that to, say `new_presidential_recording`, keeping all other fields as in the config file (either one, working or the original, broken config), then the resulting view is fine.

So for whatever reason, there seems to be something about how the view's "id" field is handled that is causing the problem.

longwave’s picture

longwave
he/him
Primary language English
Location UK
commented

Are you able to upload the two full YAML files of the broken and working views? If not, a unified diff (diff -u views_working.yaml views_broken.yaml) will be a little easier to read.

cilefen’s picture

cilefen commented

This is interesting.

mefron’s picture

mefron commented
StatusFileSize
new views_working.txt25.01 KB
new views_broken.txt25.73 KB

I just uploaded the config files to this issue. Please let me know if they aren't properly visible.

kachinsky’s picture

kachinsky commented

I faced the similar problem, not in a View, but in my custom query.

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '."mailudn"."field_display_name_valuetotal_nodes_voted_on"."total_nodes_voted_on_' at line 44

In my query I have this GROUP BY:

$query->groupBy('u.uid, u.name, u.mail, udn.field_display_name_value, total_nodes_voted_on.total_nodes_voted_on_sum, total_nodes_voted_on.avg_total_nodes_voted_on_sum, user.user_display_name_check, user_name.name_check, user_mail.mail_check');

which becomes
GROUP BY "u"."uidu"."nameu"."mailudn"."field_display_name_valuetotal_nodes_voted_on"."total_nodes_voted_on_sumtotal_nodes_voted_on"."avg_total_nodes_voted_on_sumuser"."user_display_name_checkuser_name"."name_checkuser_mail"."mail_check")

If I change my query to

$query->groupBy('u.uid')
      ->groupBy('u.name')
      ->groupBy('u.mail')
      ->groupBy('udn.field_display_name_value')
      ->groupBy('total_nodes_voted_on.total_nodes_voted_on_sum')
      ->groupBy('total_nodes_voted_on.avg_total_nodes_voted_on_sum')
      ->groupBy('user.user_display_name_check')
      ->groupBy('user_name.name_check')
      ->groupBy('user_mail.mail_check');

everything becomes fine.

lapurddrupal’s picture

lapurddrupal commented

I have the same or similar problem: After installing Autoban I get when calling in Autoban rules list-> Log analyse:
Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'log.messagelog.type' in 'group statement': SELECT "log"."message" AS "message", "log"."type" AS "type", COUNT(*) AS "cnt" FROM "watchdog" "log" WHERE "log"."type" NOT IN (:db_condition_placeholder_0, :db_condition_placeholder_1, :db_condition_placeholder_2, :db_condition_placeholder_3, :db_condition_placeholder_4) GROUP BY "log"."messagelog"."type" HAVING (COUNT(*) >= :threshold) ORDER BY "cnt" DESC; Array ( [:db_condition_placeholder_0] => autoban [:db_condition_placeholder_1] => cron [:db_condition_placeholder_2] => php [:db_condition_placeholder_3] => system [:db_condition_placeholder_4] => user [:threshold] => 5 ) in Drupal\autoban\Form\AutobanAnalyzeForm->getAnalyzeResult() (line 286 of modules/contrib/autoban/src/Form/AutobanAnalyzeForm.php).

Drupal: 9.5.8 PHP: 8.1.16

cilefen’s picture

cilefen commented

Please let’s keep this on topic as a Views issue.

mefron’s picture

mefron commented

I think @kachinsky raises an interesting point, even though it's not views-specific. If SQL queries are getting built in a wonky way somewhere else (i.e. those group_by statements), couldn't that be responsible for the problem we're seeing in views?

Version: 9.5.x-dev » 11.x-dev

Drupal core is moving towards using a “main” branch. As an interim step, a new 11.x branch has been opened, as Drupal.org infrastructure cannot currently fully support a branch named main. New developments and disruptive changes should now be targeted for the 11.x branch. For more information, see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

smustgrave’s picture

smustgrave commented

With D9 EOL should this be closed

smustgrave’s picture

smustgrave commented

Wanted to bump 1 more time before closing. If no follow up could be closed in 3 months.

Version: 11.x-dev » main

Drupal core is now using the main branch as the primary development branch. New developments and disruptive changes should now be targeted to the main branch.

Read more in the announcement.