[Libreoffice-bugs] [Bug 127040] HSQLDB to Firebird automated migration failure when record size exceeds 64k

2019-09-13 Thread bugzilla-daemon
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

2019-09-13 Thread bugzilla-daemon
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

2019-09-13 Thread bugzilla-daemon
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

2019-09-13 Thread bugzilla-daemon
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

2019-09-13 Thread bugzilla-daemon
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

2019-09-13 Thread bugzilla-daemon
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

2019-09-13 Thread bugzilla-daemon
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

2019-09-12 Thread bugzilla-daemon
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

2019-09-12 Thread bugzilla-daemon
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

2019-09-12 Thread bugzilla-daemon
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

2019-09-12 Thread bugzilla-daemon
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

2019-09-10 Thread bugzilla-daemon
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

2019-09-10 Thread bugzilla-daemon
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

2019-09-10 Thread bugzilla-daemon
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

2019-09-10 Thread bugzilla-daemon
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

2019-09-10 Thread bugzilla-daemon
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

2019-09-10 Thread bugzilla-daemon
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

2019-09-10 Thread bugzilla-daemon
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