H.4. mysql_fdw — access to MySQL databases #
- H.4.1. Features
- H.4.2. Pushdown
- H.4.3. Supported Platforms
- H.4.4. Installation
- H.4.5. Usage
- H.4.6.
CREATE SERVEROptions- H.4.7.
CREATE USER MAPPINGOptions- H.4.8.
CREATE FOREIGN TABLEOptions- H.4.9.
IMPORT FOREIGN SCHEMAOptions- H.4.10.
TRUNCATESupport- H.4.11. Functions
- H.4.12. Generated Columns
- H.4.13. Examples
- H.4.2. Pushdown
mysql_fdw is a foreign data wrapper (FDW) that connects Postgres Pro to MySQL.
The extension works with Postgres Pro and EDB Postgres Advanced Server 13, 14, 15, 16, 17, and 18.
H.4.1. Features #
H.4.1.1. Common Features #
This section describes available mysql_fdw features.
H.4.1.1.1. Writable FDW #
The user can issue insert, update, and delete statements for the foreign tables using mysql_fdw. It uses the Postgres Pro type casting mechanism to provide bidirectional type casting between MySQL and Postgres Pro data types.
H.4.1.1.2. Connection Pooling #
mysql_fdw includes a connection pooler that utilizes the same MySQL database connection for all queries in the same session. This is a performance enhancement.
H.4.1.1.3. Prepared Statements #
SELECT queries use prepared statements instead of the simple query protocol.
H.4.2. Pushdown #
H.4.2.1. WHERE Clause Pushdown #
mysql_fdw pushes down the WHERE clause of the foreign table to the foreign server. The WHERE clause is then executed on the foreign server, so fewer rows need to be transferred to Postgres Pro. This is a performance enhancement.
H.4.2.2. Column Pushdown #
mysql_fdw performs the column pushdown and only retrieves the columns that are part of the SELECT target list. This is a performance enhancement.
H.4.2.3. JOIN Pushdown #
mysql_fdw supports JOIN pushdown. Joins between two foreign tables from the same remote MySQL server are pushed to a remote server instead of fetching all rows for both tables and joining 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 joins. This is a performance enhancement.
H.4.2.4. AGGREGATE Pushdown #
mysql_fdw supports AGGREGATE pushdown. It pushes aggregates to the remote MySQL server instead of fetching all of the rows and aggregating them locally. This provides a significant performance improvement for the cases where aggregates can be pushed down. The pushdown is currently limited to the aggregate functions min(), max(), sum(), avg(), and count(), to avoid pushing down the functions that are not present on the MySQL server. Aggregate filters and orders are not pushed down either.
H.4.2.5. ORDER BY Pushdown #
mysql_fdw supports ORDER BY pushdown. If possible, the ORDER BY clause is pushed to the remote server to obtain the ordered result set from the foreign server itself. This may enable an efficient merge join. NULL ordering behavior on the MySQL server is the opposite of Postgres Pro. Thus, to obtain an equivalent result, the expression IS NULL clause is added at the beginning of each ORDER BY expression.
H.4.2.6. LIMIT OFFSET Pushdown #
mysql_fdw supports limit offset pushdown. Wherever possible, perform LIMIT and OFFSET operations on the remote server. This reduces network traffic between local Postgres Pro and remote MySQL servers. ALL/NULL options are not supported on the MySQL server, and thus they are not pushed down. OFFSET without LIMIT is not supported on the MySQL server either, so queries with that construct are not pushed down.
H.4.3. Supported Platforms #
mysql_fdw was developed on Linux, and runs on any POSIX-compliant system.
H.4.4. Installation #
The mysql_fdw extension is provided with Postgres Pro as a separate pre-built package mysql-fdw-ent-18 (for the detailed installation instructions, see Chapter 17). mysql_fdw requires the MySQL C client library, which can be downloaded from the official MySQL website.
Once you have Postgres Pro installed, create the mysql_fdw extension:
CREATE EXTENSION mysql_fdw;
That will define the required functions and create a foreign data wrapper.
H.4.5. Usage #
The following sections describe the options accepted by mysql_fdw and provide usage details.
H.4.6. CREATE SERVER Options #
This section describes the options that mysql_fdw accepts via the CREATE SERVER command.
host(string)Address or hostname of the MySQL server.
Default:
127.0.0.1.port(integer)Port number of the MySQL server.
Default:
3306.secure_auth(boolean)Enables or disables secure authentication.
Default:
true.init_command(string)SQL statement to execute when connecting to the MySQL server.
use_remote_estimate(boolean)Controls whether mysql_fdw issues remote
EXPLAINcommands to obtain cost estimates.Default:
false.reconnect(boolean)Enables or disables automatic reconnection to the MySQL server if the existing connection is lost.
Default:
false.sql_mode(string)Sets MySQL
sql_modefor the established connection.Default:
ANSI_QUOTES.ssl_key(string)The path name of the client private key file.
ssl_cert(string)The path name of the client public key certificate file.
ssl_ca(string)The path name of the Certificate Authority (CA) certificate file. This option, if used, must specify the same certificate as used by the server.
ssl_capath(string)The path name of the directory that contains trusted SSL CA certificate files.
ssl_cipher(string)The list of permissible ciphers for SSL encryption.
fetch_size(integer)The number of rows mysql_fdw must retrieve in each fetch operation. It can be specified for a foreign table or a foreign server. A table-level option overrides a server-level option.
Default:
100.character_set(string)The character set to use for the MySQL connection. The default is
auto, which means autodetection based on the operating system setting. Before the introduction of thecharacter_setoption, the character set was set similar to the Postgres Pro database encoding. To restore this behavior, set thecharacter_setoption toPGDatabaseEncoding.Default:
auto.mysql_default_file(string)Sets the MySQL default file path if connection details such as username and password must be read from the default file.
truncatable(boolean)Controls whether mysql_fdw allows foreign tables to be truncated using the
TRUNCATEcommand. It can be specified for a foreign table or a foreign server. A table-level option overrides a server-level option.Default:
true.
H.4.7. CREATE USER MAPPING Options #
This section describes the options that mysql_fdw accepts via the CREATE USER MAPPING command.
username(string)Username for connecting to MySQL.
Required.
password(string)Password for authenticating to the MySQL server.
Required.
H.4.8. CREATE FOREIGN TABLE Options #
This section describes the table-level options that mysql_fdw accepts via the CREATE FOREIGN TABLE command.
dbname(string)Name of the MySQL database to query.
Required.
table_name(string)Name of the MySQL table.
Default: Name of the foreign table.
fetch_size(integer)Same as the
fetch_sizeparameter for the foreign server.max_blob_size(integer)Maximum
BLOBobject size to read without truncation.truncatable(boolean)The same as the foreign server option.
Default:
true.
H.4.9. IMPORT FOREIGN SCHEMA Options #
mysql_fdw supports IMPORT FOREIGN SCHEMA. This section describes the custom options that the extension accepts.
import_default(boolean)Controls whether column
DEFAULTexpressions are included in the definitions of foreign tables imported from a foreign server.Default:
false.import_not_null(boolean)Controls whether column
NOT NULLconstraints are included in the definitions of foreign tables imported from a foreign server.Default:
true.import_enum_as_text(boolean)Can be used to map MySQL
ENUMtype toTEXTtype in the definitions of foreign tables. Otherwise, a warning is returned for the type that cannot be created.Default:
false.import_generated(boolean)Controls whether
GENERATEDcolumn expressions are included in the definitions of foreign tables imported from a foreign server.IMPORTwill fail if an imported generated expression uses a function or operator that does not exist on Postgres Pro.Default:
true.
H.4.10. TRUNCATE Support #
mysql_fdw implements the foreign data wrapper TRUNCATE API, introduced in Postgres Pro 14. MySQL provides a TRUNCATE command. For details, refer to https://dev.mysql.com/doc/refman/9.7/en/truncate-table.html.
The following restrictions apply:
TRUNCATE ... CASCADEis not supportedTRUNCATE ... RESTART IDENTITYis not supported and ignoredTRUNCATE ... CONTINUE IDENTITYis not supported and ignoredMySQL tables with foreign key references cannot be truncated
H.4.11. 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.configfile contents.
H.4.12. Generated Columns #
While mysql_fdw will insert or update the generated column value in MySQL, the value can be modified within MySQL. As a result, there is no guarantee that the subsequent SELECT operations will still find the expected generated value in the column. This limitation also applies to postgres_fdw.
For more details on generated columns, refer to:
H.4.13. Examples #
H.4.13.1. Creating a Foreign Server with Appropriate Configuration #
Once per foreign datasource, as a Postgres Pro superuser:
-- Create server object -- CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '127.0.0.1', port '3306');
H.4.13.2. Granting Usage on Foreign Server to Normal User in Postgres Pro #
Once for a normal user, without superuser permissions, in Postgres Pro, as a Postgres Pro superuser:
GRANT USAGE ON FOREIGN SERVER mysql_server TO pguser;
Where pguser is a sample user for working with the foreign server (and foreign tables).
It is recommended to run commands as superuser only where strictly necessary, so a normal user is allowed to access the foreign server. This is not required for the example to work, but it is a security recommendation.
H.4.13.3. User Mapping #
Create an appropriate user mapping:
-- Create user mapping -- CREATE USER MAPPING FOR pguser SERVER mysql_server OPTIONS (username 'foo', password 'bar');
Where pguser is a sample user for working with the foreign server (and foreign tables).
H.4.13.4. Creating Foreign Table #
All CREATE FOREIGN TABLE SQL commands can be executed as a normal Postgres Pro user if a correct GRANT USAGE ON FOREIGN SERVER is in place. A Postgres Pro superuser is not required for security reasons, though the commands also work with a Postgres Pro superuser.
Specify the table_name option if the MySQL table name is different from the foreign table name:
-- 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');
Additional operations on foreign table data:
-- 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 a table row --
DELETE FROM warehouse where warehouse_id = 3;
-- Update a table row --
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'))
H.4.13.5. Importing a MySQL Database as Schema to Postgres Pro #
IMPORT FOREIGN SCHEMA someschema FROM SERVER mysql_server INTO public;