mysql_fdw — MySQL foreign data wrapper (FDW) for QHB

This QHB extension implements a foreign data wrapper (FDW) for MySQL.

Please note that this version of mysql_fdw works with QHB 1.5.1 and later.

Features

Common features & enhancements

The following enhancements are added to the latest version of mysql_fdw:

Write-able FDW

The previous version was only read-only, the latest version provides the write capability. The user can now issue an INSERT, UPDATE, and DELETE statements for the foreign tables using the mysql_fdw. It uses the PG type casting mechanism to provide opposite type casting between MySQL and PG data types.

Connection Pooling

The latest version comes with a connection pooler that utilises the same MySQL database connection for all the queries in the same session. The previous version would open a new MySQL database connection for every query. This is a performance enhancement.

Prepared Statement

(Refactoring for SELECT queries to use prepared statement.)

The SELECT queries are now using prepared statements instead of simple query protocol.

Pushdowning

WHERE clause push-down

The latest version will push-down the foreign table WHERE clause to the foreign server. The WHERE condition on the foreign table will be executed on the foreign server hence there will be fewer rows to bring across to QHB. This is a performance feature.

Column push-down

The previous version was fetching all the columns from the target foreign table. The latest version does the column push-down and only brings back the columns that are part of the SELECT target list. This is a performance feature.

JOIN push-down

mysql_fdw now also supports join push-down. The joins between two foreign tables from the same remote MySQL server are pushed to a remote server, instead of fetching all the rows for both the tables and performing a join locally, thereby enhancing the performance. Currently, joins involving only relational and arithmetic operators in join-clauses are pushed down to avoid any potential join failure. Also, only the INNER and LEFT/RIGHT OUTER joins are supported, and not the FULL OUTER, SEMI, and ANTI join. This is a performance feature.

AGGREGATE push-down

mysql_fdw now also supports aggregate push-down. Push aggregates to the remote MySQL server instead of fetching all of the rows and aggregating them locally. This gives a very good performance boost for the cases where aggregates can be pushed down. The push-down is currently limited to aggregate functions min, max, sum, avg, and count, to avoid pushing down the functions that are not present on the MySQL server. Also, aggregate filters and orders are not pushed down.

ORDER BY push-down

mysql_fdw now also supports ORDER BY push-down. If possible, push ORDER BY clause to the remote server so that we get the ordered result set from the foreign server itself. It might help us to have an efficient merge join. NULLs behavior is opposite on the MySQL server. Thus to get an equivalent result, we add the expression IS NULL clause at the beginning of each of the ORDER BY expressions.

LIMIT OFFSET push-down

mysql_fdw now also supports LIMIT OFFSET push-down. Wherever possible, perform LIMIT and OFFSET operations on the remote server. This reduces network traffic between local QHB and remote MySQL servers. ALL/NULL options are not supported on the MySQL server, and thus they are not pushed down. Also, OFFSET without LIMIT is not supported on the MySQL server hence queries having that construct are not pushed.

Installation

MySQL foreign data wrapper for QHB is available as package qhb-1.5.3-mysql-fdw.

Add QHB package repository and install the extension package for chosen platform from the download page.

Note
mysql_fdw was developed on Linux, and should run on any reasonably POSIX-compliant system. Please refer to mysql_fdw documentation.

