Description:
A STORED generated column is materialised using the writing session's time_zone
and sql_mode. mysqldump does not emit the generated column's data — correctly, as
the column is derived — so the restore recomputes it.
However, mysqldump does not preserve the semantic context under which the values
were materialised. It writes fixed values into the dump header:
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
The restore therefore recomputes every generated column under UTC and under
SQL_MODE='NO_AUTO_VALUE_ON_ZERO', regardless of the settings the rows were written
with. Where the expression depends on either parameter, the restored table holds
different data from the table that was dumped. No error or warning is produced at
any stage, and the difference is visible from a session identical to the one used
before the dump.
This turns a routine backup and restore into a silent data modification. It also
means a dump/restore cycle is not idempotent for such tables, and that comparing a
restored copy against its source will show differences that look like corruption.
The manual notes that "Expression evaluation uses the SQL mode in effect at
evaluation time. If any component of the expression depends on the SQL mode,
different results may occur for different uses of the table unless the SQL mode is
the same during all uses." It does not state that mysqldump makes the SQL mode
deliberately different during the restore, nor is there any corresponding note for
time_zone.
How to repeat:
--------------------------------------------------------------------------------
How to repeat, part 1: time_zone
--------------------------------------------------------------------------------
DROP DATABASE IF EXISTS tzd; CREATE DATABASE tzd; USE tzd;
SET SESSION time_zone='+09:00';
CREATE TABLE g (
id INT PRIMARY KEY,
ts TIMESTAMP NOT NULL,
day DATE AS (CAST(ts AS DATE)) STORED,
hr INT AS (HOUR(ts)) STORED
) ENGINE=InnoDB;
INSERT INTO g(id,ts) VALUES (1,'2024-03-02 08:30:00'),(2,'2024-03-02 23:45:00');
SELECT id, ts, day, hr FROM g ORDER BY id;
+----+---------------------+------------+------+
| id | ts | day | hr |
+----+---------------------+------------+------+
| 1 | 2024-03-02 08:30:00 | 2024-03-02 | 8 |
| 2 | 2024-03-02 23:45:00 | 2024-03-02 | 23 |
+----+---------------------+------------+------+
$ mysqldump --set-gtid-purged=OFF --skip-comments tzd g | mysql ... tzdr
Reading the restored copy from an identical session (time_zone='+09:00'):
SELECT id, ts, day, hr FROM tzdr.g ORDER BY id;
+----+---------------------+------------+------+
| id | ts | day | hr |
+----+---------------------+------------+------+
| 1 | 2024-03-02 08:30:00 | 2024-03-01 | 23 |
| 2 | 2024-03-02 23:45:00 | 2024-03-02 | 14 |
+----+---------------------+------------+------+
The ts values are identical, as expected; every generated value differs. Row 1's
day moved to the previous date and both hr values are shifted by the UTC offset.
--------------------------------------------------------------------------------
How to repeat, part 2: sql_mode
--------------------------------------------------------------------------------
SET SESSION sql_mode='PAD_CHAR_TO_FULL_LENGTH';
CREATE TABLE v(id INT PRIMARY KEY, ch CHAR(10),
g INT AS (LENGTH(ch)) STORED) ENGINE=InnoDB;
INSERT INTO v(id,ch) VALUES (1,'ab'),(2,'xyz');
SELECT id, ch, g FROM v ORDER BY id; -- g = 10, 10
$ mysqldump ... | mysql ... target
SELECT id, ch, g FROM target.v ORDER BY id; -- g = 2, 3
And with TIME_TRUNCATE_FRACTIONAL, where the restore turns truncation into
rounding:
SET SESSION sql_mode='TIME_TRUNCATE_FRACTIONAL';
CREATE TABLE t(id INT PRIMARY KEY, t6 TIME(6),
g TIME AS (CAST(t6 AS TIME(0))) STORED) ENGINE=InnoDB;
INSERT INTO t(id,t6) VALUES (1,'10:00:00.600000'),(2,'11:00:00.400000');
SELECT id, t6, g FROM t ORDER BY id;
+----+-----------------+----------+
| id | t6 | g |
+----+-----------------+----------+
| 1 | 10:00:00.600000 | 10:00:00 |
+----+-----------------+----------+
$ mysqldump ... | mysql ... target
SELECT id, t6, g FROM target.t ORDER BY id;
+----+-----------------+----------+
| id | t6 | g |
+----+-----------------+----------+
| 1 | 10:00:00.600000 | 10:00:01 |
+----+-----------------+----------+
--------------------------------------------------------------------------------
Verification that the header is the cause
--------------------------------------------------------------------------------
$ mysqldump --set-gtid-purged=OFF tzd g | grep -i "SET TIME_ZONE\|SQL_MODE="
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
Both are constants written by mysqldump, not values read from the source server.
Setting the intended context around the restore does not help, because the dump's
own SET statements execute after it and override it.
Suggested fix:
Either of the following removes the silent difference:
1. Have mysqldump record the source server's time_zone and sql_mode, and emit
those values, so a restore reproduces the semantics under which the data was
materialised. The existing fixed values would remain the fallback when the
source values cannot be determined.
2. Have mysqldump emit the generated column data explicitly for STORED generated
columns (for example via a column list that includes them and a
SET SESSION information_schema-driven variant), so the values are transported
rather than recomputed.
A server-side alternative, which also addresses several related reports, is to
reject at DDL time any generated column expression that depends on a session
parameter with no argument form — that is, one that is not folded into the stored
expression. MySQL already folds such parameters where an argument form exists
(WEEK(d) is stored as week(`d`,0), and c || 'X' under PIPES_AS_CONCAT is stored as
concat(`c`,_utf8mb4'X')), so the distinction is already represented in the
normalised expression and could be checked directly. Expressions such as
cast(`ts` as date), hour(`ts`), length(`ch`) and monthname(`d`) carry no such
qualification and are the ones affected here.
Description: A STORED generated column is materialised using the writing session's time_zone and sql_mode. mysqldump does not emit the generated column's data — correctly, as the column is derived — so the restore recomputes it. However, mysqldump does not preserve the semantic context under which the values were materialised. It writes fixed values into the dump header: /*!40103 SET TIME_ZONE='+00:00' */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; The restore therefore recomputes every generated column under UTC and under SQL_MODE='NO_AUTO_VALUE_ON_ZERO', regardless of the settings the rows were written with. Where the expression depends on either parameter, the restored table holds different data from the table that was dumped. No error or warning is produced at any stage, and the difference is visible from a session identical to the one used before the dump. This turns a routine backup and restore into a silent data modification. It also means a dump/restore cycle is not idempotent for such tables, and that comparing a restored copy against its source will show differences that look like corruption. The manual notes that "Expression evaluation uses the SQL mode in effect at evaluation time. If any component of the expression depends on the SQL mode, different results may occur for different uses of the table unless the SQL mode is the same during all uses." It does not state that mysqldump makes the SQL mode deliberately different during the restore, nor is there any corresponding note for time_zone. How to repeat: -------------------------------------------------------------------------------- How to repeat, part 1: time_zone -------------------------------------------------------------------------------- DROP DATABASE IF EXISTS tzd; CREATE DATABASE tzd; USE tzd; SET SESSION time_zone='+09:00'; CREATE TABLE g ( id INT PRIMARY KEY, ts TIMESTAMP NOT NULL, day DATE AS (CAST(ts AS DATE)) STORED, hr INT AS (HOUR(ts)) STORED ) ENGINE=InnoDB; INSERT INTO g(id,ts) VALUES (1,'2024-03-02 08:30:00'),(2,'2024-03-02 23:45:00'); SELECT id, ts, day, hr FROM g ORDER BY id; +----+---------------------+------------+------+ | id | ts | day | hr | +----+---------------------+------------+------+ | 1 | 2024-03-02 08:30:00 | 2024-03-02 | 8 | | 2 | 2024-03-02 23:45:00 | 2024-03-02 | 23 | +----+---------------------+------------+------+ $ mysqldump --set-gtid-purged=OFF --skip-comments tzd g | mysql ... tzdr Reading the restored copy from an identical session (time_zone='+09:00'): SELECT id, ts, day, hr FROM tzdr.g ORDER BY id; +----+---------------------+------------+------+ | id | ts | day | hr | +----+---------------------+------------+------+ | 1 | 2024-03-02 08:30:00 | 2024-03-01 | 23 | | 2 | 2024-03-02 23:45:00 | 2024-03-02 | 14 | +----+---------------------+------------+------+ The ts values are identical, as expected; every generated value differs. Row 1's day moved to the previous date and both hr values are shifted by the UTC offset. -------------------------------------------------------------------------------- How to repeat, part 2: sql_mode -------------------------------------------------------------------------------- SET SESSION sql_mode='PAD_CHAR_TO_FULL_LENGTH'; CREATE TABLE v(id INT PRIMARY KEY, ch CHAR(10), g INT AS (LENGTH(ch)) STORED) ENGINE=InnoDB; INSERT INTO v(id,ch) VALUES (1,'ab'),(2,'xyz'); SELECT id, ch, g FROM v ORDER BY id; -- g = 10, 10 $ mysqldump ... | mysql ... target SELECT id, ch, g FROM target.v ORDER BY id; -- g = 2, 3 And with TIME_TRUNCATE_FRACTIONAL, where the restore turns truncation into rounding: SET SESSION sql_mode='TIME_TRUNCATE_FRACTIONAL'; CREATE TABLE t(id INT PRIMARY KEY, t6 TIME(6), g TIME AS (CAST(t6 AS TIME(0))) STORED) ENGINE=InnoDB; INSERT INTO t(id,t6) VALUES (1,'10:00:00.600000'),(2,'11:00:00.400000'); SELECT id, t6, g FROM t ORDER BY id; +----+-----------------+----------+ | id | t6 | g | +----+-----------------+----------+ | 1 | 10:00:00.600000 | 10:00:00 | +----+-----------------+----------+ $ mysqldump ... | mysql ... target SELECT id, t6, g FROM target.t ORDER BY id; +----+-----------------+----------+ | id | t6 | g | +----+-----------------+----------+ | 1 | 10:00:00.600000 | 10:00:01 | +----+-----------------+----------+ -------------------------------------------------------------------------------- Verification that the header is the cause -------------------------------------------------------------------------------- $ mysqldump --set-gtid-purged=OFF tzd g | grep -i "SET TIME_ZONE\|SQL_MODE=" /*!40103 SET TIME_ZONE='+00:00' */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; Both are constants written by mysqldump, not values read from the source server. Setting the intended context around the restore does not help, because the dump's own SET statements execute after it and override it. Suggested fix: Either of the following removes the silent difference: 1. Have mysqldump record the source server's time_zone and sql_mode, and emit those values, so a restore reproduces the semantics under which the data was materialised. The existing fixed values would remain the fallback when the source values cannot be determined. 2. Have mysqldump emit the generated column data explicitly for STORED generated columns (for example via a column list that includes them and a SET SESSION information_schema-driven variant), so the values are transported rather than recomputed. A server-side alternative, which also addresses several related reports, is to reject at DDL time any generated column expression that depends on a session parameter with no argument form — that is, one that is not folded into the stored expression. MySQL already folds such parameters where an argument form exists (WEEK(d) is stored as week(`d`,0), and c || 'X' under PIPES_AS_CONCAT is stored as concat(`c`,_utf8mb4'X')), so the distinction is already represented in the normalised expression and could be checked directly. Expressions such as cast(`ts` as date), hour(`ts`), length(`ch`) and monthname(`d`) carry no such qualification and are the ones affected here.