Bug Report: Incorrect timezone applied to inserted rows during online migration (ddl_strategy = vitess)

[CAEL0]

Overview of the Issue

In an environment with a UTC+9 timezone, I added a DATETIME(6) DEFAULT NOW(6) column in vitess mode.
But the data inserted during the online migration was UTC+0.

Reproduction Steps

1. Create a test table in direct mode.

SET @@ddl_strategy = 'direct';

DROP TABLE IF EXISTS `online_migration`;
CREATE TABLE `online_migration` (
  id INT AUTO_INCREMENT PRIMARY KEY
);

2. Insert one record. (this is not a reproducible requirement, optional)

INSERT INTO `online_migration` (id) VALUES (NULL);

3. Add two columns in vitess mode.

SET @@ddl_strategy = 'vitess';
ALTER TABLE `online_migration`
  ADD COLUMN x DATETIME(6) DEFAULT NOW(6),
  ADD COLUMN y TIMESTAMP(6) DEFAULT NOW(6);

4. As soon as the online migration is performed, insert records periodically.

INSERT INTO `online_migration` (id) VALUES (NULL);
SELECT SLEEP(1);
INSERT INTO `online_migration` (id) VALUES (NULL);
SELECT SLEEP(1);
INSERT INTO `online_migration` (id) VALUES (NULL);
SELECT SLEEP(1);
INSERT INTO `online_migration` (id) VALUES (NULL);
SELECT SLEEP(1);
INSERT INTO `online_migration` (id) VALUES (NULL);

5. Insert one record after the online migration is fully completed. (this is also not a reproducible requirement, just for comparison)

SELECT SLEEP(10);
INSERT INTO `online_migration` (id) VALUES (NULL);

6. See the result.

mysql> SELECT * FROM `online_migration`;
+----+----------------------------+----------------------------+
| id | x                          | y                          |
+----+----------------------------+----------------------------+
|  1 | 2025-02-25 01:25:49.975710 | 2025-02-25 10:25:49.975710 |
|  2 | 2025-02-25 01:25:49.975710 | 2025-02-25 10:25:49.975710 |
|  3 | 2025-02-25 01:25:49.975710 | 2025-02-25 10:25:49.975710 |
|  4 | 2025-02-25 01:25:50.884412 | 2025-02-25 10:25:50.884412 |
|  5 | 2025-02-25 01:25:51.905769 | 2025-02-25 10:25:51.905769 |
|  6 | 2025-02-25 01:25:52.930807 | 2025-02-25 10:25:52.930807 |
|  7 | 2025-02-25 10:26:02.964678 | 2025-02-25 10:26:02.964678 |
+----+----------------------------+----------------------------+
7 rows in set (0.01 sec)

mysql> SELECT NOW(), @@TIME_ZONE;
+---------------------+-------------+
| now()               | @@TIME_ZONE |
+---------------------+-------------+
| 2025-02-25 10:26:05 | +09:00      |
+---------------------+-------------+
1 row in set (0.01 sec)

mysql> SHOW CREATE TABLE `online_migration`;
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table            | Create Table                                                                                                                                                                                                                                                                  |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| online_migration | CREATE TABLE `online_migration` (
  `id` int NOT NULL AUTO_INCREMENT,
  `x` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
  `y` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

• The values of column x and column y must be the same as UTC+9.
• However, the value of column x is in UTC+0.
• After the online migration is completed, the value of column x (id = 7) is correctly in UTC+9.
• The value of column x that existed before the online migration (id = 1) is also in UTC+0.

Binary Version

mysql> show variables like 'version%';
+-------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name           | Value                                                                                                                                                                                          |
+-------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| version                 | 8.0.37-Vitess                                                                                                                                                                                  |
| version_comment         | Version: 18.0.9-SNAPSHOT (Git revision 0b3c986e54d1f9d033084bc23320fce63fed490c branch 'debug_18') built on Fri Jan 10 07:19:10 UTC 2025 by irteam@buildkitsandbox using go1.21.13 linux/amd64 |
| version_compile_machine | x86_64                                                                                                                                                                                         |
| version_compile_os      | Linux                                                                                                                                                                                          |
| version_compile_zlib    | 1.2.13                                                                                                                                                                                         |
+-------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

Operating System and Environment details

N/A

Log Fragments

N/A

[mattlord]

This is related to: #10102

/cc @shlomi-noach and @rohit-nayak-ps

[shlomi-noach]

I can confirm the onlineddl_vrepl_suite tests fail on a non-UTC timezone system (it's been a while since I've ran them on such a host). So this has gone under the radar for a while, because our CI runners are all UTC. Going to introduce a test that will work correctly (or fail correctly) on a UTC machine, and follow up from there.

[shlomi-noach]

See #17861

[shlomi-noach]

So the issue here is not exactly related to #17860 (comment). The reason this is happening is that as we apply binlog events, we set timezone to UTC, and this is because existing values in the binary log are UTC. However, the problem in this issue is that these columns are new, and this therefore affects the value they are assigned on creation.