Log doesn't lies, so there is clearly a problem there, even if i cannot pinpoint the problem for now (commands are orderly queued and executed)
In the meantime, can you confirm that really the code that permits to reproduced it ?
await conn.commit();
|
await conn.release();
|
The await expression in `await conn.commit();` causes async function execution to pause , so commit command will be totally done executing `await conn.release()`. If that's not the case, it would then be a node.js issue.
I have tried it again and I can still reproduce this problem. If I set "noControlAfterUse" in the pool configuration to true, I cannot reproduce this problem.
if you are able to reproduced it, could you please share the debug code, i can't understand why there is no response in log since there is `await`, like :
|
|
==> conn:1209548 Query(0,11) |
+--------------------------------------------------+
|
| 0 1 2 3 4 5 6 7 8 9 a b c d e f | |
+--------------------------------------------------+------------------+
|
| 07 00 00 00 03 43 4F 4D 4D 49 54 | .....COMMIT | |
+--------------------------------------------------+------------------+
|
|
|
<== conn:1209548 Query.readResponsePacket (0,7) |
+--------------------------------------------------+
|
| 0 1 2 3 4 5 6 7 8 9 a b c d e f | |
+--------------------------------------------------+------------------+
|
| 07 00 00 01 00 00 00 02 00 00 00 | ........... | |
+--------------------------------------------------+------------------+
|
|
|
==> conn:1209548 Reset(0,5) |
+--------------------------------------------------+
|
| 0 1 2 3 4 5 6 7 8 9 a b c d e f | |
+--------------------------------------------------+------------------+
|
| 01 00 00 00 1F | ..... | |
+--------------------------------------------------+------------------+
|
|
|
<== conn:1209548 Reset.readResetResponsePacket (0,7) |
+--------------------------------------------------+
|
| 0 1 2 3 4 5 6 7 8 9 a b c d e f | |
+--------------------------------------------------+------------------+
|
| 07 00 00 01 00 00 00 02 00 00 00 | ........... | |
+--------------------------------------------------+------------------+
|
|
Pool
import fs from 'fs';
|
import mariadb, { Pool } from 'mariadb';
|
import tls from 'tls';
|
|
|
function initConnectionPool(): Pool {
|
const {
|
DB_HOST,
|
DB_PORT,
|
DB_USER,
|
DB_PASSWORD,
|
DB_DATABASE,
|
} = process.env;
|
|
|
if (!DB_HOST || !DB_USER || !DB_PASSWORD || !DB_DATABASE) {
|
throw Error('Missing database-config');
|
}
|
|
|
console.info('Create connection pool to database... Host:', DB_HOST);
|
return mariadb.createPool({
|
timezone: 'Europe/Berlin',
|
dateStrings: true,
|
host: DB_HOST,
|
port: DB_PORT ? Number(DB_PORT) : 3306,
|
user: DB_USER,
|
password: DB_PASSWORD,
|
database: DB_DATABASE,
|
});
|
}
|
|
|
const connectionPool: Pool = initConnectionPool();
|
export default connectionPool as Pool;
|
NestJS DB-Service
Provider of the connection pool for the NestJS application.
import { Inject, Injectable, ServiceUnavailableException } from '@nestjs/common';
|
import { Pool, PoolConnection, QueryOptions } from 'mariadb';
|
|
|
@Injectable()
|
export class DatabaseService {
|
constructor(@Inject('MARIA_DB_POOL') private pool: Pool) {}
|
|
|
public getPool(): Pool {
|
return this.pool;
|
}
|
|
|
public async getConnection(): Promise<PoolConnection> {
|
try {
|
return await this.pool.getConnection();
|
} catch (ex) {
|
throw new ServiceUnavailableException('Database connection failed.');
|
}
|
}
|
}
|
|
API-Endpoint
I have removed the business logic here, which does not interact with the database.
async sendMessages(req: Request, res: Response): Promise<void> {
|
const { messages } = req.body as RequestBody;
|
|
|
const conn = await this.db.getConnection();
|
try {
|
for (let i = 0; i < messages.length; i++) {
|
// iterate through messages and add them to database
|
|
|
await conn.beginTransaction();
|
try {
|
await conn.query({ namedPlaceholders: true, sql: "INSERT INTO ..." }, { /* values*/ });
|
await conn.query({ namedPlaceholders: true, sql: "INSERT INTO ..." }, { /* values*/ });
|
await conn.query({ namedPlaceholders: true, sql: "INSERT INTO ..." }, { /* values*/ });
|
await conn.query({ namedPlaceholders: true, sql: "INSERT INTO ..." }, { /* values*/ });
|
|
await conn.commit();
|
} catch (e) {
|
await conn.rollback();
|
throw e;
|
}
|
}
|
|
|
res.status(200).end();
|
} catch (e) {
|
console.error(e);
|
res.status(500).end();
|
return;
|
} finally {
|
await conn.release();
|
}
|
}
|
Debug protokoll
I have the log from the start of the transaction. Do you also need a log from the execution of getConnection?
Is it intended that a reset result is read in after beginTransaction? Or does this look like a problem with the queue?
==> conn:1186180 Query(0,22) |
+--------------------------------------------------+
|
| 0 1 2 3 4 5 6 7 8 9 a b c d e f | |
+--------------------------------------------------+------------------+
|
| 12 00 00 00 03 53 54 41 52 54 20 54 52 41 4E 53 | .....START TRANS | |
| 41 43 54 49 4F 4E | ACTION | |
+--------------------------------------------------+------------------+
|
|
|
<== conn:1186180 Reset.readResetResponsePacket (0,7) |
+--------------------------------------------------+
|
| 0 1 2 3 4 5 6 7 8 9 a b c d e f | |
+--------------------------------------------------+------------------+
|
| 07 00 00 01 00 00 00 02 00 00 00 | ........... | |
+--------------------------------------------------+------------------+
|
|
|
<== conn:1186180 Query.readResponsePacket (0,7) |
+--------------------------------------------------+
|
| 0 1 2 3 4 5 6 7 8 9 a b c d e f | |
+--------------------------------------------------+------------------+
|
| 07 00 00 01 00 00 00 03 00 00 00 | ........... | |
+--------------------------------------------------+------------------+
|
|
|
==> conn:1186180 Query(0,601) |
QUERY REMOVED, INSERT INTO...
|
|
|
<== conn:1186180 Query.readResponsePacket (0,9) |
+--------------------------------------------------+
|
| 0 1 2 3 4 5 6 7 8 9 a b c d e f | |
+--------------------------------------------------+------------------+
|
| 09 00 00 01 00 01 FC 17 03 03 00 00 00 | ............. | |
+--------------------------------------------------+------------------+
|
|
|
==> conn:1186180 Query(0,500) |
QUERY REMOVED, INSERT INTO...
|
|
|
<== conn:1186180 Query.readResponsePacket (0,9) |
+--------------------------------------------------+
|
| 0 1 2 3 4 5 6 7 8 9 a b c d e f | |
+--------------------------------------------------+------------------+
|
| 09 00 00 01 00 01 FC 84 06 03 00 00 00 | ............. | |
+--------------------------------------------------+------------------+
|
|
|
==> conn:1186180 Query(0,598) |
QUERY REMOVED, INSERT INTO...
|
|
|
<== conn:1186180 Query.readResponsePacket (0,9) |
+--------------------------------------------------+
|
| 0 1 2 3 4 5 6 7 8 9 a b c d e f | |
+--------------------------------------------------+------------------+
|
| 09 00 00 01 00 01 FC 37 15 03 00 00 00 | .......7..... | |
+--------------------------------------------------+------------------+
|
|
|
==> conn:1186180 Query(0,238) |
QUERY REMOVED, INSERT INTO...
|
|
|
<== conn:1186180 Query.readResponsePacket (0,7) |
+--------------------------------------------------+
|
| 0 1 2 3 4 5 6 7 8 9 a b c d e f | |
+--------------------------------------------------+------------------+
|
| 07 00 00 01 00 01 00 03 00 00 00 | ........... | |
+--------------------------------------------------+------------------+
|
|
|
==> conn:1186180 Reset(0,5) |
+--------------------------------------------------+
|
| 0 1 2 3 4 5 6 7 8 9 a b c d e f | |
+--------------------------------------------------+------------------+
|
| 01 00 00 00 1F | ..... | |
+--------------------------------------------------+------------------+
|
|
|
==> conn:1186180 Query(0,11) |
+--------------------------------------------------+
|
| 0 1 2 3 4 5 6 7 8 9 a b c d e f | |
+--------------------------------------------------+------------------+
|
| 07 00 00 00 03 43 4F 4D 4D 49 54 | .....COMMIT | |
+--------------------------------------------------+------------------+
|
|
|
<== conn:1186180 Reset.readResetResponsePacket (0,7) |
+--------------------------------------------------+
|
| 0 1 2 3 4 5 6 7 8 9 a b c d e f | |
+--------------------------------------------------+------------------+
|
| 07 00 00 01 00 00 00 02 00 00 00 | ........... | |
+--------------------------------------------------+------------------+
|
|
|
<== conn:1186180 Query.readResponsePacket (0,7) |
+--------------------------------------------------+
|
| 0 1 2 3 4 5 6 7 8 9 a b c d e f | |
+--------------------------------------------------+------------------+
|
| 07 00 00 01 00 00 00 02 00 00 00 | ........... | |
+--------------------------------------------------+------------------+
|
>is it intended that a reset result is read in after beginTransaction? Or does this look like a problem with the queue?
It cannot be the case normally. Connections are not available until reset response is handle.
Your snippet code is ok. Await, by specficiation, must suspend execution until the returned promise is fulfilled or rejected, so `await conn.release();` cannot be executed before fullfillment or rejection of `await conn.commit();`.
After verification of each pool implementation line i see no possible bug.
For now, i'm a little dubious about what to do next, as i can only see a Typescript/node.js bug, that would be so obvious that it would be strange.
Could you indicate the exact node.js version you use ?
And particulary the TypeScript version and '--target' you use ?
before '--target ES2017' version, Async - Await where not using node.js native implementation, but custom wrapper (tslib if my memory is good).
I use node v14.17.3 and the target is "es2020".
Should I try another version? If yes, which one?
Here is our tsconfig:
{
|
"compilerOptions": {
|
"module": "commonjs",
|
"experimentalDecorators": true,
|
"emitDecoratorMetadata": true,
|
"declaration": true,
|
"moduleResolution": "node",
|
"target": "ES2020",
|
"lib": ["ES2020"],
|
"esModuleInterop": true,
|
"allowSyntheticDefaultImports": true,
|
"strict": true,
|
"forceConsistentCasingInFileNames": true,
|
"noImplicitAny": true,
|
"resolveJsonModule": true,
|
"isolatedModules": true,
|
"sourceMap": true,
|
"outDir": "./dist",
|
"baseUrl": "./src",
|
"removeComments": true,
|
"incremental": true,
|
"skipLibCheck": true
|
},
|
"include": ["src/**/*", "types/**/*"]
|
}
|
|
I've tryed with Typescript 4.4.3 without reproducing the issue with the same configuration you indicate.
Is it possible to provide the generated js file ?
Thank you for trying to reproduce the problem. It is a pity that it did not work. Would have been too easy too... 
I have currently removed all transactions in our application, with the hope that you can find something so I can add them back.
We lost a lot of data due to the problem, which is especially annoying since our platform is used to synchronize multiple systems. Cleaning that up is now very time consuming.
I don't think I'm allowed to publish parts of our application. I can understand that it is now very difficult for you to recreate this situation and would have no problem if you close the ticket because of this.
I hope that the problem does not occur other developers. Maybe it is also a combination of many coincidences together with our systems.
After testing with various Typescript version and target, generated code not reproduced.
logs write RESET command before COMMIT command, and that cannot correspond to actual code API-Endpoint description. Either there is some await not corresponding, or there is something else i cannot imagine.
setting issue to stalled until further information by user or another issue like this.
In two weeks, we will close this issue as incomplete since it will have spent a long time waiting for feedback.
In one week we will close this issue as incomplete since it will have spent a long time waiting for feedback.