Note
For the extension operation you need to install MariaDB or MySQL client libraries (MySQL's C client library is needed. This library can be downloaded from the official MySQL website), for example, for CentOS 7 and 8: yum install mariadb-devel.

Usage

You can set the following options for MySQL foreign server object via the CREATE SERVER command:

  • host (string): Optional. Address or hostname of the MySQL server. Default is 127.0.0.1.
  • port (integer): Optional. Port number of the MySQL server. Default is 3306.
  • secure_auth (boolean): Optional. Enable or disable secure authentication. Default is true.
  • init_command (string): Optional. SQL statement to execute when connecting to the MySQL server.
  • use_remote_estimate (boolean): Optional. Controls whether mysql_fdw issues remote EXPLAIN commands to obtain cost estimates. Default is false.
  • reconnect (boolean): Optional. Enable or disable automatic reconnection to the MySQL server if the existing connection is found to have been lost. Default is false.
  • sql_mode (string): Optional. Set MySQL sql_mode for established connection. Default is ANSI_QUOTES.
  • ssl_key (string): Optional. The path name of the client private key file.
  • ssl_cert (string): Optional. The path name of the client public key certificate file.
  • ssl_ca (string): Optional. The path name of the Certificate Authority (CA) certificate file. This option, if used, must specify the same certificate used by the server.
  • ssl_capath (string): Optional. The path name of the directory that contains trusted SSL CA certificate files.
  • ssl_cipher (string): Optional. The list of permissible ciphers for SSL encryption.
  • fetch_size (integer): Optional. This option specifies the number of rows mysql_fdw should get in each fetch operation. It can be specified for a foreign table or a foreign server. The option specified on a table overrides an option specified for the server. Default is 100.
  • character_set (string): Optional. The character set to use for MySQL connection. Default is auto which means autodetect based on the operating system setting.
  • mysql_default_file (string): Optional. Set the MySQL default file path if connection details, such as username, password, etc., need to be picked from the default file.
  • truncatable (boolean): Optional. This option controls whether mysql_fdw allows foreign tables to be truncated using the TRUNCATE command. It can be specified for a foreign table or a foreign server. A table-level option overrides a server-level option. Default is true.

You can set the following options via the CREATE USER MAPPING command when creating a user mapping:

  • username (string): Username to use when connecting to MySQL. No default.
  • password (string): Password to authenticate to the MySQL server with. No default.

You can set the following options for MySQL foreign table object via the CREATE FOREIGN TABLE command:

  • dbname (string): Mandatory. Name of the MySQL database to query.
  • table_name (string): Optional. Name of the MySQL table. Default is name of foreign table
  • max_blob_size (integer): Optional. Max blob size to read without truncation.
  • fetch_size (integer): Optional. Same as fetch_size parameter for foreign server.
  • truncatable (boolean): Optional. The same as foreign server option. Default is true.

You can set the following table-level options via the IMPORT FOREIGN SCHEMA command:

  • import_default (boolean): Optional. This option controls whether column DEFAULT expressions are included in the definitions of foreign tables imported from a foreign server. Default is false.
  • import_not_null (boolean): Optional. This option controls whether column NOT NULL constraints are included in the definitions of foreign tables imported from a foreign server. Default is true.
  • import_enum_as_text (boolean): Optional. This option can be used to map MySQL enum type to text type in the definitions of foreign tables, otherwise emit a warning for type to be created. Default is false.
  • import_generated (boolean): Optional. This option controls whether GENERATED column expressions are included in the definitions of foreign tables imported from a foreign server or not. Default is true. The IMPORT will fail altogether if an imported generated expression uses a function or operator that does not exist on QHB.

TRUNCATE Support

mysql_fdw implements the TRUNCATE API for foreign data wrapper, available since QHB version 1.5.2. MySQL does provide a TRUNCATE command.

Following restrictions apply:

  • TRUNCATE ... CASCADE is not supported
  • TRUNCATE ... RESTART IDENTITY is not supported and ignored
  • TRUNCATE ... CONTINUE IDENTITY is not supported and ignored
  • MySQL tables with foreign key references cannot be truncated

Functions

As well as the standard mysql_fdw_handler() and mysql_fdw_validator() functions, mysql_fdw provides the following user-callable utility functions:

  • mysql_fdw_version(): Returns the version number as an integer.

  • mysql_fdw_display_pushdown_list(): Displays the mysql_fdw_pushdown.config file contents.

Generated Columns

Note that while mysql_fdw will insert or update the generated column value in MySQL, there is nothing to stop the value being modified within MySQL, and hence no guarantee that in subsequent SELECT operations the column will still contain the expected generated value. This limitation also applies to postgres_fdw.

For more details on generated columns see Section Generated Columns and CREATE FOREIGN TABLE reference page.

Examples

-- load extension first time after install
CREATE EXTENSION mysql_fdw;

-- create server object
CREATE SERVER mysql_server
	FOREIGN DATA WRAPPER mysql_fdw
	OPTIONS (host '127.0.0.1', port '3306');

-- Grant usage on foreign server to normal user in QHB
GRANT USAGE ON FOREIGN SERVER mysql_server TO qhbuser;

-- create user mapping
CREATE USER MAPPING FOR qhb
	SERVER mysql_server
	OPTIONS (username 'foo', password 'bar');

-- create foreign table
CREATE FOREIGN TABLE warehouse
	(
		warehouse_id int,
		warehouse_name text,
		warehouse_created timestamp
	)
	SERVER mysql_server
	OPTIONS (dbname 'db', table_name 'warehouse');

-- insert new rows in table
INSERT INTO warehouse values (1, 'UPS', current_date);
INSERT INTO warehouse values (2, 'TV', current_date);
INSERT INTO warehouse values (3, 'Table', current_date);

-- select from table
SELECT * FROM warehouse ORDER BY 1;

warehouse_id | warehouse_name | warehouse_created
-------------+----------------+-------------------
           1 | UPS            | 10-JUL-20 00:00:00
           2 | TV             | 10-JUL-20 00:00:00
           3 | Table          | 10-JUL-20 00:00:00

-- delete row from table
DELETE FROM warehouse where warehouse_id = 3;

-- update a row of table
UPDATE warehouse set warehouse_name = 'UPS_NEW' where warehouse_id = 1;

-- explain a table with verbose option
EXPLAIN VERBOSE SELECT warehouse_id, warehouse_name FROM warehouse WHERE warehouse_name LIKE 'TV' limit 1;

                                   QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Limit  (cost=10.00..11.00 rows=1 width=36)
	Output: warehouse_id, warehouse_name
	->  Foreign Scan on public.warehouse  (cost=10.00..1010.00 rows=1000 width=36)
		Output: warehouse_id, warehouse_name
		Local server startup cost: 10
		Remote query: SELECT `warehouse_id`, `warehouse_name` FROM `db`.`warehouse` WHERE ((`warehouse_name` LIKE BINARY 'TV'))

-- Import a MySQL database as schema to QHB
IMPORT FOREIGN SCHEMA someschema
FROM SERVER mysql_server
INTO public;

License

Copyright (c) 2011-2024, EnterpriseDB Corporation.

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL ENTERPRISEDB CORPORATION BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF ENTERPRISEDB CORPORATION HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

ENTERPRISEDB CORPORATION SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND ENTERPRISEDB CORPORATION HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

See Also

Copyright © KVANTOM LLC, 2019-2026