Typeorm cache calls hanging after some time with postgres

[bharadwajambati172]

Issue Description

I am using TypeOrm of version 0.2.37 with the below configuration. I tried to update it to the latest version with proper code changes and the same configuration. After some time, the cache calls are getting timed out. So I reverted my TypeOrm version to 0.2.40 and it's working as expected. I updated it to 0.2.41 and it again started to fail.

I revised the release notes of 0.2.41 and found that this timeout issue is happening because of the below issue.

Issue resolved in 0.2.41 db caching won't work with replication enabled (https://github.com/typeorm/typeorm/issues/7694) (2d0abe7), closes https://github.com/typeorm/typeorm/issues/5919

A fix has been given for the above issue so that the master node only writes data to the cache table. But in my case, I am not using any replications and the default will be master. But at the below line https://github.com/typeorm/typeorm/commit/2d0abe7140a0aec40d50c15acd98633483db3e29#diff-791a850d0d03748d51144bb10f8ea933d0c01a840d8d8f06f83b6b6b89052624R163, I am getting a queryRunner with slave node and shouldCreateQueryRunner as true. Because of this, the conditions are getting satisfied. Inside the condition, it's creating a queryRunner for master node, but the query runner for the slave node was not getting released.

If we release the queryRunner for the slave node, it started working as expected.

Configuration used for database configuration

{
    name: "database_name",
    type: "posrgres",
    host: "postgres0.domain.com",
    port: 5432,
    username: "admin",
    password: "adminpw",
    database: "database_name",
    synchronize: false,
    schema: "schema_name",
    entities: ["list of entities"],
    logging: true,
    maxQueryExecutionTime: 5000,
    cache: {
        type: "database",
        tableName: "query_result_cache"
    },
    extras: {
        idleTimeoutMillis: 4.32e+7,
        connectionTimeoutMillis: 240000,
        keepAlive: true,
        minConnection: 5,
        maxConnection: 20
    }
}

Expected Behavior

When replication nodes are not involved in the configuration, the storeInCache function has to check the queryRunner replicationMode and has to release if the replication is of type slave.

Actual Behavior

Database queries are getting timed out with cache enabled.

Steps to Reproduce

1. Create a Postgres database connection with no replications and with cache enabled
2. Run the queries for some time
3. After some time, the query call gets timed out

My Environment

Dependency	Version
Operating System	Ubuntu
Node.js version	14.17.6
Typescript version	4.3.5
TypeORM version	0.2.41

Additional Context

Relevant Database Driver(s)

DB Type	Reproducible
aurora-mysql	no
aurora-postgres	no
better-sqlite3	no
cockroachdb	no
cordova	no
expo	no
mongodb	no
mysql	no
nativescript	no
oracle	no
postgres	yes
react-native	no
sap	no
spanner	no
sqlite	no
sqlite-abstract	no
sqljs	no
sqlserver	no

Are you willing to resolve this issue by submitting a Pull Request?

• ✖️ Yes, I have the time, and I know how to start.
• ✅ Yes, I have the time, but I don't know how to start. I would need guidance.
• ✖️ No, I don’t have the time, but I can support (using donations) development.
• ✖️ No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.

[Ginden]

Line 0.2 isn't supported anymore. Can you replicate this issue in 0.3?

[bharadwajambati172]

@Ginden I am able to replicate the issue with 0.3.6

[bharadwajambati172]

@Ginden Is there any workaround to fix this issue?

[Gonzalo9823]

Hi! I have the same problem on version 0.3.5 I'm also using Postgres an a minimal configuration of a DataSource:

export const AppDataSource = new DataSource({
  type: 'postgres',
  url: config.DATABASE_URL,
  synchronize: false,
  logging: config.NODE_ENV === 'production' ? false : ['error'],
  extra: {
    max: 15,
  }
});

I ask for the data getting the repository directly from the data source like this:

const legalCompanyGrower = await AppDataSource.getRepository(GrowerModel).findOne({
      relations: {
        legalCompanies: {
          language: true,
        },
        language: true,
      },
      where: {
        id: growerId,
        legalCompanies: {
          id: Any(legalCompaniesIds),
        },
      },
      cache: 1000
    });

Is this a bug or I am doing something wrong?

[vlrevolution]

Experiencing the same on version 0.3.5. First query works, later ones won't after a short period. Does this have to do with querying for relations ? I see that the cached query result in database does not include anything relating to relations, only the main find operation.. This for sure to me seems that could cause issues..

[hugowschneider]

I have stumbled upon the problem, and appears to be related to the ReplicationMode of the QueryRunner.

If you see these lines:

https://github.com/typeorm/typeorm/blob/83567f533482d0170c35e2f99e627962b8f99a08/src/cache/DbQueryResultCache.ts#L216-L222

Every time the connection's replication mode is "slave", the caching mechanism tries to create a new connection. Since "slave" is the default mode for the SelectQueryBuilder:

https://github.com/typeorm/typeorm/blob/83567f533482d0170c35e2f99e627962b8f99a08/src/data-source/DataSource.ts#L750-L762

It will always try to create a new connection to store the cache results and by doing this, the connection pool will use all of its connections if there is enough operations to be cached and ending a dead lock. SelectQueryBulder holding the connection while trying to store the cache which tries to get a new connection.

This is easily reproducible by the tests in test/functional/cache/custom-cache-provider.ts and using a DataSource with min and max pooled connections set to 1