maxlapshin / mysql2postgres Goto Github PK
View Code? Open in Web Editor NEWMysqldump, writing in postgresql format
License: MIT License
Mysqldump, writing in postgresql format
License: MIT License
mysql float columns become numberic 8,0 without any warning.
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 `'
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:in
write_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:in
write_indexes'
./../lib/mysql2psql/postgres_db_writer.rb:93:in `each'
If you need more information, please let me know.
Thanks for the great work.
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?
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!
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
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 ...)
Hi,
It looks like postgres_db_writer and postgres_file_writer are doing different things now:
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
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
eval' from /usr/local/rvm/gems/ruby-2.1.2/bin/ruby_executable_hooks:15:in
'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
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;
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:in
inject'
from ./mysql2psql:20:in each' from ./mysql2psql:20:in
inject'
from ./mysql2psql:20
Regards,
Bala
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:in
write_table'
from ./mysql2psql:617:in convert' from ./mysql2psql:616:in
each'
from ./mysql2psql:616:in `convert'
from ./mysql2psql:682
And the process die.
Such as Gemnasium (http://gemnasium.com/) or Versioneye (http://www.versioneye.com/).
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)!
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:in
write_table'
from ./mysql2psql.rb:540:in convert' from ./mysql2psql.rb:539:in
each'
from ./mysql2psql.rb:539:in `convert'
from ./mysql2psql.rb:570
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...
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
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.
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 | |
+-------------+--------------+------+-----+---------+-------+
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"
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?
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
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:
It's as if the mySQL table schema_migrations didn't get converted to however postgres manages schema state.
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?
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.
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:in
tables'
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
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
Is there a way to transfer only the records that are not already in the target table?
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?
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.
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,
content' ./test/integration/convert_to_file_test.rb:48:in
test_basic_numerics_bigint'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
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.
/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>'
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.
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:in
read_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
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:in
inject'
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:in
inject'
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:in
require'
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:in
require'
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
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]
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?
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
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?
Socket configuration option is not being passed to the MySQL connection.
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:in
setup'
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:in
rescue 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
***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;
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.
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;
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.
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 method
hour' for nil:NilClass (NoMethodError)
from mysql2psql:357:in each' from mysql2psql:357:in
each_with_index'
from mysql2psql:357:in process_row' from mysql2psql:637:in
block in write_contents'
from mysql2psql:241:in block in paginated_read' from mysql2psql:237:in
upto'
from mysql2psql:237:in paginated_read' from mysql2psql:635:in
write_contents'
from mysql2psql:692:in block in convert' from mysql2psql:691:in
each'
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.