Initial `IMPORT FOREIGN SCHEMA` support

[jjthiessen]

This provides basic/initial support for IMPORT FOREIGN SCHEMA in PostgreSQL 9.5+

[ahsanhadi]

Thanks. I plan to test this feature next week. On Jun 26, 2015 11:49 PM, "jjthiessen" notifications@github.com wrote:

This provides basic/initial support for IMPORT FOREIGN SCHEMA in

PostgreSQL 9.5+

You can view, comment on, or merge this pull request online at:

https://github.com/EnterpriseDB/mysql_fdw/pull/62 Commit Summary

• Update to reflect changes in PostgreSQL headers (9.5 compatibility)
• Add initial IMPORT FOREIGN SCHEMA support

File Changes

• M mysql_fdw.c https://github.com/EnterpriseDB/mysql_fdw/pull/62/files#diff-0 (293)
• M option.c https://github.com/EnterpriseDB/mysql_fdw/pull/62/files#diff-1 (29)

Patch Links:

• https://github.com/EnterpriseDB/mysql_fdw/pull/62.patch
• https://github.com/EnterpriseDB/mysql_fdw/pull/62.diff

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/pull/62.

[jjthiessen]

Amended commit to use ANSI quoting of string literals.

[jjthiessen]

@ahsanhadi I tried to replicate the crash, but I've been unable to do so thus far. However, there are potentially significant differences between our environments:

• I'm running MySQL 5.5 and I haven't setup a proper build env to test against different versions
• I'm using the current HEAD of PostgreSQL's REL9_5_STABLE git branch (it sounded like you might be using the alpha1 tarball)
• I'm merging my mysql_fdw:import-foreign-schema branch into an ephemeral fork of the official mysql_fdw:master to simulate the merge that would actually happen (I got the impression that you were manually applying patches or cherry-picking based on your earlier comments)).

I did encounter an issue with how I was quoting string literals (that would cause imports to fail (without crashing) depending on execution order). This particular quoting bug should be fixed in my most recent push.

