1

I am using Navicat Premium to Migrate my Data from Postgresql to Mysql.

I am facing a strang error while migration. It says that Migration is not possible due to Geometry object not available.

I am pasting the logs here..

[Msg] [Dtf] DataTransfer started
[Msg] [Dtf] Getting tables properties
[Msg] [Dtf] Getting tables fields
[Msg] [Dtf] Getting tables constraints
[Msg] [Dtf] Getting tables indexes
[Msg] [Dtf] Getting total records count
[Msg] [Dtf] Drop table: `geom_data`
[Err] [Dtf] 80120001: Source data type [geometry] not supported
[Err] [Dtf] Finished - Unsuccessfully
--------------------------------------------------

What could be the problem.. Can I migrate Postgresql Spatial data to MySql?? Any alternative? Any way out??

writeToBhuwan
  • 3,233
  • 11
  • 39
  • 67
  • MySQL doesn't have a datatype "geometry" and you can't create it either. Migrating spatial data from PostgreSQL to MySQL is a huge step back, are you sure you need/want this? – Frank Heikens May 11 '14 at 14:20
  • You'd probably need to get the Navicat folks to add support for PostGIS and MySQL Spatial. It doesn't seem to be supported by the tool at this time. Personally I can't imagine why you'd want to, but sometimes the technical requirements aren't the only factors... – Craig Ringer May 11 '14 at 14:21
  • Yes, I do need it.. 90 % of the data is in MySql and I need to migrate rest of the 10% from Postgres.. :( – writeToBhuwan May 11 '14 at 15:26
  • @CraigRinger.. Yes you are right.. I've found the solution using another tool.. Will post the answer very soon.. :) – writeToBhuwan May 11 '14 at 15:27

1 Answers1

1

The Task can be done through SQLyog's data Migration wizard.

SQLYog-->Powertools-->Import External Data-->Start A new Job-->
  • After that select Any ODBC Source from the radio Buttons
  • Select File DSN---> Create a New DSN--File Data Source-->PostgreSQL Unicode
  • Click Next and save the file anywhere. eg abc.dsn
  • Click next and enter postgresql details and click finish
  • Enter the MySql database details you wanna copy your data into.
  • You are ready to go.

Hope it helps.!

Note: If Postgresql contains geometrical data, please make sure that you are using MyISAM as the database engine in MySql. Also remove indexing on any geometrical element before copying.

writeToBhuwan
  • 3,233
  • 11
  • 39
  • 67