"unknown ISC error 336068726" error when creating a table with a UNIQUE CONSTRAINT containing a UTF8 column

[Luciano Rodrigues Nunes Mendes]

Hi Guys,

I'm trying to create the following table in a database (PAGE_SIZE 8192 DEFAULT CHARACTER SET UTF8) but the "unknown ISC error 336068726" error happens because I'm trying to create a table with a UNIQUE CONSTRAINT containing a UTF8 column (LINK).

This issue does not happen if the character set of this column (LINK) is WIN1252 for instance.

Could you tell if I'm doing something wrong or is this a Firebird 3.0.9 bug?

CREATE TABLE FEEDITEMS
(
  ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  FEED_ID BIGINT NOT NULL,
  TITLE VARCHAR(1024) CHARACTER SET UTF8,
  LINK VARCHAR(1024) CHARACTER SET UTF8,  
  PUBLISH_DATE TIMESTAMP DEFAULT LOCALTIMESTAMP NOT NULL,
  VISIBLE BOOLEAN DEFAULT TRUE NOT NULL,
  CONSTRAINT FEEDITEMS_PK PRIMARY KEY (ID),
  CONSTRAINT FEEDITEMS_UK_1 UNIQUE (FEED_ID, LINK)
);

Thanks in advanced,

Luciano

[Dmitry Yemanov]

15.04.2022 17:35, Luciano Rodrigues Nunes Mendes wrote:
>
> I'm trying to create the following table in a database (PAGE_SIZE 8192
> DEFAULT CHARACTER SET UTF8) but the "unknown ISC error 336068726" error
> happens because I'm trying to create a table with a UNIQUE CONSTRAINT
> containing a UTF8 column (LINK).
>
> This issue does not happen if the character set of this column (LINK) is
> WIN1252 for instance.
>
> Could you tell if I'm doing something wrong or is this a Firebird 3.0.9 bug?

Max index key length is limited by PAGE_SIZE / 4 in all modern FB
versions. In your case it means 2KB.

> CREATE TABLE FEEDITEMS
> (
>   ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
>   FEED_ID BIGINT NOT NULL,
>   TITLE VARCHAR(1024) CHARACTER SET UTF8,
>   LINK VARCHAR(1024) CHARACTER SET UTF8,

VARCHAR(1024) CHARACTER SET UTF8 occupies up to 4KB, thus violating the
aforementioned limit.


Dmitry

[Mark Rotteveel]

On 15-04-2022 16:35, Luciano Rodrigues Nunes Mendes wrote:
> I'm trying to create the following table in a database (PAGE_SIZE 8192
> DEFAULT CHARACTER SET UTF8) but the "unknown ISC error 336068726" error
> happens because I'm trying to create a table with a UNIQUE CONSTRAINT
> containing a UTF8 column (LINK).

That is error "key size too big for index <index_name>", which means you
are missing firebird.msg (or the equivalent of your connection library).

> This issue does not happen if the character set of this column (LINK) is
> WIN1252 for instance.
>
> Could you tell if I'm doing something wrong or is this a Firebird 3.0.9 bug?
>
> CREATE TABLE FEEDITEMS
> (
>   ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
>   FEED_ID BIGINT NOT NULL,
>   TITLE VARCHAR(1024) CHARACTER SET UTF8,
>   LINK VARCHAR(1024) CHARACTER SET UTF8,
>   PUBLISH_DATE TIMESTAMP DEFAULT LOCALTIMESTAMP NOT NULL,
>   VISIBLE BOOLEAN DEFAULT TRUE NOT NULL,
>   CONSTRAINT FEEDITEMS_PK PRIMARY KEY (ID),
>   CONSTRAINT FEEDITEMS_UK_1 UNIQUE (FEED_ID, LINK)
> );

The maximum key size is ~1/4 of the page size (minus some overhead), the
size of the key FEEDITEMS_UK_1 is 8 bytes + 4096 (plus maybe some bytes
for index internals), which far exceeds the maximum key size of ~2048
bytes with page_size 8192. See also
https://www.firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref30/fblangref30-ddl-index.html#fblangref30-ddl-idx-limits

Given the maximum page size on Firebird 3.0 is 16384, the maximum index
key size is ~4096, so with Firebird 3.0 you either need to:

1. Reduce the size of LINK and maybe increase the page size to 16384
2. Use a different character set than UTF8 (e.g. UNICODE_FSS so it uses
max 3 bytes per character instead of 4 bytes, or WIN1252 so it uses 1
byte per character, or use NONE and carefully control how you read and
write it)

Mark
--
Mark Rotteveel