Some tables and some columns are ignored by mysql_fdw

[lifeboy]

I have a table defined as following:

CREATE TABLE transaction_histories ( id int(11) NOT NULL AUTO_INCREMENT, description varchar(940) DEFAULT NULL, tx_ref varchar(20) DEFAULT NULL, amount int(11) DEFAULT NULL, account_balance bigint(11) DEFAULT NULL, requested_at datetime(6) DEFAULT NULL, compound_transaction_id int(11) DEFAULT NULL, transaction_item_id int(11) DEFAULT NULL, recent_transaction_id int(11) DEFAULT NULL, value_date datetime(6) DEFAULT NULL, report_category varchar(45) DEFAULT NULL, suppress_waiting_transactions tinyint(1) DEFAULT NULL, reversed varchar(45) DEFAULT NULL, created_at datetime(6) DEFAULT NULL, updated_at datetime(6) DEFAULT NULL, deleted_at datetime(6) DEFAULT NULL, account_id int(11) NOT NULL, ge_buffered_transaction_id bigint(20) DEFAULT NULL, mongo_id varchar(45) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY index_transaction_histories_on_ge_buffered_transaction_id (ge_buffered_transaction_id), KEY fk_TransactionHistory_Account1_idx (account_id), KEY idx_mongoid (mongo_id), KEY idx_tx_ref (tx_ref), KEY idx_recent_transaction_id (recent_transaction_id), KEY idx_value_date (value_date), KEY idx_compound_transaction_id (compound_transaction_id), KEY idx_value_date_recent_transaction_id (recent_transaction_id,value_date), KEY idx_account_id_value_date_recent_transaction_id (account_id,value_date,recent_transaction_id), KEY idx_account_id_value_date (account_id,value_date,requested_at), KEY idx_value_date_amount (account_id,value_date,amount,created_at), KEY transaction_histories_deleted_at_IDX (deleted_at) USING BTREE, KEY index_transaction_histories_on_deleted_at (deleted_at) ) ENGINE=InnoDB AUTO_INCREMENT=26890750 DEFAULT CHARSET=latin1`

When I define a foreign table in Postgres10, the ge_buffered_transaction_id column is not linked. If I try to add it manually, I see the error " postgres@imips_reporting ERROR: HV00L: failed to prepare the MySQL query: Unknown column 'ge_buffered_transaction_id' in 'field list'"

A similar thing occurs with the table "transaction_types": "postgres@imips_reporting ERROR: HV00L: failed to prepare the MySQL query: Table 'IMIPS.transaction_types' doesn't exist"

However, both these elements are present.

What now? Has anyone else experienced this?

[lifeboy]

What's the policy on bugs here? Is there someone that evaluates them or are we just here because github allows comments and issues here?

[lifeboy]

This issue is becoming worse. My mysql table has +27 million records, but the foreign table in postgres only has +21 million records, so either something goes wrong (and some records are ignored silently), or there's some kind of timeout, but either way, there's no place anything is recorded. :-(

This is all turning out very disappointing.

[ibrarahmad]

Can you please send your CREATE FOREIGN TABLE statement.

[lifeboy]

This is how I create the environment:

create extension mysql_fdw; CREATE SERVER mysql_imips FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '192.168.12.20',port '3306'); CREATE USER MAPPING FOR PUBLIC SERVER mysql_imips OPTIONS (username 'root',password '**********');

Initially I used:

IMPORT FOREIGN SCHEMA "IMIPS" FROM SERVER mysql_imips INTO public;

But because some tables failed to import, I changed the process:

IMPORT FOREIGN SCHEMA "IMIPS" limit to ("users","accounts","product_purchases","compound_transactions","compound_transaction_items","transaction_histories","transaction_categories","transaction_codes","transaction_sources","transaction_types") FROM SERVER mysql_imips INTO public;