maxlapshin / mysql2postgres

Mysqldump, writing in postgresql format

mysql-to-postgres - MySQL to PostgreSQL Data Translation

MRI or jruby supported. The minimum Ruby version supported in master branch is 2.1.7, and the next release will have the same requirement.

With a bit of a modified rails database.yml configuration, you can integrate mysql-to-postgresinto a project.

Installation

Currently failing, see #81...

Via RubyGems

gem install mysqltopostgres

From source

git clone https://github.com/maxlapshin/mysql2postgres.git
cd mysql2postgres
bundle install
gem build mysqltopostgres.gemspec
sudo gem install mysqltopostgres-0.3.1.gem

Sample Configuration

Configuration is written in YAML format and passed as the first argument on the command line.

default: &default
  adapter: jdbcpostgresql
  encoding: unicode
  pool: 4
  username: terrapotamus
  password: default
  host: 127.0.0.1

development: &development
  <<: *default
  database: default_development

test: &test
  <<: *default
  database: default_test

production: &production
  <<: *default
  database: default_production

mysql_data_source: &pii
  host: localhost
  port: 3306
  socket: /tmp/mysqld.sock
  username: username
  password: default
  database: awesome_possum

mysql2psql:
  mysql:
    <<: *pii

  destination:
    production:
      <<: *production
    test:
      <<: *test
    development:
      <<: *development

  tables:
  - countries
  - samples
  - universes
  - variable_groups
  - variables
  - sample_variables

  # If suppress_data is true, only the schema definition will be exported/migrated, and not the data
  suppress_data: false

  # If suppress_ddl is true, only the data will be exported/imported, and not the schema
  suppress_ddl: true

  # If force_truncate is true, forces a table truncate before table loading
  force_truncate: false

  preserve_order: true

  remove_dump_file: true

  dump_file_directory: /tmp

  report_status: json    # false, json, xml

  # If clear_schema is true, the public schema will be recreated before conversion
  # The import will fail if both clear_schema and suppress_ddl are true.
  clear_schema: false

Please note that the MySQL connection will be using socket in case the host is not defined (nil) or it is 'localhost'.

Testing

License

Licensed under the MIT license.

mysql2postgres's Issues

postgres2mysql

Hi,

I have used mysql2postgresql ruby file. its working very fine.
Now i want to change my postgres database to mysql.. is there any ruby file for converting postgres to mysql.

Thanks in advance,
sangeethkumar N

Converting VARBINARY

There is an error while converting fields of MySQL type 'VARBINARY'. Please find a patch bellow to fix this issue:

diff --git a/mysql2psql b/mysql2psql
index 1ad9a04..c555d68 100755
--- a/mysql2psql
+++ b/mysql2psql
@@ -327,6 +327,8 @@ class PostgresWriter < Writer
       "bytea"
     when "blob"
       "bytea"
+    when /varbinary/
+      "bytea"
     when "tinytext"
       "text"
     when "mediumtext"

RuntimeError: `<top (required)>': '' does not exist

I am trying to get the mysqltopostgres command to work for the second day without any luck. I am using the latest 0.2.19 version from the master branch. I suspect I'm doing something wrong, do you know what that might be?

$ mysqltopostgres
/usr/local/var/rbenv/versions/1.9.3-p550/lib/ruby/gems/1.9.1/gems/mysqltopostgres-0.2.19/bin/mysqltopostgres:30:in `<top (required)>': '' does not exist (RuntimeError)
    from /usr/local/var/rbenv/versions/1.9.3-p550/bin/mysqltopostgres:23:in `load'
    from /usr/local/var/rbenv/versions/1.9.3-p550/bin/mysqltopostgres:23:in `<main>'
$ gem list

*** LOCAL GEMS ***

activemodel (4.1.7)
activerecord (4.1.7)
activesupport (4.1.7)
addressable (2.3.6)
arel (5.0.1.20140414130214)
bigdecimal (1.2.5, 1.1.0)
builder (3.2.2)
bundler (1.7.4)
descendants_tracker (0.0.4)
faraday (0.9.0)
git (1.2.8)
github_api (0.12.2)
hashie (3.3.1)
highline (1.6.21)
i18n (0.6.11)
io-console (0.4.2, 0.3)
jeweler (2.0.1)
json (1.8.1, 1.5.5)
jwt (1.0.0)
mini_portile (0.6.0)
minitest (5.4.2, 2.5.1)
multi_json (1.10.1)
multi_xml (0.5.5)
multipart-post (2.0.0)
mysql-pr (2.9.11)
mysqltopostgres (0.2.19)
nokogiri (1.6.3.1)
oauth2 (1.0.0)
pg (0.17.1)
postgres-pr (0.6.3)
power_assert (0.1.4)
rack (1.5.2)
rake (10.3.2, 0.9.2.2)
rdoc (4.1.2, 3.9.5)
test-unit (3.0.3)
thread_safe (0.3.4)
tzinfo (1.2.2)
$ ruby -v
ruby 1.9.3p550 (2014-10-27 revision 48165) [x86_64-darwin14.0.0]

uninitialized constant Mysql::Field (NameError)

Hi Could Please help me about my below error.
Send reply to: [email protected]

.[root@db-1 postgres]# ./mysql2psql
up to here 1
up to here 2
./mysql2psql:23: (eval):1: uninitialized constant Mysql::Field (NameError)
from /opt/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in eval' from ./mysql2psql:23 from /opt/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:ininject'
from ./mysql2psql:20:in each' from ./mysql2psql:20:ininject'
from ./mysql2psql:20

Regards,
Bala

Transfer ownership or add collaborators?

mysql2postgres is awesome, but the primary repo has many unresolved issues and merge requests. We'd love to close some of these and consolidate the work being done on forks.

