Bug Hunting Session
Bug 127040 - HSQLDB to Firebird automated migration failure when record size exceeds 64k
Summary: HSQLDB to Firebird automated migration failure when record size exceeds 64k
Status: RESOLVED NOTOURBUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.2.0.0.alpha0+
Hardware: All All
: highest critical
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on: Database-Firebird-Migration
Blocks: Database-HSQLDB-Removal
  Show dependency treegraph
 
Reported: 2019-08-20 00:55 UTC by paul
Modified: 2019-09-13 09:32 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Test ODB file to migrate to FB (4.48 KB, application/vnd.oasis.opendocument.database)
2019-08-20 14:43 UTC, Alex Thurgood
Details

Note You need to log in before you can comment on or make changes to this bug.
Description paul 2019-08-20 00:55:43 UTC
Description:
Get the following errors trying to migrate a database to Firebird.

I suspect the first two below are caused by the third one:
*unsuccessful metadata update
*new record size of 116772 bytes is too big

I don't think you should be forcing a new database change like this unless it is 100% stable. This seems extremely flaky to replace a perfectly working db engine with one where a. it doesn't even migrate successfully and b. doesn't migrate forms etc. 

From HSQLDB to Firebird automated migration

Error code: 1

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" ALTER COLUMN "ID" RESTART WITH 140'

Error code: 1

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -204
*Table unknown
*Table1
*At line 1, column 8
caused by
'isc_dsql_prepare'

Error code: 1

firebird_sdbc error:
*unsuccessful metadata update
*new record size of 116772 bytes is too big
*TABLE Table1
caused by
'CREATE TABLE "Table1" ( "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH -1), "Song#" VARCHAR(255), "SongID" VARCHAR(255), "SongName" VARCHAR(255), "SongGenre" VARCHAR(255), "SongKey" VARCHAR(255), "SongBPM" VARCHAR(255), "SongNotes" VARCHAR(1000), "MasterEffect" VARCHAR(255), "MasterPatch" VARCHAR(255), "VocalEffects" VARCHAR(255), "VocalPatch" VARCHAR(255), "VocalNotes" VARCHAR(1000), "GuitarType" VARCHAR(255), "GuitarTuning" VARCHAR(255), "GuitarKey" VARCHAR(255), "GuitarEffects" VARCHAR(255), "GuitarPatch" VARCHAR(255), "GuitarNotes" VARCHAR(1000), "HarpKey" VARCHAR(255), "HarpPosition" VARCHAR(255), "HarpEffects" VARCHAR(255), "HarpPatch" VARCHAR(255), "HarpNotes" VARCHAR(1000), "BassUsedFlag" BOOLEAN, "BassInstrument" VARCHAR(255), "BassPatch" VARCHAR(255), "BassNotes" VARCHAR(1000), "DrumsUsedFlag" BOOLEAN, "DrumInstrument" VARCHAR(255), "DrumPatch" VARCHAR(255), "DrumNotes" VARCHAR(1000), "DAWNotes" VARCHAR(1000), "SongLyrics1" VARCHAR(5000), "SongLyrics2" VARCHAR(5000), "SongLyrics3" VARCHAR(5000), "OnlineCollabFlag" BOOLEAN, "OnlineCollabNotes" VARCHAR(1000), "Copyright" VARCHAR(255), "OriginalFlag" BOOLEAN, "PublicDomainFlag" BOOLEAN, "TimeSig" VARCHAR(50),PRIMARY KEY("ID"))'


Steps to Reproduce:
1.Open database
2.Try to migrate to Firebird from HSQLDB
3.FAIL to migrate

Actual Results:
Error code: 1

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" ALTER COLUMN "ID" RESTART WITH 140'

Error code: 1

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -204
*Table unknown
*Table1
*At line 1, column 8
caused by
'isc_dsql_prepare'

Error code: 1

firebird_sdbc error:
*unsuccessful metadata update
*new record size of 116772 bytes is too big
*TABLE Table1
caused by
'CREATE TABLE "Table1" ( "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH -1), "Song#" VARCHAR(255), "SongID" VARCHAR(255), "SongName" VARCHAR(255), "SongGenre" VARCHAR(255), "SongKey" VARCHAR(255), "SongBPM" VARCHAR(255), "SongNotes" VARCHAR(1000), "MasterEffect" VARCHAR(255), "MasterPatch" VARCHAR(255), "VocalEffects" VARCHAR(255), "VocalPatch" VARCHAR(255), "VocalNotes" VARCHAR(1000), "GuitarType" VARCHAR(255), "GuitarTuning" VARCHAR(255), "GuitarKey" VARCHAR(255), "GuitarEffects" VARCHAR(255), "GuitarPatch" VARCHAR(255), "GuitarNotes" VARCHAR(1000), "HarpKey" VARCHAR(255), "HarpPosition" VARCHAR(255), "HarpEffects" VARCHAR(255), "HarpPatch" VARCHAR(255), "HarpNotes" VARCHAR(1000), "BassUsedFlag" BOOLEAN, "BassInstrument" VARCHAR(255), "BassPatch" VARCHAR(255), "BassNotes" VARCHAR(1000), "DrumsUsedFlag" BOOLEAN, "DrumInstrument" VARCHAR(255), "DrumPatch" VARCHAR(255), "DrumNotes" VARCHAR(1000), "DAWNotes" VARCHAR(1000), "SongLyrics1" VARCHAR(5000), "SongLyrics2" VARCHAR(5000), "SongLyrics3" VARCHAR(5000), "OnlineCollabFlag" BOOLEAN, "OnlineCollabNotes" VARCHAR(1000), "Copyright" VARCHAR(255), "OriginalFlag" BOOLEAN, "PublicDomainFlag" BOOLEAN, "TimeSig" VARCHAR(50),PRIMARY KEY("ID"))'


Expected Results:
Successful table migration


Reproducible: Always


User Profile Reset: No



Additional Info:
Comment 1 Alex Thurgood 2019-08-20 07:21:02 UTC
The migration isn't forced on you...yet. However, I am in agreement that the Firebird migration still unfortunately isn't in a position to go primetime with people's data, but that decision doesn't rest with me (or others who have voiced similar concerns). It has been taken by the steering committee.

When the migration dialog appears, there is a link to a LO wiki page that indicates where the main problems currently lie, and how to try and mitigate those issues.

Anyway, it seems that the migration code is having an issue at least with the IDENTITY sequence because you must have restarted the numbering in your hsqldb table at some stage. Firebird relies on generators to create autoincremented ID entries, and I'm guessing that the current migration code doesn't know how to convert the renumbering here.


Created a test file from the SQL in your original report.
Chose 0 as the initial counter for the ID field, saved the DB, then altered it to restart numbering at 140 as per the ALTER TABLE statement.
Saved the ODB, quit LO, made a copy, then loaded the copy of the original ODB file and agreed to migration.
Error message as described by original poster.

Confirming.

Version: 6.3.0.4
Build ID: 057fc023c990d676a43019934386b85b21a9ee99
Threads CPU : 8; OS : Mac OS X 10.14.6; UI Render : par défaut; VCL: osx; 
Locale : fr-FR (fr_FR.UTF-8); Langue IHM : fr-FR
Calc: threaded
Comment 2 Alex Thurgood 2019-08-20 07:24:55 UTC
@Paul : you should really read this before attempting the migration :

https://wiki.documentfoundation.org/Documentation/HowTo/MigrateFromHSQLDB

this is the page that the migration dialog points to when it first appears.
Comment 3 Alex Thurgood 2019-08-20 07:28:05 UTC
This is actually a pretty critical function to get right on migration. If the user can't rely on the migration code to get the ID sequences correct, then there are going to be a lot of angry Base users.
Comment 4 paul 2019-08-20 08:35:00 UTC
Thanks for the repro and confirming it. 

Agree I wasn't being "forced" but considering how twitchy it is imo it should not even be an option in a production system at the moment. 

Also very premature to be speaking about deprecating it as well! 

I do a fair amount of db work myself, so I'm speaking both as a general user and from a tech perspective.
Comment 5 paul 2019-08-20 08:36:35 UTC
I also did read all the doc links as well as did some research. 
It was a test copy of my db I was using, and I was testing it to see how robust it was. Not very!
Comment 6 paul 2019-08-20 08:36:44 UTC Comment hidden (obsolete)
Comment 7 Alex Thurgood 2019-08-20 14:43:05 UTC
Created attachment 153538 [details]
Test ODB file to migrate to FB

