View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0004914 | SymmetricDS | Bug | public | 2021-03-24 08:16 | 2021-04-05 17:50 |
| Reporter | adisman | Assigned To | |||
| Priority | high | ||||
| Status | new | Resolution | open | ||
| Product Version | 3.12.7 | ||||
| Summary | 0004914: Unable to create SYM_FILE_SNAPSHOT table on Firebird SQL Server with UTF8 Charset | ||||
| Description | If I use SQL Server Firebird 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 server's sql limit. An error already occurs with the UTF8 character set. Page size 16KB is currently the maximum SQL Server Firebird 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 Reproduce | 1) Install SymmetricDS 12.7 2) Install SQL Firebird 3.08 3) Create a DB with charset UTF8 4) Attach Symmetric to this db with charset UTF8 | ||||
| Additional Information | 2021-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) | ||||
| Tags | ddl/schema, dialect: firebird | ||||