Bug Hunting Session
Bug 117298 - Firebird: Migration: For an hsqldb column with type Time and data generates an error during import of the data
Summary: Firebird: Migration: For an hsqldb column with type Time and data generates a...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.0.0.alpha1+
Hardware: All All
: medium normal
Assignee: Julien Nabet
URL:
Whiteboard: target:6.1.0
Keywords:
Depends on:
Blocks: Database-Firebird-Migration
  Show dependency treegraph
 
Reported: 2018-04-27 18:47 UTC by Drew Jensen
Modified: 2018-05-21 21:25 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
example odb file, single table, two columns (4.58 KB, application/vnd.oasis.opendocument.database)
2018-04-27 18:48 UTC, Drew Jensen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Drew Jensen 2018-04-27 18:47:21 UTC
Description:
The ebmedded firebird engine is configured for the TIME data type (Dialect 3 for dates) but during migration a type of DATETIME.

Steps to Reproduce:
1. Download and open attched odb file
2. select Tables and when prompted to migrate select yes.
3. The migration function generates an error 'set var type fails' during data move.


Actual Results:  
LibO displayes error message: Incorrect type for setValue 

Expected Results:
table and data migrate.


Reproducible: Always


User Profile Reset: No



Additional Info:
do not save the file.
Open it in hsql mode.
delete the single record in the table.
close and reopen, click on tables.
Answer yes for migration and it runs to completion
check the table structure, column type changed to DateTime.


User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/65.0.3325.181 Chrome/65.0.3325.181 Safari/537.36
Comment 1 Drew Jensen 2018-04-27 18:48:11 UTC
Created attachment 141723 [details]
example odb file, single table, two columns

test file.
Comment 2 MM 2018-04-27 23:39:19 UTC
Confirmed on ubuntu 16.04 x64 with Version: 6.1.0.0.alpha1+
Build ID: e9ac3a9c1ee7689c4d591a68250666c95632bd2a
CPU threads: 2; OS: Linux 4.4; UI render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-04-27_00:59:01
Locale: en-US (en_US.UTF-8); Calc:

You also might get the error:

firebird_sdbc error:
*unsuccessful metadata update
*CREATE TABLE tbl_time failed
*Table tbl_time already exists
caused by
'CREATE TABLE "tbl_time" ( "id" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH -1) PRIMARY KEY, "tst_data" TIME)'
Comment 3 Drew Jensen 2018-04-27 23:45:00 UTC
(In reply to MM from comment #2)
> Confirmed on ubuntu 16.04 x64 with Version: 6.1.0.0.alpha1+
> Build ID: e9ac3a9c1ee7689c4d591a68250666c95632bd2a
> CPU threads: 2; OS: Linux 4.4; UI render: default; VCL: gtk2; 
> TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time:
> 2018-04-27_00:59:01
> Locale: en-US (en_US.UTF-8); Calc:
> 
> You also might get the error:
> 
> firebird_sdbc error:
> *unsuccessful metadata update
> *CREATE TABLE tbl_time failed
> *Table tbl_time already exists
> caused by
> 'CREATE TABLE "tbl_time" ( "id" INTEGER GENERATED BY DEFAULT AS IDENTITY
> (START WITH -1) PRIMARY KEY, "tst_data" TIME)'

Ah - another one of those.

No this is different, what happens is that during the migration SOMETIMES when you back out a copy of the firebird schema is left in the ODB, still configured to use the HSQLdb sdbc, since the hsql data schema also still in the ODB everythig seems fine till you try to auto migrate again and then you get that table exists error.

A fix, is just to open the ODB as a zip file and remove the firebird schema in the database directory. 

As for an issue in bugzilla, I just can't yet say how to make that happen for on demand (set of steps).
Comment 4 Robert Großkopf 2018-04-28 18:33:50 UTC
I could confirm the message
Incorrect type for setValue 
appears when importing a table with TIME and values in this field.
Works well with DATE and DATETIME and values in the field.

The TIME-field isn't changed to any other field here. When importing without values it is imported as TIME. I opened the tableeditor and see TIME, I could save TIME-values without any problem.

Could be there is a different behavior when doing all this with LO 6.1. I created the database for this test with LO 6.0.4.1 every time before importing it. Doesn't test the side effects of LO 6.1 for HSQLDB.
Comment 5 Drew Jensen 2018-04-28 18:49:38 UTC
ah- went through this here again and now the field is being created as TIME, both for an empty table (migration runs without error) and when an error is generated on importing the data, at which point if you save the file as firebird and reopen it the empty table is there with a time field.

Changing summary, to reflect the error is only when there is data in that field.
Comment 6 Julien Nabet 2018-05-19 20:16:30 UTC
I submitted https://gerrit.libreoffice.org/#/c/54581/ for review on gerrit.
Comment 7 Commit Notification 2018-05-20 08:20:22 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=cb4e2aea81b85a134d35bc54b7c49081f899bd78

tdf#117298: migration Firebird, fix time data

It will be available in 6.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 8 Drew Jensen 2018-05-21 15:30:44 UTC
so verified this with 
Version: 6.1.0.0.alpha1+
Build ID: 6e7e4d9f02f286ccb817cb2c1f54a951dcebffad
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-05-21_01:55:12
Locale: en-US (en_US.UTF-8); Calc: group

