View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0004914SymmetricDSBugpublic2021-03-24 08:162025-04-09 19:34
Reporteradisman Assigned To 
Prioritylow 
Status newResolutionopen 
Product Version3.12.7 
Summary0004914: Unable to create SYM_FILE_SNAPSHOT table on Firebird 3x with UTF8 Charset and a small page size
DescriptionIf I use Firebird database version 3.08 with a page size of 16 kB and Charset UTF8 Symmetric DS will try to create a SYM_FILE_SNAPSHOT table after startup.
This table attempts to create a primary key over these 4 columns:

ALTER TABLE SYM_FILE_SNAPSHOT ADD PRIMARY KEY (TRIGGER_ID, ROUTER_ID, RELATIVE_DIR, FILE_NAME);
    TRIGGER_ID VARCHAR (128) NOT NULL,
    ROUTER_ID VARCHAR (50) NOT NULL,
    RELATIVE_DIR VARCHAR (255) NOT NULL,
    FILE_NAME VARCHAR (128) NOT NULL,


Creating an index over these four columns with that length and UTF8 character set fails because such an index exceeds the maximum size.

On an ANSI character set database, this primary key can be created because it does not exceed the Firebird limit.
An error already occurs with the UTF8 character set.

Page size 16KB is currently the maximum for the Firebird database version 3.0X

In the engine * .properties file I have set:
db.connection.properties = encoding = utf8

Because I don't need file synchronization for this case, I tried to turn it off, but the symmetric server creates these tables the same way.
Steps To Reproduce1) Install SymmetricDS 12.7
2) Install Firebird 3.08
3) Create a database/catalog with the UTF8 charset
4) Attach Symmetric to this database
Additional Information2021-03-23 22:48:31,446 INFO [finetest_corp-100] [Firebird21SymmetricDialect] [symmetric-engine-startup-3] There are SymmetricDS tables that needed altered
2021-03-23 22:48:31,486 WARN [finetest_corp-100] [V10Transaction] [symmetric-engine-startup-3] Commit not completed, state was COMMITTING StackTraceKey.init [RuntimeException:288359038] java.lang.RuntimeException: Commit not completed
    at org.firebirdsql.gds.ng.wire.version10.V10Transaction.commit(V10Transaction.java:94)
    at org.firebirdsql.jca.FBManagedConnection.internalCommit(FBManagedConnection.java:640)
    at org.firebirdsql.jca.FBLocalTransaction.internalCommit(FBLocalTransaction.java:193)
    at org.firebirdsql.jca.FBLocalTransaction.commit(FBLocalTransaction.java:167)
    at org.firebirdsql.jdbc.InternalTransactionCoordinator$AutoCommitCoordinator.statementCompleted(InternalTransactionCoordinator.java:373)
    at org.firebirdsql.jdbc.InternalTransactionCoordinator.statementCompleted(InternalTransactionCoordinator.java:134)
    at org.firebirdsql.jdbc.FBStatement.notifyStatementCompleted(FBStatement.java:259)
    at org.firebirdsql.jdbc.FBStatement.notifyStatementCompleted(FBStatement.java:254)
    at org.firebirdsql.jdbc.FBStatement.executeImpl(FBStatement.java:843)
    at org.firebirdsql.jdbc.FBStatement.execute(FBStatement.java:826)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:446)
    at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:431)
    at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:521)
    at org.jumpmind.db.sql.JdbcSqlTemplate.update(JdbcSqlTemplate.java:431)
    at org.jumpmind.db.sql.SqlScript.execute(SqlScript.java:114)
    at org.jumpmind.symmetric.db.AbstractSymmetricDialect.createOrAlterTablesIfNecessary(AbstractSymmetricDialect.java:517)
    at org.jumpmind.symmetric.db.AbstractSymmetricDialect.initTablesAndDatabaseObjects(AbstractSymmetricDialect.java:157)
    at org.jumpmind.symmetric.AbstractSymmetricEngine.setupDatabase(AbstractSymmetricEngine.java:513)
    at org.jumpmind.symmetric.AbstractSymmetricEngine.setup(AbstractSymmetricEngine.java:487)
    at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:660)
    at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:649)
    at org.jumpmind.symmetric.ClientSymmetricEngine.start(ClientSymmetricEngine.java:277)
    at org.jumpmind.symmetric.web.SymmetricEngineHolder$EngineStarter.run(SymmetricEngineHolder.java:618)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:834)

