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

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

    XMLWordPrintable

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

          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.