Dumpfile-to-rt-3.0 problem: parser: unterminated quoted string

RT Users
1

Hi,

Now, I’ve coped with the subject encoding problem, I’ve faced another one
that was mentioned on the list several times but no solution still found.

Couldn’t create attachment
$VAR1 = {
‘Subject’ => ‘’,
‘ContentType’ => ‘image/gif’,
‘Filename’ => ‘configuration.gif’,
‘Headers’ => 'Content-Type: image/gif; name=“configuration.gif”
Content-Disposition: inline; filename=“configuration.gif”
Content-Transfer-Encoding: base64
',
‘Creator’ => ‘11’,
‘Parent’ => ‘1299’,
‘Created’ => ‘2002-09-13 14:49:07+00’,
‘ContentEncoding’ => ‘base64’,
‘id’ => ‘1301’,
‘TransactionId’ => ‘1593’
};

ERROR: parser: unterminated quoted string at or near “'GIF89aпїЅпїЅ” at
character 380

It seems that import utility attempts to put decoded from base64 binary
stream (that is GIF image in my case) to the ‘content’ column of the
‘attachments’ table. To do that it creates invalid SQL query including this
binary stream.

Syslog shows the following:

Jul 13 10:52:20 sever RT: DBD::Pg::st execute failed: ERROR: parser:
unterminated quoted string at or near “'GIF89aпїЅ^AпїЅ” at character 380 at
/usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder/Handle.pm line 410.
(/usr/local/rt3/lib/RT.pm:247)
Jul 13 10:52:20 sever RT: RT::Handle=HASH(0x8e0ef90) couldn’t execute the
query ‘INSERT INTO Attachments (Subject, ContentType, Filename, Headers,
Creator, Parent, Created, ContentEncoding, Content, id, TransactionId)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)’ at
/usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder/Handle.pm line 417.
(/usr/local/rt3/lib/RT.pm:247)
Jul 13 10:52:20 sever RT: Died at
/home/barancev/tmp/rt2-to-rt3-1.23/dumpfile-to-rt-3.0 line 752.
(/usr/local/rt3/lib/RT.pm:254)

Regards,
Alexei Barantsev
UniTesK Product Line Manager, ISP RAS
mailto: barancev@ispras.ru
Phone : +7(095)912-5317(ext 4422)

2

Could anybody explain me how is it supposed to put binary attachments to the
DB?

Postgres schema defines the ‘content’ column of the ‘attachments’ table
having ‘text’ type, MySQL schema defines is as ‘longtext’. Both these types
are character ones and don’t allow arbitrary binary data. How is it possible
to put actual attachment content to such fields? Maybe there is a trick
here? Where can I read about it?

Regards,
Alexei Barantsev
UniTesK Product Line Manager, ISP RAS
mailto: barancev@ispras.ru
Phone : +7(095)912-5317(ext 4422)
Web : http://unitesk.com/

3

Hi,

I’ve detected the reason of the problem, but I don’t know how to woraroud
it.
The cause is that the attachment is marked as base64-encoded while IT IS
NOT!
It was base64-encoded in the RT2 database, I checked that up with direct SQL
query.
But serialized version of the ticket in the exported form contains decoded
content of the attachment.
I can’t find decoding in rt-2.0-to-dumpfile script so it must be performed
somewhere in the RT library (probably $att->Content() do that???). So
dumpfile-to-rt-3.0 have to encode it back to base64 to be able to store it
to the database.

In this case the problem must be in this piece of code of the
rt-2.0-to-dumpfile script:

              my $att_param ( sort keys %{ $att->{_AccessibleCache} } )

