View Issue Details

IDProjectCategoryView StatusLast Update
0002044SymmetricDSBugpublic2019-06-19 16:44
Reporterelong Assigned Tochenson  
Prioritynormal 
Status acknowledgedResolutionopen 
Product Version3.6.0 
Summary0002044: Startup failure with MySQL UTF8 database
DescriptionWith MySQL database created with UTF-8 character set, the initialization of the database fails:

2014-10-31 12:04:34,444 WARN [receipt] [JdbcSqlTemplate] [qtp1039630400-145252] Specified key was too long; max key length is 1000 bytes. Failed to execute: CREATE TABLE `sym_file_incoming`(
    `relative_dir` VARCHAR(255) NOT NULL,
    `file_name` VARCHAR(128) NOT NULL,
    `last_event_type` CHAR(1) NOT NULL,
    `node_id` VARCHAR(50) NOT NULL,
    `file_modified_time` BIGINT,
    PRIMARY KEY (`relative_dir`, `file_name`)
)

Steps To Reproducecreate database symdb character set utf8;
Tagsinstall

Relationships

related to 0002549 confirmed Interbase Installation "key size too big" 
related to 0003997 new Failure to create all SYM_tables in utf8MB4_General_ci collation 

Activities

g_d_u_m

2015-06-04 11:48

reporter   ~0000691

Last edited: 2015-06-04 14:15

View 2 revisions

Same issue with Firebird2.5 database created with utf-8 character set:

[central-000] - Firebird21SymmetricDialect - DDL failed: CREATE TABLE "SYM_FILE_INCOMING"(
    "RELATIVE_DIR" VARCHAR(255) NOT NULL,
    "FILE_NAME" VARCHAR(128) NOT NULL,
    "LAST_EVENT_TYPE" CHAR(1) NOT NULL,
    "NODE_ID" VARCHAR(50) NOT NULL,
    "FILE_MODIFIED_TIME" BIGINT,
    PRIMARY KEY ("RELATIVE_DIR", "FILE_NAME")
)
[central-000] - JdbcSqlTemplate - GDS Exception. 335544351. unsuccessful metadata update
key size exceeds implementation restriction for index "RDB$PRIMARY253"
Reason: unsuccessful metadata update
key size exceeds implementation restriction for index "RDB$PRIMARY253". Failed to execute: CREATE TABLE "SYM_FILE_INCOMING"(
    "RELATIVE_DIR" VARCHAR(255) NOT NULL,
    "FILE_NAME" VARCHAR(128) NOT NULL,
    "LAST_EVENT_TYPE" CHAR(1) NOT NULL,
    "NODE_ID" VARCHAR(50) NOT NULL,
    "FILE_MODIFIED_TIME" BIGINT,
    PRIMARY KEY ("RELATIVE_DIR", "FILE_NAME")
)
[central-000] - AbstractSymmetricEngine - An error occurred while starting SymmetricDS
org.jumpmind.db.sql.SqlException: GDS Exception. 335544351. unsuccessful metadata update
key size exceeds implementation restriction for index "RDB$PRIMARY253"
Reason: unsuccessful metadata update
key size exceeds implementation restriction for index "RDB$PRIMARY253"
        at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:288)
        at org.jumpmind.db.platform.firebird.FirebirdJdbcSqlTemplate.translate(FirebirdJdbcSqlTemplate.java:59)

Environment: Windows 7 64bits, firebird 2.5.4 x64, symmetricds-server-3.7.19, jre-7u80-windows-x64

g_d_u_m

2015-06-30 11:13

reporter   ~0000694

This looks like an issue with the SYM_FILE_INCOMING primary key which is made of a VARCHAR(255) plus a VARCHAR(128).

Because each UTF8 character can use up to 4 bytes, this leads to a 1532-byte long key ((128+255)*4), which is beyond the limit (one quarter of the page size for Firebird).

Increasing the default pagesize from 4096 to 8192 or 16384 solves this issue, but then it fails later for another primary key which is even longer.

chenson

