search

EnterpriseDB / mysql_fdw

PostgreSQL foreign data wrapper for MySQL
Other
521 stars 160 forks source link

(Fixed) After a successful PG upgrade, it failed to connect to mysql databases that it can connect. #223

Closed eugeneYWang closed 3 years ago

eugeneYWang commented 3 years ago

Upgraded PG 9.6 to PG 12 on the same VM using pg_upgrade, so the foreign data wrapper's db info and user mappings were moved to the new PG 12 too. When I try to use mysql_fdw to create a foreign table in the new PG 12 DB, It told me that 

ERROR:  failed to connect to MySQL: Unknown MySQL server host '' (-2)
CONTEXT:  SQL statement "import foreign schema etl_data
    limit to (md_loc_delta_ps)
    from SERVER c2f_prod
    into fd_mysql"
PL/pgSQL function eugene.increment_sync_md_location() line 7 at SQL statement
SQL state: HV00N

The above message is copied from pgAdmin4. Before the PG upgrade, we can read this table. In server c2f_prod, we have host value and port value specified. 

CREATE SERVER c2f_prod
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (host '192.168.xx.xx', port '3306');

Not sure why error message indicate '' as unknown mysql server host.

eugeneYWang commented 3 years ago

Tried to delete and recreate the extension and server mappings and user mappings. It still do not work.

eugeneYWang commented 3 years ago

I just got it solved.... so here is the solution:

in our case, we actually also upgraded the ubuntu version from 16.04 to 20.04 but postgresql-12-mysql-fdw is installed before OS upgrade.

So even mysql_fdw is the same version(2.5.5), the OS version of this library is 16.04.

Eventually, what I did to solve the issue is :

add postgres repository to as apt sources first , then install the library again ( actually system info prompt me this is an upgrade)

sudo apt-get install  postgresql-12-mysql-fdw

Then just restart the database.

eugeneYWang commented 3 years ago

Before I tried this solution, I recreated mysql_fdw extension. It helps to make sure the mysql_fdw sql 1.1 file is applied, otherwise it was 1.0 (strange, because before PG upgrade I have applied the sql 1.1 upgrade)

not sure if this is relevant to the solution.

luss commented 3 years ago

Personally, for this kinda upgrade I'd recommend upgrading your pg96 instance to the latest version of MySQL FDW as step one.

1.) Creating (& testing) a new Ubuntu 20.04 VM w/ exisitng versions of pg96 and mysqlfdw as step one.

2.) upgrade to latest mysqlfdw on pg96 on new instance of Ubu20 and testing that it is ok

5.) upgrade to pg12 (or eben pg13) on new instance of Ubu20

Practice it once or twice and then it will go smoothly on switchover day.

--Luss

On Fri, Jul 2, 2021 at 8:55 PM Eugene Wang @.***> wrote:

Before I tried this solution, I recreated mysql_fdw extension. It helps to make sure the mysql_fdw sql 1.1 file is applied, otherwise it was 1.0 (strange, because before PG upgrade I have applied the sql 1.1 upgrade)

not sure if this is relevant to the solution.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/issues/223#issuecomment-873318862, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAMWOHVQN5DG55KMIL7PN73TVZNZHANCNFSM47XSF2CA .