-1

I am migrating a database from PostgresSql to MySql.

We were saving files in the database as PostgreSQL bytea columns. I wrote a script to export the bytea data and then insert the data into a new MySql database as a blob. The data is inserting into Mysql fine, but it is not working at the application level. However, the application should not care, as the data is exactly the same. I am not sure what is wrong, but I feel like it is some difference between MySql vs. PostgreSQL. Any help would be greatly appreciated.

Improve this question
4
  • 1
    What did you use to export the bytea data and then insert... Did you use psqldump? Commented Apr 15, 2018 at 5:09
  • Yeah, I used psqldump then modified the sql inserts to work for MySql. Commented Apr 15, 2018 at 5:10
  • 1
    This boils down to "it's not working" without explaining what you're doing that isn't working, and what the expected results are. To give us a better understanding of the problem can you give us a minimal example that demonstrates the specific issue you're having? Commented Apr 15, 2018 at 5:53
  • 1
    There is a similar question for MySql blob to PostSql bytea, Convert mysql binary to postgresql bytea. I have the same question, but I am going the other way. I thought I was being pretty specific. I really just want to know the easiest way to convert a PostgresSql bytea column to a MySql blob. Has anyone done it, how did they do it? I don't want to use a third party product as this is the only column I am having trouble converting. Commented Apr 15, 2018 at 6:11

1 Answer 1

Reset to default
0

This could really be a number of issues, but I can provide some tips in regards to converting binary data between sql vendors.

The first thing you need to be aware of is that each sql database vendor uses different escape characters. I suspect that your binary data export is using hex and you most likely have unwanted escape characters when you import to your new database.

I recently had to do this. The exported binary data was in hex and vendor specific escape characters were included.

In your new database, check if the text value of the binary data starts with an 'x' or unusual encoding. If it does you need to get rid of this. Since you already have the data inserting properly, to test, you can just write an sql script to remove any unwanted vendor specific escape characters from each imported binary data record in your new database. Finally, you may need to unhex each each new record.

So, something like this worked for me:

UPDATE my_example_table 
SET my_blob_column = UNHEX(SUBSTRING(my_blob_column, 2, CHAR_LENGTH(my_blob_column)))

Note: The 2 in the SUBSTRING function is because the export script was using hex and prepending '\x' as a vendor specific escape character.

I am not sure that will work for you, but it maybe worth a try.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.