Attaching test file from the SQL reported by Paul.

1) Load the file in LO Base.
2) Click on the Tables icon.
3) Accept the suggestion to migrate.
4) Watch it fail miserably.
5) If the user makes the mistake of assuming that saving the file might help, in some way, e.g. to try and rescue it, a broken ODB file will be created.
Comment 8 Alex Thurgood 2019-08-20 14:44:19 UTC
I'm guessing that this problem of rewriting the correct generator goes back earlier than 6304, almost certainly to when the migration code was introduced.
Comment 9 Xisco Faulí 2019-08-21 12:12:43 UTC
Also reproducible in

Version: 6.1.0.0.alpha1+
Build ID: 3a801799536e6870f2fb111b1cc00b9575a35a39
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: x11; 
Locale: ca-ES (ca_ES.UTF-8); Calc: group
Comment 10 Xisco Faulí 2019-08-21 12:13:55 UTC
@Tamas, I thought you might be interested in this issue...
Comment 11 Julien Nabet 2019-09-09 20:16:12 UTC
I submitted a first patch here to fix sequence start:
https://gerrit.libreoffice.org/#/c/78787/

But there's also a pb of record size.
Comment 12 Julien Nabet 2019-09-09 20:22:14 UTC
According to https://firebirdsql.org/en/firebird-technical-specifications/
max record size=64kb (at least for Firebird 2.5)

confirmed in src/jrd/val.h of Firebird
53 const ULONG MAX_RECORD_SIZE     = 65535;
Comment 13 Julien Nabet 2019-09-09 20:33:01 UTC
Also present here:
http://www.firebirdfaq.org/faq61/