2021-03-23 22:48:31,493 WARN [finetest_corp-100] [Firebird21SymmetricDialect] [symmetric-engine-startup-3] DDL failed: CREATE TABLE "SYM_FILE_SNAPSHOT"(
    "TRIGGER_ID" VARCHAR(128) NOT NULL,
    "ROUTER_ID" VARCHAR(50) NOT NULL,
    "RELATIVE_DIR" VARCHAR(255) NOT NULL,
    "FILE_NAME" VARCHAR(128) NOT NULL,
    "CHANNEL_ID" VARCHAR(128) DEFAULT 'filesync' NOT NULL,
    "RELOAD_CHANNEL_ID" VARCHAR(128) DEFAULT 'filesync_reload' NOT NULL,
    "LAST_EVENT_TYPE" CHAR(1) NOT NULL,
    "CRC32_CHECKSUM" BIGINT,
    "FILE_SIZE" BIGINT,
    "FILE_MODIFIED_TIME" BIGINT,
    "LAST_UPDATE_TIME" TIMESTAMP NOT NULL,
    "LAST_UPDATE_BY" VARCHAR(50),
    "CREATE_TIME" TIMESTAMP NOT NULL,
    PRIMARY KEY ("TRIGGER_ID", "ROUTER_ID", "RELATIVE_DIR", "FILE_NAME")
)
2021-03-23 22:48:31,494 WARN [finetest_corp-100] [JdbcSqlTemplate] [symmetric-engine-startup-3] Resource Exception. unsuccessful metadata update; key size exceeds implementation restriction for index "RDB$PRIMARY106" [SQLState:42000, ISC error code:335544351]
Reason: unsuccessful metadata update; key size exceeds implementation restriction for index "RDB$PRIMARY106" [SQLState:42000, ISC error code:335544351], error code: HY000
Reason: unsuccessful metadata update; key size exceeds implementation restriction for index "RDB$PRIMARY106" [SQLState:42000, ISC error code:335544351]
Reason: unsuccessful metadata update; key size exceeds implementation restriction for index "RDB$PRIMARY106" [SQLState:42000, ISC error code:335544351]. Failed to execute: CREATE TABLE "SYM_FILE_SNAPSHOT"(
    "TRIGGER_ID" VARCHAR(128) NOT NULL,
    "ROUTER_ID" VARCHAR(50) NOT NULL,
    "RELATIVE_DIR" VARCHAR(255) NOT NULL,
    "FILE_NAME" VARCHAR(128) NOT NULL,
    "CHANNEL_ID" VARCHAR(128) DEFAULT 'filesync' NOT NULL,
    "RELOAD_CHANNEL_ID" VARCHAR(128) DEFAULT 'filesync_reload' NOT NULL,
    "LAST_EVENT_TYPE" CHAR(1) NOT NULL,
    "CRC32_CHECKSUM" BIGINT,
    "FILE_SIZE" BIGINT,
    "FILE_MODIFIED_TIME" BIGINT,
    "LAST_UPDATE_TIME" TIMESTAMP NOT NULL,
    "LAST_UPDATE_BY" VARCHAR(50),
    "CREATE_TIME" TIMESTAMP NOT NULL,
    PRIMARY KEY ("TRIGGER_ID", "ROUTER_ID", "RELATIVE_DIR", "FILE_NAME")
)
2021-03-23 22:48:31,494 ERROR [finetest_corp-100] [AbstractSymmetricEngine] [symmetric-engine-startup-3] An error occurred while starting SymmetricDS StackTraceKey.init [SqlException:1861651526] org.jumpmind.db.sql.SqlException: Resource Exception. unsuccessful metadata update; key size exceeds implementation restriction for index "RDB$PRIMARY106" [SQLState:42000, ISC error code:335544351]
Reason: unsuccessful metadata update; key size exceeds implementation restriction for index "RDB$PRIMARY106" [SQLState:42000, ISC error code:335544351], error code: HY000
Reason: unsuccessful metadata update; key size exceeds implementation restriction for index "RDB$PRIMARY106" [SQLState:42000, ISC error code:335544351]
Reason: unsuccessful metadata update; key size exceeds implementation restriction for index "RDB$PRIMARY106" [SQLState:42000, ISC error code:335544351]
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:313)
    at org.jumpmind.db.platform.firebird.FirebirdJdbcSqlTemplate.translate(FirebirdJdbcSqlTemplate.java:63)
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:302)
    at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:523)
    at org.jumpmind.db.sql.JdbcSqlTemplate.update(JdbcSqlTemplate.java:431)
    at org.jumpmind.db.sql.SqlScript.execute(SqlScript.java:114)
    at org.jumpmind.symmetric.db.AbstractSymmetricDialect.createOrAlterTablesIfNecessary(AbstractSymmetricDialect.java:517)
    at org.jumpmind.symmetric.db.AbstractSymmetricDialect.initTablesAndDatabaseObjects(AbstractSymmetricDialect.java:157)
    at org.jumpmind.symmetric.AbstractSymmetricEngine.setupDatabase(AbstractSymmetricEngine.java:513)
    at org.jumpmind.symmetric.AbstractSymmetricEngine.setup(AbstractSymmetricEngine.java:487)
    at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:660)
    at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:649)
    at org.jumpmind.symmetric.ClientSymmetricEngine.start(ClientSymmetricEngine.java:277)
    at org.jumpmind.symmetric.web.SymmetricEngineHolder$EngineStarter.run(SymmetricEngineHolder.java:618)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.firebirdsql.jdbc.FBSQLException: Resource Exception. unsuccessful metadata update; key size exceeds implementation restriction for index "RDB$PRIMARY106" [SQLState:42000, ISC error code:335544351]
