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

Can't restore a PERSISTENT column that uses DATE_FORMAT()

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1.12, 10.2.2
    • 10.3.2
    • Virtual Columns
    • None
    • MacOS 10.10.5 (export from MariaDB 10.1.12), MacOS 10.12.1 (import to MariaDB 10.2.2)

    Description

      I’m in the process of moving to 10.2, starting with slaving to my operational master (10.1.12). So I dumped the master, and started a restore on the 10.2.2 slave.

      But the restore fails with:

      ERROR 1901 (HY000) at line 7140: Function or expression 'date_format()' cannot be used in the GENERATED ALWAYS AS clause of `start_day`

      I use PERSISTENT columns with the day of the week next to date columns quite heavily. This particular one looks like:

      CREATE TABLE `a_nuke_counts` (
        `time_start` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
        `start_day` enum('Sun','Mon','Tue','Wed','Thu','Fri','Sat') AS (DATE_FORMAT(`time_start`, '%a')) PERSISTENT,
        `time_end` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
        `end_day` enum('Sun','Mon','Tue','Wed','Thu','Fri','Sat') AS (DATE_FORMAT(`time_end`, '%a')) PERSISTENT,
        `count_start` int(10) unsigned NOT NULL,
        `count_end` int(10) unsigned NOT NULL,
        `location` enum('indoor','outdoor','paper sample') NOT NULL DEFAULT 'indoor',
        `note` varchar(255) NOT NULL
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
      

      but there are many more.

      Should I simply be using VARCHAR(3) instead of ENUM for these? In this case, it's a bug that MDEV 10.1.12 allowed me define and use it as an ENUM.

      (Please don’t tell me to use emacs on the 9.1GB dump…

      ----------------
      UPDATE: I just tried executing the above table schema, adding a few records, exporting, DROPping, and importing on MDEV 10.1.12, using Sequel Pro (Mac MySQL GUI). No problem. So it may be a problem with 10.2.2 only. Or perhaps mysqldump.

      Attachments

        Activity

          serg Sergei Golubchik added a comment - - edited

          This is a problem with 10.1. 10.2 got stricter about it, there were lots of code changes around virtual columns and some old bugs got fixed. The problem with DATE_FORMAT in particular — it depends on the session locale settings, @@lc_time_names variable. So if do, say,

          set lc_time_names=de_DE;
          

          then the stored value will no longer match the result of the expression, basically, the table will be inconsistent. The correct approach in this case would've been to make these columns VIRTUAL, not PERSISTENT.

          serg Sergei Golubchik added a comment - - edited This is a problem with 10.1. 10.2 got stricter about it, there were lots of code changes around virtual columns and some old bugs got fixed. The problem with DATE_FORMAT in particular — it depends on the session locale settings, @@lc_time_names variable. So if do, say, set lc_time_names=de_DE; then the stored value will no longer match the result of the expression, basically, the table will be inconsistent. The correct approach in this case would've been to make these columns VIRTUAL , not PERSISTENT .

          It is important to have a possibility to use DATE_FORMAT with a PERSISTENT virtual column. The use case is as follows:

          ALTER TABLE `tablename` ADD `virtualcolumn` CHAR(8) AS (DATE_FORMAT(`datecolumn`,'%Y%m%d')) PERSISTENT;

          It might also be worth considering to provide a deterministic version of DATE_FORMAT.

          abienemann Alexander Bienemann (Inactive) added a comment - It is important to have a possibility to use DATE_FORMAT with a PERSISTENT virtual column. The use case is as follows: ALTER TABLE `tablename` ADD `virtualcolumn` CHAR(8) AS (DATE_FORMAT(`datecolumn`,'%Y%m%d')) PERSISTENT; It might also be worth considering to provide a deterministic version of DATE_FORMAT.

          Thoughts:

          1. introduce 3-argument version DATE_FORMAT(x, y, locale) and only allow that in persistent generated columns
          2. introduce 3-argument version DATE_FORMAT(x, y, locale) and automatically convert 2-argument version into it (with third argument being equal to the current value of lc_time_names) in persistent generated columns
          3. only allow 2-argument DATE_FORMAT() in persistent generated columns when the second argument is locale-independent (fully numerical — is that enough?)
          serg Sergei Golubchik added a comment - Thoughts: introduce 3-argument version DATE_FORMAT(x, y, locale) and only allow that in persistent generated columns introduce 3-argument version DATE_FORMAT(x, y, locale) and automatically convert 2-argument version into it (with third argument being equal to the current value of lc_time_names ) in persistent generated columns only allow 2-argument DATE_FORMAT() in persistent generated columns when the second argument is locale-independent (fully numerical — is that enough?)

          Ok to push.
          One small suggestion. Instead of having a standalone function,
          please move get_locale as method in Item.

          bar Alexander Barkov added a comment - Ok to push. One small suggestion. Instead of having a standalone function, please move get_locale as method in Item.

          Implemented approach 1, introduce 3-argument version DATE_FORMAT(x, y, locale)

          serg Sergei Golubchik added a comment - Implemented approach 1, introduce 3-argument version DATE_FORMAT(x, y, locale)

          People

            serg Sergei Golubchik
            Bytesmiths Jan Steinman
            Votes:
            2 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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