Type conversion for MySQL BLOB and LONGBLOB types?

[enterprisedb]

Type conversion for MySQL BLOB and LONGBLOB types?

[ahsanhadi]

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

On Sat, Jan 17, 2015 at 8:54 AM, cwsteinbach [email protected]
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: [phone]
Mobile: [phone]

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.

[erwa]

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.

[cimmanon]

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.

[erwa]

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.

[erwa]

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

[cwsteinbach]

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.

[mfuhrmeisterDM]

Is there any progress? I also have this problem.

[ahsanhadi]

I believe Stein was going to submit a patch?

On Mon, Feb 16, 2015 at 8:55 AM, Matthias Fuhrmeister <
[email protected]> 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: [phone]
Mobile: [phone]

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.

[cwsteinbach]

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.

[ahsanhadi]

On Wed, Feb 18, 2015 at 2:31 AM, cwsteinbach [email protected]
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: [phone]
Mobile: [phone]

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.

[ahsanhadi]

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

[cwsteinbach]

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 [email protected] 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)
.

[ahsanhadi]

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

[ddd-malay]

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.

[ahsanhadi]

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

On Wed, Aug 26, 2015 at 11:13 AM, ddd-malay [email protected]
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: [phone]
Mobile: [phone]

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.

[ibrarahmad]

commited

[ddd-malay]

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

[ahsanhadi]

On Saturday, August 29, 2015, ddd-malay [email protected] 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: [phone]
Mobile: [phone]

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.