[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 --- Comment #40 from Julien Nabet --- (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... -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 Buovjaga changed: What|Removed |Added CC||todven...@suomi24.fi --- Comment #39 from Buovjaga --- 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. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 --- Comment #38 from Lionel Elie Mamane --- 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. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 --- Comment #37 from Julien Nabet --- (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. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 --- Comment #36 from p...@sonicviz.com --- 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. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 --- Comment #35 from Lionel Elie Mamane --- (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. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 --- Comment #34 from p...@sonicviz.com --- 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. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 --- Comment #33 from Julien Nabet --- 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. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 --- Comment #32 from p...@sonicviz.com --- How can you mark it resolved when it's a critical system failure, even if it's not your bug? -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 Alex Thurgood changed: What|Removed |Added Resolution|--- |NOTOURBUG Status|NEW |RESOLVED --- Comment #31 from Alex Thurgood --- Indeed, setting back to NOTOURBUG -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 --- Comment #30 from Julien Nabet --- (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. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 --- Comment #29 from Alex Thurgood --- (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 ?). -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 --- Comment #28 from Julien Nabet --- 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); -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 --- Comment #27 from Julien Nabet --- 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. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 --- Comment #26 from Alex Thurgood --- @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. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 Julien Nabet changed: What|Removed |Added Status|RESOLVED|NEW Resolution|NOTOURBUG |--- --- Comment #25 from Julien Nabet --- 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. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 Alex Thurgood changed: What|Removed |Added CC||lio...@mamane.lu --- Comment #24 from Alex Thurgood --- >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. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 --- Comment #23 from Alex Thurgood --- 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... -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 Julien Nabet changed: What|Removed |Added Status|NEW |RESOLVED Resolution|--- |NOTOURBUG --- Comment #22 from Julien Nabet --- then let's put this one to RESOLVED/NOTOURBUG since we don't control Firebird. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k
https://bugs.documentfoundation.org/show_bug.cgi?id=127040 Julien Nabet changed: What|Removed |Added Summary|HSQLDB to Firebird |HSQLDB to Firebird |automated migration failure |automated migration failure |when IDENTITY sequence has |when record size exceeds |altered start number|64k --- Comment #21 from Julien Nabet --- Let's rephrase the title then. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs