Trying to search within a content type seems to be broken in 3.1.0. #7138

robclouth · 2020-07-23T13:09:13Z

Describe the bug
Trying to search within a content type seems to be broken in 3.1.0. It fails with a 500 error.

This is the error:
error: column "users-permissions_user.username" must appear in the GROUP BY clause or be used in an aggregate function

Steps to reproduce the behavior

Go into a content type
Try to search
The search fails

Expected behavior
That searching works.

Node.js version: v12
NPM version: Yarn 1.15.2
Strapi version: 3.1.0
Database: Postgres 12
Operating system: MacOS 10.15.2

The text was updated successfully, but these errors were encountered:

alexandrebodin · 2020-07-23T13:45:16Z

Hi, I cannot reproduce this. Is this a new project ?

robclouth · 2020-07-23T14:03:36Z

Hmm. No I started on 3.0.5 and then followed the migration guide to 3.1.0. During the database migration there were problems. Increased the max pool and it worked. Could it be that the db migration hasn't worked properly?

…

On Thu, 23 Jul 2020, 15:45 Alexandre BODIN, ***@***.***> wrote: Hi, I cannot reproduce this. Is this a new project ? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#7138 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAJP3IUZEXDRVARW3JWN4WLR5A5IFANCNFSM4PFWTWRQ> .

alexandrebodin · 2020-07-23T14:09:48Z

Do you have any logs of the errors you got ?

robclouth · 2020-07-23T14:24:09Z

No, but it was this:
Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

This is the error when seraching

[2020-07-23T14:22:31.548Z] error error: column "users-permissions_user.provider" must appear in the GROUP BY clause or be used in an aggregate function
    at Parser.parseErrorMessage (/Volumes/Data/Projects/Apps/SCGIS/backend/node_modules/pg-protocol/dist/parser.js:241:15)
    at Parser.handlePacket (/Volumes/Data/Projects/Apps/SCGIS/backend/node_modules/pg-protocol/dist/parser.js:89:29)
    at Parser.parse (/Volumes/Data/Projects/Apps/SCGIS/backend/node_modules/pg-protocol/dist/parser.js:41:38)
    at Socket.<anonymous> (/Volumes/Data/Projects/Apps/SCGIS/backend/node_modules/pg-protocol/dist/index.js:8:42)
    at Socket.emit (events.js:310:20)
    at addChunk (_stream_readable.js:286:12)
    at readableAddChunk (_stream_readable.js:268:9)
    at Socket.Readable.push (_stream_readable.js:209:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:186:23)
From previous event:
    at Client_PG._query (/Volumes/Data/Projects/Apps/SCGIS/backend/node_modules/knex/lib/dialects/postgres/index.js:234:12)
    at Client_PG.query (/Volumes/Data/Projects/Apps/SCGIS/backend/node_modules/knex/lib/client.js:158:17)
    at Runner.query (/Volumes/Data/Projects/Apps/SCGIS/backend/node_modules/knex/lib/runner.js:135:36)
    at /Volumes/Data/Projects/Apps/SCGIS/backend/node_modules/knex/lib/runner.js:39:23
From previous event:
    at Runner.run (/Volumes/Data/Projects/Apps/SCGIS/backend/node_modules/knex/lib/runner.js:25:16)
    at Builder.Target.then (/Volumes/Data/Projects/Apps/SCGIS/backend/node_modules/knex/lib/interface.js:14:43)
    at processImmediate (internal/timers.js:456:21)

It happens with all the content types not just users. If I drop and create the db so that it's empty, then rebuild the admin ui it still happens.

alexandrebodin · 2020-07-23T14:28:43Z

can you share your user model settings.json ?

robclouth · 2020-07-23T14:40:46Z

