[MDEV-32908] Corruption reported on indexed virtual column when timezone is changed - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL)
Fix Version/s: 10.5, 10.6, 10.11
Component/s: Temporal Types, Virtual Columns
Labels:
None

Description

SET TIME_ZONE='+00:00';

CREATE TABLE t (a TIMESTAMP, b DATE GENERATED ALWAYS AS (DATE(a)) VIRTUAL, KEY(b));

INSERT INTO t (a) VALUES ('1999-01-31 22:00:00'),('1990-01-01 00:00:00');

SET TIME_ZONE='+02:00';

CHECK TABLE t EXTENDED;

# Cleanup

DROP TABLE t;

With MyISAM:

10.4 64f44b22d9a3dab3d4c0b77addbcbdafde57b466
CHECK TABLE t EXTENDED;
Table Op Msg_type Msg_text
test.t check error Record at: 0 Can't find key for index: 1
test.t check error Corrupt
DROP TABLE t;
bug.t [ fail ] Found warnings/errors in server log file!
Test ended at 2023-11-29 15:10:20
line
2023-11-29 15:10:20 4 [ERROR] Got an error from thread_id=4, /data/bld/10.4-asan/storage/myisam/ha_myisam.cc:1130
2023-11-29 15:10:20 4 [ERROR] MySQL thread id 4, OS thread handle 140423800547008, query id 26 localhost root Checking table

With InnoDB (on 10.6+ where CHECK .. EXTENDED became extended):

10.6 2f467de4c4851d2f9b0f3bec54f748d92349582a
CHECK TABLE t EXTENDED;
Table Op Msg_type Msg_text
test.t check Warning InnoDB: Index 'b' contains 1 entries, should be 2.
test.t check error Corrupt
DROP TABLE t;
bug.t [ fail ] Found warnings/errors in server log file!
Test ended at 2023-11-29 15:11:00
line
2023-11-29 15:11:00 4 [ERROR] InnoDB: Flagged corruption of `b` in table `test`.`t` in CHECK TABLE; Wrong count

Attachments

Activity

Ascending order - Click to sort in descending order

Sergei Golubchik added a comment - 2023-11-29 20:25

Same without DATE():

SET TIME_ZONE='+00:00';

CREATE TABLE t (a TIMESTAMP, b DATE GENERATED ALWAYS AS (a) VIRTUAL, KEY(b));

INSERT INTO t (a) VALUES ('1999-01-31 22:00:00'),('1990-01-01 00:00:00');

SET TIME_ZONE='+02:00';

CHECK TABLE t EXTENDED;

DROP TABLE t;

Sergei Golubchik added a comment - 2023-11-29 20:25 Same without DATE() : SET TIME_ZONE= '+00:00' ; CREATE TABLE t (a TIMESTAMP , b DATE GENERATED ALWAYS AS (a) VIRTUAL, KEY (b)); INSERT INTO t (a) VALUES ( '1999-01-31 22:00:00' ),( '1990-01-01 00:00:00' ); SET TIME_ZONE= '+02:00' ; CHECK TABLE t EXTENDED; DROP TABLE t;

Elena Stepanova added a comment - 2023-11-29 20:39

DATE was added just to make the test case more "valid", to avoid the truncation warning upon INSERT.

For an additional note, the test case itself was not artificial, as it may seem based on timezone changes within a session.
Initially the failure was observed upon restoration of mysqldump dump, followed by a health check via CHECK TABLE. mysqldump by default sets the timezone to 0 in the beginning and restores the original (typically non-zero) value at the end of the dump.

Elena Stepanova added a comment - 2023-11-29 20:39 DATE was added just to make the test case more "valid", to avoid the truncation warning upon INSERT. For an additional note, the test case itself was not artificial, as it may seem based on timezone changes within a session. Initially the failure was observed upon restoration of mysqldump dump, followed by a health check via CHECK TABLE. mysqldump by default sets the timezone to 0 in the beginning and restores the original (typically non-zero) value at the end of the dump.

People

Assignee:: Alexander Barkov

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2023-11-29 13:11

Updated:: 2024-11-22 17:10

Git Integration

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

MariaDB Server