F.53. mysql_fdw

F.53.1. Installation
F.53.2. Enhancements
F.53.3. Usage
F.53.4. Examples

This LightDB extension implements a Foreign Data Wrapper (FDW) for MySQL.

F.53.1. Installation

To compile the MySQL foreign data wrapper, MySQL's C client library is needed, and the default version is 8.0.26. This library can be downloaded from the official MySQL website.

The mysql_config must also be in the library path, it resides in the MySQL lib directory.

            $ export LD_LIBRARY_PATH=/usr/lib64/mysql/:$LD_LIBRARY_PATH
        

F.53.2. Enhancements

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

F.53.2.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.

F.53.2.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.

F.53.2.3. 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 LightDB. This is a performance feature.

F.53.2.4. 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.

F.53.2.5. Prepared Statement

(Refactoring for select queries to use prepared statement)

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

F.53.2.6. 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.

F.53.3. Usage

The following parameters can be set on a MySQL foreign server object:

  • host: Address or hostname of the MySQL server. Defaults to 127.0.0.1

  • port: Port number of the MySQL server. Defaults to 3306

  • secure_auth: Enable or disable secure authentication. Default is true

  • init_command: SQL statement to execute when connecting to the MySQL server.

  • use_remote_estimate: Controls whether mysql_fdw issues remote EXPLAIN commands to obtain cost estimates. Default is false

  • reconnect: Enable or disable automatic reconnection to the MySQL server if the existing connection is found to have been lost. Default is false.

  • ssl_key: The path name of the client private key file.

  • ssl_cert: The path name of the client public key certificate file.

  • ssl_ca: 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: The path name of the directory that contains trusted SSL CA certificate files.

  • ssl_cipher: The list of permissible ciphers for SSL encryption.

  • fetch_size: 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. The default is 100.

The following parameters can be set on a MySQL foreign table object:

  • dbname: Name of the MySQL database to query. This is a mandatory option.

  • table_name: Name of the MySQL table, default is the same as foreign table.

  • max_blob_size: Max blob size to read without truncation.

  • fetch_size: Same as fetch_size parameter for foreign server.

The following parameters need to supplied while creating user mapping.

  • username: Username to use when connecting to MySQL.

  • password: Password to authenticate to the MySQL server with.

F.53.4. 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');

            -- create user mapping
            CREATE USER MAPPING FOR LightDB
            	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'))