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

mysqldump creating malformed PERSISTENT SQL

    XMLWordPrintable

Details

    Description

      Attempting to upgrade Mac OS 10.6.8 to 10.10 for various reasons. Have MariaDB 10.1.8 on the old machine and 10.1.9 on the new one.

      Did "mysqldump --all-databases --add-drop-database --allow-keywords --comments --dump-date --log-error=mysqldump.err --password=*************** --user=root --max_allowed_packet=1073741824" on the MariaDB 10.1.8 machine, used scp() to move the resulting 11GB dump to the upgraded machine, then attempted to use "echo "SET FOREIGN_KEY_CHECKS=0;" | cat - dump.sql | mysql --max_allowed_packet=2G" to import the data into a fresh install of MariaDB 10.1.9.

      Restore fails with "ERROR 1064 (42000) at line 13749: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS (INET_NTOA(IP)) PERSISTENT,"

      Doing "fgrep ' PERSISTENT,' dump.sql" gives:

        `start_day` enum('Sun','Mon','Tue','Wed','Thu','Fri','Sat') AS (DATE_FORMAT(`time_start`, '%a')) PERSISTENT,
        `end_day` enum('Sun','Mon','Tue','Wed','Thu','Fri','Sat') AS (DATE_FORMAT(`time_end`, '%a')) PERSISTENT,
        `IP_dotted` varchar(255) COLLATE utf8_bin AS (INET_NTOA(IP)) PERSISTENT,
        ...

      Note that the first two PERSISTENT statements were successfully imported. It stopped at the third, which includes "COLLATE utf8_bin" in the column definition, which doesn't seem to be <a href="https://mariadb.com/kb/en/mariadb/virtual-computed-columns/">included in the documentation for virtual columns</a>.

      I may attempt to make a simpler test case, but am short of time right now...

      Would like to have a work-around. Using emacs on an 11 GB file to nuke eight instances of COLLATE in PERSISTENT data def statements is not an option.

      Attachments

        Activity

          People

            Unassigned Unassigned
            Bytesmiths Jan Steinman
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.