Bug 71251 - EDITING - Embedded Firebird - Creating table from default choices via wizard fails
Summary: EDITING - Embedded Firebird - Creating table from default choices via wizard ...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.2.0.0.alpha0+ Master
Hardware: x86 (IA32) All
: high critical
Assignee: Julien Nabet
QA Contact:
URL:
Whiteboard: target:5.3.0 target:5.2.4
Keywords:
Depends on:
Blocks: 51780
  Show dependency treegraph
 
Reported: 2013-11-05 10:14 UTC by Alex Thurgood
Modified: 2016-11-12 08:50 UTC (History)
10 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Alex Thurgood 2013-11-05 10:14:40 UTC
How to reproduce :

1) Start LO Base
2) Choose to create a new embedded Firebird database.
3) Start the table creation wizard
4) Choose Invoices from the Business category of proposed default tables
5) Click Next at each step of the wizard, accepting defaults for field types.
6) At the Set Primary Key dialog, select Create Primary Key - Automatically Add Primary Key - Autovalue
7) Click on Next
8) Click on Finish (accept the defaults)

ERROR : table creation will not complete, and displays following error message :

Error code: 1

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -842
*Short integer expected
caused by
'isc_dsql_prepare'



Alex
Comment 1 Alex Thurgood 2013-11-05 10:17:19 UTC
This is a blocker if the user can't create via the UI from the default table choices provided by the application. The SQL error message is somewhat too sybilline for a normal user who will have no hope of understanding what is going wrong.


Alex
Comment 2 Alex Thurgood 2013-11-05 10:20:54 UTC
The problem still occurs even if the user does not choose to set a primary key at step 6 outlined above, however, the error message is different :

Error code: 1

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -204
*Data type unknown
*Implementation limit exceeded
*COLUMN Notes
caused by
'isc_dsql_prepare'


Alex
Comment 3 Alex Thurgood 2013-11-05 10:32:39 UTC
The problem occurs with the majority of the default tables proposed by LO in the Business category.

If you choose the Transactions table, the wizard doesn't even get to the end, try clicking on "Finish" and nothing happens, clicking on Next several times eventually gets you stuck on the Primary Key definition page of the dialog, and then you can go no further - there seems to be some focus issues here with the wizard and event firing/handling. I guess that this particular behaviour is a separate issue though.


Alex
Comment 4 robert 2013-11-08 18:03:02 UTC
I could confirm this behavior. Seems to be a problem with the datatypes.

*Short integer expected : There is no "Short inter" as type of a field in Base and isn't in Firebird. Could be Bug67179 or Bug71256.
*Implementation limit exceeded
*COLUMN Notes : There is defined a length of 32767 chars, but Firebird sets a limit to 32767 bytes. UTF-8, for example, allows 4byte for one character. So this must be limited to 8191 characters, when using UTF-8. For more content Firebird uses BLOB (BLOB SUB_TYPE 1).

