Type conversion for MySQL BLOB and LONGBLOB types?

Question

Type conversion for MySQL BLOB and LONGBLOB types?

Closed this issue 9 years ago · 18 comments

I am trying to use the mysql_fdw with several MySQL tables that contain BLOB and LONGBLOB fields. I mapped these fields to BYTEA and TEXT fields in the corresponding Postgres foreign table. Queries against the corresponding FTs run to completion, but it looks like each of the BYTEA and TEXT fields is getting truncated after the first three bytes.

Does the mysql_fdw currently support BLOB and LONGBLOB fields, and if so, what is the correct way of accessing these fields through the corresponding foreign table?

ibrarahmad commented 9 years ago

commited

Answer 1 · 2015-01-20T15:05:34.000Z

Can you please share the ddl that we can use to reproduce this issue?

On Sat, Jan 17, 2015 at 8:54 AM, cwsteinbach notifications@github.com
wrote:

I am trying to use the mysql_fdw with several MySQL tables that contain
BLOB and LONGBLOB fields. I mapped these fields to BYTEA and TEXT fields in
the corresponding Postgres foreign table. Queries against the corresponding
FTs run to completion, but it looks like each of the BYTEA and TEXT fields
is getting truncated after the first three bytes.

Does the mysql_fdw currently support BLOB and LONGBLOB fields, and if so,
what is the correct way of accessing these fields through the corresponding
foreign table?

—
Reply to this email directly or view it on GitHub
#37.

Ahsan Hadi
Snr Director Product Development
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: +92-51-8358874
Mobile: +92-333-5162114

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are
not the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.

Answer 2 · 2015-01-22T19:43:21.000Z

For example, in the source MySQL table, we have a field with type longblob. In the Postgres foreign table, we've used type BYTEA:

CREATE FOREIGN TABLE my_server.my_table (
  ...
  blob_field BYTEA
)
SERVER
    my_server
OPTIONS (
    dbname 'my_db',
    table_name 'my_table'
);

As @cwsteinbach mentioned above, when we query our foreign table my_server.my_table, only the first 3 bytes of the blob_field are returned.

Answer 3 · 2015-01-22T19:48:36.000Z

Hm, I have a MySQL tables with BLOBs in them and they appear normal to me (ie. they contain more than 3 bytes) when I query from PostgreSQL. It doesn't look like I'm doing anything different. PSQL version 9.4beta3 if that matters.

Answer 4 · 2015-01-22T22:23:50.000Z

I think I know what's causing the problem for us. We're gzipping the data before inserting into the MySQL blob field. So we're doing something like this:

String testString = "today is january 22, 2015.";
byte[] data = testString.getBytes("UTF-8");
byte[] gzippedData = gzipBytes(data, 0, data.length);

String sql = "update my_table set blobfield=? where id=?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setObject(1, gzippedData);
ps.setObject(2, myId);
ps.executeUpdate();

When you then try to select from the foreign table in Postgres, you get \x1f8b08 as the result while from the MySQL shell, you get the full binary blob.

If I insert the non-gzipped data (data above), I get all the bytes when querying from Postgres.

Answer 5 · 2015-01-23T20:38:03.000Z

Can you add support for reading gzipped data in blob fields? Any idea why it's currently not working?

Answer 6 · 2015-01-24T00:08:15.000Z

The root cause of this symptom is the function mysql_convert_to_pg() and its use of CStringGetDatum() to copy the field values from the mysql resultset over to postgres's row tuple. This works for BLOBs as long as the value is a null terminated string. Otherwise, the BLOB value is truncated at the first occurrence of '\0'.

We can fix this by adding some code to mysql_convert_to_pg() that sets the length of the BYTEA fields based on the value obtained from the MYSQL_BIND handle. I'm working on a patch that does this and will submit a pull request when I'm satisfied that it works.

Answer 7 · 2015-02-16T13:55:29.000Z

Is there any progress? I also have this problem.

Answer 8 · 2015-02-16T14:06:58.000Z

I believe Stein was going to submit a patch?

On Mon, Feb 16, 2015 at 8:55 AM, Matthias Fuhrmeister <
notifications@github.com> wrote:

Is there any progress? I also have this problem.

—
Reply to this email directly or view it on GitHub
#37 (comment)
.

Ahsan Hadi
Snr Director Product Development
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: +92-51-8358874
Mobile: +92-333-5162114

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are
not the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.

Answer 9 · 2015-02-17T21:31:41.000Z

I created a pull request for the patch: #40

I verified that the patch works, but I still need to do some more cleanup work before it gets committed.

Answer 10 · 2015-02-24T14:52:05.000Z

On Wed, Feb 18, 2015 at 2:31 AM, cwsteinbach notifications@github.com
wrote:

I created a pull request for the patch: #40
#40

The verified that the patch works, but I still need to do some more
cleanup work before it gets committed.

This is great.

We will also review and test before it is committed...

—
Reply to this email directly or view it on GitHub
#37 (comment)
.

Ahsan Hadi
Snr Director Product Development
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: +92-51-8358874
Mobile: +92-333-5162114

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are
not the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.

Answer 11 · 2015-03-04T11:03:08.000Z

Are you planning to submit a final patch that we can review?

Answer 12 · 2015-03-04T18:15:13.000Z

Yes, I plan to get the final version submitted for review by the end of the
week.

On Wed, Mar 4, 2015 at 3:03 AM, Ahsan Hadi notifications@github.com wrote:

Are you planning to submit a final patch that we can review?

—
Reply to this email directly or view it on GitHub
#37 (comment)
.

Answer 13 · 2015-04-17T13:40:15.000Z

Please submit the final version or give us the permission to cleanup the patch and commit it.

Answer 14 · 2015-08-26T06:13:25.000Z

I am facing problem with conversion from mysql binary(8) to postgresql bytea while using mysql_fdw. Please see the problem statement here:
http://stackoverflow.com/questions/32203372/mysql-fdw-input-syntax-error

Please let me whether the issue will be covered in the present "bug" or a new one has to be reported.

Answer 15 · 2015-08-26T16:03:17.000Z

We will get the fix committed for this issue this week.

On Wed, Aug 26, 2015 at 11:13 AM, ddd-malay notifications@github.com
wrote:

I am facing problem with conversion from mysql binary(8) to postgresql
bytea while using mysql_fdw. Please see the problem statement here:
http://stackoverflow.com/questions/32203372/mysql-fdw-input-syntax-error

Please let me whether the issue will be covered in the present "bug" or a
new one has to be reported.

—
Reply to this email directly or view it on GitHub
#37 (comment)
.

Ahsan Hadi
Snr Director Product Development
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: +92-51-8358874
Mobile: +92-333-5162114

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are
not the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.

Answer 16 · 2015-08-29T17:02:30.000Z

Will it be effective for mapping MySQL BINARY to PostgreSQL Bytea?

Answer 17 · 2015-08-29T17:08:12.000Z

On Saturday, August 29, 2015, ddd-malay notifications@github.com wrote:

Will it be effective for mapping MySQL BINARY to PostgreSQL Bytea?

Yes.

—
Reply to this email directly or view it on GitHub
#37 (comment)
.

Ahsan Hadi
Snr Director Product Development
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: +92-51-8358874
Mobile: +92-333-5162114

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are
not the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.