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.
Created attachment 141473 [details] Doc before migration
Created attachment 141474 [details] Doc after migration
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.
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...
Created attachment 141512 [details] bt with debug symbols On pc Debian x6-64 with master sources updated yesterday, I got an assertion.
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)
(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'.
(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?
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
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.
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.
(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
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.
(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.
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.
(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.
(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
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.
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?
Created attachment 141551 [details] screen shot comparing keys after drag drop of table hsql->fb
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.
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.
Tested in 6..1 and 6.2. Works as expected.