Max, if you don't have time, will you add someone to collaborators, or transfer ownership to someone who can review the new changes?

Thanks,

Aaron

packet is not EOF

I've got an error:

  /usr/local/rvm/gems/ruby-1.9.3-p551/gems/mysqltopostgres-0.2.20/bin/mysqltopostgres mysql2pg.yml
  Mysql2psql: Conversion failed: packet is not EOF
  packet is not EOF
  /usr/local/rvm/gems/ruby-2.2.0/gems/mysql-pr-2.9.11/lib/mysql-pr/protocol.rb:562:in `read_eof_packet'
  /usr/local/rvm/gems/ruby-2.2.0/gems/mysql-pr-2.9.11/lib/mysql-pr/protocol.rb:274:in `retr_fields'
  /usr/local/rvm/gems/ruby-2.2.0/gems/mysql-pr-2.9.11/lib/mysql-pr.rb:322:in `query'

the very short dump is appeared:

  -- MySQL 2 PostgreSQL dump

  SET client_encoding = 'UTF8';
  SET standard_conforming_strings = off;
  SET check_function_bodies = false;
  SET client_min_messages = warning;

Some problems while migrating a database

I just did a migration from a mysql database and had some issues. I tried some simple fixes, but I must admit, that I do not speak any Ruby ;-)

So the attached diff may inspire you to find a real solution. The problems seemed to be realted to enum columns which had some default values.

diff --git a/mysql2psql b/mysql2psql
index 1ad9a04..a035133 100755
--- a/mysql2psql
+++ b/mysql2psql
@@ -60,7 +60,7 @@

 require 'rubygems'
 require 'mysql'
-gem "pg"
+# gem "pg"
 require 'pg'
 require 'yaml'

@@ -336,10 +336,10 @@ class PostgresWriter < Writer
     when "text"
       "text"
     when /^enum/
-      default = " DEFAULT #{column[:default].nil? ? 'NULL' : column[:default]}" if default
+      default = " DEFAULT '#{column[:default].nil? ? 'NULL' : column[:default]}'" if default
       enum = column[:type].gsub(/enum|\(|\)/, '')
       max_enum_size = enum.split(',').map{ |check| check.size() -2}.sort[-1]
-      "character varying(#{max_enum_size}), check( #{column[:name]} in (#{enum}))"
+      "character varying(#{max_enum_size})" #, check( #{column[:name]} in (#{enum}))"
     else
       puts "Unknown #{column.inspect}"
       column[:type].inspect

all :decimal are :integer fields now

Using a test run, i found a problem for me.

any column using t.decimal :price got converted as an integer.

I can't find in the source or the yml file anywhere where I should have used something different. Has anyone had that happen to them before?

thanks.

missing data after migration

Using this tool to migrate between mysql 5.1 and postgres 9.1 it seems to create all the tables properly but none of the data. In the command output I end up seeing something like:

Counting rows of invite_requests...
Rows counted
Loading invite_requests...

rows loaded in 0min 0s
Counting rows of org_invite_codes...
Rows counted
Loading org_invite_codes...

Seems to suggest it didn't load any rows.

Error - column does not exist (default value)

I am getting the following error when trying to import DB. This column has a DEFAULT value:

Creating table orders...
Error:
CREATE TABLE "orders" (
"id" integer DEFAULT nextval('orders_id_seq'::regclass) NOT NULL,
"customer_id" integer,
"status" character varying(255) DEFAULT Unseen::character varying NOT NULL,
"dispatcher_note" text,
"driver_note" text,
"items_billed_archive" numeric(6, 2),
"payment_status" character varying(255),
"transaction_id" character varying(255),
"restaurant_status" text,
"faxed" text
)
WITHOUT OIDS;
Mysql2psql: conversion failed: ERROR: column "unseen" does not exist

Is there a fix or workaround for this error?

cannot load such file -- mysqltopostgres

i got the error here:

[root@localhost mysql2postgres]# mysqltopostgres
WARN: Unresolved specs during Gem::Specification.reset:
test-unit (>= 2.1.1)
WARN: Clearing out unresolved specs.
Please report a bug if this causes problems.
/usr/local/rvm/gems/ruby-2.1.2/gems/mysqltopostgres-0.2.15/bin/mysqltopostgres:7:in require': cannot load such file -- mysqltopostgres (LoadError) from /usr/local/rvm/gems/ruby-2.1.2/gems/mysqltopostgres-0.2.15/bin/mysqltopostgres:7:in<top (required)>'
from /usr/local/rvm/gems/ruby-2.1.2/bin/mysqltopostgres:23:in load' from /usr/local/rvm/gems/ruby-2.1.2/bin/mysqltopostgres:23:in

'
from /usr/local/rvm/gems/ruby-2.1.2/bin/ruby_executable_hooks:15:in eval' from /usr/local/rvm/gems/ruby-2.1.2/bin/ruby_executable_hooks:15:in'

Code coverage check, badge and increase its percentage

1. Add code coverage checking
2. Push coverage report to service such as coveralls.io which provides a badge
3. Increase the code coverage by adding unit tests (while fixing older issues like #49, #48, #41, #27 ...) up to, say 95%

Mysql2psql: conversion failed: undefined method `hour' for nil:NilClass

Hi,

I'm trying to convert a database but still getting this error:

"Mysql2psql: conversion failed: undefined method `hour' for nil:NilClass"

Regards,

Thiago Bonfante

Float not handled correctly

It is treating as a decimal but the scale and prec #s are not necessarily present for the float and the length is carried from the previous field...

