Download it now!
Bug 116980 - FIREBIRD: Migration: No migration possible with data in tables and declared realtionship
Summary: FIREBIRD: Migration: No migration possible with data in tables and declared r...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.0.0.alpha0+
Hardware: x86-64 (AMD64) All
: highest critical
Assignee: Not Assigned
URL:
Whiteboard: target:6.1.0
Keywords: dataLoss
: 117588 (view as bug list)
Depends on:
Blocks: Database-Firebird-Migration
  Show dependency treegraph
 
Reported: 2018-04-12 19:06 UTC by Robert Großkopf
Modified: 2018-05-21 19:41 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
Two database for testing: Import of data together with relationship fails (9.75 KB, application/zip)
2018-04-12 19:06 UTC, Robert Großkopf
Details
Simple example, only two tables, fails to migrate because of relationship and data (4.24 KB, application/vnd.oasis.opendocument.database)
2018-04-13 07:48 UTC, Robert Großkopf
Details
screen shot of migrated database showing persons records. (69.41 KB, image/png)
2018-04-18 16:17 UTC, Drew Jensen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2018-04-12 19:06:06 UTC
Created attachment 141315 [details]
Two database for testing: Import of data together with relationship fails

There are two database in the attachment:
First database with declared relationship, second database with the same data and without declared relationship.

Open the first database HSQLDB_FB_Test.odb with LO 6.1.0.0.alpha.
Change to the section "Tables".
After a while an error appears, something with Value ***null*** of one of the tables.
No tables will be created.

Open the second database HSQLDB_FB_Test_whithout_relationship.odb.
Change to the section "Tables".
After a while the tables will be created. (The view won't be created because of another bug).

All tested with 
Version: 6.1.0.0.alpha0+
Build-ID: dc823f5fa4a5d2eca56297b9045e5962536c00f9
CPU-Threads: 4; BS: Linux 4.4; UI-Render: Standard; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-04-10_23:32:35
Comment 1 Alex Thurgood 2018-04-13 07:32:37 UTC
Confirming with

Version: 6.1.0.0.alpha0+
Build ID: d5ed07d2a249e61937dd42a4b2efb7e7fbef02d6
CPU threads: 4; OS: Mac OS X 10.13.4; UI render: default; 
Locale: fr-FR (fr_FR.UTF-8); Calc: group

Error message :

firebird_sdbc error:
*validation error for column "Adresse"."ID", value "*** null ***"
caused by
'isc_dsql_execute'
Comment 2 Alex Thurgood 2018-04-13 07:41:14 UTC
Additional information:

Even if the user doesn't save the database after clearing the error message, closes LibreOffice and reopens the ODB file, the file seems to be damaged as no tables appear in the Tables list.
Comment 3 Alex Thurgood 2018-04-13 07:41:41 UTC
Tested on macOS 10.13.4
Comment 4 Robert Großkopf 2018-04-13 07:48:36 UTC
Created attachment 141320 [details]
Simple example, only two tables, fails to migrate because of relationship and data

Have added another example, very simple with only two tables. Same error:

firebird_sdbc error:
*validation error for column "persons"."ID", value "*** null ***"
caused by
'isc_dsql_execute'
Comment 5 Drew Jensen 2018-04-18 16:17:57 UTC
Created attachment 141469 [details]
screen shot of migrated database showing persons records.

Looks like it could be a sequencing issue now.

Using the latest Linux 64 build.

Downloaded the second attachment, TwoTablesRelated

Tried to migrate the database, it failed with a constraint violation during a record insert.

Closed without saving.

Made a copy of the original. (TwoTablesRelated_mig)

Opened the copy in hsqldb mode and deleted all the records in the persons table. Did not delete the records in sports table.

Saved and closed the file.

Opened the copy (as firebird)  and the original (as hsqldb).

Moved the persons table, via drag drop, from the original (source) to copy (target) and the data goes in without an error.

The file is fully migrated with fk relationship and data. Screen shot attached.
Comment 6 Drew Jensen 2018-04-18 16:19:41 UTC
just to be clear. I said I drag dropped to move the table, of course what I selected was to append the data to the persons table at that point.
Comment 7 Commit Notification 2018-05-02 12:10:06 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "master":

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

tdf#116980 Add constraints after data migration

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 Commit Notification 2018-05-02 17:03:41 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "master":

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

tdf#116980 tdf#116986 Fix data migration in..

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 9 Robert Großkopf 2018-05-04 07:09:42 UTC
Could confirm it is working now with
Version: 6.1.0.0.alpha1+
Build-ID: 4ed3137022efa6128ad146e4b4dfae13548431dc
CPU-Threads: 4; BS: Linux 4.4; UI-Render: Standard; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-05-04_01:13:51
Gebietsschema: de-DE (de_DE.UTF-8); Calc: group

Have tested both attached examples. Tables were imported, relation is imported, view is imported, content of tables is imported, autovalue is set right to input data directly without problems.

Should this one be set to VERIFIED?
Comment 10 Xisco Faulí 2018-05-04 08:47:51 UTC
(In reply to robert from comment #9)
> Could confirm it is working now with
> Version: 6.1.0.0.alpha1+
> Build-ID: 4ed3137022efa6128ad146e4b4dfae13548431dc
> CPU-Threads: 4; BS: Linux 4.4; UI-Render: Standard; VCL: kde4; 
> TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time:
> 2018-05-04_01:13:51
> Gebietsschema: de-DE (de_DE.UTF-8); Calc: group
> 
> Have tested both attached examples. Tables were imported, relation is
> imported, view is imported, content of tables is imported, autovalue is set
> right to input data directly without problems.
> 
> Should this one be set to VERIFIED?

Yep, setting to VERIFIED.
Tamas, thank you very much for fixing this!!
Robert, thank you very much for verifying this!!
Comment 11 Drew Jensen 2018-05-12 19:50:16 UTC
*** Bug 117588 has been marked as a duplicate of this bug. ***
Comment 12 Julien Nabet 2018-05-21 16:46:13 UTC
Nitpick: testing tdf#117298 and the pb of time value, I noticed that in "Simple example, only 2 tables, ...", the converted day in Persons table is wrong.
Indeed, I got a difference of 1 day for each date.
So instead of:
- 03.07.88
- 17.09.67
- 13.03.66

I got:
- 02.07.88
- 16.09.67
- 12.03.66
after FB migration.
Wonder if the pb has the same root cause.