This is is verified in that having data in the source file no longer generates and error message.

However, and this might already be a known issue (I'll check), the value in the field is altered.

The test file includes data of 06:00:00 PM for the time.
After migration the data is 04:47:44 AM

That is quite a change.

At this point should this issue be left open for the data change, or would folks prefer a separate issue for that?
Comment 9 Julien Nabet 2018-05-21 15:55:05 UTC
(In reply to Drew Jensen from comment #8)
>...
> The test file includes data of 06:00:00 PM for the time.
> After migration the data is 04:47:44 AM
When opening the file and refuse migration, the time I got is 12AM
Still, in both cases, there's indeed a difference with 04:47:44 AM
>...
> At this point should this issue be left open for the data change, or would
> folks prefer a separate issue for that?
Personally, I'd say as you want but perhaps there are rules about this.
Comment 10 Julien Nabet 2018-05-21 16:20:23 UTC
Lionel/Tamas: after some debugging, here's a location to start:
    332             case DataType::TIME:
    333             {
    334                 sal_Int64 value = 0;
    335                 m_pStream->ReadInt64(value);
    336                 css::util::Time time((value % 1000) * 1000000, value / 1000, 0, 0, true);
    337                 aData.push_back(makeAny(time));
    338             }
from dbaccess/source/filter/hsqldb/rowinputbinary.cxx

gdbsession gives:
(gdb) p value
$4 = 82800000
(gdb) s
com::sun::star::util::Time::Time (this=0x7fffffff0510, NanoSeconds_=@0x7fffffff0550: 0, Seconds_=@0x7fffffff04e0: 17264, Minutes_=@0x7fffffff04e8: 0, 
    Hours_=@0x7fffffff04f0: 0, IsUTC_=@0x7fffffff04dc: 1 '\001') at /home/julien/lo/libreoffice/workdir/UnoApiHeadersTarget/offapi/normal/com/sun/star/util/Time.hpp:26
26	    : NanoSeconds(NanoSeconds_)

17264 comes from wrong conversion of 82800 (82800000 / 1000) in sal_uInt16 (2^16 =  65536)

Any thoughts?
Comment 11 Drew Jensen 2018-05-21 16:23:16 UTC
(In reply to Julien Nabet from comment #9)
> (In reply to Drew Jensen from comment #8)
> >...
> > The test file includes data of 06:00:00 PM for the time.
> > After migration the data is 04:47:44 AM
> When opening the file and refuse migration, the time I got is 12AM
> Still, in both cases, there's indeed a difference with 04:47:44 AM
> >...

Ah, I think the difference between 12Am and 6PM was me attaching that file to two different issues and with different data in each. I downloaded the file from this issue for this test today.

> > At this point should this issue be left open for the data change, or would
> > folks prefer a separate issue for that?
> Personally, I'd say as you want but perhaps there are rules about this.

Just asked over on the QA irc channel, I'll go with whatever the recommend.

Thanks for all your work on this.
Comment 12 Julien Nabet 2018-05-21 17:01:03 UTC
For the second patch, I submitted https://gerrit.libreoffice.org/#/c/54646/ for review.
Since Time function accepts nanosecs, just give it the value already in nanosecs.
Comment 13 Robert Großkopf 2018-05-21 18:45:39 UTC
Don't know if its a good idea to mix the bugs.

I get at this moment only a difference from 1 hour between the internal HSQLDB and the migrated file to Firebird. Missing 1 hour in Firebird looks like summertime/wintertime or something else.
Comment 14 Drew Jensen 2018-05-21 19:33:36 UTC
it is not just an hour difference.

Just did a fresh test, new file with date, time and datetime fields.

The dates migrate fine, no change.

The time field data and the time part of the datetime field do not. They are off by an 1 hour, and the minutes and seconds have changed in the two test records.

so...
Comment 15 Julien Nabet 2018-05-21 19:41:45 UTC
Drew/Robert: 
A bit lost here :-(
I'm in France and it's summer hour, so UTC+2.
would you have some time to take a look to https://bugs.documentfoundation.org/show_bug.cgi?id=116980#c12 and give your results?
Comment 16 Drew Jensen 2018-05-21 20:06:29 UTC
OK - I figured how to make the date change also, it is still a time problem. The change in time during migration can be large enough to flip the date.

So April 1, 2010 23:30:30 will become April 2, 2010
But April 1, 2010 13:30:30 will not. The time difference interval between the input and output is the same with both records but only second rolls over to the April 2.

I have both the input and output odb in a zip file and I think at this point it really is worth a separate issue.

I'll wait half an hour in case anyone thinks I should just attach it here, otherwise I'll open that new issue and attach the file.
Comment 17 Julien Nabet 2018-05-21 20:54:59 UTC
(In reply to Drew Jensen from comment #16)
> ...
> I'll wait half an hour in case anyone thinks I should just attach it here,
> otherwise I'll open that new issue and attach the file.
Please go ahead to open a brand new bugtracker.
It'll be clearer indeed.
Comment 18 Drew Jensen 2018-05-21 21:25:33 UTC
New issue
https://bugs.documentfoundation.org/show_bug.cgi?id=117732