Reason: unsuccessful metadata update; key size exceeds implementation restriction for index "RDB$PRIMARY106" [SQLState:42000, ISC error code:335544351], error code: HY000
Reason: unsuccessful metadata update; key size exceeds implementation restriction for index "RDB$PRIMARY106" [SQLState:42000, ISC error code:335544351]
Reason: unsuccessful metadata update; key size exceeds implementation restriction for index "RDB$PRIMARY106" [SQLState:42000, ISC error code:335544351]
    at org.firebirdsql.jdbc.InternalTransactionCoordinator$AutoCommitCoordinator.statementCompleted(InternalTransactionCoordinator.java:378)
Tagsddl/schema, dialect: firebird

Activities

adisman

2021-04-05 17:50

reporter   ~0001910

To avoid exceeding the index size limit, it is possible to work around this so that all four columns contained in the primary key will not have a collate "UNICODE_CI_AI", which needs up to 6 bytes per character, but a collate "UTF8", which then needs 4 bytes per character.

pbelov

2025-04-09 19:34

manager   ~0002799

Workaround is available from Firebase: https://www.firebirdsql.org/refdocs/langrefupd21-ddl-database.html

Issue History

Date Modified Username Field Change
2021-03-24 08:16 adisman New Issue
2021-04-05 17:42 adisman Tag Attached: dialect: firebird
2021-04-05 17:42 adisman Tag Attached: ddl/schema
2021-04-05 17:50 adisman Note Added: 0001910
2025-04-09 19:34 pbelov Priority high => low
2025-04-09 19:34 pbelov Summary Unable to create SYM_FILE_SNAPSHOT table on Firebird SQL Server with UTF8 Charset => Unable to create SYM_FILE_SNAPSHOT table on Firebird 3x with UTF8 Charset and a small page size
2025-04-09 19:34 pbelov Description Updated View Revisions
2025-04-09 19:34 pbelov Steps to Reproduce Updated View Revisions
2025-04-09 19:34 pbelov Note Added: 0002799