node-mysql2
node-mysql2 copied to clipboard
sidorares
Concurrent call to mysql breaks after some interaction
I have created an singleton connection to use the same connection_id() so the rest app can make use of TEMPORARY TABLES. After some concurrent calls, the connection is broken with this error:
node_modules/mysql2/lib/connection.js:127
connection.threadId = handshakeCommand.handshake.connectionId;
TypeError: Cannot read property 'connectionId' of null
at ClientHandshake.<anonymous>
He is the code
import { Me } from '../shared/Me';
import * as knex from 'knex';
export class BucketDb {
private static _connection: { [sessionId: string]: knex } = {};
public static connect(connectionData) {
if (!this._connection || (this._connection && !this._connection[Me.profile().sessionId])) {
this._connection[Me.profile().sessionId] = knex({
client: 'mysql2',
connection: connectionData,
pool: {
min: 0, max: 1
}
});
}
// this._connection[Me.profile().sessionId].raw('select connection_id()').then((r) => {
// console.log(Me.profile().sessionId, r[0][0]);
// });
return this._connection[Me.profile().sessionId];
}
}
I probably need more info. mysql2 connection object ( mysqljs/mysql ) does not allow 'restarting', that is it's lifecycle is 'create, connect ( auth handshake ), commands, close'. Could be that the way you use it tries to initiate handshake twice?
Could you pass debug: true to driver options and check whats logged?
All request is ok, but after 30-35 request, the app give those errors. This app is to be an API (nodejs/express), so I need that it support many request concurrently. I put POOL whith max to 1 becouse I need the same connection_id() so created temporary table will be Available on a second request.
Packages:
- mysql: 5.7.16 (x86_64)
- node mysql2: 1.3.5;
- nodejs: v8.1.3;
Errors:
GET /api/v1/4cxUeOu0VL6T7NhiZ5FxXTvaiDqDEd/16/products?customerNumber=103 201 54.066 ms - 210
{ method: 'raw',
sql: '\n DROP TEMPORARY\n TABLE IF EXISTS\n bucket_5.ProductsByCustomer\n ',
bindings: [],
options: {},
__knexQueryUid: 'dcd56663-7dd2-4636-bac0-524997d8dedb' }
[ { method: 'raw',
sql: 'CREATE TEMPORARY TABLE bucket_5.ProductsByCustomer comment=\'temporary_table_by_user\' select FlatTable.productLine, avg(FlatTable.totalSale) as totalSale from bucket_5.FlatTable where FlatTable.customerNumber = \'103\' group by FlatTable.productLine limit 10',
bindings: [],
options: {},
__knexQueryUid: '423d4980-94d2-4d9e-abdc-6a21e4999efb' } ]
{ method: 'raw',
sql: '\n DROP TEMPORARY\n TABLE IF EXISTS\n bucket_5.remove\n ',
bindings: [],
options: {},
__knexQueryUid: 'e439b8c4-60e4-4aef-9eec-8cb167e420ec' }
[ { method: 'raw',
sql: 'CREATE TEMPORARY TABLE bucket_5.remove comment=\'temporary_table_by_user\' select FlatTable.customerNumber from bucket_5.FlatTable',
bindings: [],
options: {},
__knexQueryUid: '3e8c41af-c4d3-4d39-b967-b25a2b1bef89' } ]
{ method: 'select',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 10 ],
__knexQueryUid: 'd1fb2ab1-c5c9-4d54-9796-b32e791ee698',
sql: 'select * from `bucket_5`.`ProductsByCustomer` limit ?' }
GET /api/v1/4cxUeOu0VL6T7NhiZ5FxXTvaiDqDEd/16/products?customerNumber=103 201 49.026 ms - 210
/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/connection.js:127
connection.threadId = handshakeCommand.handshake.connectionId;
^
TypeError: Cannot read property 'connectionId' of null
at ClientHandshake.<anonymous> (/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/connection.js:127:55)
at emitNone (events.js:105:13)
at ClientHandshake.emit (events.js:207:7)
at ClientHandshake.Command.execute (/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/commands/command.js:34:12)
at Connection.handlePacket (/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/connection.js:500:28)
at PacketParser.onPacket (/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/connection.js:94:16)
at PacketParser.executeStart (/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/packet_parser.js:77:14)
at Socket.<anonymous> (/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/connection.js:102:29)
at emitOne (events.js:115:13)
at Socket.emit (events.js:210:7)
at addChunk (_stream_readable.js:252:12)
at readableAddChunk (_stream_readable.js:239:11)
at Socket.Readable.push (_stream_readable.js:197:10)
at TCP.onread (net.js:589:20)
[nodemon] app crashed - waiting for file changes before starting...
POOL whith max to 1 becouse I need the same connection_id()
why pool then?
would you be able to put console.log(err) here https://github.com/sidorares/node-mysql2/blob/e8d185340e2280594efd1de162060ce29921cf45/lib/commands/command.js#L33 ?
looks like connection time error is not handled correctly
If I remove pool options from the connection, the lib will create many connection_id(). With this behavior, I cant create temporary table to retrieve it later to be used on the next connection.
Here is the console.log
error to github issue { Error: Too many connections
at Packet.asError (/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/packets/packet.js:701:13)
at ClientHandshake.Command.execute (/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/commands/command.js:28:22)
at Connection.handlePacket (/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/connection.js:500:28)
at PacketParser.onPacket (/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/connection.js:94:16)
at PacketParser.executeStart (/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/packet_parser.js:77:14)
at Socket.<anonymous> (/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/connection.js:102:29)
at emitOne (events.js:115:13)
at Socket.emit (events.js:210:7)
at addChunk (_stream_readable.js:252:12)
at readableAddChunk (_stream_readable.js:239:11)
at Socket.Readable.push (_stream_readable.js:197:10)
at TCP.onread (net.js:589:20) code: 'ER_CON_COUNT_ERROR', errno: 1040, sqlState: '' }
/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/connection.js:127
connection.threadId = handshakeCommand.handshake.connectionId;
^
TypeError: Cannot read property 'connectionId' of null
at ClientHandshake.<anonymous> (/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/connection.js:127:55)
at emitNone (events.js:105:13)
at ClientHandshake.emit (events.js:207:7)
at ClientHandshake.Command.execute (/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/commands/command.js:35:12)
at Connection.handlePacket (/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/connection.js:500:28)
at PacketParser.onPacket (/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/connection.js:94:16)
at PacketParser.executeStart (/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/packet_parser.js:77:14)
at Socket.<anonymous> (/Users/macbook/Documents/app/app-one/node_modules/mysql2/lib/connection.js:102:29)
at emitOne (events.js:115:13)
at Socket.emit (events.js:210:7)
at addChunk (_stream_readable.js:252:12)
at readableAddChunk (_stream_readable.js:239:11)
at Socket.Readable.push (_stream_readable.js:197:10)
at TCP.onread (net.js:589:20)
Thanks for the log!
2 problems here:
-
for some reason you are creating too many connections. Maybe you are using one connection (inside pool) to perform few related queries per http request and then don't close it, not sure
-
driver does not handle connection error correctly ( handshake 'end' handler assumes there was handshake initialisation object which does not exist in case of error ). I need to fix this
If I remove pool options from the connection, the lib will create many connection_id()
you can just open connection and continue to use it across requests if you really need that
KnexJs does not an alternative to pooling. The lib is very useful to the complexity of the app Gaio (connect to Oracle, MSSQL, MySQL).
Maybe I am missing something, but I couldn't find on it`s documentation. I have tried to not use pool, but the lib seems to close this door.
ok sorry I forgot you are using it from knex
still, the problem is that you create new connection per profile and then not closing it. I suggest to refactor your db code not to rely on a connectionId / temp tables ( there are many solutions to this ) and just use one global knex object with pool max set to some reasonable number ( depending on avg query execution time / server size / your load that 'reasonable number' could be anything from 2 to 100s )
@sidorares Thanks for your response and thanks for opened pull request. I changed the way to keep connection using pure mysql2 module (not knex) and finish it as soon response is delivered.
import { Me } from '../shared/Me';
const mysql = require('mysql2');
export class BucketDb {
private static _connection = {};
public static connect(connectionData) {
const self = this;
if (!this._connection || (this._connection && !this._connection[Me.profile().sessionId])) {
this.instance(connectionData);
}
return this._connection[Me.profile().sessionId];
}
private static instance(connectionData) {
this._connection[Me.profile().sessionId] = mysql.createConnection(connectionData);
}
public static end() {
if (this._connection && this._connection[Me.profile().sessionId]) {
this._connection[Me.profile().sessionId].end();
}
}
}
The errors persist like the echo above, but you have said that there are many solutions to this. May you give me a clue on how to make it happen? With those changes, here is the new error.
GET /api/v1/4cxUeOu0VL6T7NhiZ5FxXTvaiDqDEd/16/products?customerNumber=103 501 2.524 ms - 101
error to github issue { Error: Too many connections
at Packet.asError (/Users/calebeaires/Documents/app/app-one/node_modules/mysql2/lib/packets/packet.js:701:13)
at ClientHandshake.Command.execute (/Users/calebeaires/Documents/app/app-one/node_modules/mysql2/lib/commands/command.js:28:22)
at Connection.handlePacket (/Users/calebeaires/Documents/app/app-one/node_modules/mysql2/lib/connection.js:515:28)
at PacketParser.onPacket (/Users/calebeaires/Documents/app/app-one/node_modules/mysql2/lib/connection.js:94:16)
at PacketParser.executeStart (/Users/calebeaires/Documents/app/app-one/node_modules/mysql2/lib/packet_parser.js:77:14)
at Socket.<anonymous> (/Users/calebeaires/Documents/app/app-one/node_modules/mysql2/lib/connection.js:102:29)
at emitOne (events.js:115:13)
at Socket.emit (events.js:210:7)
at addChunk (_stream_readable.js:252:12)
at readableAddChunk (_stream_readable.js:239:11)
at Socket.Readable.push (_stream_readable.js:197:10)
at TCP.onread (net.js:589:20) code: 'ER_CON_COUNT_ERROR', errno: 1040, sqlState: '' }
why do you need unique connection per Me.profile().sessionId? Can you just re-use one single connection for all sessions?
Nope! This app process its data via mysql using create with select to generate analytical data so it can be used on dashboard or another tasks.
Each user can make use of different parameters to filter data from an table to generate another, so this generated table may have different data (per user need).
The temporary table solution make possible that each user can access those generated table based on the parameter that the current user set.
So, the session schema must be on the connection database itself, not on the node app on a way that on the same constructed workflow with the same tables names, each user will get different data because it were saved on the MySQL/MemSQL session he is.
With Me.profile().sessionId I can make an global object to save connection per user, not on a session connection by the app.
Here is how the process works https://youtu.be/vnaauWTzH_4?t=1m22s
I found out that I didnt call connection.connect();as soon as an connection were created. Doing an query without globallyconnection.connect();` make each mysql2 request count as a new connection.
import { Me } from '../shared/Me';
const mysql = require('mysql2');
export class BucketDb {
private static _connection = {};
public static connect(connectionData) {
const self = this;
if (!this._connection || (this._connection && !this._connection[Me.profile().sessionId])) {
this.instance(connectionData);
}
return this._connection[Me.profile().sessionId];
}
private static instance(connectionData) {
connectionData['connectAttributes'] = {}
connectionData['connectAttributes']['sessionId'] = Me.profile().sessionId;
this._connection[Me.profile().sessionId] = mysql.createConnection(connectionData);
this._connection[Me.profile().sessionId].connect();
}
public static end() {
if (this._connection && this._connection[Me.profile().sessionId]) {
this._connection[Me.profile().sessionId].end();
}
}
}
I found out that I didnt callconnection.connect();as soon as an connection were created. Doing an query without globallyconnection.connect();` make each mysql2 request count as a new connection.
no, new connection is created per mysql.createConnection(connectionData);. Connection is created immediately, even if you don't call .connect()
So, the session schema must be on the connection database itself, not on the node app on a way that on the same constructed workflow with the same tables names, each user will get different data because it were saved on the MySQL/MemSQL session he is.
I think it's a bad DB design. Mysql does not like too many connections, and typically connection limit is st to 100. Each new connection lives in own os thread with all related overhead - usually >2mb stack space per thread, context switching etc etc.
"other solutions" might be: store temp data in redis or have it in mysql but in one (non-temporary) single table with session id as one of the keys
At least there is an error. I observed stuck request select 1(zero errors) through local unix socket connection. show processlist shows that my connections are sleeping.
