Last Comment Bug 848063 - [Oracle] importxml.pl fails with ORA-01830: comment timestamps are not correctly formatted
: [Oracle] importxml.pl fails with ORA-01830: comment timestamps are not correc...
Status: RESOLVED FIXED
: regression
Product: Bugzilla
Classification: Server Software
Component: Bug Import/Export & Moving (show other bugs)
: 4.2.2
: All All
: -- major (vote)
: Bugzilla 4.2
Assigned To: Frédéric Buclin
: default-qa
Mentors:
Depends on: 562308
Blocks:
  Show dependency treegraph
 
Reported: 2013-03-05 11:43 PST by Mateusz Kuśmierczyk
Modified: 2013-09-03 02:46 PDT (History)
1 user (show)
mail: approval+
mail: approval4.4+
mail: approval4.2+
See Also:
QA Whiteboard:
Iteration: ---
Points: ---


Attachments
following diff worked for me (778 bytes, patch)
2013-03-05 12:02 PST, Mateusz Kuśmierczyk
no flags Details | Diff | Splinter Review
patch, v1 (651 bytes, patch)
2013-09-01 17:16 PDT, Frédéric Buclin
LpSolit: review+
Details | Diff | Splinter Review

Description Mateusz Kuśmierczyk 2013-03-05 11:43:35 PST
User Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:19.0) Gecko/20100101 Firefox/19.0
Build ID: 20130215130331

Steps to reproduce:

Using importxml.pl to marge 2 bugzilla instances.


Actual results:

import script failed with:

DBD::Oracle::st execute failed: ORA-01830: date format picture ends before converting entire input string (DBD ERROR: error possibly near <*> indicator at char 161 in 
'INSERT INTO longdescs (bug_id, who, bug_when, isprivate,thetext, work_time) VALUES (:p1, :p2, :<*>p3, :p4, :p5, :p6)') [for Statement "INSERT INTO longdescs (bug_id, who, bug_when, isprivate,thetext, work_time)
VALUES (?, ?, ?, ?, ?, ?)" with ParamValues: :p1="___", :p2="___", :p3="2013-01-21 11:01:55 +0000", :p4=_, :p5="_______ update text ____", :p6=0] at importxml.pl line 1207.
        main::process_bug('XML::Twig=HASH(0x4279074)', 'XML::Twig::Elt=HASH(0x587f664)') called at E:/Software/Perl/site/lib/XML/T
wig.pm line 2085
        XML::Twig::_twig_end('XML::Parser::Expat=HASH(0x4279874)', 'bug') called at E:/Software/Perl/lib/XML/Parser/Expat.pm line
474
        eval {...} called at E:/Software/Perl/lib/XML/Parser/Expat.pm line 474
        XML::Parser::Expat::parse('XML::Parser::Expat=HASH(0x4279874)', '<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>\
x{a}<!DO...') called at E:/Software/Perl/lib/XML/Parser.pm line 187
        eval {...} called at E:/Software/Perl/lib/XML/Parser.pm line 186
        XML::Parser::parse('XML::Twig=HASH(0x4279074)', '<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>\x{a}<!DO...') ca
lled at E:/Software/Perl/site/lib/XML/Twig.pm line 691
        eval {...} called at E:/Software/Perl/site/lib/XML/Twig.pm line 691
        XML::Twig::parse('XML::Twig=HASH(0x4279074)', '<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>\x{a}<!DO...') call
ed at importxml.pl line 1272
 at importxml.pl line 1272.


Expected results:

The comments from XML file should have been imported without the error.
Comment 1 Mateusz Kuśmierczyk 2013-03-05 11:48:36 PST
Found that the root cause was date formatting in the exported XML/Oracle.
Turns out XML import converts the dates for bugs but not for comments.

After modifying  the code in importxml.pl to format_time for comment dates the script started working.

XML date format in my file for both bug & coments was the same (2013-01-21 11:01:55 +0000)
Comment 2 Mateusz Kuśmierczyk 2013-03-05 12:02:09 PST
Created attachment 721391 [details] [diff] [review]
following diff worked for me
Comment 3 Frédéric Buclin 2013-09-01 16:30:43 PDT
I can reproduce the error.
Comment 4 Frédéric Buclin 2013-09-01 17:16:00 PDT
Created attachment 798345 [details] [diff] [review]
patch, v1

Your fix is correct, but let's put it at another place, i.e. when parsing the XML file rather than in the SQL query itself. This is also consistent with how we do it with attachments. r=LpSolit as I simply copied Mateusz.
Comment 5 Frédéric Buclin 2013-09-01 17:22:53 PDT
It's impossible to import data when using Oracle due to this bug and the patch applies cleanly to 4.2 too (but not 4.0), so let's take it for 4.2 as well.
Comment 6 Frédéric Buclin 2013-09-02 07:17:19 PDT
This is actually a regression due to bug 562308 which landed in 4.2.
Comment 7 Frédéric Buclin 2013-09-03 02:46:24 PDT
Committing to: bzr+ssh://lpsolit%40gmail.com@bzr.mozilla.org/bugzilla/trunk/
modified importxml.pl
Committed revision 8723.

Committing to: bzr+ssh://lpsolit%40gmail.com@bzr.mozilla.org/bugzilla/4.4/
modified importxml.pl
Committed revision 8601.

Committing to: bzr+ssh://lpsolit%40gmail.com@bzr.mozilla.org/bugzilla/4.2/
modified importxml.pl
Committed revision 8225.

Note You need to log in before you can comment on or make changes to this bug.