J.2. mysql_fdw

J.2. mysql_fdw
Prev	Up	Appendix J. Additional External Modules Distributed as Separate Packages	Home	Next

J.2.1. Overview #

Version: 2.9.3

This is a foreign data wrapper (FDW) to connect Tantor SE to MySQL.

J.2.2. Installation #

To install mysql_fdw, download the installation script db_extension_installer.sh and make it executable:

wget https://public.tantorlabs.ru/db_extension_installer.sh && \
chmod +x db_extension_installer.sh

Next, you can install mysql_fdw:

J.2.2.1. Locally from the downloaded package #

Download the mysql_fdw package suitable for your OS from nexus-public.
Run the mysql_fdw installation using db_extension_installer.sh from the downloaded package:
```
./db_extension_installer.sh \
  --from-file=<path to mysql_fdw package>
```

J.2.2.2. Automatically from the package repository #

Set the value of the environment variable NEXUS_URL:
```
export NEXUS_URL="nexus-public.tantorlabs.ru"
```

Run the mysql_fdw installation using db_extension_installer.sh with the following parameters:

./db_extension_installer.sh \
  --database-type=tantor \
  --database-major-version=17 \
  --edition=se \
  --extension=mysql_fdw

J.2.3. Features #

J.2.3.1. Common features and enhancements #

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

J.2.3.1.1. 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.

J.2.3.1.2. 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.

J.2.3.1.3. Prepared Statement #

(Refactoring for select queries to use prepared statement)

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

J.2.3.2. Pushdowning #

J.2.3.2.1. 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 Tantor SE. This is a performance feature.

J.2.3.2.2. 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.

J.2.3.2.3. 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.

J.2.3.2.4. 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.

J.2.3.2.5. 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.

J.2.3.2.6. 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 Tantor SE 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.

J.2.4. Supported platforms #

mysql_fdw was developed on Linux, and should run on any reasonably POSIX-compliant system.

Please refer to mysql_fdw_documentation.

J.2.5. Prerequisites #

To compile the MySQL foreign data wrapper, MySQL’s C client library is needed. This library can be downloaded from the official MySQL website.

J.2.6. Usage #

J.2.6.1. CREATE SERVER options #

mysql_fdw accepts the following options via the CREATE SERVER command:

host as string, optional, default 127.0.0.1
Address or hostname of the MySQL server.
port as integer, optional, default 3306
Port number of the MySQL server.
secure_auth as boolean, optional, default true
Enable or disable secure authentication.
init_command as string, optional, no default
SQL statement to execute when connecting to the MySQL server.
use_remote_estimate as boolean, optional, default false
Controls whether mysql_fdw issues remote EXPLAIN commands to obtain cost estimates.
reconnect as boolean, optional, default false
Enable or disable automatic reconnection to the MySQL server if the existing connection is found to have been lost.
sql_mode as string, optional, default ANSI_QUOTES
Set MySQL sql_mode for established connection.
ssl_key as string, optional, no default
The path name of the client private key file.
ssl_cert as string, optional, no default
The path name of the client public key certificate file.
ssl_ca as string, optional, no default
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 as string, optional, no default
The path name of the directory that contains trusted SSL CA certificate files.
ssl_cipher as string, optional, no default
The list of permissible ciphers for SSL encryption.
fetch_size as integer, optional, default 100
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.
character_set as string, optional, default auto
The character set to use for MySQL connection. Default is auto which means autodetect based on the operating system setting. Before the introduction of the character_set option, the character set was set similar to the Tantor SE database encoding. To get this older behavior set the character_set to special value PGDatabaseEncoding.
mysql_default_file as string, optional, no default
Set the MySQL default file path if connection details, such as username, password, etc., need to be picked from the default file.
truncatable as boolean, optional, default true
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.

J.2.6.2. CREATE USER MAPPING options #

mysql_fdw accepts the following options via the CREATE USER MAPPING command:

username as string, no default
Username to use when connecting to MySQL.
password as string, no default
Password to authenticate to the MySQL server with.

J.2.6.3. CREATE FOREIGN TABLE options #

mysql_fdw accepts the following table-level options via the CREATE FOREIGN TABLE command.

dbname as string, mandatory
Name of the MySQL database to query. This is a mandatory option.
table_name as string, optional, default name of foreign table
Name of the MySQL table.
fetch_size as integer, optional
Same as fetch_size parameter for foreign server.
max_blob_size as integer, optional
Max blob size to read without truncation.
truncatable as boolean, optional, default true
The same as foreign server option.

J.2.6.4. IMPORT FOREIGN SCHEMA options #

mysql_fdw supports IMPORT FOREIGN SCHEMA and accepts the following custom options:

import_default as boolean, optional, default false
This option controls whether column DEFAULT expressions are included in the definitions of foreign tables imported from a foreign server.
import_not_null as boolean, optional, default true
This option controls whether column NOT NULL constraints are included in the definitions of foreign tables imported from a foreign server.
import_enum_as_text as boolean, optional, default false
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.
import_generated as boolean, optional, default true
This option controls whether GENERATED column expressions are included in the definitions of foreign tables imported from a foreign server or not. The IMPORT will fail altogether if an imported generated expression uses a function or operator that does not exist on Tantor SE.

J.2.6.5. TRUNCATE support #

mysql_fdw implements the foreign data wrapper TRUNCATE API, available from Tantor SE 14. MySQL does provide a TRUNCATE command, see TRUNCATE TABLE Statement.

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

J.2.7. 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.

J.2.8. 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:

J.2.9. Examples #

J.2.9.1. Install the extension: #

Once for a database you need, as Tantor SE superuser.

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

J.2.9.2. Create a foreign server with appropriate configuration: #

Once for a foreign datasource you need, as Tantor SE superuser.

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

J.2.9.3. Grant usage on foreign server to normal user in Tantor SE: #

Once for a normal user (non-superuser) in Tantor SE, as Tantor SE superuser. It is a good idea to use a superuser only where really necessary, so let’s allow a normal user to use the foreign server (this is not required for the example to work, but it’s security recommendation).

    GRANT USAGE ON FOREIGN SERVER mysql_server TO pguser;

Where pguser is a sample user for works with foreign server (and foreign tables).

J.2.9.4. User mapping #

Create an appropriate user mapping:

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