README.md
1MySQL Foreign Data Wrapper for PostgreSQL
2=========================================
3
4This PostgreSQL extension implements a Foreign Data Wrapper (FDW) for
5[MySQL][1].
6
7Please note that this version of mysql_fdw works with PostgreSQL and EDB
8Postgres Advanced Server 9.6, 10, 11, 12, and 13.
9
10Installation
11------------
12
13To compile the [MySQL][1] foreign data wrapper, MySQL's C client library
14is needed. This library can be downloaded from the official [MySQL
15website][1].
16
171. To build on POSIX-compliant systems you need to ensure the
18 `pg_config` executable is in your path when you run `make`. This
19 executable is typically in your PostgreSQL installation's `bin`
20 directory. For example:
21
22 ```
23 $ export PATH=/usr/local/pgsql/bin/:$PATH
24 ```
25
262. The `mysql_config` must also be in the path, it resides in the MySQL
27 `bin` directory.
28
29 ```
30 $ export PATH=/usr/local/mysql/bin/:$PATH
31 ```
32
333. Compile the code using make.
34
35 ```
36 $ make USE_PGXS=1
37 ```
38
394. Finally install the foreign data wrapper.
40
41 ```
42 $ make USE_PGXS=1 install
43 ```
44
455. Running regression test.
46
47 ```
48 $ make USE_PGXS=1 installcheck
49 ```
50 However, make sure to set the `MYSQL_HOST`, `MYSQL_PORT`, `MYSQL_USER_NAME`,
51 and `MYSQL_PWD` environment variables correctly. The default settings
52 can be found in the `mysql_init.sh` script.
53
54If you run into any issues, please [let us know][2].
55
56
57Enhancements
58------------
59
60The following enhancements are added to the latest version of
61`mysql_fdw`:
62
63### Write-able FDW
64The previous version was only read-only, the latest version provides the
65write capability. The user can now issue an insert, update, and delete
66statements for the foreign tables using the mysql_fdw. It uses the PG
67type casting mechanism to provide opposite type casting between MySQL
68and PG data types.
69
70### Connection Pooling
71The latest version comes with a connection pooler that utilises the same
72MySQL database connection for all the queries in the same session. The
73previous version would open a new MySQL database connection for every
74query. This is a performance enhancement.
75
76### WHERE clause push-down
77The latest version will push-down the foreign table where clause to
78the foreign server. The where condition on the foreign table will be
79executed on the foreign server hence there will be fewer rows to bring
80across to PostgreSQL. This is a performance feature.
81
82### Column push-down
83The previous version was fetching all the columns from the target
84foreign table. The latest version does the column push-down and only
85brings back the columns that are part of the select target list. This is
86a performance feature.
87
88### Prepared Statement
89(Refactoring for `select` queries to use prepared statement)
90
91The `select` queries are now using prepared statements instead of simple
92query protocol.
93
94### JOIN push-down
95mysql_fdw now also supports join push-down. The joins between two
96foreign tables from the same remote MySQL server are pushed to a remote
97server, instead of fetching all the rows for both the tables and
98performing a join locally, thereby enhancing the performance. Currently,
99joins involving only relational and arithmetic operators in join-clauses
100are pushed down to avoid any potential join failure. Also, only the
101INNER and LEFT/RIGHT OUTER joins are supported, and not the FULL OUTER,
102SEMI, and ANTI join. This is a performance feature.
103
104Usage
105-----
106
107The following parameters can be set on a MySQL foreign server object:
108
109 * `host`: Address or hostname of the MySQL server. Defaults to
110 `127.0.0.1`
111 * `port`: Port number of the MySQL server. Defaults to `3306`
112 * `secure_auth`: Enable or disable secure authentication. Default is
113 `true`
114 * `init_command`: SQL statement to execute when connecting to the
115 MySQL server.
116 * `use_remote_estimate`: Controls whether mysql_fdw issues remote
117 EXPLAIN commands to obtain cost estimates. Default is `false`
118 * `ssl_key`: The path name of the client private key file.
119 * `ssl_cert`: The path name of the client public key certificate file.
120 * `ssl_ca`: The path name of the Certificate Authority (CA) certificate
121 file. This option, if used, must specify the same certificate used
122 by the server.
123 * `ssl_capath`: The path name of the directory that contains trusted
124 SSL CA certificate files.
125 * `ssl_cipher`: The list of permissible ciphers for SSL encryption.
126
127The following parameters can be set on a MySQL foreign table object:
128
129 * `dbname`: Name of the MySQL database to query. This is a mandatory
130 option.
131 * `table_name`: Name of the MySQL table, default is the same as
132 foreign table.
133 * `max_blob_size`: Max blob size to read without truncation.
134
135The following parameters need to supplied while creating user mapping.
136
137 * `username`: Username to use when connecting to MySQL.
138 * `password`: Password to authenticate to the MySQL server with.
139
140Examples
141--------
142
143```sql
144-- load extension first time after install
145CREATE EXTENSION mysql_fdw;
146
147-- create server object
148CREATE SERVER mysql_server
149 FOREIGN DATA WRAPPER mysql_fdw
150 OPTIONS (host '127.0.0.1', port '3306');
151
152-- create user mapping
153CREATE USER MAPPING FOR postgres
154 SERVER mysql_server
155 OPTIONS (username 'foo', password 'bar');
156
157-- create foreign table
158CREATE FOREIGN TABLE warehouse
159 (
160 warehouse_id int,
161 warehouse_name text,
162 warehouse_created timestamp
163 )
164 SERVER mysql_server
165 OPTIONS (dbname 'db', table_name 'warehouse');
166
167-- insert new rows in table
168INSERT INTO warehouse values (1, 'UPS', current_date);
169INSERT INTO warehouse values (2, 'TV', current_date);
170INSERT INTO warehouse values (3, 'Table', current_date);
171
172-- select from table
173SELECT * FROM warehouse ORDER BY 1;
174
175warehouse_id | warehouse_name | warehouse_created
176-------------+----------------+-------------------
177 1 | UPS | 10-JUL-20 00:00:00
178 2 | TV | 10-JUL-20 00:00:00
179 3 | Table | 10-JUL-20 00:00:00
180
181-- delete row from table
182DELETE FROM warehouse where warehouse_id = 3;
183
184-- update a row of table
185UPDATE warehouse set warehouse_name = 'UPS_NEW' where warehouse_id = 1;
186
187-- explain a table with verbose option
188EXPLAIN VERBOSE SELECT warehouse_id, warehouse_name FROM warehouse WHERE warehouse_name LIKE 'TV' limit 1;
189
190 QUERY PLAN
191--------------------------------------------------------------------------------------------------------------------
192Limit (cost=10.00..11.00 rows=1 width=36)
193 Output: warehouse_id, warehouse_name
194 -> Foreign Scan on public.warehouse (cost=10.00..1010.00 rows=1000 width=36)
195 Output: warehouse_id, warehouse_name
196 Local server startup cost: 10
197 Remote query: SELECT `warehouse_id`, `warehouse_name` FROM `db`.`warehouse` WHERE ((`warehouse_name` LIKE BINARY 'TV'))
198```
199
200Contributing
201------------
202If you experience any bug and have a fix for that, or have a new idea,
203create a ticket on github page. Before creating a pull request please
204read the [contributing guidelines][3].
205
206Support
207-------
208This project will be modified to maintain compatibility with new
209PostgreSQL and EDB Postgres Advanced Server releases.
210
211If you require commercial support, please contact the EnterpriseDB sales
212team, or check whether your existing PostgreSQL support provider can
213also support mysql_fdw.
214
215
216License
217-------
218Copyright (c) 2011-2021, EnterpriseDB Corporation.
219
220Permission to use, copy, modify, and distribute this software and its
221documentation for any purpose, without fee, and without a written
222agreement is hereby granted, provided that the above copyright notice
223and this paragraph and the following two paragraphs appear in all
224copies.
225
226See the [`LICENSE`][4] file for full details.
227
228[1]: http://www.mysql.com
229[2]: https://github.com/enterprisedb/mysql_fdw/issues/new
230[3]: CONTRIBUTING.md
231[4]: LICENSE
232