Support of MYSQL Json fields

[eMerzh]

Hi,

i was trying to use Mysql_fdw to fetch a table with a field in json:

CREATE TABLE `thing` (
  `id` int,
  `value` json DEFAULT NULL
);

i've declared my FDW using:

CREATE FOREIGN TABLE ft_thing(
  "id" int,
  "value" text
)
SERVER mysql_srv
     OPTIONS (dbname 'db', table_name 'thing');

(i've also tried json or jsonb) but, when i try to select from this table, i got :

=# select * from ft_thing limit 10;
ERROR:  failed to bind the MySQL query:

=#

i'm using latest FDW from apt of postgresql, and postgresql 9.4

Is there a work arround? or am i doing smth wrong?

Thanks

[sam-lc]

I have the same problem. Is any solution?

[acodercat]

me too!

[sabirovruslan]

Hello! I have the same problem. The containers are in the same network, the tables are imported without errors, but as soon as I make a select, an error occurs for tables with a json type field. There are no problems with other tables.

[nickvanw]

The issue here is likely related to the version of MySQL libraries that you compiled this against - I was seeing the same issue using the included default-libmysqlclient-dev(version 1.0.2) in Debian 9, which installs libmariadbclient-dev-compat (10.1.38-0+deb9u1)

Unfortunately, the JSON type wasn't added to MariaDB until 10.2, which means that the client library won't set the right buffer_type when preparing the statement, which creates some sort of mismatch or server error when trying to query.

I was able to get this working by pulling in libmysqlclient20_5.7.26-1debian9_amd64.deb and libmysqlclient-dev_5.7.26-1debian9_amd64.deb from MySQL directly. Compiling master with zero changes made this work fine.

I suspect this should be documented somewhere - I think the library could be more defensive about this in general, but I'm still not totally sure how you get away with sending the same (?) MYSQL_TYPE_STRING with a newer set of headers and have everything work fine. I suspect the newer MySQL client does something smart around this, but given the problem seems fixed, I am not inclined to keep digging. Perhaps, this client should also send MYSQL_TYPE_JSON as the buffer_type when it's defined. I dunno.

The client also doesn't dump the full MySQL error when something bad happens preparing a statement.

That being said, I'm not super familiar with this codebase or the MySQL codebase, so there's likely something I'm missing.

But, yeah, use a newer version of your MySQL headers and this'll probably go away.

[ncnytg]

The issue here is likely related to the version of MySQL libraries that you compiled this against - I was seeing the same issue using the included default-libmysqlclient-dev(version 1.0.2) in Debian 9, which installs libmariadbclient-dev-compat (10.1.38-0+deb9u1)

Unfortunately, the JSON type wasn't added to MariaDB until 10.2, which means that the client library won't set the right buffer_type when preparing the statement, which creates some sort of mismatch or server error when trying to query.

I was able to get this working by pulling in libmysqlclient20_5.7.26-1debian9_amd64.deb and libmysqlclient-dev_5.7.26-1debian9_amd64.deb from MySQL directly. Compiling master with zero changes made this work fine.

I suspect this should be documented somewhere - I think the library could be more defensive about this in general, but I'm still not totally sure how you get away with sending the same (?) MYSQL_TYPE_STRING with a newer set of headers and have everything work fine. I suspect the newer MySQL client does something smart around this, but given the problem seems fixed, I am not inclined to keep digging. Perhaps, this client should also send MYSQL_TYPE_JSON as the buffer_type when it's defined. I dunno.

The client also doesn't dump the full MySQL error when something bad happens preparing a statement.

That being said, I'm not super familiar with this codebase or the MySQL codebase, so there's likely something I'm missing.

But, yeah, use a newer version of your MySQL headers and this'll probably go away.

This helped me 👍

Originally I used the yum package but I realized it wasn't working with tables that had JSON types.

For RHEL I got it working by using mysql-devel from Oracle-MySQL's Repo from here: https://dev.mysql.com/downloads/repo/yum/ and compiling it from the README.

The MariaDB-devel (defaults on mysql-devel without the repo) on RHEL/Centos 7 is mariadb-devel-5.5.64-1.el7.x86_64 and JSON type was created in 5.7.x on Oracle-MySQL. I think MariaDB started supporting JSON type last year.

[MihaiRaduSandu]

Hello @ncnytg,

Can you give me more details about how you managed to solve the problem please? I am not very good with linux, I installed the latest version of postgres 14.4, I installed the latest mysql_fdw 2.8, and it's not working, now I installed the rhel mysql-devel using yum and still nothing. If you can give me any advice I would greatly appreciate it.

Thank you, Mihai