I retrieved last Firebird sources from github (see https://github.com/FirebirdSQL/firebird/commits/master), no evolution about src/jrd/val.h
still const ULONG MAX_RECORD_SIZE     = 65535

Git grepping on this const only gives:
src/jrd/dfw.epp:5581:   if (offset > MAX_RECORD_SIZE)
src/jrd/val.h:54:const ULONG MAX_RECORD_SIZE    = 65535;

First line concerns an error message.

Taking a look at the bugtracker, I noticed http://tracker.firebirdsql.org/browse/CORE-755
So increasing record size is a very old demand
Comment 14 Commit Notification 2019-09-09 20:58:06 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/f4f8bccbd4e2c3979a83d5b2f49e16a99a3a2016%5E%21

Related tdf#127040: fix START WITH

It will be available in 6.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 15 paul 2019-09-10 01:56:28 UTC
Great work, ty!
Comment 16 Julien Nabet 2019-09-10 05:18:37 UTC
(In reply to paul from comment #15)
> Great work, ty!

My patch was wrong, -1 isn't a pb to start a sequence.
The only pb seems to be record size and LO isn't responsable for this.
IMHO, I'd put NOTOURBUG since Firebird migration is now experimental.
Comment 17 paul 2019-09-10 05:23:59 UTC
In that case maybe they shouldn't be using Firebird in the first place...
Comment 18 Commit Notification 2019-09-10 06:41:47 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/2acafef57153bfa1ade59e887a55457ba677b976%5E%21

Revert "Related tdf#127040: fix START WITH"

It will be available in 6.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 19 Julien Nabet 2019-09-10 06:57:54 UTC
(In reply to paul from comment #17)
> In that case maybe they shouldn't be using Firebird in the first place...

That's why we put back Firebird migration at experimental.
See http://document-foundation-mail-archive.969070.n3.nabble.com/About-putting-back-Firebird-experimental-td4265392.html#a4266137
The goal with Firebird is to use an embedded DB which isn't dependent on Java.
But Firebird has quite some limitations and IMHO, Firebird migration shouldn't be proposed at all even in experimental since there are too much pitfalls. Related code for this part should be removed.
Comment 20 paul 2019-09-10 07:43:24 UTC
Ah, I didn't see that. Thanks for the link. I agree, that's the most sensible approach right now.
Comment 21 Julien Nabet 2019-09-10 07:56:07 UTC
Let's rephrase the title then.
Comment 22 Julien Nabet 2019-09-10 07:56:48 UTC
then let's put this one to RESOLVED/NOTOURBUG since we don't control Firebird.
Comment 23 Alex Thurgood 2019-09-10 09:30:08 UTC
I love that we only find this out 3 years after deciding to go for FB as the default option...

This page discussing UNIDAC would seem to indicate that the problem is more linked to our migration code not being able to prepare an insert statement in chunks that respects FB's 64K statement size limit :

https://forums.devart.com/viewtopic.php?t=27624

in which case, this would definitely be our bug...
Comment 24 Alex Thurgood 2019-09-10 09:41:53 UTC
From the discussion at :

https://stackoverflow.com/questions/42340848/jooq-firebird-implementation-limit-exceeded

I'm guessing that our migration code is re-casting the VARCHAR() fields to something much bigger than their UTF8 defaults, which is why the statement fails the 64K limit test on execution.
Comment 25 Julien Nabet 2019-09-10 09:49:49 UTC
Alex: I must recognize I'm a bit confused. Indeed, I don't understand why you're talking about chunking but since I know very few about Firebird, let's revert the state.

If you want to change the title, don't hesitate.
Comment 26 Alex Thurgood 2019-09-10 10:10:38 UTC
@Julien : the title reflects the symptoms of the bug, so I wouldn't want to change it unless there is a better way to describe that.

The use of "chunk" in comment 23 is my layman's way of wondering whether it would be possible to have the migration assistant split the data into more manageable chunks that would pass the 64K limit test that FB imposes. I don't know how this could be done, or even if it is doable.

My remarks in comment 24 refer to whether our migration assistant code is correctly handling the table data for VARCHAR fields when some of those fields contain NULL values (as is the case with the test hsqldb file), and instead seems to be trying to insert 4x5000 bytes e.g. for VARCHAR(5000) and a corresponding number of bytes for the other VARCHAR fields which can also be NULL in terms of actual data.

I guess that someone who knows the how the migration code works would be able to answer this.
Comment 27 Julien Nabet 2019-09-10 10:13:48 UTC
Alex: even if you create the table in several times, if row size is limited to 64k, you won't be able to put all the columns.
About varchar, I think you're right, I suppose it's utf-8 which can be coded until 4 bytes.
Comment 28 Julien Nabet 2019-09-12 09:23:07 UTC
Alex: I created a Firebird embedded database from scratch then just copied pasted the 'CREATE TABLE "Table1" ( "ID" IN... ' 
from initial comment in Tools/SQL part in order to be sure we don't call anything related to migration (except the request of course). I could reproduce the error 
*unsuccessful metadata update
*new record size of 116772 bytes is too big

I took a look to https://stackoverflow.com/questions/822837/firebird-utf8-varchar-size, we got this:
"Using the UTF8 character set for VARCHAR(N) fields needs to reserve enough space for any N UTF8 characters"
So even if in the insert requests you'll make, you won't fill each column entirely, the total size of all columns (so record size) contained in your table must be 64k max.


Then I thought perhaps migration code should generate a "Create table" request by using another charset to avoid to use 4 bytes for each character.

So I tried to add "character set iso8859_1" to one of the line, eg:
"SongLyrics3" VARCHAR(5000) character set iso8859_1,

I got:
*unsuccessful metadata update
*Table1
*CHARACTER SET ISO8859_1 is not installed
caused by
'CREATE TABLE "Table1" (
...

Same result with "WIN1250"

Finally I tried the example of Uniloader link to try to reproduce the error, I could create a table with:
CREATE TABLE TEST_SOURCE(
A VARCHAR(1400) DEFAULT '' ,
B INTEGER DEFAULT 0 );
then could insert a line with:
INSERT INTO TEST_SOURCE (A,B) VALUES('TOM',10);
Comment 29 Alex Thurgood 2019-09-12 10:28:45 UTC
(In reply to Julien Nabet from comment #28)

Nice testing work thanks ! :)

Looks like we're stuck then. 

Any tables that have SUM(VARCHAR(N)) > 64K will fail with default UTF-8 encoding, and even if we could use ISO8859_1, it would fail to pick up non-ASCII data and probably then fail elsewhere (truncation ?).
Comment 30 Julien Nabet 2019-09-12 12:05:41 UTC
(In reply to Alex Thurgood from comment #29)
> (In reply to Julien Nabet from comment #28)
> 
> Nice testing work thanks ! :)
> 
> Looks like we're stuck then. 
> 
> Any tables that have SUM(VARCHAR(N)) > 64K will fail with default UTF-8
> encoding, and even if we could use ISO8859_1, it would fail to pick up
> non-ASCII data and probably then fail elsewhere (truncation ?).

Probably yes and if we'd use ISO8859_1 (which only needs 1 byte per character), we may store 4x than UTF8 but we'll be still constrained to 64k whereas in HSQLDB, I quote http://hsqldb.sourceforge.net/web/hsqlFAQ.html:
"There is no imposed limitation. Number of columns, tables, indexes, size of columns and so on is limited only by the memory. For example, a user reported using a SELECT statement with 41 LEFT OUTER JOIN clauses on a huge database for a data mining application. "

That's why I had set NOTOURBUG.
Comment 31 Alex Thurgood 2019-09-13 05:56:57 UTC
Indeed, setting back to NOTOURBUG
Comment 32 paul 2019-09-13 07:49:32 UTC
How can you mark it resolved when it's a critical system failure, even if it's not your bug?
Comment 33 Julien Nabet 2019-09-13 08:04:54 UTC
Because Firebird dev doesn't depend on us so no need to keep a bugtracker opened for nothing.
Moreover, as previously indicated, migration has been put back to experimental.
IMHO, migration part should be removed since it's quite impossible to deal with all cases and limitations.
Finally, as Alex indicated nothing forces you to migrate to Firebird.

Now if you want absolutely to migrate, you should prepare your HSQLDB DB by reducing column sizes (when it's possible of course) until it's ok for Firebird specs.
Of course, make some backups before doing such things.
Comment 34 paul 2019-09-13 08:15:43 UTC
No problem, I was just curious how you coordinate with Firebird dev that doesn't depend on you, but is critical to the operation of the platform once you go with it. 

>>IMHO, migration part should be removed since it's quite impossible to deal with all cases and limitations.

I agree. 

>>Finally, as Alex indicated nothing forces you to migrate to Firebird.
Now if you want absolutely to migrate, you should prepare your HSQLDB DB by reducing column sizes (when it's possible of course) until it's ok for Firebird specs.
Of course, make some backups before doing such things.

I don't want to migrate at all, especially with it in the twitchy unreliable state it's in now. But the way the dialog was presented it essentially is warning you that it's going to switch permanently so it's our way or the highway. So I tested it with a duplicate file and here we are.
Comment 35 Lionel Elie Mamane 2019-09-13 08:25:29 UTC
(In reply to paul from comment #17)
> In that case maybe they shouldn't be using Firebird in the first place...

Do you have a better alternative to propose? An embeddable non-Java license-compatible SQL database?

(In reply to Alex Thurgood from comment #31)
> Indeed, setting back to NOTOURBUG

Well, one way to mitigate / work around the issue is to convert large VARCHAR columns to CLOB instead of VARCHAR... At least theoretically, we could do that.
Comment 36 paul 2019-09-13 08:47:47 UTC
Doing a solutions eval is non-trivial, especially for something like databases. Have you evaluated all the non-commercial license compatible ones here? (just a quick search, there could easily be more somewhere in various  development stages) http://embedded-databases.com/ 

I guess my point is a "solution" is not a solution if it doesn't work, even if it's the only "solution" available. If it makes the product worse (potentially) that's a fail.
Comment 37 Julien Nabet 2019-09-13 09:26:29 UTC
(In reply to paul from comment #36)
> Doing a solutions eval is non-trivial, especially for something like
> databases. Have you evaluated all the non-commercial license compatible ones
> here? (just a quick search, there could easily be more somewhere in various 
> development stages) http://embedded-databases.com/
> ...
If you remove DBs with commercial license only, those relying on Java/Oracle (that was the initial goal to get away from HSQLDB), those noSQL or not enough mature or too small community, I don't see any DBs.
Comment 38 Lionel Elie Mamane 2019-09-13 09:32:01 UTC
And before anybody says SQLite, it does not have SQL-style static typing (it does dynamic typing), has no true date datatype (and column type; the ODBC driver kindof does its own implementation of that...), etc, etc.