2

I am using nodejs, express and node-mysql2 for my application. I want timezone for each connection to use UTC so that my time_created and time_modified (MYSQL: On update CURRENT_TIMESTAMP) column will have timestamp in UTC only. I don't have permissions to set timestamp of MYSQL server hence I need to do this in my application only.

I am using connection pool and adding an event listener for connection event which set timezone for that connection to be UTC.

pool.on('connection', (conn) => {
  conn.query("SET time_zone='+00:00';", (error) => {
    if (error) {
      throw error;
    }
  });
});

My route definition is similar to this:

router.get('/route', async (req, res) => {
    try {
      const [rows] = await pool.execute('SELECT * FROM student', []);
      return res.status(200).send('Success');
    } catch (e) {
      return res.status(500).send('Failure');
    }
});

If this query "SET time_zone='+00:00'" in event listener fails, my node server get crashed with stacktrace on console. I want to catch this kind of errors in my route or anywhere so that I can send 500 response to client. Can you tell me what is good approach to handle this kind of exception thrown from event listeners in express route?

AnSh
  • 147
  • 1
  • 6
  • How many time pool will emit `connection` event? – JackOfAshes - Mohit Gawande Jun 25 '19 at 10:53
  • @JackOfAshes-MohitGawande I am not sure but I think it depend on number of parallel connection I need. I don't have much load on system for now hence you can consider 4-5 at max. – AnSh Jun 25 '19 at 10:59
  • you can use a global variable where you are implementing get route handler and update this global variable from your custom error event handler and fire this custom event in case of error occurrence in database connection event handler's query. – JackOfAshes - Mohit Gawande Jun 25 '19 at 11:07
  • You can try to set timezone options during connection creation only. Please check this link: https://github.com/mysqljs/mysql#connection-options – Sagar Chilukuri Jun 25 '19 at 11:10
  • @SagarCh I tried that but apparently it does not change the behavior of MYSQL: On update CURRENT_TIMESTAMP, MYSQL server takes its host locale/timestamp and do not take UTC. I tried with timezone: '+00:00' OR timezone: 'Z' or timezone: 'UTC' but nothing works. I am using mysql2 instead of mysql so it may work with mysql. – AnSh Jun 25 '19 at 11:18
  • @JackOfAshes-MohitGawande You mean to say I need to set global variable in case of exception throws in .on('connection'). I believe I need to add the check in my route definition to see what is the value of this global variable. So if I am not wrong this can affect another routes too though for their connection there is no error occurred. – AnSh Jun 25 '19 at 11:21
  • In connection event handler you are getting connection. Try attaching some information as key to this connection object inside event handler. and instead of pool.execute try pool.getConnection -> connection.query and before connection.query check if you can access that attached information. – JackOfAshes - Mohit Gawande Jun 25 '19 at 11:32

0 Answers0