{
  "kind": "collectionType",
  "collectionName": "users-permissions_user",
  "info": {
    "name": "user",
    "description": ""
  },
  "options": {
    "timestamps": true
  },
  "attributes": {
    "email": {
      "type": "email",
      "minLength": 6,
      "configurable": false,
      "required": true
    },
    "provider": {
      "type": "string",
      "configurable": false
    },
    "password": {
      "type": "password",
      "minLength": 6,
      "configurable": false,
      "private": true
    },
    "resetPasswordToken": {
      "type": "string",
      "configurable": false,
      "private": true
    },
    "confirmed": {
      "type": "boolean",
      "default": false,
      "configurable": false
    },
    "blocked": {
      "type": "boolean",
      "default": false,
      "configurable": false
    },
    "role": {
      "model": "role",
      "via": "users",
      "plugin": "users-permissions",
      "configurable": false
    },
    "username": {
      "type": "string",
      "minLength": 3,
      "unique": false,
      "required": false
    },
    "firstName": {
      "type": "string",
      "required": true
    },
    "lastName": {
      "type": "string",
      "required": true
    },
    "organization": {
      "type": "string",
      "required": true
    },
    "employmentType": {
      "type": "enumeration",
      "enum": [
        "commercial",
        "nonProfit",
        "government",
        "selfEmployed"
      ],
      "required": false
    },
    "address1": {
      "type": "string",
      "required": true
    },
    "address2": {
      "type": "string",
      "required": false
    },
    "city": {
      "type": "string",
      "required": true
    },
    "state": {
      "type": "string",
      "required": true
    },
    "postalCode": {
      "type": "string",
      "required": true
    },
    "country": {
      "type": "string",
      "required": true
    },
    "projects": {
      "collection": "project",
      "via": "owner"
    },
    "postedJobs": {
      "collection": "job",
      "via": "owner"
    },
    "ownedChapters": {
      "collection": "chapter",
      "via": "owner"
    },
    "events": {
      "collection": "event",
      "via": "owner"
    },
    "sentMessages": {
      "collection": "message",
      "via": "sender"
    },
    "receivedMessages": {
      "collection": "message",
      "via": "recipient"
    },
    "hostingWorkshops": {
      "collection": "workshop",
      "via": "host"
    },
    "membershipType": {
      "model": "membership-type"
    },
    "payments": {
      "collection": "payment",
      "via": "user"
    },
    "organizations": {
      "collection": "organization",
      "via": "users"
    },
    "interests": {
      "type": "text",
      "required": false
    },
    "about": {
      "type": "text"
    },
    "public": {
      "type": "boolean",
      "default": false,
      "private": false,
      "required": false
    },
    "title": {
      "type": "string"
    },
    "newsItems": {
      "collection": "news-item",
      "via": "owner"
    },
    "jobTitle": {
      "type": "string"
    },
    "lat": {
      "type": "float"
    },
    "long": {
      "type": "float"
    },
    "active": {
      "type": "boolean",
      "default": true
    },
    "deleted": {
      "type": "boolean"
    },
    "chapter": {
      "model": "chapter",
      "via": "members"
    },
    "subscription": {
      "model": "subscription",
      "via": "user"
    },
    "inactiveReason": {
      "type": "enumeration",
      "enum": [
        "noSubscription",
        "expiredSubscription"
      ]
    },
    "image": {
      "model": "file",
      "via": "related",
      "allowedTypes": [
        "images"
      ],
      "plugin": "upload",
      "required": false
    },
    "oldId": {
      "type": "biginteger"
    },
    "listServ": {
      "type": "boolean",
      "default": false
    }
  }
}

robclouth · 2020-07-23T14:42:58Z

It also fails after creating a new content type with just one field: "name"

error error: column "tests.name" must appear in the GROUP BY clause or be used in an aggregate function

{
  "kind": "collectionType",
  "collectionName": "tests",
  "info": {
    "name": "Test"
  },
  "options": {
    "increments": true,
    "timestamps": true
  },
  "attributes": {
    "name": {
      "type": "string"
    }
  }
}

robclouth · 2020-07-23T14:49:23Z

Ok I've narrowed it down. It happens when you sort and query at the same time.

GET http://localhost:1337/tests?_sort=name%3AASC&_q=test fails

It only happens when the sort is added, otherwise it's fine

petersg83 · 2020-07-23T15:37:58Z

Hi, I'll dig into it. Thank you :)

robclouth · 2020-07-23T15:55:30Z

Thanks!

petersg83 · 2020-07-23T15:55:55Z

I couldn't reproduce the issue. Do you have any more info ? Some custom code that could be related (extensions).
If you can reproduce the issue with a fresh install it so I can reproduce the steps, it would be awesome ! :)

robclouth · 2020-07-23T16:01:18Z

I'll try. But if I can't without the full project (which seems likely) any hints as to where to look for the issue?
I have some custom code is users-permissions but would that be related?
What version of the pg client is recommended? I have "latest" 8.3.0.

robclouth · 2020-07-23T16:03:03Z

Here's my deps

"dependencies": {
"@sendgrid/client": "^7.2.1",
"@sendgrid/mail": "^7.2.1",
"fecha": "^4.2.0",
"knex": "<0.20.0",
"pg": "latest",
"slugify": "^1.4.4",
"strapi": "3.1.0",
"strapi-admin": "3.1.0",
"strapi-connector-bookshelf": "3.1.0",
"strapi-plugin-content-manager": "3.1.0",
"strapi-plugin-content-type-builder": "3.1.0",
"strapi-plugin-email": "3.1.0",
"strapi-plugin-graphql": "^3.1.0",
"strapi-plugin-upload": "3.1.0",
"strapi-plugin-users-permissions": "3.1.0",
"strapi-provider-email-sendgrid": "^3.1.0",
"strapi-utils": "3.1.0"
},

espenbye · 2020-07-23T17:56:04Z

Same problem here. Happens on all content types.

[2020-07-23T17:28:17.447Z] error error: column "stations.updated_at" must appear in the GROUP BY clause or be used in an aggregate function at Parser.parseErrorMessage (/srv/strapi/strapi-development/node_modules/pg-protocol/dist/parser.js:241:15) at Parser.handlePacket (/srv/strapi/strapi-development/node_modules/pg-protocol/dist/parser.js:89:29) at Parser.parse (/srv/strapi/strapi-development/node_modules/pg-protocol/dist/parser.js:41:38) at Socket.<anonymous> (/srv/strapi/strapi-development/node_modules/pg-protocol/dist/index.js:8:42) at Socket.emit (events.js:315:20) at addChunk (_stream_readable.js:295:12) at readableAddChunk (_stream_readable.js:271:9) at Socket.Readable.push (_stream_readable.js:212:10) at TCP.onStreamRead (internal/stream_base_commons.js:186:23) From previous event: at Client_PG._query (/srv/strapi/strapi-development/node_modules/knex/lib/dialects/postgres/index.js:234:12) at Client_PG.query (/srv/strapi/strapi-development/node_modules/knex/lib/client.js:158:17) at Runner.query (/srv/strapi/strapi-development/node_modules/knex/lib/runner.js:135:36) at /srv/strapi/strapi-development/node_modules/knex/lib/runner.js:39:23 From previous event: at Runner.run (/srv/strapi/strapi-development/node_modules/knex/lib/runner.js:25:16) at Builder.Target.then (/srv/strapi/strapi-development/node_modules/knex/lib/interface.js:14:43) at processImmediate (internal/timers.js:456:21) [2020-07-23T17:28:17.448Z] debug GET /content-manager/explorer/application::station.station/count?_limit=10&_sort=updated_at%3AASC&_q=asdasd&_start=0 (33 ms) 500

espenbye · 2020-07-23T20:56:18Z

The error can be reproduced:

Create new Strapi instance on DigitalOcean (from the Marketplace).
Follow the migration steps (since it does not have the ADMIN_JWT_SECRET yet) linked here.
Create admin user
Create new content type
Create some items in the new content type.
Try searching and error shows.

alexandrebodin · 2020-07-24T09:47:45Z

I'll try. But if I can't without the full project (which seems likely) any hints as to where to look for the issue?
I have some custom code is users-permissions but would that be related?
What version of the pg client is recommended? I have "latest" 8.3.0.

You should check the extensions you made to the user permissions plugin for sure ! it was modified to allow for admin features.

petersg83 · 2020-07-24T12:52:03Z

Thank you, I reproduced it with postgresql. We will dig into it!

robclouth · 2020-07-24T15:33:18Z

Hey, sorry it seems like you've only fixed the count search. Normal search with filters also fails:
GET http://localhost:1337/tests?_sort=name%3AASC&_q=test

petersg83 · 2020-07-24T16:14:24Z

It works on my side. How did you try it? Did you checkout on branch master? (as version 3.1.2 is not published yet)

alexandrebodin added the status: can not reproduce Not enough information to reproduce label Jul 23, 2020

lauriejim added the source: core:content-manager Source is core/content-manager package label Jul 24, 2020

petersg83 mentioned this issue Jul 24, 2020

fix count with sort param #7159

Merged

alexandrebodin closed this as completed in #7159 Jul 24, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Trying to search within a content type seems to be broken in 3.1.0. #7138

Trying to search within a content type seems to be broken in 3.1.0. #7138

robclouth commented Jul 23, 2020

alexandrebodin commented Jul 23, 2020

robclouth commented Jul 23, 2020 via email

alexandrebodin commented Jul 23, 2020

robclouth commented Jul 23, 2020

alexandrebodin commented Jul 23, 2020

robclouth commented Jul 23, 2020

robclouth commented Jul 23, 2020 •

edited

robclouth commented Jul 23, 2020 •

edited

petersg83 commented Jul 23, 2020

robclouth commented Jul 23, 2020

petersg83 commented Jul 23, 2020

robclouth commented Jul 23, 2020

robclouth commented Jul 23, 2020

espenbye commented Jul 23, 2020 •

edited

espenbye commented Jul 23, 2020

alexandrebodin commented Jul 24, 2020

petersg83 commented Jul 24, 2020

robclouth commented Jul 24, 2020

petersg83 commented Jul 24, 2020

Trying to search within a content type seems to be broken in 3.1.0. #7138

Trying to search within a content type seems to be broken in 3.1.0. #7138

Comments

robclouth commented Jul 23, 2020

alexandrebodin commented Jul 23, 2020

robclouth commented Jul 23, 2020 via email

alexandrebodin commented Jul 23, 2020

robclouth commented Jul 23, 2020

alexandrebodin commented Jul 23, 2020

robclouth commented Jul 23, 2020

robclouth commented Jul 23, 2020 • edited

robclouth commented Jul 23, 2020 • edited

petersg83 commented Jul 23, 2020

robclouth commented Jul 23, 2020

petersg83 commented Jul 23, 2020

robclouth commented Jul 23, 2020

robclouth commented Jul 23, 2020

espenbye commented Jul 23, 2020 • edited

espenbye commented Jul 23, 2020

alexandrebodin commented Jul 24, 2020

petersg83 commented Jul 24, 2020

robclouth commented Jul 24, 2020

petersg83 commented Jul 24, 2020

robclouth commented Jul 23, 2020 •

edited

robclouth commented Jul 23, 2020 •

edited

espenbye commented Jul 23, 2020 •

edited