ERROR: failed to load the mysql query: (null) HINT: Export LD_LIBRARY_PATH to locate the library in PostgreSQL 16 on Rocky Linux 8

[nanren8bang]

Good morning,

I am trying to install and setup mysql_fdw in PsotgreSQL 16 in Rocky Linux 8.

I followed the instruction from https://github.com/EnterpriseDB/mysql_fdw.

Step 1 to Step 5 work fine.

After 5 steps , I checked the necessary files and they are all there after installation , see below:

---

root@# pwd /usr/pgsql-16/share/extension [root]# ll my* -rw-r--r--. 1 root root 156 Feb 27 08:03 mysql_fdw--1.0--1.1.sql -rw-r--r--. 1 root root 722 Feb 27 08:03 mysql_fdw--1.0.sql -rw-r--r--. 1 root root 282 Feb 27 08:03 mysql_fdw--1.1--1.2.sql -rw-r--r--. 1 root root 838 Feb 27 08:03 mysql_fdw--1.1.sql -rw-r--r--. 1 root root 1007 Feb 27 08:03 mysql_fdw--1.2.sql -rw-r--r--. 1 root root 546 Feb 27 08:03 mysql_fdw.control -rw-r--r--. 1 root root 11489 Feb 27 08:03 mysql_fdw_pushdown.config

[root]# pwd /usr/pgsql-16/lib [root@pro-db-6 lib]# ll my* -rwxr-xr-x. 1 root root 683168 Feb 27 08:03 mysql_fdw.so

When I tried to create the mysql extension , I got error as below:

postgres=# create extension mysql_fdw; ERROR: failed to load the mysql query: (null) HINT: Export LD_LIBRARY_PATH to locate the library. postgres=#

I did some search and did not find anything can fix it so far.

Any one experienced this and share some idea to fix it ?

Respectfully

[surajkharage19]

Hi,

Can you please check if any of the workaround is useful for you from this issue - https://github.com/EnterpriseDB/mysql_fdw/issues/69?

[nanren8bang]

Good evening, @surajkharage19 , I found /usr/lib64/mysql/libmysqlclient.so and copy it into /usr/pgsql-16/lib, restarted PostgreSQL-16 , still got the same error.

[nanren8bang]

Good morning,

For error message : "HINT: Export LD_LIBRARY_PATH to locate the library", I checked below file,

[root@]# ll /usr/lib64/mysql total 70096 -rw-r--r--. 1 root root 64059848 Dec 14 11:13 libmysqlclient.a lrwxrwxrwx. 1 root root 20 Dec 14 11:34 libmysqlclient.so -> libmysqlclient.so.23 lrwxrwxrwx. 1 root root 24 Dec 14 11:34 libmysqlclient.so.23 -> libmysqlclient.so.23.0.0 -rwxr-xr-x. 1 root root 7554520 Dec 14 11:45 libmysqlclient.so.23.0.0 -rw-r--r--. 1 root root 148822 Dec 14 11:09 libmysqlservices.a drwxr-xr-x. 2 root root 4096 Feb 26 20:42 plugin drwxr-xr-x. 2 root root 4096 Feb 26 20:42 private

As you can see, file libmysqlclient.so already in directory /usr/lib64/mysql which mentioned by file /etc/ld.so.conf.d/mysql-x86_64.conf. IF PostgreSQL load libs follow file /etc/ld.so.conf.d/mysql-x86_64.conf, and the ONLY ;in needed for mysql_fdw is libmysqlclient.so (I doubt ), error message should not pop up .

I also copy the /usr/lib64/mysql/libmysqlclient.so into /usr/pgsql-16/lib, the same error still pop up.

I guess I did not install enoight libs for mysql_fdw?

I list isntalled rpm as below

[root@]# rpm -qa |grep mysql

mysql-community-libs-8.3.0-1.el8.x86_64 mysql80-community-release-el8-9.noarch mysql-8.0.32-1.module+el8.8.0+1283+4b88a3a8.0.1.x86_64 mysql-connector-c++-jdbc-8.3.0-1.el8.x86_64 mysql-community-common-8.3.0-1.el8.x86_64 mysql-community-client-plugins-8.3.0-1.el8.x86_64 mysql-connector-c++-devel-8.3.0-1.el8.x86_64 mysql-common-8.0.32-1.module+el8.8.0+1283+4b88a3a8.0.1.x86_64 mysql-community-devel-8.3.0-1.el8.x86_64 mysql-connector-c++-8.3.0-1.el8.x86_64 [root@]#

