[MDEV-32908] Corruption reported on indexed virtual column when timezone is changed Created: 2023-11-29  Updated: 2023-11-29

Status: Open
Project: MariaDB Server
Component/s: Temporal Types, Virtual Columns
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
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



 Comments   
Comment by Sergei Golubchik [ 2023-11-29 ]

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;

Comment by Elena Stepanova [ 2023-11-29 ]

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.

Generated at Thu Feb 08 10:34:56 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.