2016-11-17 18:06

administrator   ~0000910

Using prefix indexes work around this issue. As of 3.8 you can pre-run this script to get SymmetricDS working

CREATE TABLE `sym_file_incoming`(
    `relative_dir` VARCHAR(255) NOT NULL,
    `file_name` VARCHAR(128) NOT NULL,
    `last_event_type` CHAR(1) NOT NULL,
    `node_id` VARCHAR(50) NOT NULL,
    `file_modified_time` BIGINT,
    PRIMARY KEY (`relative_dir`(50), `file_name`(50))
);

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` DATETIME NOT NULL,
    `last_update_by` VARCHAR(50) NULL,
    `create_time` DATETIME NOT NULL,
    PRIMARY KEY (`trigger_id`, `router_id`, `relative_dir`(50), `file_name`(50)));


CREATE TABLE `sym_grouplet_link`(
    `grouplet_id` VARCHAR(50) NOT NULL,
    `external_id` VARCHAR(255) NOT NULL,
    `create_time` DATETIME NOT NULL,
    `last_update_by` VARCHAR(50) NULL,
    `last_update_time` DATETIME NOT NULL,
    PRIMARY KEY (`grouplet_id`, `external_id`(50))
);


CREATE TABLE `sym_parameter`(
    `external_id` VARCHAR(255) NOT NULL,
    `node_group_id` VARCHAR(50) NOT NULL,
    `param_key` VARCHAR(80) NOT NULL,
    `param_value` MEDIUMTEXT NULL,
    `create_time` DATETIME,
    `last_update_by` VARCHAR(50) NULL,
    `last_update_time` DATETIME,
    PRIMARY KEY (`external_id`(50), `node_group_id`, `param_key`(50))
);

CREATE TABLE `sym_registration_redirect`(
    `registrant_external_id` VARCHAR(255) NOT NULL,
    `registration_node_id` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`registrant_external_id`(128))
);

CREATE TABLE `sym_registration_request`(
    `node_group_id` VARCHAR(50) NOT NULL,
    `external_id` VARCHAR(255) NOT NULL,
    `status` CHAR(2) NOT NULL,
    `host_name` VARCHAR(60) NOT NULL,
    `ip_address` VARCHAR(50) NOT NULL,
    `attempt_count` INTEGER DEFAULT 0,
    `registered_node_id` VARCHAR(50) NULL,
    `error_message` MEDIUMTEXT NULL,
    `create_time` DATETIME NOT NULL,
    `last_update_by` VARCHAR(50) NULL,
    `last_update_time` DATETIME NOT NULL,
    PRIMARY KEY (`node_group_id`, `external_id`(50), `create_time`)
);

CREATE INDEX `sym_idx_reg_req_1` ON `sym_registration_request` (`node_group_id`, `external_id`(50), `status`, `host_name`(50), `ip_address`(50));

Issue History

Date Modified Username Field Change
2014-10-31 16:27 elong New Issue
2014-12-10 14:48 chenson Assigned To => josh-a-hicks
2014-12-10 14:48 chenson Status new => assigned
2014-12-10 14:50 chenson Target Version 3.7.0 =>
2015-05-10 17:30 chenson Assigned To josh-a-hicks => chenson
2015-05-10 17:30 chenson Target Version => 3.8.0
2015-06-04 11:48 g_d_u_m Note Added: 0000691
2015-06-04 14:15 g_d_u_m Note Edited: 0000691 View Revisions
2015-06-19 17:05 chenson Target Version 3.8.0 =>
2015-06-30 11:13 g_d_u_m Note Added: 0000694
2016-04-07 12:17 admin Relationship added related to 0002549
2016-11-17 18:06 chenson Note Added: 0000910
2016-11-17 19:14 chenson Target Version => 3.9.0
2017-06-06 13:42 chenson Status assigned => acknowledged
2017-06-06 13:42 chenson Target Version 3.9.0 =>
2019-04-23 13:08 elong Tag Attached: install
2019-06-19 16:44 elong Relationship added related to 0003997