View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0002044 | SymmetricDS | Bug | public | 2014-10-31 16:27 | 2019-06-19 16:44 |
Reporter | elong | Assigned To | chenson | ||
Priority | normal | ||||
Status | acknowledged | Resolution | open | ||
Product Version | 3.6.0 | ||||
Summary | 0002044: Startup failure with MySQL UTF8 database | ||||
Description | With 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 Reproduce | create database symdb character set utf8; | ||||
Tags | install | ||||
|
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 |
|
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. |
|
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)); |
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 |