sequelize: Connection with pool never closes

I am using AWS Lambda and API Gateway together to build a REST API for the web application at my job. I noticed that the connections to the MySQL server never get closed, or am I wrong based on the info below?

Single AWS Lambda function

var Sequelize = require('sequelize'),
    seqlz = new Sequelize('db', 'user', 'password', {
        host: 'mydbdomain.com',
        port: '3306',
        dialect: 'mysql',
        timezone: 'UTC-05:00',
        pool: {
            max: 1,
            min: 0,
            idle: 5000
        }
    }),
    Promotion = seqlz.define('promotion', {
        tournamentId: {
            type: Sequelize.BIGINT(20),
            primaryKey: true
        },
        iconFilePath: Sequelize.STRING,
        name: Sequelize.STRING(75),
        eventStartDate: Sequelize.DATE,
        eventEndDate: Sequelize.DATE
    }, {
        timestamps: false,
        tableName: 'Tournament'
    }),
    Advertiser = seqlz.define('advertiser', {
        advertiserId: {
            type: Sequelize.BIGINT(20),
            primaryKey: true
        },
        name: Sequelize.STRING(75),
    }, {
        timestamps: false,
        tableName: 'Advertiser'
    }),
    Game = seqlz.define('game', {
        gameId: {
            type: Sequelize.BIGINT(20),
            primaryKey: true
        },
        name: Sequelize.STRING(75)
    }, {
        timestamps: false,
        tableName: 'Game'
    }),
    GamePlayLog = seqlz.define('gamePlayLog', {
        gamePlayLogId: {
            type: Sequelize.BIGINT(20),
            primaryKey: true
        },
        createdDate: Sequelize.DATE
    }, {
        timestamps: false,
        tableName: 'GamePlayLog'
    });

Promotion.belongsTo(Advertiser, {foreignKey: 'advertiserId'});
Promotion.belongsTo(Game, {foreignKey: 'gameId'});
Promotion.hasMany(GamePlayLog, {foreignKey: 'tournamentId'});

exports.handler = function(event, context) {
    Promotion.findAll({
        attributes: [
            'tournamentId',
            'name',
            'eventStartDate',
            'eventEndDate'
        ],
        where: {status: 3},
        include: [
            {
                model: Advertiser,
                attributes: ['name']
            },
            {
                model: Game,
                attributes: ['name']
            },
            {
                model: GamePlayLog,
                attributes: ['createdDate']
            }
        ]
    }).then(function(promos) {
        context.done(null, promos);
    });
};

I’d assume the connection would automatically close after 5 seconds of no use, but it’s not not unless I’m misinterpreting what I’m seeing on MySQL Workbench. I tried using sequelize.close() before context.done() (with and w/o the pool) but would get an error at random times: Unhandled rejection Error: ConnectionManager.getConnection was called after the connection manager was closed!

Here’s an image of my MySQL Workbench. At the time of this screenshot, the connections had been hanging for over 500 seconds if I’m not mistaken. Way more than 5 seconds lol. They are the ones that begin with ec2.

http://oi67.tinypic.com/314qkqe.jpg

Any help is appreciated!

About this issue

Original URL
State: closed
Created 8 years ago
Comments: 16 (11 by maintainers)

Most upvoted comments

^^^ The guy looks credible

catamphetamine on Aug 27, 2020

For people experiencing issues in AWS Lambda, I have created a pull request that documents how to properly configure sequelize for Lambda. Hopefully you’ll be able to see it in https://sequelize.org/master/manual/aws-lambda.html once it gets merged. In the meantime, please check #12642 out and provide feedback if you find any inaccuracies or space for improvement.

marcogrcr on Aug 26, 2020

@mickhansen Thanks for your patience, it turns out that putting everything inside the exports.handler function did the trick. BUT, using pool, the connection still never closed after many seconds. I AM able to now close the connection manually without getting the previous error.

exports.handler = function(event, context) {
    var Sequelize = require('sequelize'),
        seqlz = seqlz || new Sequelize('db', 'user', 'pw', {
                host: 'mydbdomain.com',
                port: '3306',
                dialect: 'mysql',
                timezone: 'UTC-05:00'
            }),
        Promotion = seqlz.define('promotion', {
            tournamentId: {
                type: Sequelize.BIGINT(20),
                primaryKey: true
            },
            iconFilePath: Sequelize.STRING,
            name: Sequelize.STRING(75),
            eventStartDate: Sequelize.DATE,
            eventEndDate: Sequelize.DATE
        }, {
            timestamps: false,
            tableName: 'Tournament'
        }),
        Advertiser = seqlz.define('advertiser', {
            advertiserId: {
                type: Sequelize.BIGINT(20),
                primaryKey: true
            },
            name: Sequelize.STRING(75),
        }, {
            timestamps: false,
            tableName: 'Advertiser'
        }),
        Game = seqlz.define('game', {
            gameId: {
                type: Sequelize.BIGINT(20),
                primaryKey: true
            },
            name: Sequelize.STRING(75)
        }, {
            timestamps: false,
            tableName: 'Game'
        }),
        GamePlayLog = seqlz.define('gamePlayLog', {
            gamePlayLogId: {
                type: Sequelize.BIGINT(20),
                primaryKey: true
            },
            createdDate: Sequelize.DATE
        }, {
            timestamps: false,
            tableName: 'GamePlayLog'
        });

    Promotion.belongsTo(Advertiser, {foreignKey: 'advertiserId'});
    Promotion.belongsTo(Game, {foreignKey: 'gameId'});
    Promotion.hasMany(GamePlayLog, {foreignKey: 'tournamentId'});

    Promotion.findAll({
        attributes: [
            'tournamentId',
            'name',
            'eventStartDate',
            'eventEndDate'
        ],
        where: {status: 3},
        include: [
            {
                model: Advertiser,
                attributes: ['name']
            },
            {
                model: Game,
                attributes: ['name']
            },
            {
                model: GamePlayLog,
                attributes: ['createdDate']
            }
        ]
    }).then(function(promos) {
        seqlz.close();
        context.done(null, promos);
    });
};

jirehstudios on Feb 17, 2016

Threatening to move to another ORM because you’re unwilling to isolate the error to Sequelize isn’t really helpfull in solving it.

Like i mentioned a few times Lambda will reuse the context, have you ruled out that this is the issue? Please test whether it also hangs when using it outside of Lambda.

mickhansen on Feb 17, 2016

But where in the lambda file? In the global context? Then it might possible be reused by multiple parallel calls.

mickhansen on Feb 17, 2016