example: mysql table created as:
CREATE TABLE tmp_data (
row_names date NOT NULL DEFAULT '0000-00-00',
z_ticker varchar(80) NOT NULL DEFAULT '',
px1 float DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

emits:
CREATE TABLE "tmp_data" (
"row_names" date NOT NULL,
"z_ticker" character varying(80) NOT NULL,
"px1" numeric(80, 0),
CONSTRAINT tmp_data_pkey PRIMARY KEY("row_names", "z_ticker")
)
WITHOUT OIDS;

Windows: mysql gem requires libmysql.dll from MySQL 5.0. Crashes with anything newer

Hi.

Could you please reflect in documentation, that ruby gem mysql on Windows requires libmysql.dll from MySQL server 5.0. And so old MySQL server release is already nowhere to be found.

libmysql.dll from MySQL 5.1 and 5.5 crashes application.

I had to download php-5.2.17-Win32-VC6-x86.zip and take libmysql.dll from there. It worked. I placed it into Ruby\bin folder.

Float converts incorrectly

mysql float columns become numberic 8,0 without any warning.

Incremental updates?

Is there a way to transfer only the records that are not already in the target table?

mediawiki conversion fails on first table

Hi there :)

This looks like an extremely useful tool, but after fiddling around getting the necessary ruby environment configured (never really messed with Ruby before), I still can't get it to work.

I'm trying to convert a mediawiki installation, at the output is as follows:

Creating table archive...
Unknown {:auto_increment=>false, :type=>"varbinary(255)", :length=>255, :table_name=>"archive", :decimals=>nil, :null=>false, :name=>"ar_title", :primary_key=>false}
Unknown {:auto_increment=>false, :type=>"varbinary(255)", :length=>255, :table_name=>"archive", :decimals=>nil, :null=>false, :name=>"ar_user_text", :primary_key=>false}
Unknown {:auto_increment=>false, :type=>"binary(14)", :length=>14, :table_name=>"archive", :decimals=>nil, :null=>false, :name=>"ar_timestamp", :default=>"\000\000\000\000\000\000\000\000\000\000\000\000\000\000", :primary_key=>false}
Error:
CREATE TABLE "archive" (
  "ar_namespace" integer DEFAULT 0 NOT NULL,
  "ar_title" ,
  "ar_text" bytea NOT NULL,
  "ar_comment" bytea NOT NULL,
  "ar_user" integer DEFAULT 0 NOT NULL,
  "ar_user_text" ,
  "ar_timestamp" ,
  "ar_minor_edit" smallint DEFAULT 0 NOT NULL,
  "ar_flags" bytea NOT NULL,
  "ar_rev_id" integer,
  "ar_text_id" integer,
  "ar_deleted" smallint DEFAULT 0 NOT NULL,
  "ar_len" integer,
  "ar_page_id" integer,
  "ar_parent_id" integer
)
WITHOUT OIDS;
./mysql2psql:546:in `exec': ERROR:  syntax error at or near "," (PGError)
LINE 3:   "ar_title" ,
                     ^
    from ./mysql2psql:546:in `write_table'
    from ./mysql2psql:697:in `convert'
    from ./mysql2psql:696:in `each'
    from ./mysql2psql:696:in `convert'
    from ./mysql2psql:757

As I say, it took me a while to get the right environment to make it run (installed 3 different ruby/postgres implementations, before I found the right one), so perhaps I screwed that up?

Would be grateful for any advice. Thanks!

Edit: the three fields that have failed are blobs, 'mediumblob' and two 'tinyblob's respectively. is this script supposed to be able to handle blobs?

Edit again: Please ignore the above - it's total nonsense, I'm sorry. The fields which fail parsing are listed as types 'varbinary(255)', 'varbinary(255)' and 'binary(14)' respectively. Looking at the script, it's clear that these are unhandled.

I created a new mediawiki installation with a postgres backend, so that I could see how these fields are stored there. The varbinary fields are in the postgres schema as text fields, and the binary(14) are timestamps. So it seems I'm probably gonna have to roll my own solution to this anyway.

how to configure

i installed but executing mysql-to-postgres getting following error

sysadmin@system8:~/mysql2postgres$ mysql-to-postgres
/var/lib/gems/1.9.1/gems/bundler-1.3.5/lib/bundler/runtime.rb:33:in block in setup': You have already activated pg 0.9.0, but your Gemfile requires pg 0.17.0. Using bundle exec may solve this. (Gem::LoadError) from /var/lib/gems/1.9.1/gems/bundler-1.3.5/lib/bundler/runtime.rb:19:insetup'
from /var/lib/gems/1.9.1/gems/bundler-1.3.5/lib/bundler.rb:120:in setup' from /var/lib/gems/1.9.1/gems/bundler-1.3.5/lib/bundler/setup.rb:7:in<top (required)>'
from /usr/lib/ruby/1.9.1/rubygems/custom_require.rb:59:in require' from /usr/lib/ruby/1.9.1/rubygems/custom_require.rb:59:inrescue in require'
from /usr/lib/ruby/1.9.1/rubygems/custom_require.rb:35:in require' from /var/lib/gems/1.9.1/gems/mysql2psql-0.1.0/bin/mysql-to-postgres:5:in<top (required)>'
from /usr/local/bin/mysql-to-postgres:19:in load' from /usr/local/bin/mysql-to-postgres:19:in

'

sequence owner

suspect that something is lost in translation with regard to primary keys/sequences during the migration.

I've been battling with a strange bug on rails 3.1 where running db:schema:dump was dumping the tables without the "id" primary key specified, e.g.

create_table "activities", :force => true do |t|

was becoming:

create_table "activities", :id => false, :force => true do |t|

This was only happening for tables that were created prior to the migration, so I suspected something was missing in the schema structure and tracked it down to missing owners on the ID sequences - tables added after the migration from mysql had the owner set when I dumped the schema manually, e.g:

ALTER SEQUENCE "accounts_id_seq" OWNED BY "accounts"."id";

Those that were ported from MySQL did not.

mysql2psql fails on mac os x 10.7.4

arul:test arul$ mysql2psql
/Library/Ruby/Gems/1.8/gems/mysql2psql-0.1.0/bin/../lib/mysql2psql/mysql_reader.rb:18: (eval):1: uninitialized constant Mysql::Field (NameError)
from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/rubygems/custom_require.rb:31:in eval' from /Library/Ruby/Gems/1.8/gems/mysql2psql-0.1.0/bin/../lib/mysql2psql/mysql_reader.rb:18 from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/rubygems/custom_require.rb:31:ininject'
from /Library/Ruby/Gems/1.8/gems/mysql2psql-0.1.0/bin/../lib/mysql2psql/mysql_reader.rb:17:in each' from /Library/Ruby/Gems/1.8/gems/mysql2psql-0.1.0/bin/../lib/mysql2psql/mysql_reader.rb:17:ininject'
from /Library/Ruby/Gems/1.8/gems/mysql2psql-0.1.0/bin/../lib/mysql2psql/mysql_reader.rb:17
from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/rubygems/custom_require.rb:31:in gem_original_require' from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/rubygems/custom_require.rb:31:inrequire'
from /Library/Ruby/Gems/1.8/gems/mysql2psql-0.1.0/bin/../lib/mysql2psql.rb:5
from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/rubygems/custom_require.rb:31:in gem_original_require' from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/rubygems/custom_require.rb:31:inrequire'
from /Library/Ruby/Gems/1.8/gems/mysql2psql-0.1.0/bin/mysql2psql:5
from /usr/bin/mysql2psql:19:in `load'
from /usr/bin/mysql2psql:19

/mysql2psql:166:in `list_tables': No database selected (Mysql::Error)

Hello,

I've downloaded both Full_Simple_Invoices.sql and structure.sql from SimpleInvoices project in order to try to migrate database to Postgres, but with both I get the following:

[gour@gaura-nitai si] ./mysql2psql
./mysql2psql:166:in list_tables': No database selected (Mysql::Error) from ./mysql2psql:166:intables'
from ./mysql2psql:614:in convert' from ./mysql2psql:679:in

'

Here is the list of tables as shown within mysql:

mysql> show tables;
+---------------------------+
| Tables_in_simple_invoices |
+---------------------------+
| si_biller                 |
| si_custom_fields          |
| si_customers              |
| si_extensions             |
| si_invoice_item_tax       |
| si_invoice_items          |
| si_invoice_type           |
| si_invoices               |
| si_log                    |
| si_payment                |
| si_payment_types          |
| si_preferences            |
| si_products               |
| si_sql_patchmanager       |
| si_system_defaults        |
| si_tax                    |
| si_user                   |
| si_user_domain            |
| si_user_role              |
+---------------------------+
19 rows in set (0.00 sec)

Any idea?

Sincerely,
Gour

"not an index" errors

Hello,

I'm trying this script to migrate my Drupal to psql.

I ran the script, and all the tables were create. But I got two errors:

Indexing table node...
Couldn't create indexes on #Mysql2psql::MysqlReader::Table:0x7f082e76c8b0 ([{:primary=>true, :columns=>["nid"]}, {:unique=>true, :columns=>["vid"], :name=>"vid"}, {:columns=>["changed"], :name=>"node_changed"}, {:columns=>["created"], :name=>"node_created"}, {:columns=>["moderate"], :name=>"node_moderate"}, {:columns=>["status", "type", "nid"], :name=>"node_status_type"}, {:columns=>["title", "type"], :name=>"node_title_type"}, {:columns=>["type"], :name=>"node_type"}, {:columns=>["uid"], :name=>"uid"}, {:columns=>["tnid"], :name=>"tnid"}, {:columns=>["translate"], :name=>"translate"}, {:columns=>["promote", "status", "sticky", "created"], :name=>"node_frontpage"}])
ERROR: "node_type" is not an index
HINT: Use DROP TABLE to remove a table.
./../lib/mysql2psql/postgres_db_writer.rb:108:in exec' ./../lib/mysql2psql/postgres_db_writer.rb:108:inwrite_indexes'
./../lib/mysql2psql/postgres_db_writer.rb:93:in `each'

Indexing table users...
Couldn't create indexes on #Mysql2psql::MysqlReader::Table:0x7f082e76c298 ([{:primary=>true, :columns=>["uid"]}, {:unique=>true, :columns=>["name"], :name=>"name"}, {:columns=>["access"], :name=>"access"}, {:columns=>["created"], :name=>"created"}, {:columns=>["mail"], :name=>"mail"}])
ERROR: "access" is not an index
HINT: Use DROP TABLE to remove a table.
./../lib/mysql2psql/postgres_db_writer.rb:108:in exec' ./../lib/mysql2psql/postgres_db_writer.rb:108:inwrite_indexes'
./../lib/mysql2psql/postgres_db_writer.rb:93:in `each'

If you need more information, please let me know.

Thanks for the great work.

postgres_db_writer and postgres_file_writer: same but different

Hi,

It looks like postgres_db_writer and postgres_file_writer are doing different things now:

• db writer creates indexes, and file writer ignores them:
  https://github.com/maxlapshin/mysql2postgres/blob/master/lib/mysql2psql/postgres_db_writer.rb#L87:
  https://github.com/maxlapshin/mysql2postgres/blob/master/lib/mysql2psql/postgres_file_writer.rb#L109
• db writer doesn't truncate, but file writer does, when the force_truncate flag is on
• db writer changes its behavior based on the Postgres version, and file writer doesn't (because it doesn't have a connection, but perhaps the user could pass a flag)
• there are a lot of duplicated SQL statements between the files

As a user I would expect the "MySQL->Postgres" and "MySQL->file->Postgres" methods to have the same result. Should we refactor to reduce duplication and the differences between the two modes?

Aaron

Can't get integration test to run

Probably missing something obvious here. The databases are set up as suggested in the Readme, but when I try and run the integration test, I get a series of errors,

1. Error:
   test_table_creation(ConvertToDbTest):
   NameError: uninitialized class variable @@mysql2psql in ConvertToDbTest
   ./test/integration/convert_to_db_test.rb:28:in `test_table_creation'
2. Error:
   test_basic_numerics_bigint(ConvertToFileTest):
   NameError: uninitialized class variable @@content in ConvertToFileTest
   ./test/integration/convert_to_file_test.rb:24:incontent' ./test/integration/convert_to_file_test.rb:48:in test_basic_numerics_bigint'
   and a few more. What am I missing? Thanks for any help,
   Rick

Float Field Unsupported

My sample table contains a float/double field called "value", and your script doesn't appear to support this data type. This is the error I get:

Error:
CREATE TABLE "mytable" (
"id" integer DEFAULT nextval('mytable_id_seq'::regclass) NOT NULL,
"value"
)
WITH (OIDS=FALSE);
./mysql2psql.rb:421:in exec': ERROR: syntax error at or near ")" (PGError) LINE 7: ) ^ from ./mysql2psql.rb:421:inwrite_table'
from ./mysql2psql.rb:540:in convert' from ./mysql2psql.rb:539:ineach'
from ./mysql2psql.rb:539:in `convert'
from ./mysql2psql.rb:570

mysql2postgres fails to correctly handle double fields

I ran into the following issue where mysql2postgres didn't correctly convert the following mysql fields:

CREATE TABLE `graphs` (
  ... various fields ...
  `yaxismin` double(16,4) NOT NULL DEFAULT '0.0000',
  `yaxismax` double(16,4) NOT NULL DEFAULT '0.0000',

Rather than producing valid postgres SQL, it gave the following:

CREATE TABLE "graphs" (
  ... various fields ...
  "yaxismin" ,
  "yaxismax" ,

and crashed with the following error:

./mysql2psql:533:in `exec': ERROR:  syntax error at or near "," (PGError)
LINE 6:   "yaxismin" ,
                     ^
        from ./mysql2psql:533:in `write_table'
        from ./mysql2psql:684:in `convert'
        from ./mysql2psql:683:in `each'
        from ./mysql2psql:683:in `convert'
        from ./mysql2psql:744

I've fixed this bug in my branch at http://github.com/blinken/mysql2postgres.

MySQL connection socket not used

Socket configuration option is not being passed to the MySQL connection.

Gem build warnings

While running gem build mysqltopostgres.gemspec, the following warnings are coming:

WARNING:  licenses is empty, but is recommended.  Use a license abbreviation from:
http://opensource.org/licenses/alphabetical
WARNING:  open-ended dependency on mysql-pr (>= 2.9.10) is not recommended
  if mysql-pr is semantically versioned, use:
    add_runtime_dependency 'mysql-pr', '~> 2.9', '>= 2.9.10'
WARNING:  open-ended dependency on activerecord (>= 3.2.6) is not recommended
  if activerecord is semantically versioned, use:
    add_runtime_dependency 'activerecord', '~> 3.2', '>= 3.2.6'
WARNING:  open-ended dependency on test-unit (>= 2.1.1) is not recommended
  if test-unit is semantically versioned, use:
    add_runtime_dependency 'test-unit', '~> 2.1', '>= 2.1.1'
WARNING:  See http://guides.rubygems.org/specification-reference/ for help

Would it make sense to add version numbers as suggested?

Foreign Key Actions Supported?

I just installed the mysql2psql gem (0.1.0).

I imported a database and there were no errors. However, upon inspect I can see that the on update and on delete actions on all my foreign keys are set to NO ACTION in the newly created psql database. In MySql all the FKs have on update and on delete actions set.

Is this a bug in the gem or something not supported yet?

Add automated testing

Travis CI, http://docs.travis-ci.com/user/languages/ruby/

UTF8 data not migrating correctly

I'm finding that utf8 data that is present in my source database (confirmed via the mysql command-line client) des not end up encoded correctly in the destination database (confirmed with psql). In both the yml configuration blocks for my source and destination databases, I have:

encoding: utf8

Also, I confirmed that the character encodings on both the source and destination databases are utf8.

Any ideas what the fix might be?

syntax error in 'load'

Kinda of a ruby noobie. I'm getting this error:

