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

INT used instead of UUID in view placeholder by mariadb-dump

    XMLWordPrintable

Details

    Description

      When database contains views they are dumped by mariadb-dump in the alphabetical order twice.
      First time a placeholders with columns of corresponding types are dumped. Second time an actual views definitions are dumped. That is intended to prevent collisions when view definition is dependent on some other view not loaded yet.

      A problem: for columns of UUID type placeholders uses INT. If dependent view's name alphabetically is smaller than name of a view it is depend on, then on dependent view's definition load parser throws an error:

      ERROR 4078 (HY000) at line 106: Illegal parameter data types uuid and int for operation '='

      Workarounds:
      For small databases mariadb --force dbname < dump.sql could be performed twice in a row. First run will throw an error, the second run will be ok.
      For big databases two dumps should be created - one plain dump with

      mariadb-dump -ER dbname > dump.sql

      and another dump with views only

      mariadb -BN -e "
      SELECT TABLE_NAME
        FROM information_schema.tables
       WHERE TABLE_SCHEMA = '$SOURCEDB'
         AND TABLE_TYPE = 'VIEW'
       ORDER BY TABLE_NAME ASC " |
      while read viewname
      do
        $mariadb-dump --no-create-db --no-data --triggers --comments ${SOURCEDB} ${viewname} >> views.sql
      done
      

      Then the common dump should be loaded with `--force` option and then views dump should be loaded.

           mariadb --force dbname < dump.sql
           mariadb --force dbname < views.sql 
      

      If views have more that one level of dependency then views.dump should be loaded again and again until error disappear.

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              ArtemP Artem Perekresny
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.