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:
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
@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.
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.
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.
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!
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.
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.
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
@Tamas, I thought you might be interested in this issue...
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.
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;
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
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.
Great work, ty!
(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.
In that case maybe they shouldn't be using Firebird in the first place...
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.
(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.
Ah, I didn't see that. Thanks for the link. I agree, that's the most sensible approach right now.
Let's rephrase the title then.
then let's put this one to RESOLVED/NOTOURBUG since we don't control Firebird.
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...
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.
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.
@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.
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.
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);
(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 ?).
(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.
Indeed, setting back to NOTOURBUG
How can you mark it resolved when it's a critical system failure, even if it's not your bug?
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.
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.
(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.
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.
(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.
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.
I had a chat with Alex about slide 28 of this: https://firebirdsql.org/file/community/conference-2019/1_firebird_on_the_road_from4_to_5.pdf "Longer (unlimited) record size" would apparently get rid of this compatibility issue.
(In reply to Buovjaga from comment #39) > I had a chat with Alex about slide 28 of this: > https://firebirdsql.org/file/community/conference-2019/ > 1_firebird_on_the_road_from4_to_5.pdf > > "Longer (unlimited) record size" would apparently get rid of this > compatibility issue. Firebird 3.0 has been released in 2016, Firebird 4.0 is still beta. I think we'll be able to talk about FB 5.0 in 2022...