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

mysqldump fails to dump geometry types properly

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.33a
    • 5.5.36
    • None
    • Debian Linux (Wheezy)

    Description

      When backing up a database with MYSQL_TYPE_GEOMETRY fields, mysqldump will output those fields in binary format by default. This causes issues upon restore.

      The typical error returned by mysql when restoring such a dump is :

      "Cannot get GEOMETRY object from the data you send to the Geometry field".

      Using the --hex-blob option fails to treat GEOMETRY types as blobs.

      A simple solution is to change the following line in mysqldump.c

      field->type == MYSQL_TYPE_TINY_BLOB)) ? 1 : 0;

      for
      field->type == MYSQL_TYPE_TINY_BLOB ||
      field->type == MYSQL_TYPE_GEOMETRY)) ? 1 : 0;

      Attachments

        Activity

          Hi,

          Could you please provide an example of the data which triggers the error? It would be useful for testing.
          I tried some primitive values and didn't get into any trouble, so apparently it does not happen always.

          elenst Elena Stepanova added a comment - Hi, Could you please provide an example of the data which triggers the error? It would be useful for testing. I tried some primitive values and didn't get into any trouble, so apparently it does not happen always.

          Hi Elena,

          I'm having a hard time locating an exact instance of a data dump with failing details.
          I will try to generate one from some past records we've had issues with.
          Regardless of that, the problem with dumping data as binary in an SQL file is also one of charsets and carriage return interpretation.
          This is of particular importance or interest when working accross platforms and trying to extract or manipulate SQL statements from a dump accross MariaDB installations of deployments.
          mysqldump should have the ability to dump all records in non-binary format regardless of type in order to avoid corruption of data when moving charsets around.
          Geometry objects being the only other record type to store binary information appart from Blobs, I propose it should either be considered as a Blob as well or alternatively have a switch like "--hex-geometry" to allow for non-binary dumping.

          Best regards,

          Guillaume

          ggiraudon Guillaume Giraudon added a comment - Hi Elena, I'm having a hard time locating an exact instance of a data dump with failing details. I will try to generate one from some past records we've had issues with. Regardless of that, the problem with dumping data as binary in an SQL file is also one of charsets and carriage return interpretation. This is of particular importance or interest when working accross platforms and trying to extract or manipulate SQL statements from a dump accross MariaDB installations of deployments. mysqldump should have the ability to dump all records in non-binary format regardless of type in order to avoid corruption of data when moving charsets around. Geometry objects being the only other record type to store binary information appart from Blobs, I propose it should either be considered as a Blob as well or alternatively have a switch like "--hex-geometry" to allow for non-binary dumping. Best regards, Guillaume
          holyfoot Alexey Botchkov added a comment - Fix: http://lists.askmonty.org/pipermail/commits/2014-February/005920.html

          pushed into 5.3

          holyfoot Alexey Botchkov added a comment - pushed into 5.3

          People

            holyfoot Alexey Botchkov
            ggiraudon Guillaume Giraudon
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.