mysql_fdw

mysql_fdw is an open-source plug-in. MogDB is developed and adapted based on the open-source mysql_fdw Release 2.5.3.

To compile and use mysql_fdw, the MariaDB development packages must be included in the environment. Therefore, MogDB does not compile mysql_fdw by default. The following describes how to compile and use mysql_fdw.

Compiling mysql_fdw

To compile mysql_fdw, install the development library and header file of MariaDB. You are advised to use the official MariaDB repositories. For details about how to select a repository, visit http://downloads.mariadb.org/mariadb/repositories/.

After the repository is configured, run the yum install MariaDB-devel MariaDB-shared command to install the related development libraries. In addition, MariaDB-client is a client tool of the MariaDB. You can install it as required to connect to the MariaDB for testing.

After installing the development packages, start mysql_fdw compilation. Add the -enable-mysql-fdw option when running the configure command. Perform compilation using the common MogDB compilation method.

After the compilation is complete, the mysql_fdw.so file is generated in lib/postgresql/ in the installation directory. SQL files and control files related to mysql_fdw are stored in share/postgresql/extension/ in the installation directory.

If the -enable-mysql-fdw option is not added during compilation and installation, compile mysql_fdw again after MogDB is installed, and then manually place the mysql_fdw.so file to lib/postgresql/ in the installation directory, and place mysql_fdw-1.0-1.1.sql, mysql_fdw-1.1.sql, and mysql_fdw.control to share/postgresql/extension/ in the installation directory.

Using mysql_fdw

• To use mysql_fdw, install and connect to MariaDB or MySQL server.
• Load the mysql_fdw extension using CREATE EXTENSION mysql_fdw;.
• Create a server object using CREATE SERVER.
• Create a user mapping using CREATE USER MAPPING.
• Create a foreign table using CREATE FOREIGN TABLE. The structure of the foreign table must be the same as that of the MySQL or MariaDB table. The first column in the table on the MySQL or MariaDB must be unique, for example, PRIMARY KEY and UNIQUE.
• Perform normal operations on the foreign table, such as INSERT, UPDATE, DELETE, SELECT, EXPLAIN, ANALYZE and COPY.
• Drop a foreign table using DROP FOREIGN TABLE.
• Drop a user mapping using DROP USER MAPPING.
• Drop a server object using DROP SERVER.
• Drop an extension using DROP EXTENSION mysql_fdw;.

Common Issues

• When a foreign table is created on the MogDB, the table is not created on the MariaDB or MySQL server. You need to use the MariaDB or MySQL server client to connect to the MariaDB or MySQL server to create a table.
• The MariaDB or MySQL server user used for creating USER MAPPING must have the permission to remotely connect to the MariaDB or MySQL server and perform operations on tables. Before using a foreign table, you can use the MariaDB or MySQL server client on the machine where the MogDB server is located and use the corresponding user name and password to check whether the MariaDB or MySQL server can be successfully connected and operations can be performed.
• The Can't initialize character set SQL_ASCII (path: compiled_in) error occurs when the DML operation is performed on a foreign table. MariaDB does not support the SQL_ASCII encoding format. Currently, this problem can be resolved only by modifying the encoding format of the MogDB database. Change the database encoding format to update pg_database set encoding = pg_char_to_encoding('UTF-8') where datname = 'postgres';. Set datname based on the actual requirements. After the encoding format is changed, start a gsql session again so that mysql_fdw can use the updated parameters. You can also use -locale=LOCALE when running gs_initdb to set the default encoding format to non-SQL_ASCII.

Precautions

• SELECT JOIN between two MySQL foreign tables cannot be pushed down to the MariaDB or MySQL server for execution. Instead, SELECT JOIN is divided into two SQL statements and transferred to the MariaDB or MySQL server for execution. Then the processing result is summarized in the MogDB.
• The IMPORT FOREIGN SCHEMA syntax is not supported.
• CREATE TRIGGER cannot be executed for foreign tables.