Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22436

DELETE FOR PORTION with a boundary withing daylight timeshift interval causes internal error and data loss

    XMLWordPrintable

Details

    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.

      Attachments

        Activity

          People

            nikitamalyavin Nikita Malyavin
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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