/usr/local/lib/ruby/1.9.1/yaml.rb:133:in load': syntax error on line 21, col 0:alert_batch_send,' (ArgumentError)
from /usr/local/lib/ruby/1.9.1/yaml.rb:133:in load' from mysql2postgres:649:inread_config'
from mysql2postgres:667:in `

'

I am also using the pg gem, not the postgres gem, due to I have ruby 1.9.1. This is 64-bit centos 5.3

MySQL database to PostgreSQL schema

I am just running this utility for the first time. I was expecting it to put all tables from a MySQL 'database' into a newly created PostgreSQL schema with the same name as the MySQL db. But it turns out that all tables get put into the 'public' schema.

Would be nice to have the option to specify a schema, but by default I would argue it makes sense to make a new schema with the name of the MySQL database.

Best regards & thanks for this script!

Error migrating moodle: syntax error in or next to "DEFAULT" (PGError)

Creating table moodleenrol_authorize...
Error:
CREATE TABLE "moodleenrol_authorize" (
"id" integer DEFAULT nextval('moodleenrol_authorize_id_seq'::regclass) NOT NULL,
"paymentmethod" character varying(6), check( paymentmethod in ('cc','echeck')) DEFAULT cc NOT NULL,
"refundinfo" integer DEFAULT 0 NOT NULL,
"ccname" character varying(255) NOT NULL,
"courseid" bigint DEFAULT 0 NOT NULL,
"userid" bigint DEFAULT 0 NOT NULL,
"transid" bigint DEFAULT 0 NOT NULL,
"status" bigint DEFAULT 0 NOT NULL,
"timecreated" bigint DEFAULT 0 NOT NULL,
"settletime" bigint DEFAULT 0 NOT NULL,
"amount" character varying(10) NOT NULL,
"currency" character(3) DEFAULT 'USD'::char NOT NULL
)
WITHOUT OIDS;
./mysql2psql:466:in exec': ERRO: erro de sintaxe em ou próximo a "DEFAULT" (PGError) LINE 3: ...ying(6), check( paymentmethod in ('cc','echeck')) DEFAULT cc... ^ from ./mysql2psql:466:inwrite_table'
from ./mysql2psql:617:in convert' from ./mysql2psql:616:ineach'
from ./mysql2psql:616:in `convert'
from ./mysql2psql:682

And the process die.

mysql year() datatype not converted and left blank.

***MYSQL DUMP:

DROP TABLE IF EXISTS amex_quarterly_metrics;
/!40101 SET @saved_cs_client = @@character_set_client */;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE amex_quarterly_metrics (
year year(4) NOT NULL,
quarter enum('1','2','3','4') NOT NULL,
chargeoffs float DEFAULT NULL,
delinquency float DEFAULT NULL,
PRIMARY KEY (year,quarter)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client */;

***POSTGRES CONVERSION:

DROP TABLE IF EXISTS "amex_quarterly_metrics" CASCADE;

CREATE TABLE "amex_quarterly_metrics" (
"year" ,
"quarter" character varying(1) check( quarter in ('1','2','3','4')) NOT NULL,
"chargeoffs" numeric(4, 0),
"delinquency" numeric(4, 0),
CONSTRAINT amex_quarterly_metrics_pkey PRIMARY KEY("year", "quarter")
)
WITHOUT OIDS;

Couldn't create indexes / undefined method "parse_csv"

The following error occurs when trying to convert InnoDB tables with foreign keys.

Couldn't create indexes on #Mysql2psql::MysqlReader::Table:0x10118db28 ([{:primary=>true, :columns=>["id"]}, {:columns=>["rr"], :name=>"rr"}])
undefined method `parse_csv' for "rr":String
/Library/Ruby/Gems/1.8/../mysql2psql/mysql_reader.rb:110:in "load_indexes"
/Library/Ruby/Gems/1.8/../mysql2psql/mysql_reader.rb:105:in "each"
/Library/Ruby/Gems/1.8/../mysql2psql/mysql_reader.rb:105:in "load_indexes"

The foreign key is defined as follows:

constraint "rr" foreign key ("rr") references "other" ("id") on delete cascade on update cascade

ProtocolError: packet is not EOF

I am getting the following error when running in my local setup (mysql 5.5.40, ruby 2.1.5 on ubuntu 14.04):

$ mysql --version
mysql  Ver 14.14 Distrib 5.5.40, for debian-linux-gnu (x86_64) using readline 6.3

$ RAILS_ENV=production mysqltopostgres database.yml
gems/mysql-pr-2.9.11/lib/mysql-pr/protocol.rb:562:in `read_eof_packet': packet is not EOF (MysqlPR::ProtocolError)
        from gems/mysql-pr-2.9.11/lib/mysql-pr/protocol.rb:274:in `retr_fields'
        from gems/mysql-pr-2.9.11/lib/mysql-pr.rb:322:in `query'
        from gems/mysql-pr-2.9.11/lib/mysql-pr.rb:442:in `list_tables'
        from bundler/gems/mysql2postgres-084519061f48/lib/mysql2psql/mysql_reader.rb:193:in `tables'
        from bundler/gems/mysql2postgres-084519061f48/lib/mysql2psql/converter.rb:21:in `convert'
        from bundler/gems/mysql2postgres-084519061f48/lib/mysqltopostgres.rb:51:in `convert'
        from bundler/gems/mysql2postgres-084519061f48/bin/mysqltopostgres:23:in `<top (required)>'
        from bin/mysqltopostgres:23:in `load'
        from bin/mysqltopostgres:23:in `<main>'
        from bin/ruby_executable_hooks:15:in `eval'
        from bin/ruby_executable_hooks:15:in `<main>'

MySQL config (nothing special):

[...]
mysql_data_source: &pii
  hostname: localhost
  port: 3306
  socket: /var/run/mysqld/mysqld.sock
  username: xx
  password: xx
  database: xx

mysql2psql:
  mysql:
    <<: *pii

[...]

Thanks!
Thomas

Add dependency version checking badge

Such as Gemnasium (http://gemnasium.com/) or Versioneye (http://www.versioneye.com/).

Error with SMALLINT column and -1 value

in first sorry for my bad english. I hope you understand me.

In table with small int column with value set to -1, the script make record with 4294967295 value.
I have searched in source code, particularly in paginated_read function (line 232)
If i replace mysql.prepare and mysql.execute with a simple mysql.query, it's ok (i remake query for each limit).

def paginated_read(table, page_size)
count = table.count_for_pager
return if count < 1
counter = 0
0.upto((count + page_size)/page_size) do |i|
query = table.has_id? ? 'WHERE id >= '+String(i_page_size)+' AND id < '+String(table.has_id? ? (i+1)_page_size : page_size)+'' : 'LIMIT '+String(i_page_size)+','+String(table.has_id? ? (i+1)_page_size : page_size)+''
query = "SELECT #{table.columns.map{|c| ""+c[:name]+""}.join(", ")} FROM #{table.name} #{query}"
statement = @mysql.query(query)
while row = statement.fetch_row
counter += 1
yield(row, counter)
end
end
counter
end

gem --version
1.3.5

ruby --version
ruby 1.8.7 (2008-08-11 patchlevel 72) [x86_64-linux]

mysql --version
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2

I don't understand why fetch don't return -1 value and fetch_row return -1

Thanks
Seb

ps: sorry for my atrocious ruby code but it's the first time (read and write ruby code ...)

cannot load such file -- mysql2psql/connection

/vagrant/mysql2postgres (2.1.3p242) 
λ mysqltopostgres 
/usr/local/lib/site_ruby/2.1.0/rubygems/core_ext/kernel_require.rb:54:in `require': cannot load such file -- mysql2psql/connection (LoadError)
        from /usr/local/lib/site_ruby/2.1.0/rubygems/core_ext/kernel_require.rb:54:in `require'
        from /var/lib/gems/2.1.0/gems/mysqltopostgres-0.2.15/lib/mysql2psql/postgres_db_writer.rb:2:in `<top (required)>'
        from /usr/local/lib/site_ruby/2.1.0/rubygems/core_ext/kernel_require.rb:54:in `require'
        from /usr/local/lib/site_ruby/2.1.0/rubygems/core_ext/kernel_require.rb:54:in `require'
        from /var/lib/gems/2.1.0/gems/mysqltopostgres-0.2.15/lib/mysqltopostgres.rb:22:in `<top (required)>'
        from /usr/local/lib/site_ruby/2.1.0/rubygems/core_ext/kernel_require.rb:54:in `require'
        from /usr/local/lib/site_ruby/2.1.0/rubygems/core_ext/kernel_require.rb:54:in `require'
        from /var/lib/gems/2.1.0/gems/mysqltopostgres-0.2.15/bin/mysqltopostgres:7:in `<top (required)>'
        from /usr/local/bin/mysqltopostgres:23:in `load'
        from /usr/local/bin/mysqltopostgres:23:in `<main>'

problem with conversion of mysql bit(1) to postgres boolean

Hi all:

I have tried to migrate a mysql database to postgres, and got some errors. Here is the error log:
psql:/opt/cth/data/cth20110323_data_pg.sql:756407: error: invalid boolean type input syntax: ""
background: COPY coordinator, row 3, col haszone: ""

I checked mysql database and the generated sql script for postgres, and found that "haszone" field is bit(1) type in mysql, bit(1) is stored as binary value in mysql, and this binary value is not written to sql script file correctly. Actually this issue is explained on "Notes, Limitations, Outstanding Issues.." section, but that tweak doesn't work for me.

If bit(1) could be converted to t/f in generated sql, that would be great.

thanks for your work.

can't convert nil into String (TypeError)

Hello!

I'm trying to use the script to convert mysql database used in SimpleInvoices to Postgresql, but it fails with:

[gour@gaura-nitai mysql2postgres]  ./mysql2psql       
./mysql2psql:279:in `initialize': can't convert nil into String (TypeError)
    from ./mysql2psql:279:in `open'
    from ./mysql2psql:279:in `initialize'
    from ./mysql2psql:678:in `new'
    from ./mysql2psql:678:in `'

trouble with creating postgres gem

I first ran into the issue here:

http://rubyforge.org/tracker/index.php?func=detail&aid=26744&group_id=3214&atid=12396

wherein with ruby > 1.9 you can't create a postgres gem.

Unfortunately, I can't seem to create a pg gem either. I keep getting:

[brad@hpemysql01 pg]$ sudo gem install pg
Building native extensions. This could take a while...
ERROR: Error installing pg:
ERROR: Failed to build gem native extension.

/usr/local/bin/ruby extconf.rb
ERROR: can't find pg_config.
HINT: Make sure pg_config is in your PATH
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of
necessary libraries and/or headers. Check the mkmf.log file for more
details. You may need configuration options.

But I know that pg_config is in my path:
PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/brad/bin:/home/brad/bin:/home/brad/bin:/usr/local/lib/ruby/1.9.1:/home/brad/bin:/usr/local/lib/ruby/1.9.1:/usr/local/pgsql/bin

it is in the /usr/local/pgsql/bin dir. not sure what i'm missing?

Missing records after import

I ran the conversion against an existing database to see how it performs.

Everything reported OK, but the first table reported exactly 200000 rows in phppgadmin

Counting rows of addresses...
Rows counted
Loading addresses...
220000 of 223453 rows loaded. [ETA: 2011/07/19 19:31 (00h:00m:01s)]
223453 rows loaded in 1min 17s

The max(id) in the original mysql table is 236853, in the pgsql table it is 213041

At a glance this has happened to 2 other tables truncating at 10000 and 20000 rows respectively.

I am going to compare the row counts of every table now to see if this is happening elsewhere. Has anyone else encountered this?

MySQL 5.0.51a in an Ubuntu 9.04 VM
PostgreSQL 8.3.9 in an Ubuntu 9.04 VM

mysql2postgres running on OSX 10.6.8, ruby 1.8.7, pg gem 0.9.0, mysql gem 2.8.1

Minimum supported version of Ruby?

What would/should/shall be the minimum supported Ruby version?

I am running rubocop on the files and it might reduce the support for very old versions of Ruby...

unknown "float(x,y)" and "set('s1','s2',...)"

I got 3 types of errors when running the script on my data. Here are the descriptions:

=========================
1. float(x,y):

error message:
Unknown {:auto_increment=>false, :type=>"float(10,5)", :length=>10, :table_name=>"trubripes_gene_ensembl__homolog_Mdom__dm", :decimals=>"5", :null=>true, :name=>"dn_4014", :primary_key=>false}

mysql:
`dn_4014` float(10,5) DEFAULT NULL,
--------------------------------------
2. set('x',...):

error message:
Unknown {:auto_increment=>false, :type=>"set('SOFA','biosapiens','gosubset_prok','goslim_candida','goslim_generic','goslim_goa','goslim_pir','goslim_plant','goslim_pombe','goslim_yeast')", :length=>40, :table_name=>"acarolinensis_gene_ensembl__go_biological_process__dm", :decimals=>nil, :null=>true, :name=>"subsets_1006", :primary_key=>false}

mysql:
`subsets_1006` set('SOFA','biosapiens','gosubset_prok','goslim_candida','goslim_generic','goslim_goa','goslim_pir','goslim_plant','goslim_pombe','goslim_yeast') DEFAULT NULL,
--------------------------------------
3. set('x',...):

error message:
Unknown {:auto_increment=>false, :type=>"set('ESSENTIAL_SPLICE_SITE','STOP_GAINED','STOP_LOST','COMPLEX_INDEL','FRAMESHIFT_CODING','NON_SYNONYMOUS_CODING','SPLICE_SITE','PARTIAL_CODON','SYNONYMOUS_CODING','REGULATORY_REGION','WITHIN_MATURE_miRNA','5PRIME_UTR','3PRIME_UTR','INTRONIC','NMD_TRANSCRIPT','UPSTREAM','DOWNSTREAM','WITHIN_NON_CODING_GENE','NO_CONSEQUENCE','INTERGENIC')", :length=>4, :table_name=>"btaurus_gene_ensembl__transcript_variation__dm", :decimals=>nil, :null=>true, :name=>"consequence_type_2026", :default=>"INTERGENIC", :primary_key=>false}

mysql:
`consequence_type_2026` set('ESSENTIAL_SPLICE_SITE','STOP_GAINED','STOP_LOST','COMPLEX_INDEL','FRAMESHIFT_CODING','NON_SYNONYMOUS_CODING','SPLICE_SITE','PARTIAL_CODON','SYNONYMOUS_CODING','REGULATORY_REGION','WITHIN_MATURE_miRNA','5PRIME_UTR','3PRIME_UTR','INTRONIC','NMD_TRANSCRIPT','UPSTREAM','DOWNSTREAM','WITHIN_NON_CODING_GENE','NO_CONSEQUENCE','INTERGENIC') DEFAULT 'INTERGENIC',  `
--------------------------------------

(very) quick (and ugly yet working) fix, add lines:

    when "float(10,5)"
      default = " DEFAULT #{column[:default].nil? ? 'NULL' : column[:default].to_f}" if default
      "real"
    when "set('SOFA','biosapiens','gosubset_prok','goslim_candida','goslim_generic','goslim_goa','goslim_pir','goslim_plant','goslim_pombe','goslim_yeast')"
      default = default + "::character varying" if default
      "character varying(#{column[:length]})"      
    when "set('ESSENTIAL_SPLICE_SITE','STOP_GAINED','STOP_LOST','COMPLEX_INDEL','FRAMESHIFT_CODING','NON_SYNONYMOUS_CODING','SPLICE_SITE','PARTIAL_CODON','SYNONYMOUS_CODING','REGULATORY_REGION','WITHIN_MATURE_miRNA','5PRIME_UTR','3PRIME_UTR','INTRONIC','NMD_TRANSCRIPT','UPSTREAM','DOWNSTREAM','WITHIN_NON_CODING_GENE','NO_CONSEQUENCE','INTERGENIC')"
      default = default + "::character varying" if default
      "character varying(#{column[:length]})"

---

Obviously the above quick fix only works for my data, however I guess someone in desperate need of a quick fix could reuse the idea.

Being a complete ignorant in ruby, I will leave it to the experts to come up with a proper fix (with "starts_with" or regex I suppose)!

Odd error in the middle of loading a table

In the middle of loading a table, the script threw this:

Loading mat_event...
520000 of 1351268 rows loaded. [ETA: 2010/09/28 11:08 (00h:01m:52s)]mysql2psql:360:in block in process_row': undefined methodhour' for nil:NilClass (NoMethodError)
from mysql2psql:357:in each' from mysql2psql:357:ineach_with_index'
from mysql2psql:357:in process_row' from mysql2psql:637:inblock in write_contents'
from mysql2psql:241:in block in paginated_read' from mysql2psql:237:inupto'
from mysql2psql:237:in paginated_read' from mysql2psql:635:inwrite_contents'
from mysql2psql:692:in block in convert' from mysql2psql:691:ineach'
from mysql2psql:691:in convert' from mysql2psql:750:in

'

Not sure what to make of it. Line 360 of that script looks pretty straightforward.

Thanks for the help.

running migrations after conversion?

I've converted using file and then importing as well as directly into postgres.

After quickly poking around, I see my tables/columns are present, but running 'rake db:migrate' wants to run every migration in my app.

There are two odd things about this:

1. It's as if the mySQL table schema_migrations didn't get converted to however postgres manages schema state.

2. rake db:migrate -- about half of my migrations run fine. This is incredibly odd my first migration creates the "events" table, but that table already exists -- postgres doesn't blow up on that?

Anyways, I would expect that db:migrate would have to do nothing after converting the DBs -- did I skip something?

char(2) as primary key unsupported

mysql> describe categories;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| category    | char(2)      | NO   | PRI |         |       |
| description | varchar(40)  | NO   |     |         |       |
| image_1     | varchar(255) | YES  |     | NULL    |       |
| image_2     | varchar(255) | YES  |     | NULL    |       |
| image_3     | varchar(255) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+