Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4, 10.5
-
None
Description
The following preliminary INSERTs are only needed if your timezone tables are empty. The failure is reproducible with these as well as with properly created TZ data.
Create timezone data |
insert into mysql.time_zone_name values ('EET',385),('GMT', 490); |
insert into mysql.time_zone_transition values (385,1553994000,0),(385,1572138000,1); |
insert into mysql.time_zone_transition_type values (385,0,10800,1,'EEST'),(385,1,7200,0,'EET'),(490,0,0,0,'GMT'); |
insert into mysql.time_zone values (385,'N'),(490,'N'); |
Test case, requires timezone data |
set time_zone= EET; |
drop table if exists t1; |
create table t1 ( |
a int(11) NOT NULL, |
s timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', |
e timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', |
PERIOD FOR `se` (`s`, `e`) |
);
|
insert into t1 values (1,'2019-01-01','2020-01-01'); |
--error ER_TRUNCATED_WRONG_VALUE
|
delete from t1 for portion of se from '2019-03-31 03:30:00' to '2019-04-01 00:00:00'; |
select * from t1; |
DELETE is expected to return an error, but the behavior should be the same as currently happens on UPDATE:
MariaDB [test]> update t1 for portion of se from '2019-03-31 03:30:00' to '2019-04-01 00:00:00' set a = 2; |
ERROR 1292 (22007): Incorrect datetime value: '2019-03-31 03:30:00' for column `test`.`t1`.`s` at row 1 |
MariaDB [test]> select * from t1; |
+---+---------------------+---------------------+ |
| a | s | e |
|
+---+---------------------+---------------------+ |
| 1 | 2019-01-01 00:00:00 | 2020-01-01 00:00:00 |
|
+---+---------------------+---------------------+ |
1 row in set (0.001 sec) |
Instead, first DELETE returns a wrong error:
10.4 503fd2115 |
MariaDB [test]> delete from t1 for portion of se from '2019-03-31 03:30:00' to '2019-04-01 00:00:00'; |
ERROR 1030 (HY000): Got error 1 "Operation not permitted" from storage engine MyISAM |
And if you run SELECT after that, the table is empty, data has been lost:
MariaDB [test]> select * from t1; |
Empty set (0.000 sec) |
The wrong error part is reproducible with at least MyISAM, InnoDB, Aria.
The data loss happens with MyISAM and Aria, not InnoDB.
Reproducible on 10.4 and 10.5.