Node MySQL Connection Pool - wait for database to start

Question

How do I check if a MySQL database is ready for some queries from a Node MySQL Connection Pool?

I have a Docker environment consisting of thee containers:

container 1: web server
container 2: api
container 3: database

The database container runs a MySQL database. The api container connects to that database. All three containers are started at the same time. The web server container is up after 0,5s. The api container is up after 2s. The database server is up after 20s.

Currently, the api tries to access the tables of the database before the database is up and running. This leads to errors like connection refused. The following code segment always ends up with the message "Error querying database!" when the MySQL database is not yet up:

const sql: string = 'SELECT * FROM sometable;';
MySQL.createPool({
    connectionLimit: 10,
    acquireTimeout: 30000,
    waitForConnections: true,
    database: 'mydatabase',
    host: 'localhost',
    multipleStatements: true,
    password: 'mypassword',
    user: 'root',
}).query(sql, (err, result) => {
    if (result) {
        console.log('Successfully queried database.');
    } else {
        console.log('Error querying database!');
    }
});

Versions in use:

OS: Ubuntu 19.10
Node: v13.6.0
MySQL (Node API): "@types/mysql": "2.15.8" and "mysql": "2.17.1"
MySQL (Docker Database): mysql:5.7.28
TypeScript: 3.7.4

I would like to check (and wait) the database readiness out of the api, possibly using the Connection Pool I use for queries. Is that possible?

niry · Accepted Answer · 2020-01-16 16:28:37Z

2

Retry to connect with `setTimeout()`:

(answer in Javascript rather than typescript)

'use strict';

const dbpool = require('mysql').createPool({
    connectionLimit: 10,
    acquireTimeout: 30000,
    waitForConnections: true,
    database: 'mydatabase',
    host: 'localhost',
    multipleStatements: true,
    password: 'mypassword',
    user: 'root',
});

const sql = 'SELECT * FROM sometable;';

const attemptConnection = () =>
  dbpool.getConnection((err, connection) => {
  if (err) {
    console.log('error connecting. retrying in 1 sec');
    setTimeout(attemptConnection, 1000);
  } else {
    connection.query(sql, (errQuery, results) => {
      connection.release();
      if (errQuery) {
        console.log('Error querying database!');
      } else {
        console.log('Successfully queried database.');
      }
    });
  }
});

attemptConnection();

Here is my test run:

$ sudo service mysql stop; node test.js & sudo service mysql start
[1] 24737
error connecting. retrying in 1 sec
error connecting. retrying in 1 sec
$ Successfully queried database.

FYI, The program never ends because it needs dbpool.end();

edited Jan 16, 2020 at 16:28

answered Jan 16, 2020 at 1:16

niry

3,31825 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Socrates Over a year ago

IMO it should be obligatory to provide a statement when placing a downvote. @StackOverflow: Please do that! ;) As for your code, it helped! And it actually is so simple! Thanks for your help niry.

João Pimentel Ferreira Over a year ago

why don't you simply finish the function attemptConnection with })());, i.e., you self invoke it: blog.mgechev.com/2012/08/29/…

João Pimentel Ferreira Over a year ago

I got it, the callback in setTimeout can't be a self-invoked function, ignore hence my last comment

niry Over a year ago

@JoãoPimentelFerreira you could do let attemptConnection; (attemptConnection = () => '...')() but you can't do it with const.

Charlie · Accepted Answer · 2020-01-17 08:21:21Z

0

Your API should try to connect to the database with a timeout and a certain threshold of connection attempts. However, there are readily available solutions for this scenario.

Try using wait-for-mysql module.

waitForMy = require 'wait-for-mysql'
config =
  username: user
  password: pass
  quiet: true
  query: 'SELECT 1'

waitForMy.wait(config)

edited Jan 17, 2020 at 8:21

answered Jan 16, 2020 at 16:35

Charlie

24k12 gold badges65 silver badges96 bronze badges

7 Comments

niry Over a year ago

You have a typo in wait-form-mysql link. it should be wait-for-mysql. If you look at the source code of it, it does what I've suggested, right here: setTimeout testConnection, connectDelay

Charlie Over a year ago

@niry We should always use existing libraries without trying tto reinvent the wheel. Thanks for the tip of the typo.

niry Over a year ago

Ho did you get all the way to "reinventing the wheel"? It is merely setTimeout() and wrapping in a function.

niry Over a year ago

I'm not against using libraries, and your solution might work perfectly fine. Here are example reasons to not use a library: #1. You want to learn. #2. Not maintained libraries tend to cause issues (FYI, Latest commit was on Sep 29, 2015) #3. Adding a library when the solution is so simple isn't really saving anything, in fact, it might cost you more.

niry Over a year ago

#1 - "Founded in 2008, Stack Overflow is the largest, most trusted online community for anyone that codes to learn...". #3 - there is absolutely no correlation between weekly downloads and time saved or not.

|

João Pimentel Ferreira · Accepted Answer · 2020-03-14 22:42:22Z

Here you have a variation but with no need to mysql pooling. I'm using this on my server and it does work:

const mysql = require('mysql')
var db // global, to use later to db.query
var dbInfo = {
  host     : 'example.org',
  database : 'some_database',
  user     : 'bob',
  password : 'secret'
}

function connectToDb(callback) {
  const attemptConnection = () => {
    console.log('Attempting to connect to db')
    dbInfo.connectTimeout = 2000 // same as setTimeout to avoid server overload 
    db = mysql.createConnection(dbInfo)
    db.connect(function (err) {
      if (err) {
        console.log('Error connecting to database, try again in 1 sec...')
        db.destroy() // end immediately failed connection before creating new one
        setTimeout(attemptConnection, 2000)
      } else {
        callback()
      }
    })
  }
  attemptConnection()
}

// now you simply call it with normal callback
connectToDb( () => {
  console.log('Connection successfully')
  // do some queries
  db.query(..)
})

Collectives™ on Stack Overflow

Node MySQL Connection Pool - wait for database to start

3 Answers 3

Retry to connect with `setTimeout()`:

4 Comments

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Retry to connect with setTimeout():

4 Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Retry to connect with `setTimeout()`: