Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
2.5.5
-
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