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

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

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.12, 10.2.2
    • Fix Version/s: 10.3.2
    • Component/s: Virtual Columns
    • Labels:
      None
    • Environment:
      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

            People

            • Assignee:
              serg Sergei Golubchik
              Reporter:
              Bytesmiths Jan Steinman
            • Votes:
              2 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: