mysql_fwd cannot connect from PG16 to mysql

[fan32111]

Hello, I have a PG13 with mysql_fdw since years successfully running. I started to migrate PG13 to PG16. Now mysql_fdw seems not sending the password anymore

as example. this query works at PG13, but not on PG16: select * from foreign_schema .table;

I got on PG16 from mysql: ERROR: failed to connect to MySQL: Access denied for user 'postgis'@'X.X.X.X' (using password: NO)

Any ideas ?

PG13(debian 11) : postgresql-13-mysql-fdw/oldstable,now 2.5.5-2 PG16 (ubuntu server jammy): postgresql-16-mysql-fdw/jammy-pgdg,now 2.9.1-2.pgdg22.04+1

best regards andi

[surajkharage19]

Hi @fan32111,

Can you please share below details to help you with the issue - 1: How you have upgraded your database from PG13 to PG16? using pg_upgrade or any other method? 2: PG 13 and PG16 connect to the same MySQL server? 3: Can you please share server and user mapping details from PG13 and PG16?

[fan32111]

Hello surajkharage19-

Can you please share below details to help you with the issue - I have PG13 and PG16 in parallel running - PG13 is in still in production. PG16 still in test modus before going in production. My task: I need fetch some tables from mysql - rows from mysql will be georeferenced one time by day (cronjob calls pg/plsql script) and stored as postgis table in separate schema. works fine on PG13 since years. problem is not able to connect to mysql with PG16/Ubuntu server jammy/mysql_fwd 2.9.1 - all software as apt packages. it seems mysql_fdw sends no password on PG16 - that's my assumption.

I answer in between lines:

1: How you have upgraded your database from PG13 to PG16? using pg_upgrade or any other method? 1st) I used pg_dump and restore and 2nd) time I created empty database and installed the database wrapper and user mapping manually - both with same result

2: PG 13 and PG16 connect to the same MySQL server? yes

3: Can you please share server and user mapping details from PG13 and PG16? -- CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'xxxxxxx',port '3306'); -- CREATE USER MAPPING FOR "user@company.domain" SERVER mysql_server OPTIONS (username 'postgis', password 'postgis'); -- GRANT USAGE ON FOREIGN SERVER mysql_server TO "user@company.domain"; -- IMPORT FOREIGN SCHEMA DB_ON_MYSQL FROM SERVER mysql_server into pg_schema;

creates and grants done as db_superuser; import and selects as "user@company.domain" user

best regards - andi

[surajkharage19]

Hi @fan32111,

Thanks for sharing the details. I have tested with PG 16 at my end and test case is working fine.

Can you please check pg_user_mapping catalog to see if the password is associate with user mapping?

Also, just to narrow down the issue, are you able to connect to MySQL server from PG16 server using mysql binary (without FDW)?

Since the hostname is different, make sure that you have created the user with correct hostname and password.

[fan32111]

Hello surajkharage19,

I checked pg_user_mapping in information_schema view. It all right.

And I tested yesterday with mysql binary and get same error:

postgres@ pg16:# mysql -u postgis -h mysqlhost -p Enter password: ERROR 1045 (28000): Access denied for user 'postgis'@'mysqlhost' (using password: NO)

Although I provide a password server says (using password: NO). same error as mysql_fdw throws.

Mysql is a old version, and not under my control ;-) Server Version: 5.1.73-1 - (Debian) Protokoll-Version: 10

best regards

[fan32111]

Hi surajkharage19

I tested further and installed mariadb-client.

With mariedb-client mysql binary I am able to connect and able to execute select statements... I guess the problem is with libmysql and its dependent libraries.

_Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 529656 Server version: 5.1.73-1 (Debian) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL mysql Ver 15.1 Distrib 10.6.18-MariaDB, for debian-linux-gnu (x8664) using EditLine wrapper

[surajkharage19]

Thanks @fan32111 for sharing the details.

From the details shared, it is clear that this is not an issue with mysql_fdw as such as you were not able to connect using mysql binary. Also, with mariadb libs, you able to connect, so you can go ahead with this or else you might need to check with MySQL team to fix the earlier issue. Thank you.

[fan32111]

Thank you for your patience. I will try to compile and link against libmariadb.

I let you know if this will be a success.

[fan32111]

compile and link against mariadb-dev libs works like a charm.

postgresql debian developer should not link against mysqllibs anymore