Bug Hunting Session
Bug 117092 - Migration to Firebird fails for a simple table
Summary: Migration to Firebird fails for a simple table
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.0.0.alpha0+
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:6.1.0
Keywords:
Depends on:
Blocks: Database-Firebird-Migration
  Show dependency treegraph
 
Reported: 2018-04-18 20:49 UTC by Gerhard Weydt
Modified: 2018-07-01 19:48 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
Error message (8.40 KB, image/png)
2018-04-18 20:49 UTC, Gerhard Weydt
Details
Doc before migration (10.46 KB, application/vnd.sun.xml.base)
2018-04-18 20:51 UTC, Gerhard Weydt
Details
Doc after migration (10.70 KB, application/vnd.sun.xml.base)
2018-04-18 20:51 UTC, Gerhard Weydt
Details
bt with debug symbols (11.55 KB, text/plain)
2018-04-21 06:31 UTC, Julien Nabet
Details
patch (1.72 KB, text/plain)
2018-04-21 07:23 UTC, Julien Nabet
Details
screen shot comparing keys after drag drop of table hsql->fb (147.94 KB, image/png)
2018-04-23 00:37 UTC, Drew Jensen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gerhard Weydt 2018-04-18 20:49:44 UTC
Created attachment 141472 [details]
Error message

The migration from HSQLDB to Firebird fails for a Database document with one very simple table, containing only two INTEGER fields and one DATE field, with an error message indicating that a BLOB is the reason.
Enclosed attachments provide Doc before migration, Doc after migration and Error message.
Comment 1 Gerhard Weydt 2018-04-18 20:51:02 UTC
Created attachment 141473 [details]
Doc before migration
Comment 2 Gerhard Weydt 2018-04-18 20:51:47 UTC
Created attachment 141474 [details]
Doc after migration
Comment 3 Robert Großkopf 2018-04-19 05:39:47 UTC
Could confirm the buggy behavior: There is no BLOB in the table, but Firebird couldn't import the table. 

... and another bug appears: I tried to have a look at the table and want edit it: LO 6.1.0.0alpha0 crashed immediately.
Comment 4 Xisco Faulí 2018-04-20 09:31:39 UTC
Reproduced in

Version: 6.1.0.0.alpha0+
Build ID: 159dd28651788a19848eae56693ad06ed947414d
CPU threads: 4; OS: Linux 4.13; UI render: default; VCL: gtk3; 
Locale: ca-ES (ca_ES.UTF-8); Calc: group

which is the first commit after the migration...
Comment 5 Julien Nabet 2018-04-21 06:31:07 UTC
Created attachment 141512 [details]
bt with debug symbols

On pc Debian x6-64 with master sources updated yesterday, I got an assertion.
Comment 6 Julien Nabet 2018-04-21 07:23:50 UTC
Created attachment 141513 [details]
patch

With the patch included, primary keys are parsed but the statement generated to create the table fails:
CREATE TABLE "MitgliedJahr" ( "Id" INTEGER NOT NULL PRIMARY KEY, "Jahr" INTEGER NOT NULL PRIMARY KEY, "Datum" DATE)

(Attempt to define a second PRIMARY KEY for the same table)
Comment 7 Robert Großkopf 2018-04-21 07:37:52 UTC
(In reply to Julien Nabet from comment #6)
> 
> (Attempt to define a second PRIMARY KEY for the same table)

There must be 2 primary keys, because there are 2 primary keys in the original table of HSQLDB:
"Id" is '0' in every row, "Jahr" changes the values from '2015' up to '2018'.
Comment 8 Julien Nabet 2018-04-21 09:35:21 UTC
(In reply to robert from comment #7)
> (In reply to Julien Nabet from comment #6)
> > 
> > (Attempt to define a second PRIMARY KEY for the same table)
> 
> There must be 2 primary keys, because there are 2 primary keys in the
> original table of HSQLDB:
> "Id" is '0' in every row, "Jahr" changes the values from '2015' up to '2018'.

Yes, I saw this so it means we can't deal with primary key column by column. Perhaps with an alter table at the end after having processed all the columns?
Comment 9 Julien Nabet 2018-04-21 11:25:52 UTC
I tried to create a multiple primary key with a brand new embedded Firebird file.
I even closed the file and reopened it just to be sure the table is taken into account but it gave this:

warn:connectivity.firebird:12594:12594:connectivity/source/drivers/firebird/Statement.cxx:121: isc_dsql_execute failed
warn:connectivity.firebird:12594:12594:connectivity/source/drivers/firebird/Util.cxx:54: firebird_sdbc error:
*unsuccessful metadata update
*ALTER TABLE TABLE1 failed
*SQL error code = -607
*Invalid command
*Table TABLE1 does not exist
caused by
'alter table table1
add constraint PK_MY_TABLE
primary key (ID,ID_1);'

Could it be due to the fact that we're behind the current Firebird version?
We use 3.0.0 whereas last one is 3.0.3
I had tried to upgrade but I failed because of Macos pb. Meanwhile, I saw in Firebird roadmap that Firebird devs were perhaps focused on next release Firebird 4.0
Comment 10 Gerhard Weydt 2018-04-21 12:44:02 UTC
I should perhaps give some information about this table.
It is a single table isolated from a database because it's the one that caused trouble. The table describes the relation between the tables Mitglied and Jahr, hence its name. The relation is n:m, id is the primary key of Mitglied and Jahr is primary key of table Jahr. I needed a unique key consisting of these two fields, and as HSQLDB always wants a primary key, I chose not to create an additional column, but define this unique index as the primary key.
In fact a unique index would have been sufficient. I tried to create a similar table in 6.1 with Firebird, with a unique index, which is explicitly allowed as one of the two ways to guarantee uniqueness, but the table isn't editable in Base. Now this could be the fault of Base and not of Firebird, because it's the same reaction as with a HSQLDB table without primary key. I will do some more testing and will probably enter that as another bug report, as it's not connected with migration.
Comment 11 Gerhard Weydt 2018-04-21 19:17:47 UTC
In reply to comment #9 by Julien:
According to the Firebird documentation for version 2.5 (!) setting a primary key with more than one column is definitely possible. And the generated syntax is also correct.
I suggest you create a new bug that Firebird does not allow primary keys with more than one column, in contrast to the documentation and also to HSQLDB. This bug here could perhaps be referenced, and a comment should be added that this is no migration issue, but an issue of Firebird implementation in Base.
I think that's better than renaming this bug, because one has to read 8 comments before one gets to the point.
Comment 12 Julien Nabet 2018-04-21 19:30:38 UTC
(In reply to Gerhard Weydt from comment #11)
> In reply to comment #9 by Julien:
> ...
> I suggest you create a new bug that Firebird does not allow primary keys
> with more than one column, in contrast to the documentation and also to
> HSQLDB. This bug here could perhaps be referenced, and a comment should be
> added that this is no migration issue, but an issue of Firebird
> implementation in Base.
> ...

I know very few Firebird process and perhaps it's already fixed in the current version.

Lionel: thought you might be interested in this one or have some thoughts here, so I put you in cc
Comment 13 Gerhard Weydt 2018-04-21 20:27:23 UTC
I used the latest version available at the time of download, probably April 17th.

Version: 6.1.0.0.alpha0+
Build ID: 9c4eaa7b81a40d97fe49b85272b40bfeaaf44f86
CPU threads: 4; OS: Windows 10.0; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-04-16_03:31:36
Locale: de-DE (de_DE); Calc: group

Julien: if you do not want to create a new bug report, I will do it, but I will be waiting some time, in case Lionel sends a comment.
Comment 14 Julien Nabet 2018-04-21 20:31:19 UTC
(In reply to Gerhard Weydt from comment #13)
> ...
> Julien: if you do not want to create a new bug report, I will do it, but I
> will be waiting some time, in case Lionel sends a comment.

I wasn't talking about LO but Firebird version.
Last master sources of LO use Firebird 3.0.0 whereas current Firebird version is 3.0.3
The problem is to be able to build Firebird 3.0.3 in LO master sources for every env: Linux, Windows and MacOS.
Comment 15 Gerhard Weydt 2018-04-21 20:39:43 UTC
referring to comment 10:
I created a new bug 117048 for the problem that t is not possible to edit data if the table has only a unique key, contrary to the statement on save that exactly this is allowed.
Comment 16 Gerhard Weydt 2018-04-21 20:56:09 UTC
(In reply to Julien Nabet from comment #14)
> (In reply to Gerhard Weydt from comment #13)
> ...
> I wasn't talking about LO but Firebird version.
> Last master sources of LO use Firebird 3.0.0 whereas current Firebird
> version is 3.0.3
> The problem is to be able to build Firebird 3.0.3 in LO master sources for
> every env: Linux, Windows and MacOS.

As I said, the use of multiple columns is already described in the manual for Firebird 2.5, so I don't think it probable that installing Firebird 3.0.3 instead of 3.0.0 can solve the problem. It is much more probable that the reason must be sought within LibO's connection and handling of Firebird.
Comment 17 Robert Großkopf 2018-04-22 06:48:31 UTC
(In reply to Gerhard Weydt from comment #15)
> I created a new bug 117048 for the problem that t is not possible to edit
> data if the table has only a unique key, contrary to the statement on save
> that exactly this is allowed.
Wrong bug-number. Right number:
https://bugs.documentfoundation.org/show_bug.cgi?id=117148
Comment 18 Gerhard Weydt 2018-04-22 21:49:59 UTC
Inspired by Julien's example in comment 9 I undertook some (or much) more testing and arrived at two sets of interesting results:
FIRST:
I used (on Windows 10) Tools -> SQL... to get similar results of executed SQL as Julien.
Creating a primary key for two fields (and as well for a single field, although I have not covered all test cases for that constellation, too) fails in most cases due to some issue of case sensitivity. For example: For a table whose name is already uppercase, but the field names are not, you get:
Column not found: ID in statement [ALTER TABLE TABELLE2  ADD PRIMARY KEY (id, id_1)]
Mark that the column not found is uppercase whereas it's lowercase in the statement.
This is, by the way, an example using HSQLDB, showing that it's not a database issue, but one of the UI of Base.
Using Tools -> SQL... works, though, quite well, if Table and field names (at least for the key fields) are uppercase.
Now using an all uppercase scenario we finally get a message which helps us on:

1: firebird_sdbc error:
*unsuccessful metadata update
*ALTER TABLE TABLE3 failed
*Column: ID not defined as NOT NULL - cannot be used in PRIMARY KEY constraint definition
caused by
'ALTER TABLE TABLE3 ADD PRIMARY KEY (ID, ID_1);'

SECOND:
It follows that the crucial problem is the fact that the fields used for a primary key should be not nullable!
I haven't yet researched if that is a categorical request for a primary key, generally, but  it seems that LibO requires that.
So if you set "Entry required" to Yes for the fields used for a primary key, you can define the primary key in the UI of a table, selecting the relevant field definitions and using the Ctrl-Key and Context menu -> Primary Key.
If you haven't set the "Entry required"-property for the key fields, you will be prompted by the message: "The column "..." could not be changed. Should the column instead be deleted and the new format appended?" for each of those fields, resulting in a deletion of the original field and the addition of a new one, quite similar, but carrying the attribute "non-nullable. (This it the same for HSQLDB and Firebird).
If "Entry required" was originally set or you answered Yes to all questions for deleting and appending, the primary key is created.
If you do this for a HSQLDB-database, the type seems to be implicitly changed to not nullable.

CONCLUSION:
The usage of Uppercase seems at least not understandable. But his seems to be restricted to the usage of the SQL dialog.

If no one wants to tackle a complete redesign of database connection (and I have no reason why it would be better), then
- migrating existing HSQLDB databases to Firebird you would probably have to set all primary key columns to non nullable.
Comment 19 Drew Jensen 2018-04-23 00:21:23 UTC
It may be worth noting, using the migrate function, it failed but I save the file with a new name, then open the original file in hsql mode a standard drag and drop of the table from hsql_src->fb_trgt works as expected and you get the table with data and the same key structure as what is in the original file. So this nut has been cracked somewhere in the current code once already; right?
Comment 20 Drew Jensen 2018-04-23 00:37:37 UTC
Created attachment 141551 [details]
screen shot comparing keys after drag drop of table hsql->fb
Comment 21 Commit Notification 2018-05-14 07:22:39 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "master":

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

tdf#117092 dbahsql: handle multiple primary keys

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 22 Tamas Bunth 2018-05-14 07:27:42 UTC
The above commit intends to solve the initial problem described by Gerhard. If there are further problems (slightly related or not related to this one), please open a new bug.
Comment 23 Gerhard Weydt 2018-07-01 19:48:08 UTC
Tested in 6..1 and 6.2. Works as expected.