Download it now!
Bug 126408 - Base: HSQL to Firebird migration corrupts data
Summary: Base: HSQL to Firebird migration corrupts data
Status: RESOLVED DUPLICATE of bug 119675
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.3.0.0.alpha1+
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Firebird-Migration
  Show dependency treegraph
 
Reported: 2019-07-15 17:56 UTC by Lars Jødal
Modified: 2019-07-17 16:34 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Lars Jødal 2019-07-15 17:56:36 UTC
Description:
Loading a test database in LO 6.3.0.1, I am asked:
"The document contains embedded HSQL data, which is deprecated. Would you like to migrate to Firebird now?" 
However, answering "Yes" leaves me with corrupted data.

Test case: attachment 135786 [details] (originally created for bug 112023). This small sample base contains these data:

    MyTimestamp            MyDouble
    01-01-2015 00:00:01    1.2345
    01-01-2015 00:00:15    3.1415

which in standard formatting are shown like

    MyTimestamp            MyDouble
    01-01-15 00:00         1.23
    01-01-15 00:00         3.14

Opening the sample database in LO 6.2.5.2, or answering "Later" to migration, this is how the data appear.

However, answering "Yes" to migration, the time data are shifted one hour, and the decimal data are completely corrupted:

    MyTimestamp            MyDouble
    31-12-14 23:00         0
    31-12-14 23:00         1.10E+227


Steps to Reproduce:
1. Open the sample database in LO 6.3.0.1 (or earlier 6.3.0.x)
2. Click on "Tables", answer "Later" to migration
3. Open Table1 - data are correct (as described above)
4. Close database
5. Re-open sample database, click on "Tables" and answer "Yes" to migration
6. Open Table 1 and notice corrupted data

Actual Results:
After migration, timestamps are shifted by 1 hour and numeric data are corrupted.

Expected Results:
After migration, all data should be as originally.


Reproducible: Always


User Profile Reset: No



Additional Info:
The link https://wiki.documentfoundation.org/Documentation/HowTo/MigrateFromHSQLDB confirms that migration is buggy, including the bug that shifts timestamps by 1 or 2 hours. If this is known to be buggy, it seems to early to deprecate the HSQL. If buggy migration is kept, then a lot of Base users are likely going to lose data and lose confidence in Base.

Migration problem found with version 6.3.0.1:

Version: 6.3.0.1 (x64)
Build ID: 41ac97386aba908b6db860cfb4cfe2da871886ae
CPU threads: 8; OS: Windows 10.0; UI render: GL; VCL: win; 
Locale: da-DK (da_DK); UI-Language: en-GB
Calc: C

Tested in 6.3.0.0.Alpha1 with similar results.
Current "fresh" version 6.2.5.2 does not suggest migration and accordingly works fine.
Comment 1 Xisco Faulí 2019-07-16 14:41:38 UTC
MyDouble column problem is a duplicate of bug 126268

Timestamp problem reproduced in

Version: 6.4.0.0.alpha0+
Build ID: ed2107f0488887528d8e49135d3270705f389ed5
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: ca-ES (ca_ES.UTF-8); UI-Language: en-US
Calc: threaded
Comment 2 Xisco Faulí 2019-07-16 16:26:32 UTC
At this point < https://opengrok.libreoffice.org/xref/core/dbaccess/source/filter/hsqldb/rowinputbinary.cxx?r=90882591#353 >

these are the values

debug:664:664: 2014-Dec-31 23:00:01
debug:664:664: 2014-Dec-31 23:00:15

so, 1 hour is lost somewhere...
Comment 3 Lars Jødal 2019-07-17 07:07:05 UTC
(In reply to Xisco Faulí from comment #1)
> MyDouble column problem is a duplicate of bug 126268

The problem with DOUBLE seem to be different from the problem with DECIMAL. Testing with build dated 2019-07-17:

Version: 6.4.0.0.alpha0+ (x86)
Build ID: c738be4de6886a0c96b7d10df7e78c8b2964c135
CPU threads: 4; OS: Windows 10.0; UI render: GL; VCL: win; 
Locale: da-DK (da_DK); UI-Language: en-US
Calc: CL

In this version, the DECIMAL problem appears to be fixed (tested with attachment 152620 [details]), while the DOUBLE problem remains (tested with attachment 135786 [details]).

Regarding time shift, the link https://wiki.documentfoundation.org/Documentation/HowTo/MigrateFromHSQLDB describes it like this:

"The Migration Wizard does not manage to correctly migrate date fields, time fields, and date / time fields. This is because the HSQLDB works with local time zones. During the date-time migration, this suddenly turns into summer time values ​​that are 2 hours earlier, but only one hour in winter time."

I.e., the bug is known, and the link also gives a workaround, but not something that is part of automated migration.
Comment 4 Alex Thurgood 2019-07-17 09:20:52 UTC
From memory, this is probably a timezone issue. There is already a report for this somewhere.

AFAICR, HSQLDB stores a Java datetime with timezone corrections. However, this is not taken into account by Firebird when migrating.
Comment 5 Xisco Faulí 2019-07-17 16:34:02 UTC
(In reply to Alex Thurgood from comment #4)
> From memory, this is probably a timezone issue. There is already a report
> for this somewhere.
> 
> AFAICR, HSQLDB stores a Java datetime with timezone corrections. However,
> this is not taken into account by Firebird when migrating.

Yes, see bug 119675

*** This bug has been marked as a duplicate of bug 119675 ***