Connection.query is not being executed with simple SELECT

[avi312singh]

Using express and have this middleware inside my get allRows endpoint. When I hit it in POSTMAN locally it just keep spinning with: Sending request... and the program never errors.

router.get('/allRows', async (req, res) => {
    console.log("HIT")
    // also another guard for table name inside an array is needed
    if (req.query.tableName) {
            console.log("HIT")
            connection.connect();
            connection.query(`SELECT * FROM ${req.query.tableName}`), (err, rows, fields) => {
                console.log("HIT", rows)
                if (err) console.log(err);
                res.status(200).json({
                    message: `Successfully got all data from ${req.query.tableName} LIMIT 1`,
                    result: rows
                });
                console.log("result")
            };
    }
    else {
        res.status(400).json({
            message: `Please provide table name`,
        });
        console.log('Missing table name');
    }
});

I am getting 2 console hits at the moment when debugging, here are some screenshots below from the debugger - you can see that as soon as it hits connection.query() it just skips over it and doesn't go into the arrow function.

debugger at connection.query [debugger after connection.query]https://i.stack.imgur.com/uUVSZ.jpg

[avi312singh]

here's the same table I am trying to query with node in workbench

[mbaumgartl]

May be your Node application is unable to connect to the database. Add a callback to the connect method and check for errors:

connection.connect(function(err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }

  console.log('connected as id ' + connection.threadId);
});

[avi312singh]

Hi I have added the callback and it still skips over it

Here is the connection object when I was debugging:

{
  _events: {
  },
  _eventsCount: 0,
  _maxListeners: undefined,
  config: {
    host: "xxxx.xxxxx.eu-west-2.rds.amazonaws.com",
    port: 3306,
    localAddress: undefined,
    socketPath: undefined,
    user: "xxxxx",
    password: "xxxxx",
    database: "xxxxx",
    connectTimeout: 10000,
    insecureAuth: false,
    supportBigNumbers: false,
    bigNumberStrings: false,
    dateStrings: false,
    debug: undefined,
    trace: true,
    stringifyObjects: false,
    timezone: "local",
    flags: "",
    queryFormat: undefined,
    pool: undefined,
    ssl: false,
    localInfile: true,
    multipleStatements: false,
    typeCast: true,
    maxPacketSize: 0,
    charsetNumber: 33,
    clientFlags: 455631,
  },
  _socket: {
    connecting: true,
    _hadError: false,
    _parent: null,
    _host: "xxxxx.xxxxx.eu-west-2.rds.amazonaws.com",
    _readableState: {
      objectMode: false,
      highWaterMark: 16384,
      buffer: {
        head: null,
        tail: null,
        length: 0,
      },
      length: 0,
      pipes: [
      ],
      flowing: true,
      ended: false,
      endEmitted: false,
      reading: false,
      sync: true,
      needReadable: false,
      emittedReadable: false,
      readableListening: false,
      resumeScheduled: true,
      errorEmitted: false,
      emitClose: false,
      autoDestroy: false,
      destroyed: false,
      errored: null,
      closed: false,
      closeEmitted: false,
      defaultEncoding: "utf8",
      awaitDrainWriters: null,
      multiAwaitDrain: false,
      readingMore: false,
      decoder: null,
      encoding: null,
    },
    _events: {
      end: [
        function onReadableStreamEnd() {
          if (!this.allowHalfOpen) {
            this.write = writeAfterFIN;
            if (this.writable)
              this.end();
            else if (!this.writableLength)
              this.destroy();
          } else if (!this.destroyed && !this.writable && !this.writableLength)
            this.destroy();
        },
        function () {
          if (Events.usingDomains && ee.domain) {
            ee.domain.enter();
            fn.apply(this, arguments);
            ee.domain.exit();
          } else {
            fn.apply(this, arguments);
          }
        },
      ],
      data: function () {
        if (Events.usingDomains && ee.domain) {
          ee.domain.enter();
          fn.apply(this, arguments);
          ee.domain.exit();
        } else {
          fn.apply(this, arguments);
        }
      },
      error: function () { [native code] },
      connect: [
        function () { [native code] },
        function () { [native code] },
      ],
      timeout: function () { [native code] },
    },
    _eventsCount: 5,
    _maxListeners: undefined,
    _writableState: {
      objectMode: false,
      highWaterMark: 16384,
      finalCalled: false,
      needDrain: false,
      ending: false,
      ended: false,
      finished: false,
      destroyed: false,
      decodeStrings: false,
      defaultEncoding: "utf8",
      length: 0,
      writing: false,
      corked: 0,
      sync: true,
      bufferProcessing: false,
      onwrite: function () { [native code] },
      writecb: null,
      writelen: 0,
      afterWriteTickInfo: null,
      buffered: [
      ],
      bufferedIndex: 0,
      allBuffers: true,
      allNoop: true,
      pendingcb: 0,
      prefinished: false,
      errorEmitted: false,
      emitClose: false,
      autoDestroy: false,
      errored: null,
      closed: false,
      closeEmitted: false,
      writable: true,
    },
    allowHalfOpen: false,
    _sockname: null,
    _pendingData: null,
    _pendingEncoding: "",
    server: null,
    _server: null,
    timeout: 10000,
  },
  _protocol: {
    _events: {
      data: function(data) {
        connection._socket.write(data);
      },
      end: [
        function() {
          connection._socket.end();
        },
        function () { [native code] },
      ],
      handshake: function () { [native code] },
      initialize: function () { [native code] },
      unhandledError: function () { [native code] },
      drain: function () { [native code] },
      enqueue: function () { [native code] },
    },
    _eventsCount: 7,
    _maxListeners: undefined,
    readable: true,
    writable: true,
    _config: {
      host: "xxxxx.xxxxx.eu-west-2.rds.amazonaws.com",
      port: 3306,
      localAddress: undefined,
      socketPath: undefined,
      user: "xxxxx",
      password: "xxxxx",
      database: "xxxxx",
      connectTimeout: 10000,
      insecureAuth: false,
      supportBigNumbers: false,
      bigNumberStrings: false,
      dateStrings: false,
      debug: undefined,
      trace: true,
      stringifyObjects: false,
      timezone: "local",
      flags: "",
      queryFormat: undefined,
      pool: undefined,
      ssl: false,
      localInfile: true,
      multipleStatements: false,
      typeCast: true,
      maxPacketSize: 0,
      charsetNumber: 33,
      clientFlags: 455631,
    },
    _connection: [Circular],
    _callback: null,
    _fatalError: null,
    _quitSequence: null,
    _handshake: true,
    _handshaked: false,
    _ended: false,
    _destroyed: false,
    _queue: [
      {
        _events: {
          error: function(err) {
            self._delegateError(err, sequence);
          },
          packet: function(packet) {
            sequence._timer.active();
            self._emitPacket(packet);
          },
          timeout: function() {
            var err = new Error(sequence.constructor.name + ' inactivity timeout');

            err.code    = 'PROTOCOL_SEQUENCE_TIMEOUT';
            err.fatal   = true;
            err.timeout = sequence._timeout;

            self._delegateError(err, sequence);
          },
          "start-tls": function () {
            sequence._timer.active();
            self._connection._startTLS(function(err) {
              if (err) {
                // SSL negotiation error are fatal
                err.code  = 'HANDSHAKE_SSL_ERROR';
                err.fatal = true;
                sequence.end(err);
                return;
              }

              sequence._timer.active();
              sequence._tlsUpgradeCompleteHandler();
            });
          },
          end: [
            function () {
              self._handshaked = true;

              if (!self._fatalError) {
                self.emit('handshake', self._handshakeInitializationPacket);
              }
            },
            function () {
              self._dequeue(sequence);
            },
          ],
        },
        _eventsCount: 5,
        _maxListeners: undefined,
        _callback: function () {
          var domains = [];
          var ret;

          while (process.domain) {
            domains.shift(process.domain);
            process.domain.exit();
          }

          try {
            ret = fn.apply(this, arguments);
          } finally {
            for (var i = 0; i < domains.length; i++) {
              domains[i].enter();
            }
          }

          return ret;
        },
        _callSite: {
        },
        _ended: false,
        _timeout: undefined,
        _timer: {
          _object: [Circular],
          _timeout: null,
        },
        _config: {
          host: "xxxxx.xxxxx.eu-west-2.rds.amazonaws.com",
          port: 3306,
          localAddress: undefined,
          socketPath: undefined,
          user: "xxxxx",
          password: "xxxxx",
          database: "sys",
          connectTimeout: 10000,
          insecureAuth: false,
          supportBigNumbers: false,
          bigNumberStrings: false,
          dateStrings: false,
          debug: undefined,
          trace: true,
          stringifyObjects: false,
          timezone: "local",
          flags: "",
          queryFormat: undefined,
          pool: undefined,
          ssl: false,
          localInfile: true,
          multipleStatements: false,
          typeCast: true,
          maxPacketSize: 0,
          charsetNumber: 33,
          clientFlags: 455631,
        },
        _handshakeInitializationPacket: null,
      },
    ],
    _handshakeInitializationPacket: null,
    _parser: {
      _supportBigNumbers: false,
      _buffer: {
      },
      _nextBuffers: {
        bufs: [
        ],
        size: 0,
      },
      _longPacketBuffers: {
        bufs: [
        ],
        size: 0,
      },
      _offset: 0,
      _packetEnd: null,
      _packetHeader: null,
      _packetOffset: null,
      _onError: function () { [native code] },
      _onPacket: function () { [native code] },
      _nextPacketNumber: 0,
      _encoding: "utf-8",
      _paused: false,
    },
  },
  _connectCalled: true,
  state: "disconnected",
  threadId: null,
}