{
if ($att_param eq ‘Content’) {
$att_ds->{$att_param} = $att->Content();

                     } else {

                            $att_ds->{$att_param} =

$att->_Value($att_param)
if (defined $att->_Value($att_param)
);
}

and actualy there is no need to decode attachment content if it is
base64-encoded?

Regards,
Alexei Barantsev
UniTesK Product Line Manager, ISP RAS
mailto: barancev@ispras.ru
Phone : +7(095)912-5317(ext 4422)
Web : http://unitesk.com/

4

Could anybody explain me how is it supposed to put binary attachments to the
DB?

Postgres schema defines the ‘content’ column of the ‘attachments’ table
having ‘text’ type, MySQL schema defines is as ‘longtext’. Both these types
are character ones and don’t allow arbitrary binary data. How is it possible
to put actual attachment content to such fields? Maybe there is a trick
here? Where can I read about it?

Using the rt2-to-rt3 toolkit to move between mysql and Pg isn’t really
supported. That’s what it looks like you’re doing. The two databases
have different encoding requirements…

5

Using the rt2-to-rt3 toolkit to move between mysql and Pg isn’t really
supported. That’s what it looks like you’re doing. The two databases
have different encoding requirements…

Is that documented somewhere? I ended up not going that route, but
did some testing under the assumption that the conversion was
possible.

Les Mikesell
les@futuresource.com

6

I’ve detected the reason of the problem, but I don’t know how to
woraroud
it.
The cause is that the attachment is marked as base64-encoded while IT IS
NOT!
It was base64-encoded in the RT2 database, I checked that up with direct SQL
query.
But serialized version of the ticket in the exported form contains decoded
content of the attachment.
I can’t find decoding in rt-2.0-to-dumpfile script so it must be performed
somewhere in the RT library (probably $att->Content() do that???). So
dumpfile-to-rt-3.0 have to encode it back to base64 to be able to store it
to the database.

In this case the problem must be in this piece of code of the
rt-2.0-to-dumpfile script:

              my $att_param ( sort keys %{ $att->{_AccessibleCache} } )

{
if ($att_param eq ‘Content’) {
$att_ds->{$att_param} = $att->Content();

                     } else {

                            $att_ds->{$att_param} =

$att->_Value($att_param)
if (defined $att->_Value($att_param)
);
}

and actualy there is no need to decode attachment content if it is
base64-encoded?

Regards,

Alexei Barantsev
I’m having a similar problem moving data from RT2.0.13 to RT3.2.0 using
Oracle as a backend.
The same problem, data in RT2 is base64 encoded in the database, which
is regulated through the BinarySafeBlobs boolean in SearchBuilder. If
set to true it will store binaries in binary else it will base64 encode
them and store them in ASCII.
If you read through Attachment.pm in lib/RT there it will say that it
will decode base64 before returning it to the client app in this case
rt2-to-dumpfile which will store it decoded into the dumpfiles. There it
will also store that it is base64 encoded data which is not correct.
Further when this data is imported using dumpfile-to-rt3 I think
something else goes wrong

  • it should honor either the fact that the encoding is no longer base64
    thus ignoring the explicit encoding-type.
  • or maybe it shouldn’t use Import but Create in which case it may
    correctly figure out that it is binary and needs to be encoded.

Looking at some code it looks like there is something that might be
usable to both of us namely: RT::AlwaysUseBase64.
I’m going to investigate tomorrow if either BLOB’s in Oracle work or if
I can force base64 encoding which it already should do according to
SearchBuilder/Handle/Oracle.pm

Joop
PS:
Sorry for any mistake in logic, its getting late after quite a few
looongg days.

Joop van de Wege JoopvandeWege@mococo.nl

7

somewhere in the RT library (probably $att->Content() do that???). So
dumpfile-to-rt-3.0 have to encode it back to base64 to be able to store it
to the database.

In this case the problem must be in this piece of code of the
rt-2.0-to-dumpfile script:

I’m not sure why we’re doing that. Of course, it was “quickie” code
written over a year ago. But it still should have had a comment for the
special case. I’m sorry :confused:

–
Alexei Barantsev
I’m having a similar problem moving data from RT2.0.13 to RT3.2.0 using
Oracle as a backend.
The same problem, data in RT2 is base64 encoded in the database, which
is regulated through the BinarySafeBlobs boolean in SearchBuilder. If
set to true it will store binaries in binary else it will base64 encode
them and store them in ASCII.
If you read through Attachment.pm in lib/RT there it will say that it
will decode base64 before returning it to the client app in this case
rt2-to-dumpfile which will store it decoded into the dumpfiles. There it
will also store that it is base64 encoded data which is not correct.

Right. that was the special casing in the dumper code. Which you might
want to try disabling.

Further when this data is imported using dumpfile-to-rt3 I think
something else goes wrong

  • it should honor either the fact that the encoding is no longer base64
    thus ignoring the explicit encoding-type.
  • or maybe it shouldn’t use Import but Create in which case it may
    correctly figure out that it is binary and needs to be encoded.

It may be that you want to patch Import to be more sensible abouut the
data it gets.

Looking at some code it looks like there is something that might be
usable to both of us namely: RT::AlwaysUseBase64.

I’m not convinced that that will do the right thing for you.

I’m going to investigate tomorrow if either BLOB’s in Oracle work or if
I can force base64 encoding which it already should do according to
SearchBuilder/Handle/Oracle.pm

We sepnt a bunch of time with some oracle consultants, who were pretty
sure that switching to blobs would lose us critical functionality. It
may have been searching related.
Jesse

8

-----Original Message-----
From: Jesse Vincent [mailto:jesse@bestpractical.com]
Sent: Tuesday, July 13, 2004 7:06 PM
To: Alexei Barantsev
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] dumpfile-to-rt-3.0 problem: parser:
unterminated quoted string

Could anybody explain me how is it supposed to put binary
attachments
to the DB?

Postgres schema defines the ‘content’ column of the ‘attachments’
table having ‘text’ type, MySQL schema defines is as
‘longtext’. Both
these types are character ones and don’t allow arbitrary
binary data.
How is it possible to put actual attachment content to
such fields?
Maybe there is a trick here? Where can I read about it?

Using the rt2-to-rt3 toolkit to move between mysql and Pg
isn’t really supported. That’s what it looks like you’re
doing. The two databases have different encoding requirements…

No, I’m moving from Postgres to Postgres. I just mentioned MySQL to show
that both these database scemas don’t allow for storing binary data to the
‘content’ field.

Alexei Barantsev
UniTesK Product Line Manager, ISP RAS
mailto: barancev@ispras.ru
Phone : +7(095)912-5317(ext 4422)
Web : http://unitesk.com/

9

Hi,

I’ve detected the reason of the problem, but I don’t know how to woraroud
it.
The cause is that the attachment is marked as base64-encoded while IT IS
NOT!
It was base64-encoded in the RT2 database, I checked that up with direct SQL
query.
But serialized version of the ticket in the exported form contains decoded
content of the attachment.
I can’t find decoding in rt-2.0-to-dumpfile script so it must be performed
somewhere in the RT library (probably $att->Content() do that???). So
dumpfile-to-rt-3.0 have to encode it back to base64 to be able to store it
to the database.

In this case the problem must be in this piece of code of the
rt-2.0-to-dumpfile script:

              my $att_param ( sort keys %{ $att->{_AccessibleCache} } )

{
if ($att_param eq ‘Content’) {
$att_ds->{$att_param} = $att->Content();

                     } else {

                            $att_ds->{$att_param} =

$att->_Value($att_param)
if (defined $att->_Value($att_param)
);
}

and actualy there is no need to decode attachment content if it is
base64-encoded?
You’re right about that and the solution is simple. Just change the
following line:
if ($att_param eq ‘Content’) {
$att_ds->{$att_param} = $att->Content();
into
if ($att_param eq ‘Content’) {
$att_ds->{$att_param} = $att->_Value($att_param);

Or just dump the whole if construction since it is of no use anymore.
What happens is that the export is done using the base64 encoded data
and not the binary data together with the header ContentType set to
base64. The import tool is much harder to convince todo something
special thats why I use this patch. The import tool just import the data
encoded together with the correct encoding type and puts it all nicely
into the database. Viewing the tickets this way and clicking on the
download links gives me nicely the binary data on disk :wink:

Jesse, feel free to enhence rt2-to-rt3 with this information.
Personally I would have liked a solution where the export does the right
thing, namely what it does originally since then the exported data then
looks like the original mail with binary data but I can’t get the
encoding type right so that the import side does the right thing. Maybe
when I spend much, much more time on it but this just works and I only
need to get my data into RT3.

Joop

Joop van de Wege JoopvandeWege@mococo.nl

10

Hi,

Yes, that is just what I did - I always transferred to RT3
$att->_Value($att_param) whatever it is ‘Content’ or not, and that does
work.
Now I have all my old base64-encoded attachments also base64-encoded in new
RT3 database.
And RT3 web-interface (or, more probably, RT library behind it) does all the
magic to decode it and return real binary code to the browser.
I don’t know about other databases but for Postgres content decoding during
migration should be disabled.

Regards,
Alexei