[CORE-1336] DB connection pool exhausted querying products

[SGFGOV]

Bug report

Describe the bug

const catProduct = (
await this.client.products.list({
categories: [medusaCategory.id],
limit: 1,
})
).products;

results in an axios response:-
data: {
type: 'invalid_data',
message: 'property categories should not exist'
}

results in an error

However, when I query like so

const catProduct = (
  await this.client.products.list({
    category_id: [medusaCategory.id],
    limit: 1,
  })
).products;

There is no response to the client, and server seems to be ignoring the request and the client hangs.. awaiting response from server (medusa)

The complete code snipet

async onModuleInit() {
this.medusaService = (await this.moduleRef.get(MedusaService, {
strict: false,
})) as MedusaService;
const medusaCategories = (
await this.medusaService.client.productCategories.list({
limit: 100,
})
).product_categories;
this.categories = await Promise.all(
medusaCategories.map(
async (t) => await this.medusaService.convert2FrontendCategory(t),
),
);
}

async convert2FrontendCategory(medusaCategory: MedusaCategory) {
const catProduct = (
await this.client.products.list({
categories: [medusaCategory.id],
limit: 1,
})
).products;

const images = catProduct ? catProduct?.[0]?.images : undefined;
const image = images ? images[0] : undefined;
const category: Category = {

System information

Medusa version (including plugins): 1.8.2

Expected behavior

It shouldn't hang

_CORE-1336

[SGFGOV]

#3906 and this issue could be related

[olivermrbl]

@SGFGOV – can I get you to ensure your products are published and the category is active + visible? :)

[SGFGOV]

All products have been published and the category is active and visible

[SGFGOV]

In fact curl has no problem receiving the info

[SGFGOV]

the server responds like so

::ffff:172.31.34.235 - - [25/Apr/2023:01:56:55 +0000] "GET /store/products?category_id%5B0%5D=pcat_01GYFXKX2EHPNN29C9143AV8GY&limit=1 HTTP/1.1" - - "-" "axios/1.2.1"
::ffff:172.31.34.235 - - [25/Apr/2023:01:56:55 +0000] "GET /store/products?category_id%5B0%5D=pcat_01GYPX5TVB5RSV83ETS6AP0JMK&limit=1 HTTP/1.1" - - "-" "axios/1.2.1"
::ffff:172.31.34.235 - - [25/Apr/2023:01:56:55 +0000] "GET /store/products?category_id%5B0%5D=pcat_01GYPX6B583RKE83NB2TF2KQDT&limit=1 HTTP/1.1" - - "-" "axios/1.2.1"
::ffff:172.31.34.235 - - [25/Apr/2023:01:56:55 +0000] "GET /store/products?category_id%5B0%5D=pcat_01GYPX61TN6KKCKZ9QC8HGTPA7&limit=1 HTTP/1.1" - - "-" "axios/1.2.1"
::ffff:172.31.34.235 - - [25/Apr/2023:01:56:55 +0000] "GET /store/products?category_id%5B0%5D=pcat_01GYGS967YQ97018WS7NQJH9T3&limit=1 HTTP/1.1" - - "-" "axios/1.2.1"
::ffff:172.31.34.235 - - [25/Apr/2023:01:56:55 +0000] "GET /store/products?category_id%5B0%5D=pcat_01GYGS7VQ2TYPXRRHFMCV2SZXX&limit=1 HTTP/1.1" - - "-" "axios/1.2.1"
::ffff:172.31.34.235 - - [25/Apr/2023:01:56:55 +0000] "GET /store/products?category_id%5B0%5D=pcat_01GYPX60FDFG7R14DQBXJW7S0M&limit=1 HTTP/1.1" - - "-" "axios/1.2.1"
::ffff:172.31.34.235 - - [25/Apr/2023:01:56:55 +0000] "GET /store/products?category_id%5B0%5D=pcat_01GYPX7HFPGKZNEAAAMYK6992E&limit=1 HTTP/1.1" - - "-" "axios/1.2.1"
::ffff:172.31.34.235 - - [25/Apr/2023:01:56:55 +0000] "GET /store/products?category_id%5B0%5D=pcat_01GYPX5YDSFKZAC5H6AB52R6Q5&limit=1 HTTP/1.1" - - "-" "axios/1.2.1"
::ffff:172.31.34.235 - - [25/Apr/2023:01:56:55 +0000] "GET /store/products?category_id%5B0%5D=pcat_01GYPX6GMD65RK8CQMABR1F76Y&limit=1 HTTP/1.1" - - "-" "axios/1.2.1"

[SGFGOV]

It hangs inside typeorm/driver/postgres/PostgresQueryRunner.js line 69 using typeorm 0.3.14

[SGFGOV]

Ok so i figured this out. the reason was the pool was exhausted.
So changed the dataSourceSettings in the database loader like so
poolSize: 100,
maxQueryExecutionTime:10e3,
poolErrorHandler: (err)=>{
console.log(err);
},
logNotifications:true,
connectTimeoutMS:60e3

Now I'm able to query 5 categories at a time.. I'm sure these are not optimal values. but I'm able to query 5 categories at a time without the server hanging

[olivermrbl]

Thanks for sharing this @SGFGOV - helps a lot. We will investigate this issue asap and keep you posted. This take high priority for us.

[olivermrbl]

Are you using the inventory and stock location modules?

[SGFGOV]

Yes..I am using them..but have not added any data to those.. not dependent on inventory at the moment. Still fixing catalogue

[fxmb]

I can confirm that this issue seems to be related to product-categories and also exists for me as soon as I issue product / product category heavy queries. Thanks for looking into this @SGFGOV !

[olivermrbl]

@SGFGOV @fxmb – we've identified the culprit and are working on resolving it asap. I hope to be able to share something early next week.

[fxmb]

Amazing thanks!

[SGFGOV]

@olivermrbl fantastic..looking forward to the fix

[olivermrbl]

@fxmb @SGFGOV – we haven't forgotten this one. We are very close to having a patch ready. I expect, we can publish a snapshot tomorrow or Wednesday 👍

[SGFGOV]

@olivermrbl Any luck here? Will this make it to the release ?

[olivermrbl]

@SGFGOV, my apologies. I never reverted to this issue. The problem should be resolved by now. Let me know if this is not the case :)