FAQ
I used pg_dump to extract the data from my database with bytea fields
(having pictures) in plain text format. I encoutered some problems to
restore those bytea data to the blobs field in a Mysql database. Any advice?

Search Discussions

  • Raymond O'Donnell at May 13, 2009 at 9:51 pm

    On 13/05/2009 21:56, George Kao wrote:
    I used pg_dump to extract the data from my database with bytea fields
    (having pictures) in plain text format. I encoutered some problems to
    restore those bytea data to the blobs field in a Mysql database. Any advice?
    A bit more detail would be nice.... we're only guessing otherwise.

    - Platform(s)?
    - Version?
    - Commands executed?
    - Error messages?
    - Log entries?
    - ....

    Ray.

    ------------------------------------------------------------------
    Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
    rod@iol.ie
    Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
    ------------------------------------------------------------------
  • George Kao at May 14, 2009 at 1:31 pm
    The platform is WinXP. I have database with fields of bytea in Posgresql
    8.1. The objective was to transfer the entire databasae (schema + data)
    to Mysql 5.1. I used the pg_dump from Posgresql 8.1 to extract the
    schema and data. With some minor touch-up of the sql script generated, I
    am able to run the script with mysql.exe to store the database except
    for the bytea data...


    -----Original Message-----
    From: pgsql-general-owner@postgresql.org
    On Behalf Of Raymond
    O'Donnell
    Sent: Wednesday, May 13, 2009 5:52 PM
    To: George Kao
    Cc: pgsql-general@postgresql.org
    Subject: Re: [GENERAL] how to extract data from bytea so it is be used
    in blob for mysql database
    On 13/05/2009 21:56, George Kao wrote:
    I used pg_dump to extract the data from my database with bytea fields
    (having pictures) in plain text format. I encoutered some problems to
    restore those bytea data to the blobs field in a Mysql database. Any
    advice?

    A bit more detail would be nice.... we're only guessing otherwise.

    - Platform(s)?
    - Version?
    - Commands executed?
    - Error messages?
    - Log entries?
    - ....

    Ray.

    ------------------------------------------------------------------
    Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
    rod@iol.ie Galway Cathedral Recitals:
    http://www.galwaycathedral.org/recitals
    ------------------------------------------------------------------

    --
    Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To
    make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-general
  • Raymond O'Donnell at May 14, 2009 at 6:21 pm

    On 14/05/2009 14:18, George Kao wrote:
    The platform is WinXP. I have database with fields of bytea in Posgresql
    8.1. The objective was to transfer the entire databasae (schema + data)
    to Mysql 5.1. I used the pg_dump from Posgresql 8.1 to extract the
    schema and data. With some minor touch-up of the sql script generated, I
    am able to run the script with mysql.exe to store the database except
    for the bytea data...
    OK, grand - but you still haven't said what problems you're running
    into. We can't really help until you give a bit more detail.

    Have you read up on the bytea type in the docs? When you do a
    text-format dump, the data in bytea columns is dumped in escaped octal,
    so you need to handle this somehow when reloading into MySQL.

    Ray.

    ------------------------------------------------------------------
    Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
    rod@iol.ie
    Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
    ------------------------------------------------------------------
  • George Kao at May 14, 2009 at 7:07 pm
    Thanks for the replying.

    The problem has been narrowed down to restoring a particular table with
    Bytea field to Mysql database.
    The data in escaped octal is not acceptable. Is there a way to dump the
    bytea from posgresql database in hex format? I think it is acceptable by
    mysql.exe.

    George
    -----Original Message-----
    From: Raymond O'Donnell
    Sent: Thursday, May 14, 2009 2:14 PM
    To: George Kao
    Cc: pgsql-general@postgresql.org
    Subject: Re: [GENERAL] how to extract data from bytea so it is be used in
    blob for mysql database
    On 14/05/2009 14:18, George Kao wrote:
    The platform is WinXP. I have database with fields of bytea in
    Posgresql 8.1. The objective was to transfer the entire databasae
    (schema + data) to Mysql 5.1. I used the pg_dump from Posgresql 8.1 to
    extract the schema and data. With some minor touch-up of the sql
    script generated, I am able to run the script with mysql.exe to store
    the database except for the bytea data...
    OK, grand - but you still haven't said what problems you're running into. We
    can't really help until you give a bit more detail.

    Have you read up on the bytea type in the docs? When you do a text-format
    dump, the data in bytea columns is dumped in escaped octal, so you need to
    handle this somehow when reloading into MySQL.

    Ray.

    ------------------------------------------------------------------
    Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie
    Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
    ------------------------------------------------------------------
  • Raymond O'Donnell at May 14, 2009 at 7:21 pm

    On 14/05/2009 20:07, George Kao wrote:
    The data in escaped octal is not acceptable. Is there a way to dump the
    bytea from posgresql database in hex format? I think it is acceptable by
    No, I'm fairly sure that you can't do it directly in the pg_dump output.
    However, you could maybe use the functions here -

    http://www.postgresql.org/docs/8.3/static/functions-binarystring.html

    - to write something that will output what you need.

    Ray.

    ------------------------------------------------------------------
    Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
    rod@iol.ie
    Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
    ------------------------------------------------------------------
  • George Kao at May 14, 2009 at 9:03 pm
    The functions, encode, converts the binary string to HEX representation. It
    does seem to work as mysql's blob accepts the HEX data representation. Any
    program that you are aware of for dumping the data to the SQL script (i.e.
    INSERT INTO ...) while having the option to convert those binary string to
    hex?

    -----Original Message-----
    From: Raymond O'Donnell
    Sent: Thursday, May 14, 2009 3:15 PM
    To: george.kao@group-upc.com
    Cc: pgsql-general@postgresql.org
    Subject: Re: [GENERAL] how to extract data from bytea so it is be used in
    blob for mysql database
    On 14/05/2009 20:07, George Kao wrote:
    The data in escaped octal is not acceptable. Is there a way to dump
    the bytea from posgresql database in hex format? I think it is
    acceptable by
    No, I'm fairly sure that you can't do it directly in the pg_dump output.
    However, you could maybe use the functions here -

    http://www.postgresql.org/docs/8.3/static/functions-binarystring.html

    - to write something that will output what you need.

    Ray.

    ------------------------------------------------------------------
    Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie
    Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
    ------------------------------------------------------------------
  • Allan Kamau at May 14, 2009 at 3:19 pm
    Hi George,
    This is my guess to the cause of the problem (I could be wrong).

    You edited the pg_dump generated sql file to suit to suit the
    requirements of your target server (MySQL), during this edition, your
    editor may have inserted line terminators where it found long sentences,
    this long sentences may have been the contents of your bytea column(s)
    thereby changing the data of the bytea column(s).

    Allan.

    On Thu, May 14, 2009 at 3:18 PM, George Kao wrote:
    The platform is WinXP. I have database with fields of bytea in Posgresql
    8.1. The objective was to transfer the entire databasae (schema + data)
    to Mysql 5.1. I used the pg_dump from Posgresql 8.1 to extract the
    schema and data. With some minor touch-up of the sql script generated, I
    am able to run the script with mysql.exe to store the database except
    for the bytea data...
    >
    >
    -----Original Message-----
    From: pgsql-general-owner@postgresql.org
    On Behalf Of Raymond
    O'Donnell
    Sent: Wednesday, May 13, 2009 5:52 PM
    To: George Kao
    Cc: pgsql-general@postgresql.org
    Subject: Re: [GENERAL] how to extract data from bytea so it is be used
    in blob for mysql database
    >
    On 13/05/2009 21:56, George Kao wrote:
    I used pg_dump to extract the data from my database with bytea fields
    (having pictures) in plain text format. I encoutered some problems to
    restore those bytea data to the blobs field in a Mysql database. Any
    advice? >
    A bit more detail would be nice.... we're only guessing otherwise. >
    - Platform(s)?
    - Version?
    - Commands executed?
    - Error messages?
    - Log entries?
    - .... >
    Ray. >
    ------------------------------------------------------------------
    Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
    rod@iol.ie Galway Cathedral Recitals:
    http://www.galwaycathedral.org/recitals
    ------------------------------------------------------------------ >
    --
    Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To
    make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-general >
    --
    Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-general
    >
  • George Kao at May 14, 2009 at 3:37 pm
    I have tried to pg_dump only one single table with bytea field and the
    result is the same when I come to restore with mysql.

    -----Original Message-----
    From: Allan Kamau
    Sent: Thursday, May 14, 2009 11:15 AM
    To: Postgres-General
    Cc: George Kao
    Subject: Re: [GENERAL] how to extract data from bytea so it is be used in
    blob for mysql database

    Hi George,
    This is my guess to the cause of the problem (I could be wrong).

    You edited the pg_dump generated sql file to suit to suit the requirements
    of your target server (MySQL), during this edition, your editor may have
    inserted line terminators where it found long sentences, this long sentences
    may have been the contents of your bytea column(s) thereby changing the data
    of the bytea column(s).

    Allan.

    On Thu, May 14, 2009 at 3:18 PM, George Kao wrote:
    The platform is WinXP. I have database with fields of bytea in Posgresql
    8.1. The objective was to transfer the entire databasae (schema + data) >
    to Mysql 5.1. I used the pg_dump from Posgresql 8.1 to extract the > schema
    and data. With some minor touch-up of the sql script generated, I > am able
    to run the script with mysql.exe to store the database except > for the
    bytea data...
    >
    >
    -----Original Message-----
    From: pgsql-general-owner@postgresql.org
    On Behalf Of Raymond >
    O'Donnell > Sent: Wednesday, May 13, 2009 5:52 PM > To: George Kao > Cc:
    pgsql-general@postgresql.org > Subject: Re: [GENERAL] how to extract data
    from bytea so it is be used > in blob for mysql database > > On
    13/05/2009 21:56, George Kao wrote:
    I used pg_dump to extract the data from my database with bytea fields
    (having pictures) in plain text format. I encoutered some problems to >>
    restore those bytea data to the blobs field in a Mysql database. Any >
    advice?
    >
    A bit more detail would be nice.... we're only guessing otherwise. >
    - Platform(s)?
    - Version?
    - Commands executed?
    - Error messages?
    - Log entries?
    - .... >
    Ray. >
    ------------------------------------------------------------------
    Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland >
    rod@iol.ie Galway Cathedral Recitals:
    http://www.galwaycathedral.org/recitals
    ------------------------------------------------------------------ >
    --
    Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To >
    make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-general >
    --
    Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To
    make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-general
    >

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedMay 13, '09 at 9:05p
activeMay 14, '09 at 9:03p
posts9
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase