Segmentation fault when running query against foreign table

kblum007 commented 3 years ago

I'm running PostgreSQL 12.5 on a Centos 7 VM, MariaDB 10.5.8 on same VM.

I have postgresql12-devel installed.
I have the postgresql bin and mariadb bin in the path
I installed (successfully) usi using the instructions on Github today
I created the extension, server, user mapping, and foreign table
I execute this command and postgres crashes: SELECT "DataCenterID" FROM dcim."fac_DataCenter"
The error I get is: server process (PID 32338) was terminated by signal 11: Segmentation fault

Any assistance would be greatly appreciated.

kblum007 commented 3 years ago

Additional information further down in the log: 57P02: terminating connection because of crash of another server process 2020-12-17 13:26:05.917 CST [1020]DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

surajkharage19 commented 3 years ago

Hi @kblum007,

Thanks for reporting this issue.

Can you please share the more details on how the table definition looks like from the PG and MySQL(MariaDB) side? How you configured the mysql_fdw? using source code (latest commit) or using RPMs? As per error mentioned above, server is crashed, can you please share the backstrace by attaching the gdb to core dump.

I tried to reproduce the same issue on Mariadb 10.5.8, but could not able to reproduce. Tried below table DDLs, please correct if I am missing anything.


MariaDB

MariaDB [suraj]> create table fac_DataCenter(DataCenterID int);
Query OK, 0 rows affected (0.008 sec)

MariaDB [suraj]> insert into fac_DataCenter values(19);
Query OK, 1 row affected (0.011 sec)

PG:
postgres=# CREATE FOREIGN TABLE "fac_DataCenter"("DataCenterID" int) server mysql_svr OPTIONS(dbname 'suraj', table_name 'fac_DataCenter');
CREATE FOREIGN TABLE
postgres=# 
postgres=# SELECT "DataCenterID" FROM public."fac_DataCenter";
 DataCenterID 
--------------
           19
(1 row)

kblum007 commented 3 years ago

Here is most of the information. I don’t know how to get “the backstrace by attaching the gdb to core dump”. Can you advise?

I used this version of mysql_fdw: https://github.com/EnterpriseDB/mysql_fdw/archive/REL-2_5_5.tar.gz

Configuration: (host '127.0.0.1', port '3306')

Table on postgres: CREATE FOREIGN TABLE dcim."fac_DataCenter" ( "DataCenterID" INT NOT NULL, "Name" VARCHAR(255) NOT NULL, "SquareFootage" INT DEFAULT NULL, "DeliveryAddress" VARCHAR(255) DEFAULT NULL, "Administrator" VARCHAR(80) DEFAULT NULL, "MaxkW" INT DEFAULT NULL, "DrawingFileName" VARCHAR(55) NOT NULL DEFAULT '', "EntryLogging" BOOLEAN NOT NULL DEFAULT FALSE, "ContainerID" INT NOT NULL DEFAULT 0, "MapX" INT NOT NULL DEFAULT 0, "MapY" INT NOT NULL DEFAULT 0, "U1Position" VARCHAR(7) NOT NULL DEFAULT 'Bottom', "rec_update" TIMESTAMP NOT NULL DEFAULT current_timestamp, "rec_update_by" TEXT NOT NULL DEFAULT user ) SERVER dcim;

Table on MariaDB: CREATE TABLE fac_DataCenter ( DataCenterID int(11) NOT NULL, Name varchar(255) NOT NULL, SquareFootage int(11) DEFAULT NULL, DeliveryAddress varchar(255) DEFAULT NULL, Administrator varchar(80) DEFAULT NULL, MaxkW int(11) DEFAULT NULL, DrawingFileName varchar(55) NOT NULL DEFAULT '', EntryLogging tinyint(1) NOT NULL DEFAULT 0, ContainerID int(11) NOT NULL DEFAULT 0, MapX int(11) NOT NULL DEFAULT 0, MapY int(11) NOT NULL DEFAULT 0, U1Position varchar(7) NOT NULL DEFAULT 'Bottom', rec_update timestamp NOT NULL DEFAULT current_timestamp(), rec_update_by text NOT NULL DEFAULT user(), PRIMARY KEY (DataCenterID), UNIQUE KEY ux_facDataCenter_DataCenterID (DataCenterID), UNIQUE KEY ux_facDataCenter_Name (Name), CONSTRAINT ck_facDataCenter_ContainerID CHECK (ContainerID = 0), CONSTRAINT ck_facDataCenter_U1Position CHECK (U1Position = 'Bottom'), CONSTRAINT ck_facDataCenter_EntryLogging CHECK (EntryLogging = 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

From: surajkharage19 notifications@github.com Sent: Friday, December 18, 2020 3:16 AM To: EnterpriseDB/mysql_fdw mysql_fdw@noreply.github.com Cc: Blum, Kimber kimber7@illinois.edu; Mention mention@noreply.github.com Subject: Re: [EnterpriseDB/mysql_fdw] Segmentation fault when running query against foreign table (#212)

Hi @kblum007https://github.com/kblum007,

Thanks for reporting this issue.

Can you please share the more details on how the table definition looks like from the PG and MySQL(MariaDB) side? How you configured the mysql_fdw? using source code (latest commit) or using RPMs? As per error mentioned above, server is crashed, can you please share the backstrace by attaching the gdb to core dump.

I tried to reproduce the same issue on Mariadb 10.5.8, but could not able to reproduce. Tried below table DDLs, please correct if I am missing anything.

MariaDB

MariaDB [suraj]> create table fac_DataCenter(DataCenterID int);

Query OK, 0 rows affected (0.008 sec)

MariaDB [suraj]> insert into fac_DataCenter values(19);

Query OK, 1 row affected (0.011 sec)

PG:

postgres=# CREATE FOREIGN TABLE "fac_DataCenter"("DataCenterID" int) server mysql_svr OPTIONS(dbname 'suraj', table_name 'fac_DataCenter');

CREATE FOREIGN TABLE

postgres=#

postgres=# SELECT "DataCenterID" FROM public."fac_DataCenter";

DataCenterID

(1 row)

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/EnterpriseDB/mysql_fdw/issues/212#issuecomment-747964382, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AIRACZ6BZNTCX7CCDQE7V4DSVMMWVANCNFSM4VADW2BQ.

kblum007 commented 3 years ago

Correction in my postgres table definition:

The line “SERVER dcim;”

is actually SERVER dcim (dbname ‘dcim’, table_name ‘fac_DataCenter’).

From: Blum, Kimber Sent: Friday, December 18, 2020 9:59 AM To: EnterpriseDB/mysql_fdw reply@reply.github.com; EnterpriseDB/mysql_fdw mysql_fdw@noreply.github.com Cc: Mention mention@noreply.github.com Subject: RE: [EnterpriseDB/mysql_fdw] Segmentation fault when running query against foreign table (#212)

Here is most of the information. I don’t know how to get “the backstrace by attaching the gdb to core dump”. Can you advise?

I used this version of mysql_fdw: https://github.com/EnterpriseDB/mysql_fdw/archive/REL-2_5_5.tar.gz

Configuration: (host '127.0.0.1', port '3306')

Table on postgres: CREATE FOREIGN TABLE dcim."fac_DataCenter" ( "DataCenterID" INT NOT NULL, "Name" VARCHAR(255) NOT NULL, "SquareFootage" INT DEFAULT NULL, "DeliveryAddress" VARCHAR(255) DEFAULT NULL, "Administrator" VARCHAR(80) DEFAULT NULL, "MaxkW" INT DEFAULT NULL, "DrawingFileName" VARCHAR(55) NOT NULL DEFAULT '', "EntryLogging" BOOLEAN NOT NULL DEFAULT FALSE, "ContainerID" INT NOT NULL DEFAULT 0, "MapX" INT NOT NULL DEFAULT 0, "MapY" INT NOT NULL DEFAULT 0, "U1Position" VARCHAR(7) NOT NULL DEFAULT 'Bottom', "rec_update" TIMESTAMP NOT NULL DEFAULT current_timestamp, "rec_update_by" TEXT NOT NULL DEFAULT user ) SERVER dcim;

Table on MariaDB: CREATE TABLE fac_DataCenter ( DataCenterID int(11) NOT NULL, Name varchar(255) NOT NULL, SquareFootage int(11) DEFAULT NULL, DeliveryAddress varchar(255) DEFAULT NULL, Administrator varchar(80) DEFAULT NULL, MaxkW int(11) DEFAULT NULL, DrawingFileName varchar(55) NOT NULL DEFAULT '', EntryLogging tinyint(1) NOT NULL DEFAULT 0, ContainerID int(11) NOT NULL DEFAULT 0, MapX int(11) NOT NULL DEFAULT 0, MapY int(11) NOT NULL DEFAULT 0, U1Position varchar(7) NOT NULL DEFAULT 'Bottom', rec_update timestamp NOT NULL DEFAULT current_timestamp(), rec_update_by text NOT NULL DEFAULT user(), PRIMARY KEY (DataCenterID), UNIQUE KEY ux_facDataCenter_DataCenterID (DataCenterID), UNIQUE KEY ux_facDataCenter_Name (Name), CONSTRAINT ck_facDataCenter_ContainerID CHECK (ContainerID = 0), CONSTRAINT ck_facDataCenter_U1Position CHECK (U1Position = 'Bottom'), CONSTRAINT ck_facDataCenter_EntryLogging CHECK (EntryLogging = 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

From: surajkharage19 notifications@github.com<mailto:notifications@github.com> Sent: Friday, December 18, 2020 3:16 AM To: EnterpriseDB/mysql_fdw mysql_fdw@noreply.github.com<mailto:mysql_fdw@noreply.github.com> Cc: Blum, Kimber kimber7@illinois.edu<mailto:kimber7@illinois.edu>; Mention mention@noreply.github.com<mailto:mention@noreply.github.com> Subject: Re: [EnterpriseDB/mysql_fdw] Segmentation fault when running query against foreign table (#212)

Hi @kblum007https://github.com/kblum007,

Thanks for reporting this issue.

Can you please share the more details on how the table definition looks like from the PG and MySQL(MariaDB) side? How you configured the mysql_fdw? using source code (latest commit) or using RPMs? As per error mentioned above, server is crashed, can you please share the backstrace by attaching the gdb to core dump.

I tried to reproduce the same issue on Mariadb 10.5.8, but could not able to reproduce. Tried below table DDLs, please correct if I am missing anything.

MariaDB

MariaDB [suraj]> create table fac_DataCenter(DataCenterID int);

Query OK, 0 rows affected (0.008 sec)

MariaDB [suraj]> insert into fac_DataCenter values(19);

Query OK, 1 row affected (0.011 sec)

PG:

postgres=# CREATE FOREIGN TABLE "fac_DataCenter"("DataCenterID" int) server mysql_svr OPTIONS(dbname 'suraj', table_name 'fac_DataCenter');

CREATE FOREIGN TABLE

postgres=#

postgres=# SELECT "DataCenterID" FROM public."fac_DataCenter";

DataCenterID

(1 row)

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/EnterpriseDB/mysql_fdw/issues/212#issuecomment-747964382, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AIRACZ6BZNTCX7CCDQE7V4DSVMMWVANCNFSM4VADW2BQ.

kblum007 commented 3 years ago

SERVER dcim OPTIONS (dbname 'dcim', table_name 'fac_DataCenter');

From: Blum, Kimber Sent: Friday, December 18, 2020 10:30 AM To: EnterpriseDB/mysql_fdw reply@reply.github.com; EnterpriseDB/mysql_fdw mysql_fdw@noreply.github.com Cc: Mention mention@noreply.github.com Subject: RE: [EnterpriseDB/mysql_fdw] Segmentation fault when running query against foreign table (#212)

Correction in my postgres table definition:

The line “SERVER dcim;”

is actually SERVER dcim (dbname ‘dcim’, table_name ‘fac_DataCenter’).

From: Blum, Kimber Sent: Friday, December 18, 2020 9:59 AM To: EnterpriseDB/mysql_fdw reply@reply.github.com; EnterpriseDB/mysql_fdw mysql_fdw@noreply.github.com Cc: Mention mention@noreply.github.com Subject: RE: [EnterpriseDB/mysql_fdw] Segmentation fault when running query against foreign table (#212)

Here is most of the information. I don’t know how to get “the backstrace by attaching the gdb to core dump”. Can you advise?

I used this version of mysql_fdw: https://github.com/EnterpriseDB/mysql_fdw/archive/REL-2_5_5.tar.gz

Configuration: (host '127.0.0.1', port '3306')

Table on postgres: CREATE FOREIGN TABLE dcim."fac_DataCenter" ( "DataCenterID" INT NOT NULL, "Name" VARCHAR(255) NOT NULL, "SquareFootage" INT DEFAULT NULL, "DeliveryAddress" VARCHAR(255) DEFAULT NULL, "Administrator" VARCHAR(80) DEFAULT NULL, "MaxkW" INT DEFAULT NULL, "DrawingFileName" VARCHAR(55) NOT NULL DEFAULT '', "EntryLogging" BOOLEAN NOT NULL DEFAULT FALSE, "ContainerID" INT NOT NULL DEFAULT 0, "MapX" INT NOT NULL DEFAULT 0, "MapY" INT NOT NULL DEFAULT 0, "U1Position" VARCHAR(7) NOT NULL DEFAULT 'Bottom', "rec_update" TIMESTAMP NOT NULL DEFAULT current_timestamp, "rec_update_by" TEXT NOT NULL DEFAULT user ) SERVER dcim;

Table on MariaDB: CREATE TABLE fac_DataCenter ( DataCenterID int(11) NOT NULL, Name varchar(255) NOT NULL, SquareFootage int(11) DEFAULT NULL, DeliveryAddress varchar(255) DEFAULT NULL, Administrator varchar(80) DEFAULT NULL, MaxkW int(11) DEFAULT NULL, DrawingFileName varchar(55) NOT NULL DEFAULT '', EntryLogging tinyint(1) NOT NULL DEFAULT 0, ContainerID int(11) NOT NULL DEFAULT 0, MapX int(11) NOT NULL DEFAULT 0, MapY int(11) NOT NULL DEFAULT 0, U1Position varchar(7) NOT NULL DEFAULT 'Bottom', rec_update timestamp NOT NULL DEFAULT current_timestamp(), rec_update_by text NOT NULL DEFAULT user(), PRIMARY KEY (DataCenterID), UNIQUE KEY ux_facDataCenter_DataCenterID (DataCenterID), UNIQUE KEY ux_facDataCenter_Name (Name), CONSTRAINT ck_facDataCenter_ContainerID CHECK (ContainerID = 0), CONSTRAINT ck_facDataCenter_U1Position CHECK (U1Position = 'Bottom'), CONSTRAINT ck_facDataCenter_EntryLogging CHECK (EntryLogging = 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

From: surajkharage19 notifications@github.com<mailto:notifications@github.com> Sent: Friday, December 18, 2020 3:16 AM To: EnterpriseDB/mysql_fdw mysql_fdw@noreply.github.com<mailto:mysql_fdw@noreply.github.com> Cc: Blum, Kimber kimber7@illinois.edu<mailto:kimber7@illinois.edu>; Mention mention@noreply.github.com<mailto:mention@noreply.github.com> Subject: Re: [EnterpriseDB/mysql_fdw] Segmentation fault when running query against foreign table (#212)

Hi @kblum007https://github.com/kblum007,

Thanks for reporting this issue.

Can you please share the more details on how the table definition looks like from the PG and MySQL(MariaDB) side? How you configured the mysql_fdw? using source code (latest commit) or using RPMs? As per error mentioned above, server is crashed, can you please share the backstrace by attaching the gdb to core dump.

I tried to reproduce the same issue on Mariadb 10.5.8, but could not able to reproduce. Tried below table DDLs, please correct if I am missing anything.

MariaDB

MariaDB [suraj]> create table fac_DataCenter(DataCenterID int);

Query OK, 0 rows affected (0.008 sec)

MariaDB [suraj]> insert into fac_DataCenter values(19);

Query OK, 1 row affected (0.011 sec)

PG:

postgres=# CREATE FOREIGN TABLE "fac_DataCenter"("DataCenterID" int) server mysql_svr OPTIONS(dbname 'suraj', table_name 'fac_DataCenter');

CREATE FOREIGN TABLE

postgres=#

postgres=# SELECT "DataCenterID" FROM public."fac_DataCenter";

DataCenterID

(1 row)

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/EnterpriseDB/mysql_fdw/issues/212#issuecomment-747964382, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AIRACZ6BZNTCX7CCDQE7V4DSVMMWVANCNFSM4VADW2BQ.

surajkharage19 commented 3 years ago

Thank you for sharing the additional information, still could not reproduce the issue with the table definitions provided by you. Can you please confirm on below points?

Do you have any data in the above mentioned table? If yes, is it possible for you to share some sample data using which we can reproduce the issue?
Are you seeing this issue (crash) everytime when you execute that query? or it just happened only once?
Are you getting this issue with other tables (or other queries) as well?

Regarding how to generate backtrack from core dump:

If you have enabled the core dump then in case of server crash, postgres creates core dump in data directory. Generally core dump file name looks like: core. under data directory. You can enable core dump by running ulimit -c unlimited before starting the server, or by using the -c option to pg_ctl start.

After enabling the core dump, next time when you execute the query which result in crash, you will see core dump created in postgres data directory. Once we have core dump then we can generate the backstrace from that, but before that make sure that you have debug symbols installed. You can install debug symbols using below command for PG12 (Assuming that you have installed PG12 using YUM):

yum --enablerepo=pgdg12-updates-debuginfo install postgresql12-debuginfo

Once you know the executable path and the core dump file location, just run gdb with those as arguments - gdb -q /path/to/postgres /path/to/core.

e.g: gdb -q -c $PGDATA/core.12345 /usr/pgsql-12/bin/postgres

And then execute "bt" to generate backtrace.

You can follow below link for more details on this: http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD#Getting_a_trace_from_a_randomly_crashing_backend

surajkharage19 commented 2 years ago

Hi @kblum007,

We have not received any further updates on this. If your issue is resolved then can you please close the case from your end?

EnterpriseDB / mysql_fdw

Segmentation fault when running query against foreign table #212