search

dimitri / pgloader

Migrate to PostgreSQL in a single command!
http://pgloader.io
Other
5.33k stars 540 forks source link

Migration from and to Azure Hosted DBs failed #912

Open pandaholic opened 5 years ago

pandaholic commented 5 years ago

Hi,

We are migrating MySQL DB to PostgreSQL DB (both are hosted on Azure). And we receive very strange error:

[root@vm-test home]# pgloader --debug commands.load
pgloader version 3.6.1
compiled with SBCL 1.4.0-1.el7
sb-impl::*default-external-format* :UTF-8
tmpdir: #P"/tmp/pgloader/"
2019-02-19T07:53:14.122000Z NOTICE Starting pgloader, log system is ready.
2019-02-19T07:53:14.153000Z INFO Starting monitor
2019-02-19T07:53:14.181000Z LOG pgloader version "3.6.1"
2019-02-19T07:53:14.936000Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://gitlab@postgresql-gitlab-test@postgresql-gitlab-test.postgres.database.azure.com:5432/test_gitlab {100620E313}>
2019-02-19T07:53:14.936000Z DEBUG SET client_encoding TO 'utf8'
2019-02-19T07:53:14.940000Z DEBUG SET application_name TO 'pgloader'
2019-02-19T07:53:14.980000Z LOG Migrating from #<MYSQL-CONNECTION mysql://gitlab@gitlab-mysql-test@gitlab-mysql-test.mysql.database.azure.com:3306/gitlab_test {100620A6B3}>
2019-02-19T07:53:14.980000Z LOG Migrating into #<PGSQL-CONNECTION pgsql://gitlab@postgresql-gitlab-test@postgresql-gitlab-test.postgres.database.azure.com:5432/test_gitlab {100620E313}>
2019-02-19T07:53:15.176000Z ERROR mysql: Failed to connect to mysql at "gitlab-mysql-test.mysql.database.azure.com" (port 3306) as user "gitlab@gitlab-mysql-test": The value of SEQUENCE is "&zM5YoV:'9{", which is not of type IRONCLAD::SIMPLE-OCTET-VECTOR.
2019-02-19T07:53:15.176000Z LOG report summary reset
       table name     errors       read   imported      bytes      total time       read      write
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
  fetch meta data          0          0          0                     0.000s
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
2019-02-19T07:53:15.260000Z INFO Stopping monitor

.Do you know anything about issues with the migration of db hosted on Azure MySQL server? We tried different versions of pgloader, different OS (CentOS, Ubuntu), tried another database hosted on Azure, but the issue was the same (SEQUENCE value was different every time).

Here is .load file:

LOAD DATABASE

     FROM mysql://gitlab@@gitlab-mysql-test:password@gitlab-mysql-test.mysql.database.azure.com:3306/gitlab_test
     INTO postgresql://gitlab@@postgresql-gitlab-test:password@postgresql-gitlab-test.postgres.database.azure.com:5432/test_gitlab

WITH include no drop, truncate, disable triggers, create no tables,
     create no indexes, preserve index names, no foreign keys,
     data only

ALTER SCHEMA 'gitlab_test' RENAME TO 'public'

;

Thanks in advance!

dimitri commented 5 years ago

Which version of MySQL do you have? It looks like the MySQL driver is failing to read/parse some of the early connection packets from your database server...

pandaholic commented 5 years ago

Which version of MySQL do you have? It looks like the MySQL driver is failing to read/parse some of the early connection packets from your database server...

It's 5.7

Matroxt commented 4 years ago

Hi, I am having the same issue, pgloader can't manage to connect to the Azure Hosted database

I'm running it inside a docker container. I pulled the code from release 3.6.1 and rebuild the image

Here is the load file I'm using:

LOAD DATABASE
    FROM mssql://user:pwd@mydbdatabase.windows.net:1433/mydb
    INTO postgresql://user:pwd@127.0.0.1/my-db

 WITH quote identifiers

 SET work_mem to '12MB', maintenance_work_mem to '128MB', search_path to 'dbo'
 ;

When running with the debug flag, I get

root@b4077409cb70:~# pgloader --debug mig.load 
pgloader version 3.6.1
compiled with SBCL 1.4.16.debian
sb-impl::*default-external-format* :ANSI_X3.4-1968
tmpdir: #P"/tmp/pgloader/"
2019-09-23T18:27:22.003000Z NOTICE Starting pgloader, log system is ready.
2019-09-23T18:27:22.019000Z INFO Starting monitor
2019-09-23T18:27:22.022000Z LOG pgloader version "3.6.1"
2019-09-23T18:27:22.033000Z INFO Parsed command:
LOAD DATABASE
    FROM mssql://user:pwd@mydbdatabase.windows.net:1433/mydb
    INTO postgresql://user:pwd@127.0.0.1/my-db

WITH quote identifiers

SET work_mem to '12MB', maintenance_work_mem to '128MB', search_path to 'dbo'
;

2019-09-23T18:27:22.173000Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION postgresql://user:pwd@127.0.0.1/my-db {1XXXX35DC3}>
2019-09-23T18:27:22.175000Z DEBUG SET client_encoding TO 'utf8'
2019-09-23T18:27:22.209000Z DEBUG SET work_mem TO '12MB'
2019-09-23T18:27:22.252000Z DEBUG SET maintenance_work_mem TO '128MB'
2019-09-23T18:27:22.298000Z DEBUG SET search_path TO dbo
2019-09-23T18:27:22.344000Z DEBUG SET application_name TO 'pgloader'
2019-09-23T18:27:22.493000Z LOG Migrating from #<MSSQL-CONNECTION mssql://user:pwd@mydbdatabase.windows.net:1433/mydb {1XXXX34053}>
2019-09-23T18:27:22.493000Z LOG Migrating into #<PGSQL-CONNECTION postgresql://user:pwd@127.0.0.1/my-db {1XXXX35DC3}>
Max connections reached, increase value of TDS_MAX_CONN
2019-09-23T18:27:22.680000Z ERROR mssql: Failed to connect to mssql at "mydbdatabase.windows.net" (port 1433) as user "user": %dbsqlexec fail
2019-09-23T18:27:22.680000Z LOG report summary reset
       table name     errors       read   imported      bytes      total time       read      write
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
  fetch meta data          0          0          0                     0.000s    
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
2019-09-23T18:27:22.715000Z INFO Stopping monitor

/etc/freetds/freetds.conf:

[global]
        tds version = 7.4
        client charset = UTF-8
yayayahei commented 4 years ago

@pandaholic @Matroxt Hi all, I've meet the same problem. Do you have other tools to migrate mysql on Azure to local postgres? How do you handle migration after then?

nick-solly commented 3 years ago

I'm also facing the same problem connecting to an Azure MySQL DB:

Failed to connect to mysql at "************.mysql.database.azure.com" (port 3306) as user "************": The value of SEQUENCE is "*Mc3UeWj~(I", which is not of type IRONCLAD::SIMPLE-OCTET-VECTOR.
ssung-yugabyte commented 2 years ago

I think this is related to azure db user naming. In azure MySQL, the user name is "user@server_name". so the connection string to azure db becomes "user@ser_name@ip:port/db" As you can see, there are 2 @ in the connection string.

AbhijeetKumar2 commented 1 year ago

@ssung-yugabyte ; what is solution for this..

akonyer commented 1 year ago

I am also wondering if there is a solution for this. Currently I am backing up the database locally and then running the migration - but there has to be a way to connect directly to the Azure SQL Database?

I am getting the same error:

Failed to connect to mssql at "blah" (port 1433) as user "blahuser": %dbsqlexec fail

AftabSaraz commented 1 year ago

Anyone found solution to this?

it is @ character which is creating the problem - escaped with %40 but gives error on that too.

miguelci commented 1 year ago

same problem here...tried a lot of variations, but always getting the error. im able to login to database with intellij, but pgloader always returns the authentication error

JorisMarnasBO commented 6 months ago

Hi, Same probleme for me... I know that this issue is old, but have any of you found a solution to connect azure mariadb service to pgloader ?

pmcgannon22 commented 4 months ago

Any updates on this? Really frustrating. Is this an issue with the username or some form of authentication protocol that pgloader doesn't support? I know you mentioned something similar @dimitri

michivi commented 4 months ago

I don't know if it would help for MariaDB, but for SQL Server, you can escape the @ in the user by duplicating it. So your user@something becomes user@@something@database:port in your configuration file.

tomme87 commented 1 month ago

This is still an issue. Escaping the @ by duplicating it does not work.