chill117 / express-mysql-session Goto Github PK

Hello,
I was hoping you could tell me what am I doing wrong when I try to use this module with my app.
I have a database I use for this express app already in place, and i would like that sessions are just one table in it.
I have a module for database db.js:

var mysql = require('mysql');
var connection = mysql.createPool({
    connectionLimit : 10,
    multipleStatements : true,
    host : 'localhost',
    user : 'username',
    password : 'pass',
    database : 'my_db'
});

connection.getConnection(function(err){
    if(err) throw err;
});

module.exports = connection;

//I am then able to use it like that in other files:
var db = require('./db);
db.query(...)

Now, when i tried to use your module to store my session (i use var req.session.user which holds all info of currently logged in user) I have a problem, because it keeps saying that it cannot read property 'user' of undefined - which means session is not created.

I have this in app.js file:

var db = require('./db');
var session = require('express-session');
var MySQLStore = require('express-mysql-session')(session);

var sesStore = new MySQLStore({}, db);

app.use(session({
	secret : "EETTZUUZKJGFD",
	name : "my_site",
	saveUninitialized : true,
	resave : true,
	store : sesStore,
	cookie : {
		maxAge : 3600000
	}
}));

I would really appreciate the help, thank you

When I try to execute this line:
var MySQLStore = require('express-mysql-session')(session);
I am getting that it is impossible to set the proporty options of undefined.
The error happens in line 39 of index.js.

Do anyone have any idea about how to solve it?

Hello,
My plan is to call some extra code when the destroy function is called by me or automatically by your program(when you check expiry), how can I do that ?

Example :
sessionStore.on('destroy', function(){
...
})

Is it possible ?

I noticed that my server was performing poorly when there were many simultaneous requests and finally tracked it down to using express-mysql-session. Specifically, I was on version 1.0.0 and I noticed that if I set resave:false in the session options, I no longer had the problem. However, I saw that express-mysql-session 1.0.0 did support touch, and therefore was unsafe to use with resave:false. I upgraded to 1.2.0, but now the performance issues are back.

Questions:

• How can I debug performance issues with express-mysql-session?

• Why do the docs show a sample config with resave:true? Shouldn't this be set to false if express-mysql-session supports touch?

Express 4.0 has stopped budling session store. Additional dependencies needs to be added to the package to fix this. Thx

Perhaps I am missing something simple here, if so, please gently point me in the right direction.

I've noticed when I add a session expiration time, the cookie converts from a browser-based session cookie to a normal client-cookie. I'd would prefer to keep the cookies as pure browser-based session cookies but still have the option for them to be automatically cleared from the DB at an interval.

I'm consistently getting this error:

SyntaxError: Unexpected token
at Object.parse (native)
at Query._callback (/vagrant/server/node_modules/express-mysql-session/lib/index.js:157:34)
at Query.Sequence.end (/vagrant/server/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24)
at Query._handleFinalResultPacket (/vagrant/server/node_modules/mysql/lib/protocol/sequences/Query.js:143:8)
at Query.EofPacket (/vagrant/server/node_modules/mysql/lib/protocol/sequences/Query.js:127:8)
at Protocol._parsePacket (/vagrant/server/node_modules/mysql/lib/protocol/Protocol.js:271:23)
at Parser.write (/vagrant/server/node_modules/mysql/lib/protocol/Parser.js:77:12)
at Protocol.write (/vagrant/server/node_modules/mysql/lib/protocol/Protocol.js:39:16)
at Socket. (/vagrant/server/node_modules/mysql/lib/Connection.js:82:28)
at Socket.EventEmitter.emit (events.js:95:17)

It seems that the mysql driver is escaping the data element when the SQL is generated.

Data Element:

{"cookie":{"originalMaxAge":null,"expires":null,"httpOnly":true,"path":"/"}}

Generated SQL:

INSERT INTO sessions (session_id, expires, data) VALUES ('705L-OuRjyiksGE0iosiCj81LOfoxmQo', 1441302828, '{"cookie":{"originalMaxAge":null,"expires":null,"httpOnly":true,"path":"/"}}') ON DUPLICATE KEY UPDATE expires = VALUES(expires), data = VALUES(data)

Is there a configuration I've missed? I would think if everyone was seeing this it would be more widely known.

Config:

 mysql = require("mysql"),
    options = {
        host: "localhost",
        port: 3306,
        user: ... 
        password: ...
        database: ...
    },
    db = mysql.createConnection(options),
    sessionStore = new SessionStore({}, db),

app.use(session({
    key: "...",
    secret:"...",
    store: sessionStore,
    resave: true,
    saveUninitialized: true
}));

If the MySQL server goes away, it appears that express-mysql-session doesn't attempt to reconnect automatically.

server-0 Error: Cannot enqueue Query after fatal error.
server-0 at Protocol._validateEnqueue (/home/ubuntu/server/node_modules/express-mysql-session/node_modules/mysql-connection-manager/node_modules/mysql/lib/protocol/Protocol.js:199:16)
server-0 at Protocol._enqueue (/home/ubuntu/server/node_modules/express-mysql-session/node_modules/mysql-connection-manager/node_modules/mysql/lib/protocol/Protocol.js:135:13)
server-0 at Connection.query (/home/ubuntu/server/node_modules/express-mysql-session/node_modules/mysql-connection-manager/node_modules/mysql/lib/Connection.js:201:25)
server-0 at MySQLStore.get (/home/ubuntu/server/node_modules/express-mysql-session/lib/index.js:135:19)

I think that the managing of connections is orthogonal to the objective of this module. The dependency with mysql-connection-manager could be decoupled by accepting, in the construction of the session object, either a connection (and use #query), a pool (and use #query) or a function which in turn is expected to return a connection. Then, the following would work:

var connection = mysql.createConnection(options);
var sessionStore = new MySQLStore({}/* session store options */, connection);

var pool = mysql.createPool(options);
var sessionStore = new MySQLStore({}/* session store options */, pool);

var MySQLConnectionManager = require('mysql-connection-manager');
var manager = new MySQLConnectionManager(options);
function getConnection() { return manager.connection; }
var sessionStore = new MySQLStore({}/* session store options */, getConnection);

In any case, as we have been discussing in this issue I think I'd recommend deprecating mysql-connection-manager, and this issue goes in that direction.

Actually, if the possibility of passing a MySQLConnectionManager itself was never documented, I'd just accept a connection or pool (which are "the same" if you just use #query and similar).

I am happy to change the code and submit a pull request, if you agree with this.

I have on app project that connect to a distinct MySQL by user demand. This application use MySQL session to create temporary tables per MySQL distinct database connection. I have i have succeeded created session when app starts, but this app really need to change the session store implementation module when a different MySQL connection is need by user.

1. First -> create app and implement session store middleware like express documentation
2. Tried to change session store on router demand

I have tried to change the session store like this:

router.get('/v1/:databaseName', function (req, res, next) {

    const databaseName = req.params.databaseName

    const options = {
	host: 'localhost',
	port: 3306,
	user: 'root',
	password: 'root',
	database: databaseName
     };

    const sessionStore = new MySQLStore(options);

    req.session.save(function () {
        req.session.store = sessionStore
        // do things from here
    });

});

So, how to use session store module on demand?

I try to npm install and it installed 0.4.4. but the doc on https://www.npmjs.com/package/express-mysql-session on actually for v1.0.0-rc.0. This might be a minor thing but it took me hours to figure out. It would be nice if a notice could be put in readme to reduce possible confusion.

The below code always creates a sessions database table with name 'sessions' instead of my defined 'httpSessions'

My dive into the source code suggests that if options.createDatabaseTable == true then index.js pulls its sql code from schema.sql in the module root folder. And that always uses 'sessions' to name the database table.

Perhaps I misunderstand the documentation but at the very least this is counter intuitive.

var MysqlSessionStore = require('express-mysql-session');

var sessionStore = new MysqlSessionStore({
    host: mysql_host,
    port: mysql_port,
    user: mysql_user, 
    password: mysql_password,
    database: mysql_database,
    schema: {
        tableName: 'httpSessions'
    }
});

I love this library - just trying to improve it. Thanks!

Now I would like to send a websocket when a session is expired so that I disconnect in real time the user, I'm trying to read the mysql bin/log with the npm package zongji to catch changes on my DB but It would be easier to have the events, can you add this functionality ? Is it long to develop ?

First of all, thanks for the great library.

As the title suggest, I want to expire all session of a given user after he has performed a password reset.

Since the userID is stored in the data field in mysql, I would be extremely inefficient to check each row and delete/expire all rows with the matching userID (since the data column is a JSON string that isn't indexed by userID).

I guess an easy solution would be to stroe the userID as a separate column inside the session table, however, I'm not sure how to fill that column then. Since the other fields are all automatically handled by express-mysql-session?

Maybe there is another obvious solution that I just don't see right now?

Thanks for you help.

Hi,

Thanks for the great library. I'm using node-mysql2 because it is a lot faster and because it has promise support. The problem is this library is not compatible with queries in the form of promises. Right now it only has support for queries that invoke callbacks therefore making it incompatible with node-mysql2 promise-based connections.

This is a really easy fix, it could either detect if the connection passed into the library is promise based or an option could be based in that flags the connection as promise based.

This would only require putting any connection.query calls inside of a wrapper so it can make the query via callback or promise based on the options passed in.

e.g:

MySQLStore.prototype._query = function (sql, params, callback) {
  if (this.options.promise) {
    this.connection.query(sql, params)
      .then((rows) => {
        callback(null, rows);
      }).catch((error) => {
        callback(error, null);
      });
  } else {
    this.connection.query(sql, params, callback);
  }
}

Note that I have just typed the above code out, it's not tested and is just a basis for the suggested implementation. All current this.connection.query calls would then be replaced with this._query.

Hopefully this all makes sense, if you have any questions feel free to ask.

I believe it's due to the fact that setExpirationInterval() is not being called anywhere in the code so nothing to initiate the setInterval callback?

express version 4.0

npm start generates the following
/node_modules/express-mysql-session/lib/index.js:31
this.setDefaultOptions()
^

TypeError: Object # has no method 'setDefaultOptions'
at module.exports (/home/ubuntu/d/myapp/node_modules/express-mysql-session/lib/index.js:31:7)
at Object. (/home/ubuntu/d/myapp/app.js:22:25)
at Module._compile (module.js:456:26)
at Object.Module._extensions..js (module.js:474:10)
at Module.load (module.js:356:32)
at Function.Module._load (module.js:312:12)
at Module.require (module.js:364:17)
at require (module.js:380:17)
at Object. (/home/ubuntu/d/myapp/bin/www:7:11)
at Module._compile (module.js:456:26)

Is there a way to connect using a socket path or a db url?

Thanks in advance!!

have error
not have

"cookie":{"originalMaxAge":null,"expires":null,"httpOnly":true,"path"

it's will
{"cookie":{"originalMaxAge":null,"expires":null,"httpOnly":true,"path":"/"},data.......}

I have the below code working just fine, but after a rough 12-16 hours period it shuts down and I get this message:

PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR

Could you point out what did I do wrong? I don't have a great knowledge about this stuff, followed a tutorial, however the connection is not stable.

var express = require("express");
var mysql   = require("mysql");
var bodyParser  = require("body-parser"); 
var md5 = require('md5');
var rest = require("./REST.js");
var app  = express();
var currentDateAndTime = new Date();

function REST(){
    var self = this;
    self.connectMysql();
};

REST.prototype.connectMysql = function() {
    var self = this;
    var pool      =    mysql.createPool({
        connectionLimit : 100,
        host : "localhost",
            user : "",
            password : "",
            database : "",
        debug    :  false
    });
    pool.getConnection(function(err,connection){
        if(err) {
          self.stop(err);
        } else {
          self.configureExpress(connection);
        }
    });
}

REST.prototype.configureExpress = function(connection) {
      var self = this;
      app.use(bodyParser.urlencoded({ extended: true }));
      app.use(bodyParser.json());
      var router = express.Router();
      app.use('/api', router);
      var rest_router = new rest(router,connection,md5);
      self.startServer();
}

REST.prototype.startServer = function() {
      app.listen(3000,function(){
          console.log(currentDateAndTime + " All right ! I am alive at Port 3000.");
      });
}

REST.prototype.stop = function(err) {
    console.log(currentDateAndTime + " ISSUE WITH MYSQL n" + err);
    process.exit(1);
}

new REST();

I am using the sql uri for my other database connections, can I not use it for this too or do I need to make it into a set of keys for the options.

Basically if you have options set with tableName but not columnName like so:

schema: {
  tableName: 'sessions'
}

You're required to also set options.schema.columnNames because the default values aren't set. Probably should use the default values still for columnNames even if tableName is specified.

Or should kick back an cb(error)

Throws this currently:

TypeError: Cannot read property 'session_id' of undefined
     at /foo/node/node_modules/express-mysql-session/lib/index.js:109:35
     at fs.js:272:14
     at Object.oncomplete (fs.js:108:15)

I may swing back and fix this and do a pull request later.

It would be nice to be able to specify the name of the sessions table to create as an option to the session store. Possible use case is consistent naming of database tables. Thanks.

session = require('express-session'),
MySQLStore = require('express-mysql-session')(session);

sessionStore = new MySQLStore({}, con);

app = require('sockpress').init({
        key: '...',
        store: sessionStore,
        secret: '...',
        resave: true,
        saveUninitialized: true,
        cookie: {expires: new Date("2105")}
});

Trips up with a type error on line 157 node_modules/express-mysql-session/lib/index.js as getTime cannot function against what is a string

Changing:

expires = data.cookie.expires;

To:

expires = new Date(data.cookie.expires);

resolves the issue but it feels like data.cookies.expires should already be a date object.

Following is the error stack:

Error: Cannot enqueue Query after fatal error.
at Protocol._validateEnqueue (/users/pankajsharma/HomeGullyWork/homebulls_website/node_modules/mysql/lib/protocol/Protocol.js:193:16)
at Protocol._enqueue (/users/pankajsharma/HomeGullyWork/homebulls_website/node_modules/mysql/lib/protocol/Protocol.js:129:13)
at Connection.query (/users/pankajsharma/HomeGullyWork/homebulls_website/node_modules/mysql/lib/Connection.js:185:25)
at [object Object].SessionStore.get (/users/pankajsharma/HomeGullyWork/homebulls_website/node_modules/express-mysql-session/lib/index.js:118:18)
at Object.session as handle
at next (/users/pankajsharma/HomeGullyWork/homebulls_website/node_modules/express/node_modules/connect/lib/proto.js:190:15)
at Object.cookieParser as handle
at next (/users/pankajsharma/HomeGullyWork/homebulls_website/node_modules/express/node_modules/connect/lib/proto.js:190:15)
at Object.methodOverride as handle
at next (/users/pankajsharma/HomeGullyWork/homebulls_website/node_modules/express/node_modules/connect/lib/proto.js:190:15)
at Object.urlencoded as handle
at next (/users/pankajsharma/HomeGullyWork/homebulls_website/node_modules/express/node_modules/connect/lib/proto.js:190:15)
at Object.json as handle
at next (/users/pankajsharma/HomeGullyWork/homebulls_website/node_modules/express/node_modules/connect/lib/proto.js:190:15)
at Object.exports.allowCrossDomain as handle
at next (/users/pankajsharma/HomeGullyWork/homebulls_website/node_modules/express/node_modules/connect/lib/proto.js:190:15)
at Object.logger (/users/pankajsharma/HomeGullyWork/homebulls_website/node_modules/express/node_modules/connect/lib/middleware/logger.js:156:5)
at next (/users/pankajsharma/HomeGullyWork/homebulls_website/node_modules/express/node_modules/connect/lib/proto.js:190:15)
at Object.compression as handle
at next (/users/pankajsharma/HomeGullyWork/homebulls_website/node_modules/express/node_modules/connect/lib/proto.js:190:15)
at Object.expressInit as handle
at next (/users/pankajsharma/HomeGullyWork/homebulls_website/node_modules/express/node_modules/connect/lib/proto.js:190:15)
at Object.query as handle
at next (/users/pankajsharma/HomeGullyWork/homebulls_website/node_modules/express/node_modules/connect/lib/proto.js:190:15)
at Function.app.handle (/users/pankajsharma/HomeGullyWork/homebulls_website/node_modules/express/node_modules/connect/lib/proto.js:198:3)
at Server.app (/users/pankajsharma/HomeGullyWork/homebulls_website/node_modules/express/node_modules/connect/lib/connect.js:65:37)
at Server.emit (events.js:98:17)
at HTTPParser.parser.onIncoming (http.js:2112:12)
at HTTPParser.parserOnHeadersComplete as onHeadersComplete
at Socket.socket.ondata (http.js:1970:22)
at TCP.onread (net.js:527:27)

Hi !
I've a weird error :

10% 0/1 build modules/Users/aurelienphixl/Documents/PhiXL Work/MyNetwork/node_modules/express-session-mysql/lib/index.js:39
this.options = options || {};

TypeError: Cannot set property 'options' of undefined
at module.exports (/Users/aurelienphixl/Documents/PhiXL Work/MyNetwork/node_modules/express-session-mysql/lib/index.js:39:15)
at Object.<anonymous> (/Users/aurelienphixl/Documents/PhiXL Work/MyNetwork/app.js:9:63)
at Module._compile (module.js:541:32)
at Object.Module._extensions..js (module.js:550:10)
at Module.load (module.js:456:32)
at tryModuleLoad (module.js:415:12)
at Function.Module._load (module.js:407:3)
at Module.require (module.js:466:17)
at require (internal/module.js:20:19)
at Object.<anonymous> (/Users/aurelienphixl/Documents/PhiXL Work/MyNetwork/bin/www:7:11)    

I'm using Node 6.0.0, Express 4.13.1, Express-session 1.13.0
I'm not really sure to understand it.

Can you help me ? :)

Thanks

Delete please.

Is a patch to make sure that if a set is called on an SID that no gets return that SID until the set has returned something anyone would want?

I ran into this issue in my own mysqlstore when my client and node server were on the same machine and my DB was on another high latency server. My node instance called the set, redirected my client, then called a get which returned before the set had finished, giving me a new user login bug.

Okay, so I know this sounds a bit crazy, but I'm currently running a site that uses Steam for OpenID, and part of that is that it passes back their steam username, which is serialized in the session.

We had a user who's cookie JSON was continually stored as unfinished--it simply cut-off halfway through. Turns out he had Unicode Emojii in his steam name that caused the cookie to just give up and stop there--causing a failure later when the website tried to read the session since it couldn't parse an invalid JSON file (see attached images).

I'm not sure where it's failing in the process, but it appears that when a unicode emojii gets passed through, the cookie stops being written completely and is sent incomplete (note that the JSON object gets cut off after the user's name, right where the unicode emojii start)

I'm wondering if anyone has tackled doing a multi-tenant system with express-mysql-session?

Our system currently has a mysql database for each tenant. Thoughts?

I am trying to log every session login/logout/time-out. I can do login and logout from server api, but I also want to do logging for time-out. Is there a way to do it right now using this module?

I am having some issues with mysql connection on CI environment after adding express-mysql-session and trying to debug the cause of it. However DEBUG=express-mysql-session* is not working, I dont see any debug messages on console. Please let me know how to enable debugging.

Hi,
When the server verified if the user connect, the session exist, but if the user go on other page, the session is undefined:

/* ----------------> INITIALIZE */
let express    = require('express');
let app        = express();
let session    = require('express-session')
let MySQLStore = require('express-mysql-session')(session);
let mysql      = require('mysql');
let bodyParser = require('body-parser')
let sha512     = require('js-sha512');



/* ----------------> BODY PARSER MANAGEMENT DATA TO JSON */
app.use(bodyParser.urlencoded( { extended : true } ) )
app.use(bodyParser.json())


/* ----------------> PORT SERVER NODE CONNECT */
app.listen(5000, function () {
  console.log('Connection server on port 5000');
});
/* ----------------> CROSS ORIGINE */
app.use(function(req, res, next) {
  res.header('Access-Control-Allow-Credentials', true);
  res.header("Access-Control-Allow-Origin", req.headers.origin);
  res.header('Access-Control-Allow-Methods', 'GET,PUT,POST,DELETE,OPTIONS');
  res.header('Access-Control-Allow-Headers', 'X-CSRF-Token, X-Requested-With, Accept, Accept-Version, Content-Length, Content-MD5, Content-Type, Date, X-Api-Version');

  if ('OPTIONS' == req.method) {
      res.sendStatus(200);
  } else {
    next();
  }

});

/* ----------------> MODULE MYSQL JS */
let db_config = {
  host     : 'localhost',
  user     : 'root',
  password : '',
  database : 'test'
};


function handleDisconnect () {
  mysqlClient = mysql.createConnection(db_config); // Recreate the connection, since
                                                  // the old one cannot be reused.

  mysqlClient.connect(function(err) {              // The server is either down
    if(err) {                                     // or restarting (takes a while sometimes).
      console.log('error when connecting to db:', err);
      setTimeout(handleDisconnect, 2000); // We introduce a delay before attempting to reconnect,
    }                                     // to avoid a hot loop, and to allow our node script to
  });                                     // process asynchronous requests in the meantime.
                                          // If you're also serving http, display a 503 error.
  mysqlClient.on('error', function(err) {
    console.log('db error', err);
    if(err.code === 'PROTOCOL_CONNECTION_LOST') { // Connection to the MySQL server is usually
      handleDisconnect();                         // lost due to either server restart, or a
    } else {                                      // connnection idle timeout (the wait_timeout
      throw err;                                  // server variable configures this)
    }
  });
}

global.mysqlClient = {};
handleDisconnect();

/* ----------------> MANAGEMENT SESSION MYSQL */
app.use(session({
    secret: 'SessSav57845sdf',
    store: new MySQLStore({}, mysqlClient),
    resave: true,
    saveUninitialized: true,
    cookie: { secure: false, httpOnly:false }
}));

/* ----------------> ROUTER */

/* REGISTRATION */

app.post('/registration', function( req, res ){

  let params         = []
  let numberID       = Math.floor(Math.random() * (999999 - 111111)) + 111111;
  let regexEmail     = new RegExp("^[0-9a-z._-]+@{1}[0-9a-z.-]{2,}[.]{1}[a-z]{2,6}$", "i")
  let email          = regexEmail.test(req.body.email)
  let regexTelephone = new RegExp("[0-9]")
  let telephone      = regexTelephone.test(req.body.telephone)

  if( email == true && telephone == true ){

    params = [ 'ID-' + numberID,
               req.body.email,
               sha512(req.body.password),
               req.body.ville,
               req.body.telephone ]

    let sql = 'INSERT INTO users (name,email,password,ville,telephone) VALUES (?,?,?,?,?)'
    mysqlClient.query(sql, params, function(err, results) {
      if (err) {
        console.log(err)
      } else {
        console.log('Sucess')
      }
    })

  } else {
      console.log('error')
  }
})

/* RECOGNITION */
app.post('/recognition', function( req, res ){

  let email = req.body.email
  let password = sha512(req.body.password)

  let sql = "SELECT * FROM users WHERE email=? AND password=? LIMIT 1"
  mysqlClient.query(sql, [email,password], function( err, results ){
    if ( results.length != 0 ){
      req.session.user = results[0]  
      console.log('Successful recognition !', req.session ); // CONSOLE LOG 1

      res.json(req.session)


    } else {
        console.log('Email or Password incorrect...')
    }

  })
})

for CONSOLE LOG 1 response :

Successful recognition ! Session {                                                                                                      
  cookie:                                                                                                                               
   { path: '/',                                                                                                                         
     _expires: null,                                                                                                                    
     originalMaxAge: null,                                                                                                              
     httpOnly: false,                                                                                                                   
     secure: false },                                                                                                                   
  user:                                                                                                                                 
   RowDataPacket {                                                                                                                      
     id: 4,                                                                                                                             
     avatar: '001.png',                                                                                                                 
     name: 'ID-490850',                                                                                                                 
     email: '[email protected]',                                                                                                           
     password: 'ee26b0dd4af7e749aa1a8ee3c10ae9923f618980772e473f8819a5d4940e0db27ac185f8a0e1d5f84f88bc887fd67b143732c304cc5fa9ad8e6f57f5
0028a8ff',                                                                                                                              
     ville: 'Marseille',                                                                                                                
     telephone: '0685613761' } } 

suite code : when the user change page :

/* LOGOUT */
app.get('/logout', function( req, res ){
  req.session.destroy()
  console.log('lol logout')
  res.sendStatus(200)
})

/* ACCOUNT */
app.get('/account', function(req,res){
  console.log(req.session.user) // CONSOLE LOG 2
})

for CONSOLE LOG 2 response :
undefined

Question : Why the session doesn't exist after change page or redirection page
(redirection page with window.location....)

Thanks you
Parser.Error

Does this module handle DB disconnects?

I'm current'y trying to figure out a bug I get when pushing my project to heroku. Not sure if express-mysql-session is causing it, but I get the following error in my console:

     throw er; // Unhandled 'error' event
     ^

Error: Connection lost: The server closed the connection.
   at Protocol.end (/app/node_modules/mysql/lib/protocol/Protocol.js:109:13)
   at Socket.<anonymous> (/app/node_modules/mysql/lib/Connection.js:102:28)
   at emitNone (events.js:85:20)
   at Socket.emit (events.js:179:7)
   at endReadableNT (_stream_readable.js:913:12)
   at _combinedTickCallback (node.js:377:13)
   at process._tickCallback (node.js:401:11)
Program node --harmony ./index.js exited with code 1
Starting child process with 'node --harmony ./index.js

I assume the connection is lost and needs to be reset, but I'm not sure if the express-mysql-session has a way to handle this. Or is there a way to feed it a pool rather than a connection?

Thanks in advance!

When i deployed the project with pm2 sometimes user session getting terminated suddenly due to connection issues is mysql but i am not getting any messages in console.Is it possible to debug messages from script to console when some mysql connection issue happens by giving some options while creating store ?

Hi, I'm using sails js along with your express-mysql-session, and this is how my session.js look like:

var MySQLSessionStore = require('express-mysql-session');

var store_options = {
host: 'localhost',
port: 3306,
user: 'usrName',
password: 'xxxxxxxxxxx',
database: 'databaseA',
createDatabaseTable: true,
schema: {
        tableName: 'sessions',
        columnNames: {
            session_id: 'session_id',
            expires: 'expires',
            data: 'data'
        }
    }
}

module.exports.session = {
store: new MySQLSessionStore(store_options)
};

And it work fine for me. I was able to create a session and save it data to my local database.

The problem come when I trying to use a database from a remote server instead of my local host, i change the option to

host: 'http://xxx.xxx.xxx.xxx/',
port: 3306,
user: 'usrName',
password: 'xxxxxxxxxxx',
database: 'databaseA',

and when I sails lift and enter the website I got this error:

"Error occurred in session middleware :: 'Error: Cannot enqueue Query after fatal error.\n at Protocol._validateEnqueue (C:\Sails\Rank FE\Rank\node_modules\express-mysql-session\node_modules\mysql-connection-manager\node_modules\mysql\lib\protocol\Protocol.js:193:16)\n at Protocol.enqueue (C:\Sails\Rank FE\Rank\node_modules\express-mysql-session\node_modules\mysql-connection-manager\node_modules\mysql\lib\protocol\Protocol.js:129:13)\n at Connection.query (C:\Sails\Rank FE\Rank\node_modules\express-mysql-session\node_modules\mysql-connection-manager\node_modules\mysql\lib\Connection.js:185:25)\n at SessionStore.get (C:\Sails\Rank FE\Rank\node_modules\express-mysql-session\lib\index.js:151:18)\n at session (C:\Sails\Rank FE\Rank\node_modules\sails\node_modules\express\node_modules\connect\node_modules\express-session\index.js:324:11)\n at Object..defaults.session [as handle](C:\Sails\Rank FE\Rank\node_modules\sails\lib\hooks\http\middleware\defaults.js:50:11)\n at next (C:\Sails\Rank FE\Rank\node_modules\sails\node_modules\express\node_modules\connect\lib\proto.js:169:15)\n at Object.cookieParser [as handle](C:\Sails\Rank FE\Rank\node_modules\sails\node_modules\express\node_modules\connect\node_modules\cookie-parser\index.js:48:5)\n at next (C:\Sails\Rank FE\Rank\node_modules\sails\node_modules\express\node_modules\connect\lib\proto.js:169:15)\n at Object.startRequestTimer [as handle](C:\Sails\Rank FE\Rank\node_modules\sails\lib\hooks\http\middleware\defaults.js:72:7)\n at next (C:\Sails\Rank FE\Rank\node_modules\sails\node_modules\express\node_modules\connect\lib\proto.js:169:15)\n at Object.expressInit [as handle](C:\Sails\Rank FE\Rank\node_modules\sails\node_modules\express\lib\middleware.js:30:5)\n at next (C:\Sails\Rank FE\Rank\node_modules\sails\node_modules\express\node_modules\connect\lib\proto.js:169:15)\n at Object.query [as handle](C:\Sails\Rank FE\Rank\node_modules\sails\node_modules\express\node_modules\connect\lib\middleware\query.js:45:5)\n at next (C:\Sails\Rank FE\Rank\node_modules\sails\node_modules\express\node_modules\connect\lib\proto.js:169:15)\n at Function.app.handle (C:\Sails\Rank FE\Rank\node_modules\sails\node_modules\express\node_modules\connect\lib\proto.js:177:3)'"

And if I remove the http part from the host so the option is host: 'xxx.xxx.xxx.xxx' then I got this error:

Error occurred in session middleware :: 'Error: connect ETIMEDOUT\n at Connection._handleConnectTimeout (C:\Sails\Rank FE\Rank\node_modules\express-mysql-session\node_modules\mysql-connection-manager\node_modules\mysql\lib\Connection.js:346:13)\n at Socket.g (events.js:199:16)\n at Socket.emit (events.js:104:17)\n at Socket._onTimeout (net.js:348:8)\n at _makeTimerTimeout (timers.js:467:11)\n at Timer.unrefTimeout (timers.js:531:5)\n --------------------\n at Protocol._enqueue (C:\Sails\Rank FE\Rank\node_modules\express-mysql-session\node_modules\mysql-connection-manager\node_modules\mysql\lib\protocol\Protocol.js:135:48)\n at Protocol.handshake (C:\Sails\Rank FE\Rank\node_modules\express-mysql-session\node_modules\mysql-connection-manager\node_modules\mysql\lib\protocol\Protocol.js:52:41)\n at Connection.connect (C:\Sails\Rank FE\Rank\node_modules\express-mysql-session\node_modules\mysql-connection-manager\node_modules\mysql\lib\Connection.js:109:18)\n at MySQLConnectionManager.createConnection (C:\Sails\Rank FE\Rank\node_modules\express-mysql-session\node_modules\mysql-connection-manager\lib\index.js:66:13)\n at MySQLConnectionManager.connect (C:\Sails\Rank FE\Rank\node_modules\express-mysql-session\node_modules\mysql-connection-manager\lib\index.js:56:8)\n at MySQLConnectionManager.reconnect (C:\Sails\Rank FE\Rank\node_modules\express-mysql-session\node_modules\mysql-connection-manager\lib\index.js:160:7)\n at null._onTimeout (C:\Sails\Rank FE\Rank\node_modules\express-mysql-session\node_modules\mysql-connection-manager\lib\index.js:148:8)\n at Timer.listOnTimeout (timers.js:110:15)'

Any idea how I might fix this?

This necessary to set "resave" option to false

I found that when some middleware extends the Session object prototype with e.g. some additional functions the express-mysql-session store somehow removes such functions...

In particular... I'm trying to use the easy-session module (https://www.npmjs.com/package/easy-session) in combination with this session store....

I found that when I use easy-session with default session store (memory-store) everything works normally, however, when I use express-mysql-session, it deletes the Session.prototype functions added by the easy-session...

req.session.__proto__ functions available (when using memory-store - correct):

touch,resetMaxAge,save,reload,destroy,regenerate,login,logout,setLastRequest,isGuest,isLoggedIn,isFresh,setRole,getRole,hasRole,hasNotRole

req.session.__proto__ functions available (when using express-mysql-session - missing functions):

touch,resetMaxAge,save,reload,destroy,regenerate

How to repair this?

It would be nice to use an already existing database connection instead of opening a new one and having 2 connections.

Is it simple enough to update the mysql dependancy to 2.14.0 or 2.14.1 ? Will it cause any knock-on consequences?
https://snyk.io/vuln/npm:mysql:20170317

Low risk vulnerability discovered in 2.13.0

test

instead of depending on self.option.debug value

Forgot to tell us how we retrieve the data from cookie session...

io.js

• Make necessary modifications to run on io.js
• Get tests passing locally under io.js
• Add io.js environment to travis-ci config

https://gist.github.com/c0debreaker/c7288c87b1db8c0e5d60ee7f8ee2e3d5

Here are few lines copied from the output

1) clear(cb) "before all" hook:
   Error: ER_BAD_DB_ERROR: Unknown database 'session_test'
  at Handshake.Sequence._packetToError (node_modules/mysql/lib/protocol/sequences/Sequence.js:51:14)
  at Handshake.ErrorPacket (node_modules/mysql/lib/protocol/sequences/Handshake.js:103:18)
  at Protocol._parsePacket (node_modules/mysql/lib/protocol/Protocol.js:280:23)
  at Parser.write (node_modules/mysql/lib/protocol/Parser.js:74:12)
  at Protocol.write (node_modules/mysql/lib/protocol/Protocol.js:39:16)
  at Socket.<anonymous> (node_modules/mysql/lib/Connection.js:109:28)
  at readableAddChunk (_stream_readable.js:176:18)
  at Socket.Readable.push (_stream_readable.js:134:10)
  at TCP.onread (net.js:548:20)

    --------------------
  at Protocol._enqueue (node_modules/mysql/lib/protocol/Protocol.js:141:48)
  at Protocol.handshake (node_modules/mysql/lib/protocol/Protocol.js:52:41)
  at PoolConnection.connect (node_modules/mysql/lib/Connection.js:136:18)
  at Pool.getConnection (node_modules/mysql/lib/Pool.js:48:16)
  at Pool.query (node_modules/mysql/lib/Pool.js:200:8)
  at MySQLStore.<anonymous> (lib/index.js:103:20)
  at tryToString (fs.js:455:3)
  at FSReqWrap.readFileAfterClose [as oncomplete] (fs.js:442:12)

In case when "express-mysql-session" uses connection pooling then sometimes it leads to race condition regardless of setting save option for "express-session" module (false or true has no matter).
It can be obviously visible when we use extra debug calls like following in express-mysql-session/lib/index.js:

For "Setting session, sql:" here:

SessionStore.prototype.set = function(session_id, data, cb) {
    ...
    this.connection.query(sql, params, function(error) {
+        debug_log('Setting session, sql: %s, params: %j', sql, params);

For "Getting session, sql: " and "Getting session, session:" here:

SessionStore.prototype.get = function(session_id, cb) {
    ...
    this.connection.query(sql, params, function(error, rows) {
+        debug_log('Getting session, sql: %s, params: %j', sql, params);
        if (error) {
            return cb(error, null);
        }
        var session = !!rows[0] ? JSON.parse(rows[0].data) : null;
+        debug_log('Getting session, session: %j', session);

So I decided to switch off connection pooling but anyways, how can I be fully confident in lack of such race condition situations?

Upon trying to create the MySqlStore variable I get this error.

undefined:1
undefined
^

SyntaxError: Unexpected token u in JSON at position 0
    at Object.parse (native)
    at MySQLStore.clone (C:\Projects\wclb\node_modules\express-mysql-session\lib\index.js:403:15)
    at new MySQLStore (C:\Projects\wclb\node_modules\express-mysql-session\lib\index.js:40:23)
    at module.exports (C:\Projects\wclb\node_modules\express-mysql-session\lib\index.js:427:10)
    at Object.<anonymous> (C:\Projects\wclb\app.js:8:50)
    at Module._compile (module.js:570:32)
    at Object.Module._extensions..js (module.js:579:10)
    at Module.load (module.js:487:32)
    at tryModuleLoad (module.js:446:12)
    at Function.Module._load (module.js:438:3)

This is my code up to where the error occurs

var express = require('express');
var app = express();
var session = require('cookie-session');
var MySQLStore = require('express-mysql-session')(session);

I am using node v6.9.5 and expressjs v4.14.1 and express-mysql-session v1.2.0.

Currently, express-mysql-session cannot properly handle DB connection failures. After a connection has been terminated, the store will always fail with:

Error during request (500): Error: Cannot enqueue Query after fatal error.
at Protocol._validateEnqueue (MY_CODE\node_modules\express-mysql-session\node_modules\mysql-connection-manager\node_modules\mysql\lib\protocol\Protocol.js:193:16)
at Protocol._enqueue (MY_CODE\node_modules\express-mysql-session\node_modules\mysql-connection-manager\node_modules\mysql\lib\protocol\Protocol.js:129:13)
at Connection.query (MY_CODE\node_modules\express-mysql-session\node_modules\mysql-connection-manager\node_modules\mysql\lib\Connection.js:185:25)
at SessionStore.get (MY_CODE\node_modules\express-mysql-session\lib\index.js:127:18)
at Layer.session [as handle] (MY_CODE\node_modules\express-session\index.js:404:11)

That is actually not a bug but intended behavior. I quote node-mysql's official documentation:

Re-connecting a connection is done by establishing a new connection. Once terminated, an existing connection object cannot be re-connected by design.

However, gracefully handling connection failures are still an open issue.

In version 0.0.12, express-mysql-session had a reconnect() method. I would suggest to 1) either re-introduce that behavior or 2) provide some other way of handling DB connection problems.

For reference, I am sharing the code that handled connection errors in 0.0.12, based on this suggestion (it works very well, and I will now go back to 0.0.12 for that reason):

// remember if connection already failed (since mysql lib might raise errors more than once)
var fataled = false;
var reconnectTimer = null;

// create store
var store = ...;

// use MySQL connection error handler hack
// see: https://github.com/felixge/node-mysql/issues/832/#issuecomment-44478560
var del = store.connection._protocol._delegateError;
store.connection._protocol._delegateError = function(err, sequence){
    if (fataled) return;        // ignore silently

    if (err.fatal) {
        // fatal error -> Gotta re-connect!
        fataled = true; 
        var reconnectDelay = dbOptions.reconnectDelay || 5;

        console.error('mysql fatal error: ' + err.stack);
        console.warn('Connection to database lost. Reconnecting in ' + reconnectDelay + 's...');

        // re-connect after delay
        reconnectTimer = setTimeout(function() {
            fataled = false;
            reconnectTimer = null;
            store.reconnect();
        }, reconnectDelay * 1000);
    }
    else {
        // propagate error
        return del.call(store.connection._protocol, err, sequence);
    }
};

// use `store`...
var session = { ... store: store, ... };

0.12.x is latest stable release of node.js.

• Get tests passing locally under node.js v0.12.x
• Add node.js v0.12.x environment to travis-ci config