The mysql_fdw installation instruction mentioned "To compile the MySQL foreign data wrapper, MySQL's C client library is needed. This library can be downloaded from the official MySQL website."

But did not list the detailed rpm packages which mysql_fdw needed .

I may missed some rpm packages?

Respectfully

[nanren8bang]

ALso for error message "ERROR: failed to load the mysql query:",

Which mysql query was failed to load ?

Below are all sql file mysql_fdw related after installation ,

root@# pwd /usr/pgsql-16/share/extension [root]# ll my* -rw-r--r--. 1 root root 156 Feb 27 08:03 mysql_fdw--1.0--1.1.sql -rw-r--r--. 1 root root 722 Feb 27 08:03 mysql_fdw--1.0.sql -rw-r--r--. 1 root root 282 Feb 27 08:03 mysql_fdw--1.1--1.2.sql -rw-r--r--. 1 root root 838 Feb 27 08:03 mysql_fdw--1.1.sql -rw-r--r--. 1 root root 1007 Feb 27 08:03 mysql_fdw--1.2.sql -rw-r--r--. 1 root root 546 Feb 27 08:03 mysql_fdw.control -rw-r--r--. 1 root root 11489 Feb 27 08:03 mysql_fdw_pushdown.config

[nanren8bang]

To fix this issue , I removed all mysql rpm package and ONLY need to install mysql_fdw_16. Thanks all for hep to resolve this issue.

[devrimgunduz]

I was about to suggest that. I build the RPMs against mariadb, not closed source mysql.

[surajkharage19]

Glad to hear that your problem is solved. We will go ahead and close this issue.

[aolaog]

i also occur this error message, but i never install mysql packages with rpm command. can you tell me how can i resovle this problem? thanks

[nanren8bang]

What I did in Rocky 8.9 months ago:

1: it need mariadb package ( no need mysql package , at least in my Rocky 8.9 server :-)

2: run command : sudo yum install mysql_fdw_16

3: check if these mariadb packages(around 4 in Rocky Linux 8) installed properly by running : rpm -qa|grep mariadb

4: Try to create MySQL FDW in Postgrsql : create extension mysql_fdw;

5: If step 4 success , all done. Otherwise, try below steps:

, To build on POSIX-compliant systems you need to ensure the pg_config executable is in your path when you run make. This executable is typically in your PostgreSQL installation's bin directory. For example:

[root]# pwd /tmp [root]# git clone https://github.com/EnterpriseDB/mysql_fdw.git

[root]# ll total 16 drwxr-xr-x. 5 root root 4096 Feb 28 10:44 mysql_fdw

[root]# cd mysql_fdw/

[root]$ which pg_config /usr/pgsql-16/bin/pg_config

$ export PATH=/usr/pgsql-16/bin/:$PATH

The mysql_config must also be in the path, it resides in the MySQL bin directory.

We found mysql_config in directory /usr/bin

[root]# which mysql_config /usr/bin/mysql_config

[root]# export PATH=/usr/bin/:$PATH

Also need below two packages:

[root]# yum install clang llvm

Finally install the mysql foreign data wrapper. [root]# ll

drwxr-xr-x. 5 root root 4096 Feb 28 10:44 mysql_fdw

[root]# cd mysql_fdw/

[root@ mysql_fdw]# make USE_PGXS=1

[root@ mysql_fdw]# make USE_PGXS=1 install

[root@ mysql_fdw]#

Running regression test.

[root@ mysql_fdw]# make USE_PGXS=1 installcheck

Last step:

postgres=# create extension mysql_fdw; CREATE EXTENSION postgres=# postgres=# \dew List of foreign-data wrappers Name | Owner | Handler | Validator -----------+----------+-------------------+--------------------- mysql_fdw | postgres | mysql_fdw_handler | mysql_fdw_validator (1 row)

postgres=#

Hope this help :-)