Also I have other endpoints which are using the same code structure to perform queries on the database and they are working fine, for example here is a POST endpoint:

router.post('/serverInfo', async (req, res) => {
    if (req.query.playerCount && req.query.botCount && req.query.serverName && req.query.mapName) {
        connection.query(`INSERT INTO serverInfo (playerCount, botCount, serverName, mapName) VALUES ('${req.query.playerCount}', '${req.query.botCount}', '${req.query.serverName}', '${req.query.mapName}')`, (err, result, fields) => {
            if (err) console.log(err);
            if (result) {
                res.status(201).json({
                    playerCount: req.query.playerCount, botCount: req.query.botCount, serverName: req.query.serverName, mapName: req.query.mapName
                });
                console.log(chalk.blue('Database entry ' + chalk.whiteBright.underline(keyword('serverInfo') + ' added/updated for serverInfo endpoint!')))
                console.log({ playerCount: req.query.playerCount, botCount: req.query.botCount, serverName: req.query.serverName, mapName: req.query.mapName })
            }
            if (fields) console.log(fields);
        });
    } else {
        res.send('Please provide playerCount, botCount, serverName and mapName in request')
        console.log('Missing a parameter');
    }
})

[avi312singh]

Here is the line which is failing https://github.com/avi312singh/FTYD-Express-App/blob/aggregatedStats/routes/serverstats.js#L219

[mbaumgartl]

For some routes you close the connection and for some routes you don't call the end method. Also I don't know how this application performs if it needs to handle multiple requests at the the time with a single connection.

If I were you I would switch to connection pooling and use the shortcut described in the documentation: https://github.com/mysqljs/mysql#pooling-connections

Your code should become shorter and more readable.

Also think about proper error handling (e.g. always return a response - even in case of an error) and proper escaping. Don't use template strings like this. Have a look at https://github.com/mysqljs/sqlstring and https://github.com/mysqljs/named-placeholders.

[avi312singh]

Ok thanks will look into it

[avi312singh]

Ok actually it was a fricken syntax error... https://github.com/avi312singh/FTYD-Express-App/commit/8947d1957fd9e9717ca2bb0bb661c7e5d9e48b9e