Uploaded image for project: 'MariaDB Connector/node.js'
  1. MariaDB Connector/node.js
  2. CONJS-211

Session timezone unset on connection re-use with connection pool

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 2.5.5
    • 3.0.2
    • pool
    • None
    • Node 14.19.3
      MariaDB 15.1 Distrib 10.3.34-MariaDB x86_64
      Ubuntu 20.04

    Description

      We are finding dates sporadically double-converted between time zones when using the connection pool, producing a wrong date (even when rendered as epoch time). Test code appears to show that session time zone reverts to SYSTEM after the first use of each connection in the pool. Sample code is as follows:

      const mariadb = require('mariadb');
       
      (async () => {
        let pool;
       
        try {
          pool = mariadb.createPool({
            connectionLimit: 5,
            database: process.env.DB_NAME,
            user: process.env.DB_USER,
            password: process.env.DB_PASSWORD,
            timezone: 'Z',
          });
       
          const cs = [1, 2, 3, 4, 5];
       
          await Promise.all(cs.map(async n => {
            let conn;
            try {
              conn = await pool.getConnection();
              const res = await conn.query(
                'SELECT @@time_zone AS tz, SYSDATE() AS d, CONNECTION_ID() AS id');
              console.log(`\nConnection ${n} TZ: ${res[0].tz}`);
              console.log(`SYSDATE() = ${res[0].d}`);
              console.log(`CONNECTION_ID() = ${res[0].id}`);
              // await new Promise(res => setTimeout(res, 2000));
            } finally {
              if (conn) conn.end();
            }
          }));
       
        } finally {
          if (pool) await pool.end();
        }
      })();
      

      Results are non-deterministic, but this is typical:

      Connection 1 TZ: +00:00
      SYSDATE() = Fri Aug 05 2022 01:46:22 GMT-0400 (Eastern Daylight Saving Time)
      CONNECTION_ID() = 264

      Connection 2 TZ: SYSTEM
      SYSDATE() = Thu Aug 04 2022 21:46:22 GMT-0400 (Eastern Daylight Saving Time)
      CONNECTION_ID() = 264

      Connection 3 TZ: SYSTEM
      SYSDATE() = Thu Aug 04 2022 21:46:22 GMT-0400 (Eastern Daylight Saving Time)
      CONNECTION_ID() = 264

      Connection 4 TZ: SYSTEM
      SYSDATE() = Thu Aug 04 2022 21:46:22 GMT-0400 (Eastern Daylight Saving Time)
      CONNECTION_ID() = 264

      Connection 5 TZ: +00:00
      SYSDATE() = Fri Aug 05 2022 01:46:22 GMT-0400 (Eastern Daylight Saving Time)
      CONNECTION_ID() = 265

      The first use of connection ID 264 correctly reports UTC in @@time_zone, and reads the correct time from SYSDATE(). The next three uses of 264 incorrectly see SYSTEM in @@time_zone and show the wrong time when decoding SYSDATE() - specifically, the time appears to have been double-converted from UTC to EDT. 0146 EDT is correct, but subsequent uses of the connection apparently see 0146 UTC and then convert that to 2146 EDT on the previous day.

      Removing the comment on the await new Promise line creates a two-second delay before the connection is returned to the pool, forcing the next request to use a new connection. In this case all five requests report the correct time and time zone. However, if we then keep the connection pool at 5 and perform 10 requests instead, we get the same problem:

      Connection 1 TZ: +00:00
      SYSDATE() = Fri Aug 05 2022 01:53:13 GMT-0400 (Eastern Daylight Saving Time)
      CONNECTION_ID() = 267

      Connection 2 TZ: +00:00
      SYSDATE() = Fri Aug 05 2022 01:53:13 GMT-0400 (Eastern Daylight Saving Time)
      CONNECTION_ID() = 268

      Connection 3 TZ: +00:00
      SYSDATE() = Fri Aug 05 2022 01:53:13 GMT-0400 (Eastern Daylight Saving Time)
      CONNECTION_ID() = 269

      Connection 4 TZ: +00:00
      SYSDATE() = Fri Aug 05 2022 01:53:13 GMT-0400 (Eastern Daylight Saving Time)
      CONNECTION_ID() = 270

      Connection 5 TZ: +00:00
      SYSDATE() = Fri Aug 05 2022 01:53:13 GMT-0400 (Eastern Daylight Saving Time)
      CONNECTION_ID() = 271

      Connection 6 TZ: SYSTEM
      SYSDATE() = Thu Aug 04 2022 21:53:15 GMT-0400 (Eastern Daylight Saving Time)
      CONNECTION_ID() = 267

      Connection 7 TZ: SYSTEM
      SYSDATE() = Thu Aug 04 2022 21:53:15 GMT-0400 (Eastern Daylight Saving Time)
      CONNECTION_ID() = 268

      Connection 8 TZ: SYSTEM
      SYSDATE() = Thu Aug 04 2022 21:53:15 GMT-0400 (Eastern Daylight Saving Time)
      CONNECTION_ID() = 269

      Connection 9 TZ: SYSTEM
      SYSDATE() = Thu Aug 04 2022 21:53:15 GMT-0400 (Eastern Daylight Saving Time)
      CONNECTION_ID() = 270

      Connection 10 TZ: SYSTEM
      SYSDATE() = Thu Aug 04 2022 21:53:15 GMT-0400 (Eastern Daylight Saving Time)
      CONNECTION_ID() = 271

      Attachments

        Activity

          People

            diego dupin Diego Dupin
            ormondtools Lincoln McCormick
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.