Commands out of sync; you can't run this command now

[richardji7]

I have a PostgreSQL 13 installation on Ubuntu 20 using the PostgreSQL repo and installed mysql_fdw, everything works fine. I then have another PostgreSQL 13 installation which I compiled from source including the mysql_fdw. No issue during make.

I gone through all the same steps of creating server, map user and create foreign table. But when I select from the table I get.

rji_pdap=# select * from dolt_source_types; ERROR: failed to prepare the MySQL query: Commands out of sync; you can't run this command now Time: 0.536 ms

Any help is appreciated.

[jeevanladhe]

Hi Richard,

Thanks for reaching out. IIUC you have two setups of PostgreSQL and mysql_fdw, one works fine and on the other one you are seeing the error reported.

Can you please provide us some more information as requested below to help us understand the issue better:

1. What version of MySQL is being used?
2. Can you please run following command and share the output with us: CHECK TABLE dolt_source_types;
3. Are you getting this issue reproduced consistently?
4. What happens when you run the same query on some other foreign table of the same MySQL server?
5. Just out of curiosity - do remember by mistake, before you started getting this error, you killed some query running on any of the foreign table belonging the same MySQL foreign server?

Regards, Jeevan Ladhe

On Mon, May 31, 2021 at 12:27 AM Richard Ji @.***> wrote:

I have a PostgreSQL 13 installation on Ubuntu 20 using the PostgreSQL repo and installed mysql_fdw, everything works fine. I then have another PostgreSQL 13 installation which I compiled from source including the mysql_fdw. No issue during make.

I gone through all the same steps of creating server, map user and create foreign table. But when I select from the table I get.

rji_pdap=# select * from dolt_source_types; ERROR: failed to prepare the MySQL query: Commands out of sync; you can't run this command now Time: 0.536 ms

Any help is appreciated.

— 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/218, or unsubscribe https://github.com/notifications/unsubscribe-auth/AQKBZ4VEYZK4LMBDL4IC4T3TQKDBTANCNFSM45ZUCOQA .

[richardji7]

1. What version of MySQL is being used? It is actually a DB called Dolt but it is MySQL compatible. The working PostgreSQL and not working PostgreSQL are both talking to the same Dolt server.
2. Can you please run following command and share the output with us: CHECK TABLE dolt_source_types; =# select * from dolt_source_types ; ERROR: failed to execute the MySQL query: Commands out of sync; you can't run this command now Time: 11.847 ms =# check table dolt_source_types; ERROR: syntax error at or near "check" LINE 1: check table dolt_source_types; ^
3. Are you getting this issue reproduced consistently? Yes
4. What happens when you run the same query on some other foreign table of the same MySQL server? Same error.
5. Just out of curiosity - do remember by mistake, before you started getting this error, you killed some query running on any of the foreign table belonging the same MySQL foreign server? No, and I also restarted both PostgreSQL and Dolt.

[jeevanladhe]

It is actually a DB called Dolt but it is MySQL compatible. The working PostgreSQL and not working PostgreSQL are both talking to the same Dolt server.

I don't know much about Dolt, not sure if Dolt is doing any play here.

CHECK TABLE dolt_source_types;

Sorry, I should have been more clear. This command "CHECK TABLE" is to be executed on the MySQL side. Also, you should mention the corresponding name of the table on MySQL side if that is different from foreign table name dolt_source_types.

I would suggest that you use the FDW with MySQL or MariaDB flavor, because that is what we do our testing on. If you observe that the problem is appearing on these also, then we surely have something that we need to look into.

Please let us know if you still get this on MySQL or MariaDB.

Regards, Jeevan Ladhe

On Mon, May 31, 2021 at 11:06 AM Richard Ji @.***> wrote:

1. What version of MySQL is being used? It is actually a DB called Dolt but it is MySQL compatible. The working PostgreSQL and not working PostgreSQL are both talking to the same Dolt server.
2. Can you please run following command and share the output with us: CHECK TABLE dolt_source_types;

select * from dolt_source_types ;

ERROR: failed to execute the MySQL query: Commands out of sync; you can't run this command now Time: 11.847 ms check table dolt_source_types;

ERROR: syntax error at or near "check" LINE 1: check table dolt_source_types; ^ 3. Are you getting this issue reproduced consistently? Yes 4. What happens when you run the same query on some other foreign table of the same MySQL server? Same error. 5. Just out of curiosity - do remember by mistake, before you started getting this error, you killed some query running on any of the foreign table belonging the same MySQL foreign server? No, and I also restarted both PostgreSQL and Dolt.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/issues/218#issuecomment-851191203, or unsubscribe https://github.com/notifications/unsubscribe-auth/AQKBZ4XM4J7W5SYYE33BWXDTQMN4BANCNFSM45ZUCOQA .

[richardji7]

So I tested my compiled PostgreSQL with mysql_fdw against MariaDB 10.5 and it's working fine. So I guess the issue is Dolt specific. Any tips on how to trace mysql_fdw and see what's being called so I might be able to fix it? Thanks.

[jeevanladhe]

On Mon, May 31, 2021 at 11:35 PM Richard Ji @.***> wrote:

So I tested my compiled PostgreSQL with mysql_fdw against MariaDB 10.5 and it's working fine. So I guess the issue is Dolt specific.

Good to know.

Any tips on how to trace mysql_fdw and see what's being called so I might be able to fix it? Thanks.

I am not sure how mysql_fdw can help in that case, but maybe you can compare Dolt server logs with MySQL server logs, and compare what is being sent from mysql_fdw and compare what is different on Dolt.

Regards, Jeevan Ladhe

[richardji7]

This is the complete server log from Dolt for when I try to execute "select * from dolt_source_types;". And if I try to execute another query to Dolt, nothing happens on the Dolt server log, as if that connection is dead. I'd have to quit psql and relogin again. It does look like Dolt got the query and returned 3 rows.

INFO: NewConnection: client 3
INFO: audit trail
TRACE: connection 3: received query SET sql_mode='ANSI_QUOTES'
TRACE: connection 3: executing query
TRACE: connection 3 spooling result row []
TRACE: committing transaction Disabled transaction
TRACE: connection 3 returning result &{[] 1 0  [[]] <nil>}
TRACE: connection 3: received query SET sql_mode='ANSI_QUOTES'
TRACE: connection 3: executing query
TRACE: connection 3 spooling result row []
TRACE: committing transaction Disabled transaction
TRACE: connection 3 returning result &{[] 1 0  [[]] <nil>}
TRACE: connection 3: received query SELECT `id`, `name`, `description` FROM `pdap_datasets`.`source_types`
TRACE: connection 3: executing query
TRACE: connection 3 spooling result row [INT32(1) VARCHAR("court") VARCHAR("A Court is providing this data.")]
TRACE: connection 3 spooling result row [INT32(2) VARCHAR("direct") VARCHAR("A governing body or law enforcement agency is providing data directly.")]
TRACE: connection 3 spooling result row [INT32(3) VARCHAR("third party") VARCHAR("A third party is aggregating data from another type of source.")]
TRACE: committing transaction Disabled transaction

[surajkharage19]

HI @richardji7,

As stated earlier, we don't certify dolt server against mysql_fdw. Can you please check further with MySQL and we are happy to provide assistance for the same. If original issue is resolved, can you please close the case from your end?