While you'll have to amend the SQL test code and login parameters to match your server, I've used something like the following to bootstrap, build, and test (in reality, I generally use separate scripts for each component and I don't do clean builds each time I test, but I have run the following in my environment (with different SQL matching my MySQL setup), and it executes without the crash that you mention):

#!/bin/bash

# Directory structure setup
BASE=${1:-${HOME}/src/import-foreign-schema-test}
mkdir -p ${BASE} \
    && cd ${BASE} \
    || exit

# PostgreSQL setup
if [[ ! -d "${BASE}/postgres" ]]; then
    git clone https://github.com/postgres/postgres.git ${BASE}/postgres
fi

cd ${BASE}/postgres \
    && git checkout REL9_5_STABLE \
    && git pull \
    || exit

HASH=$(git rev-parse HEAD)
HASHDIR=${BASE}/pg/${HASH}

export PGPORT=9595
export PGDATA=${HASHDIR}/data
export PATH=${HASHDIR}/bin:${PATH}

./configure --prefix=${HASHDIR} \
    && make clean \
    && make \
    && make check install \
    || exit

if [[ ! -d "${PGDATA}" ]]; then
    initdb
fi

pg_ctl -l ${HASHDIR}/pg.log start

# mysql_fdw setup
if [[ ! -d "${BASE}/mysql_fdw" ]]; then
    git clone https://github.com/EnterpriseDB/mysql_fdw.git ${BASE}/mysql_fdw
    git remote add jjthiessen https://github.com/jjthiessen/mysql_fdw.git
fi

cd ${BASE}/mysql_fdw \
    && git checkout master \
    && git pull \
    && (git branch -D ephemeral; git checkout -b ephemeral) \
    && git fetch --all \
    && git merge jjthiessen/import-foreign-schema \
    && (rm *.o *.so; make USE_PGXS=1) \
    && make USE_PGXS=1 install \
    || exit

# mysql_fdw test cases
psql -d postgres <<SQL
\timing

show server_version_num;

drop database if exists mysql_fdw-test;
create database mysql_fdw-test encoding 'LATIN1' lc_collate 'C' lc_ctype 'C' template template0;

\c mysql_fdw-test;

create extension mysql_fdw;
create server mysql-host foreign data wrapper mysql_fdw options (host '***.***.***.***', port '3306');
create user mapping for ******** server mysql-host options (username '********', password '********');

create schema test;

import foreign schema **** from server mysql into test;

create foreign table test.x2 (
     a int,
     b text
) server mysql-host options (dbname '****', table_name 'x');

select * from test.x2 limit 3;
select * from test.x where a > 5;

\det
SQL

# Clean-up
pg_ctl stop

Are you able to run (something like) the above (with the SQL adapted appropriately)? Are you able to give the actual SQL script that you were using for testing (with any sensitive details redacted)? Do you have a scripted build and test process that I could replicate on my end?

[ahsanhadi]

Ibrar has been able to reproduce the crash after applying your patch. The crash is random in nature, the crash occurs because the patch is calling this function directly from the mysql lib "mysql_num_rows". You need to call this function as

    _mysql_stmt_bind_param = dlsym(mysql_dll_handle,

"mysql_stmt_bind_param");

Please see the comments for function mysql_load_library in mysql_fdw.c, it explains why the function from mysql lib needs to called in this way.

Ibrar will commit a fix for this after applying your patch.

On Thu, Jul 9, 2015 at 12:52 AM, jjthiessen notifications@github.com wrote:

@ahsanhadi https://github.com/ahsanhadi I tried to replicate the crash, but I've been unable to do so thus far. However, there are potentially significant differences between our environments:

• I'm running MySQL 5.5 and I haven't setup a proper build env to test against different versions
• I'm using the current HEAD of PostgreSQL's REL9_5_STABLE git branch (it sounded like you might be using the alpha1 tarball http://www.postgresql.org/ftp/source/v9.5alpha1/)
• I'm merging my mysql_fdw:import-foreign-schema branch into an ephemeral fork of the official mysql_fdw:master to simulate the merge that would actually happen (I got the impression that you were manually applying patches or cherry-picking based on your earlier comments)).

I did encounter an issue with how I was quoting string literals (that would cause imports to fail (without crashing) depending on execution order). This particular quoting bug should be fixed in my most recent push.

While you'll have to amend the SQL test code and login parameters to match your server, I've used something like the following to bootstrap, build, and test (in reality, I generally use separate scripts for each component and I don't do clean builds each time I test, but I have run the following in my environment (with different SQL matching my MySQL setup), and it executes without the crash that you mention):

!/bin/bash

Directory structure setup

BASE=${1:-${HOME}/src/import-foreign-schema-test} mkdir -p ${BASE} \ && cd ${BASE} \ || exit

PostgreSQL setup

if [[ ! -d "${BASE}/postgres" ]]; then git clone https://github.com/postgres/postgres.git ${BASE}/postgres fi

cd ${BASE}/postgres \ && git checkout REL9_5_STABLE \ && git pull \ || exit

HASH=$(git rev-parse HEAD) HASHDIR=${BASE}/pg/${HASH}

export PGPORT=9595 export PGDATA=${HASHDIR}/data export PATH=${HASHDIR}/bin:${PATH}

./configure --prefix=${HASHDIR} \ && make clean \ && make \ && make check install \ || exit

if [[ ! -d "${PGDATA}" ]]; then initdb fi

pg_ctl -l ${HASHDIR}/pg.log start

mysql_fdw setup

if [[ ! -d "${BASE}/mysql_fdw" ]]; then git clone https://github.com/EnterpriseDB/mysql_fdw.git ${BASE}/mysql_fdw git remote add jjthiessen https://github.com/jjthiessen/mysql_fdw.git fi

cd ${BASE}/mysql_fdw \ && git checkout master \ && git pull \ && (git branch -D ephemeral; git checkout -b ephemeral) \ && git fetch --all \ && git merge jjthiessen/import-foreign-schema \ && (rm .o .so; make USE_PGXS=1) \ && make USE_PGXS=1 install \ || exit

mysql_fdw test cases

psql -d postgres <<SQL \timing

show server_version_num;

drop database if exists mysql_fdw-test; create database mysql_fdw-test encoding 'LATIN1' lc_collate 'C' lc_ctype 'C' template template0;

\c mysql_fdw-test;

create extension mysql_fdw; create server mysql-host foreign data wrapper mysqlfdw options (host '**._..**', port '3306'); create user mapping for ** server mysql-host options (username '*****', password '***');

create schema test;

import foreign schema **\ from server mysql into test;

create foreign table test.x2 ( a int, b text ) server mysql-host options (dbname '****', table_name 'x');

select * from test.x2 limit 3; select * from test.x where a > 5;

\det SQL

Clean-up

pg_ctl stop

Are you able to run (something like) the above (with the SQL adapted appropriately)? Are you able to give the actual SQL script that you were using for testing (with any sensitive details redacted)? Do you have a scripted build and test process that I could replicate on my end?

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/pull/62#issuecomment-119712883 .

Ahsan Hadi Snr Director Product Development EnterpriseDB Corporation The Enterprise Postgres Company

Phone: +92-51-8358874 Mobile: +92-333-5162114

Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.

[ibrarahmad]

jjthiessen: Please rebase your patch, so I can merge the pull request.

On Thu, Jul 23, 2015 at 8:03 PM, Ahsan Hadi notifications@github.com wrote:

Ibrar has been able to reproduce the crash after applying your patch. The crash is random in nature, the crash occurs because the patch is calling this function directly from the mysql lib "mysql_num_rows". You need to call this function as

_mysql_stmt_bind_param = dlsym(mysql_dll_handle, "mysql_stmt_bind_param");

Please see the comments for function mysql_load_library in mysql_fdw.c, it explains why the function from mysql lib needs to called in this way.

Ibrar will commit a fix for this after applying your patch.

On Thu, Jul 9, 2015 at 12:52 AM, jjthiessen notifications@github.com wrote:

@ahsanhadi https://github.com/ahsanhadi I tried to replicate the crash, but I've been unable to do so thus far. However, there are potentially significant differences between our environments:

• I'm running MySQL 5.5 and I haven't setup a proper build env to test against different versions
• I'm using the current HEAD of PostgreSQL's REL9_5_STABLE git branch (it sounded like you might be using the alpha1 tarball http://www.postgresql.org/ftp/source/v9.5alpha1/)
• I'm merging my mysql_fdw:import-foreign-schema branch into an

ephemeral fork of the official mysql_fdw:master to simulate the merge that would actually happen (I got the impression that you were manually applying patches or cherry-picking based on your earlier comments)).

I did encounter an issue with how I was quoting string literals (that would cause imports to fail (without crashing) depending on execution order). This particular quoting bug should be fixed in my most recent push.

While you'll have to amend the SQL test code and login parameters to match your server, I've used something like the following to bootstrap, build, and test (in reality, I generally use separate scripts for each component and I don't do clean builds each time I test, but I have run the following in my environment (with different SQL matching my MySQL setup), and it executes without the crash that you mention):

!/bin/bash

Directory structure setup

BASE=${1:-${HOME}/src/import-foreign-schema-test} mkdir -p ${BASE} \ && cd ${BASE} \ || exit

PostgreSQL setup

if [[ ! -d "${BASE}/postgres" ]]; then git clone https://github.com/postgres/postgres.git ${BASE}/postgres fi

cd ${BASE}/postgres \ && git checkout REL9_5_STABLE \ && git pull \ || exit

HASH=$(git rev-parse HEAD) HASHDIR=${BASE}/pg/${HASH}

export PGPORT=9595 export PGDATA=${HASHDIR}/data export PATH=${HASHDIR}/bin:${PATH}

./configure --prefix=${HASHDIR} \ && make clean \ && make \ && make check install \ || exit

if [[ ! -d "${PGDATA}" ]]; then initdb fi

pg_ctl -l ${HASHDIR}/pg.log start

mysql_fdw setup

if [[ ! -d "${BASE}/mysql_fdw" ]]; then git clone https://github.com/EnterpriseDB/mysql_fdw.git ${BASE}/mysql_fdw git remote add jjthiessen https://github.com/jjthiessen/mysql_fdw.git fi

cd ${BASE}/mysql_fdw \ && git checkout master \ && git pull \ && (git branch -D ephemeral; git checkout -b ephemeral) \ && git fetch --all \ && git merge jjthiessen/import-foreign-schema \ && (rm .o .so; make USE_PGXS=1) \ && make USE_PGXS=1 install \ || exit

mysql_fdw test cases

psql -d postgres <<SQL \timing

show server_version_num;

drop database if exists mysql_fdw-test; create database mysql_fdw-test encoding 'LATIN1' lc_collate 'C' lc_ctype 'C' template template0;

\c mysql_fdw-test;

create extension mysql_fdw; create server mysql-host foreign data wrapper mysqlfdw options (host '**._..**', port '3306'); create user mapping for ** server mysql-host options (username '*****', password '***');

create schema test;

import foreign schema **\ from server mysql into test;

create foreign table test.x2 ( a int, b text ) server mysql-host options (dbname '****', table_name 'x');

select * from test.x2 limit 3; select * from test.x where a > 5;

\det SQL

Clean-up

pg_ctl stop

Are you able to run (something like) the above (with the SQL adapted appropriately)? Are you able to give the actual SQL script that you were using for testing (with any sensitive details redacted)? Do you have a scripted build and test process that I could replicate on my end?

— Reply to this email directly or view it on GitHub < https://github.com/EnterpriseDB/mysql_fdw/pull/62#issuecomment-119712883> .

Ahsan Hadi Snr Director Product Development EnterpriseDB Corporation The Enterprise Postgres Company

Phone: +92-51-8358874 Mobile: +92-333-5162114

Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/pull/62#issuecomment-124133564 .

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

[jjthiessen]

I've amended and rebased my branch accordingly.

[ahsanhadi]

Can you please generate a pull request for this?

On Fri, Jul 24, 2015 at 5:17 AM, jjthiessen notifications@github.com wrote:

I've amended and rebased my branch accordingly.

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/pull/62#issuecomment-124275213 .

Ahsan Hadi Snr Director Product Development EnterpriseDB Corporation The Enterprise Postgres Company

Phone: +92-51-8358874 Mobile: +92-333-5162114

Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.

[jjthiessen]

This (#62) is a PR for merging jjthiessen/mysql_fdw:import-foreign-schema into EnterpriseDB/mysql_fdw:master. Were you wanting a separate PR to a different branch?

[ahsanhadi]

It looks good to commit now...

On Fri, Jul 24, 2015 at 10:10 PM, jjthiessen notifications@github.com wrote:

This (#62 https://github.com/EnterpriseDB/mysql_fdw/pull/62) is a PR for merging jjthiessen/mysql_fdw:import-foreign-schema into EnterpriseDB/mysql_fdw:master. Were you wanting a separate PR to a different branch?

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/pull/62#issuecomment-124582996 .

Ahsan Hadi Snr Director Product Development EnterpriseDB Corporation The Enterprise Postgres Company

Phone: +92-51-8358874 Mobile: +92-333-5162114

Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.