Don't know if we should report a bug for every fieldtype, which is chosen wrong.
Comment 5 robert 2013-11-08 18:16:55 UTC
(In reply to comment #3)
 
> If you choose the Transactions table, the wizard doesn't even get to the
> end, try clicking on "Finish" and nothing happens, clicking on Next several
> times eventually gets you stuck on the Primary Key definition page of the
> dialog, and then you can go no further - there seems to be some focus issues
> here with the wizard and event firing/handling. I guess that this particular
> behaviour is a separate issue though.

The wizard doesn't allow to set types and format for this special table. Could be it is a problem with the Bool-field inside of this table. There is no special Bool-field in Firebird.
Comment 6 Lionel Elie Mamane 2013-11-09 01:30:27 UTC
(In reply to comment #5)
> Could be it is a problem with the Bool-field inside of this table. There is
> no special Bool-field in Firebird.

Hmm... Could we fake BOOLEAN in the driver, if necessary? (Meaning, if that's the problem). Maybe we could just do it like MySQL and at table *creation* time, just map BOOLEAN to TINYINT(1) or something like that. (And then, the table has a TINYINT(1) field, not a BOOLEAN field.)

(note: firebird 3 has/will have BOOLEAN datatype)
Comment 7 Lionel Elie Mamane 2013-11-09 01:32:41 UTC
(In reply to comment #6)

> Hmm... Could we fake BOOLEAN in the driver, if necessary? (Meaning, if
> that's the problem). Maybe we could just do it like MySQL and at table
> *creation* time, just map BOOLEAN to TINYINT(1) or something like that. (And
> then, the table has a TINYINT(1) field, not a BOOLEAN field.)\

Maybe add a CHECK clause "check (A=0 or A=1)" as suggested by http://tracker.firebirdsql.org/browse/CORE-726?focusedCommentId=20162&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#action_20162
Comment 8 Andrzej Hunt 2013-11-09 18:02:56 UTC
(In reply to comment #6)
> (In reply to comment #5)
> > Could be it is a problem with the Bool-field inside of this table. There is
> > no special Bool-field in Firebird.
> 
> Hmm... Could we fake BOOLEAN in the driver, if necessary? (Meaning, if
> that's the problem). Maybe we could just do it like MySQL and at table
> *creation* time, just map BOOLEAN to TINYINT(1) or something like that. (And
> then, the table has a TINYINT(1) field, not a BOOLEAN field.)
> 
> (note: firebird 3 has/will have BOOLEAN datatype)

It shouldn't be that hard to fake it at creation time, and reading the field as a boolean would also work fine, however there is still the issue that when reading it won't present itself as a boolean, i.e. we wouldn't have the checkboxes etc. in the UI when editing table data directly (because Base thinks it's just an integer).

However we could simply read whether there is a CHECK constraint in place every time we read a SMALLINT (that's the smallest datatype in firebird -- there's no tinyint).

(CHECK constraints (and not-nulls) are in RDB$CHECK_CONSTRAINTS, mapping to RDB$TRIGGERS, still need to figure out how to detect this specific check once there though.)
Comment 9 Lionel Elie Mamane 2013-11-09 18:12:37 UTC
(In reply to comment #8)
> (In reply to comment #6)

>> Hmm... Could we fake BOOLEAN in the driver, if necessary? (Meaning, if
>> that's the problem). Maybe we could just do it like MySQL and at table
>> *creation* time, just map BOOLEAN to TINYINT(1) or something like that. (And
>> then, the table has a TINYINT(1) field, not a BOOLEAN field.)

> It shouldn't be that hard to fake it at creation time, and reading the field
> as a boolean would also work fine, however there is still the issue that
> when reading it won't present itself as a boolean, i.e. we wouldn't have the
> checkboxes etc. in the UI when editing table data directly (because Base
> thinks it's just an integer).

I think that's OK, although not optimal.

> However we could simply read whether there is a CHECK constraint in place
> every time we read a SMALLINT (that's the smallest datatype in firebird --
> there's no tinyint).

That would be even better. We can check whether the CHECK constraint is the one that LibreOffice puts when faking BOOLEAN.
Comment 10 Gerry 2014-01-15 16:54:51 UTC
I can confirm this bug on LO 4.2 RC2. I get the same error message as reported in the bug description.

Version: 4.2.0.2, Build-ID: 601a398b803303d1a40a3299729531824fe0db56
Ubuntu 13.04
Comment 11 Björn Michaelsen 2014-01-17 00:43:33 UTC Comment hidden (obsolete)
Comment 12 ribotb 2014-04-25 07:38:31 UTC
Adding as blocker for making Firebird the default
Comment 13 Kevin Suo 2014-06-17 09:58:02 UTC
Set platform to ALL, as I can also reproduce on Windows XP SP1, version 4.3.0.0 beta2.
Comment 14 Alex Thurgood 2015-01-03 17:40:47 UTC Comment hidden (no-value)
Comment 15 Thomas Krumbein 2015-06-26 07:37:11 UTC
Can confirm even in LibO 5.0.0. RC1 - on windows 8.1

Version: 5.0.0.1
Build-ID: 9a0b23dd0ab9652e0965484934309f2d49a7758e
Gebietsschema: de-DE (de_DE)
Comment 16 Robinson Tryon (qubit) 2015-10-07 22:20:28 UTC
Dropping Severity -> critical (we've deprecated the 'blocker' value)
https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Severity#Severity_Levels

(In reply to Lionel Elie Mamane from comment #6)
> 
> (note: firebird 3 has/will have BOOLEAN datatype)

Lionel: Looks like Firebird 3 beta 2 is available --> does it fix this issue?
http://www.firebirdsql.org/en/firebird-3-0-0-beta2/
Comment 17 ribotb 2015-10-08 08:07:26 UTC
Hi,

(In reply to Robinson Tryon (qubit) from comment #16)
> 
> (In reply to Lionel Elie Mamane from comment #6)
> > 
> > (note: firebird 3 has/will have BOOLEAN datatype)
> 
Read in Firebird 3 Release Notes :

New Data Type Support :
A true BOOLEAN type (True/False/Unknown), complete with support for logical predicates

Bernard
Comment 18 Lionel Elie Mamane 2016-06-29 01:25:20 UTC
The problem with BOOLEAN will be made obsolete by the upgrade to Firebird 3, so most of the discussion in this bug is moot.

If there are other things that keep the default wizard option from succeeding, then they need to be fixed separately. I suggest to use this bug as a tracker bug and to open new bugs for each issue, and add them to this bug's "depends on".
Comment 19 Julien Nabet 2016-08-19 17:45:55 UTC
With master sources updated today, I had another error:
warn:connectivity.firebird:4079:1:connectivity/source/drivers/firebird/Statement.cxx:126: isc_dsql_execute failed
warn:connectivity.firebird:4079:1:connectivity/source/drivers/firebird/Util.cxx:52: firebird_sdbc error:
*unsuccessful metadata update
*CREATE TABLE Invoices failed
*Dynamic SQL Error
*SQL error code = -204
*Data type unknown
*Implementation limit exceeded
*COLUMN <Missing arg #1 - possibly status vector overflow>
caused by
'CREATE TABLE "Invoices" ("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY,"Notes" VARCHAR(32767),"Status" VARCHAR(50),"ShipDate" DATE,"InvoiceID" INTEGER,"ShippedTo" VARCHAR(50),"CustomerID" INTEGER,"DeliveryID" INTEGER,"EmployeeID" INTEGER,"ShippedVia" VARCHAR(50),"InvoiceDate" DATE,"Salesperson" VARCHAR(50),"ShippingCost" DOUBLE PRECISION, PRIMARY KEY  ("ID"))'

There's a pop with a stacktrace but I can't retrieve its content.
Comment 20 Lionel Elie Mamane 2016-08-19 19:43:22 UTC
(In reply to Julien Nabet from comment #19)

> "Notes" VARCHAR(32767)

The maximum length of a VARCHAR in Firebird is 32,765. That's likely the problem. What can we do about that?

 - The firebird-sdbc driver caps the length to 32765,
   even if the calling code asks for bigger.

 - We change the wizard to ask fro 32700 or 32000 or 32500,
   that is not to try to go to the extreme limit of what
   "most databases" support for VARCHAR (that is 32676)
   which will increase compatibility in general,
   not only for Firebird.
Comment 21 robert 2016-10-21 07:18:44 UTC
Following the description of Alex for this bug I tested it with Firebird 3.0.

When accepting the defaults for the table of the wizard the SQL-code won't work:
The field "Note" is defined as VARCHAR(32767)
Limit for VARCHAR and Firebird should be 32767 *bytes*, but it works if I set 32767 to 16383. 32767 bytes aren't the same as 32767 characters - specially for UTF8.

Version: 5.3.0.0.alpha0+
Build ID: e7324c5705eaa38a2c9aa0636f01a73f033ba4d6
CPU Threads: 4; OS Version: Linux 4.1; UI Render: default; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2016-10-20_06:12:58
Locale: de-DE (de_DE.UTF-8); Calc: group
Comment 22 Julien Nabet 2016-11-11 19:57:37 UTC
I submitted  a patch here:
https://gerrit.libreoffice.org/#/c/30783/

Max length is 32765 not 32767 (see http://www.firebirdsql.org/en/firebird-technical-specifications/)
Comment 23 robert 2016-11-11 20:27:06 UTC
(In reply to Julien Nabet from comment #22)
> 
> Max length is 32765 not 32767 (see
> http://www.firebirdsql.org/en/firebird-technical-specifications/)

You are right. Seems it has been changed with Firebird 2.0. Have tested the insert with 32765 - works right.
Comment 24 Commit Notification 2016-11-11 21:56:15 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=ba8a41bac3765f7b3562d357aeb13f752ea14ba4

tdf#71251: VARCHAR max length is 32765 for Firebird

It will be available in 5.3.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.
Comment 25 Julien Nabet 2016-11-11 22:21:07 UTC
Backport for 5.2 branch waiting for reviewing:
https://gerrit.libreoffice.org/#/c/30786/
Comment 26 Commit Notification 2016-11-12 08:49:50 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=489914a676ae5aadc68063d6d41de111728f2eb4&h=libreoffice-5-2

tdf#71251: VARCHAR max length is 32765 for Firebird

It will be available in 5.2.4.

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.