View Issue Details

IDProjectCategoryView StatusLast Update
0000792SymmetricDSImprovementpublic2014-02-03 01:52
Reportertregnago Assigned Tochenson  
Prioritynormal 
Status closedResolutionfixed 
Target Version3.1.4Fixed in Version3.1.4 
Summary0000792: Support tables with names greater than 50 chars
DescriptionTables with very long names (greater than 50 chars) generates an exception and blocks syncronization.
Additional InformationI think that this is because system fields are all defined as char(50)
TagsNo tags attached.

Activities

chenson

2012-08-30 15:03

administrator   ~0000074

Can you give me an example? What database platform? Is the table configured to sync using wild cards or does the table just exist in the database?

tregnago

2012-08-30 15:16

reporter   ~0000076

In PostgreSQL 8.4, tables are included with wildcards, with something like that:
insert into sym_trigger
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('data','*','data',current_timestamp,current_timestamp);

Troubles begins with tables with crazy names like:
anagrafiche_schedeinformativerischi_checklist_questions

thanks

tregnago

2012-09-03 15:33

reporter  

symmetric-schema.xml.diff (10,573 bytes)   
Index: src/main/resources/symmetric-schema.xml
===================================================================
--- src/main/resources/symmetric-schema.xml	(revision 6229)
+++ src/main/resources/symmetric-schema.xml	(working copy)
@@ -206,7 +206,7 @@
         <column name="trigger_id" type="VARCHAR" size="50" required="true" primaryKey="true"  description="Unique identifier for a trigger." />
         <column name="source_catalog_name" type="VARCHAR" size="50"  description="Optional name for the catalog the configured table is in." />
         <column name="source_schema_name" type="VARCHAR" size="50"  description="Optional name for the schema a configured table is in." />
-        <column name="source_table_name" type="VARCHAR" size="50" required="true"  description="The name of the source table that will have a trigger installed to watch for data changes." />
+        <column name="source_table_name" type="VARCHAR" size="150" required="true"  description="The name of the source table that will have a trigger installed to watch for data changes." />
         <column name="channel_id" type="VARCHAR" size="20" required="true"  description="The channel_id of the channel that data changes will flow through." />
         <column name="sync_on_update" type="BOOLEANINT" size="1" required="true" default="1"  description="Whether or not to install an update trigger." />
         <column name="sync_on_insert" type="BOOLEANINT" size="1" required="true" default="1"  description="Whether or not to install an insert trigger." />
@@ -236,7 +236,7 @@
         <column name="router_id" type="VARCHAR" size="50" required="true" primaryKey="true"  description="Unique description of a specific router" />
         <column name="target_catalog_name" type="VARCHAR" size="50"  description="Optional name for the catalog a target table is in. Only use this if the target table is not in the default catalog." />
         <column name="target_schema_name" type="VARCHAR" size="50"  description="Optional name of the schema a target table is in. On use this if the target table is not in the default schema." />
-        <column name="target_table_name" type="VARCHAR" size="50"  description="Optional name for a target table.  Only use this if the target table name is different than the source." />
+        <column name="target_table_name" type="VARCHAR" size="150"  description="Optional name for a target table.  Only use this if the target table name is different than the source." />
         <column name="source_node_group_id" type="VARCHAR" size="50" required="true"  description="Routers with this node_group_id will install triggers that are mapped to this router." />
         <column name="target_node_group_id" type="VARCHAR" size="50" required="true"  description="The node_group_id for nodes to route data to.  Note that routing can be further narrowed down by the configured router_type and router_expression." />
         <column name="router_type" type="VARCHAR" size="50"  description="The name of a specific type of router.  Out of the box routers are 'default','column','bsh', and 'subselect.'  Custom routers can be configured as extension points." />
@@ -287,7 +287,7 @@
         <column name="external_id" type="VARCHAR" size="50" description="A domain-specific identifier for context within the local system. For example, the retail store number. "/>
         <column name="status" type="CHAR" size="2"  required="true"  description="The current status of the registration attempt.  Valid statuses are NR (not registered), IG (ignored), OK (sucessful)" />
         <column name="host_name" type="VARCHAR" size="60" required="true"  description="The host name of a workstation or server. If more than one instance of SymmetricDS runs on the same server, then this value can be a 'server id' specified by -Druntime.symmetric.cluster.server.id" />
-        <column name="ip_address" type="VARCHAR" size="21" required="true"  description="The ip address for the host." />
+        <column name="ip_address" type="VARCHAR" size="50" required="true"  description="The ip address for the host." />
         <column name="attempt_count" type="INTEGER" default="0" description="The number of registration attempts." />
         <column name="registered_node_id" type="VARCHAR" size="50" description="A unique identifier for a node." />
         <column name="create_time" type="TIMESTAMP" required="true"  description="Timestamp when this entry was created." />
@@ -308,7 +308,7 @@
     <table name="trigger_hist" description="A history of a table's definition and the trigger used to capture data from the table. When a database trigger captures a data change, it references a trigger_hist entry so it is possible to know which columns the data represents. trigger_hist entries are made during the sync trigger process, which runs at each startup, each night in the syncTriggersJob, or any time the syncTriggers() JMX method is manually invoked. A new entry is made when a table definition or a trigger definition is changed, which causes a database trigger to be created or rebuilt.">
         <column name="trigger_hist_id" type="INTEGER" required="true" primaryKey="true" autoIncrement="true"  description="Unique identifier for a trigger_hist entry" />
         <column name="trigger_id" type="VARCHAR" size="50" required="true"  description="Unique identifier for a trigger" />
-        <column name="source_table_name" type="VARCHAR" size="50" required="true"  description="The name of the source table that will have a trigger installed to watch for data changes." />
+        <column name="source_table_name" type="VARCHAR" size="150" required="true"  description="The name of the source table that will have a trigger installed to watch for data changes." />
         <column name="source_catalog_name" type="VARCHAR" size="50"  description="The catalog name where the source table resides." />
         <column name="source_schema_name" type="VARCHAR" size="50"  description="The schema name where the source table resides." />
         <column name="name_for_update_trigger" type="VARCHAR" size="50" required="true"  description="The name used when the insert trigger was created." />
@@ -326,7 +326,7 @@
     
     <table name="data" description="The captured data change that occurred to a row in the database. Entries in data are created by database triggers.">
         <column name="data_id" type="BIGINT" required="true" primaryKey="true" autoIncrement="true"  description="Unique identifier for a data." />
-        <column name="table_name" type="VARCHAR" size="50" required="true"  description="The name of the table in which a change occurred that this entry records." />
+        <column name="table_name" type="VARCHAR" size="150" required="true"  description="The name of the table in which a change occurred that this entry records." />
         <column name="event_type" type="CHAR" size="1" required="true"  description="The type of event captured by this entry. For triggers, this is the change that occurred, which is 'I' for insert, 'U' for update, or 'D' for delete. Other events include: 'R' for reloading the entire table (or subset of the table) to the node; 'S' for running dynamic SQL at the node, which is used for adhoc administration." />
         <column name="row_data" type="LONGVARCHAR"  description="The captured data change from the synchronized table. The column values are stored in comma-separated values (CSV) format." />
         <column name="pk_data" type="LONGVARCHAR"  description="The primary key values of the captured data change from the synchronized table. This data is captured for updates and deletes. The primary key values are stored in comma-separated values (CSV) format." />
@@ -512,7 +512,7 @@
         <column name="failed_line_number" type="BIGINT" required="true" default="0" description="The current line number in the CSV for this batch that failed." />
         <column name="target_catalog_name" type="VARCHAR" size="50" description="The catalog name for the table being loaded." />
         <column name="target_schema_name" type="VARCHAR" size="50" description="The schema name for the table being loaded." />
-        <column name="target_table_name" type="VARCHAR" size="50" required="true" description="The table name for the table being loaded." />
+        <column name="target_table_name" type="VARCHAR" size="150" required="true" description="The table name for the table being loaded." />
         <column name="event_type" type="CHAR" size="1" required="true" description="The type of event captured by this entry. For triggers, this is the change that occurred, which is 'I' for insert, 'U' for update, or 'D' for delete. Other events include: 'R' for reloading the entire table (or subset of the table) to the node; 'S' for running dynamic SQL at the node, which is used for adhoc administration." />
         <column name="binary_encoding" type="VARCHAR" size="10" required="true" default="HEX" description="The type of encoding the source system used for encoding binary data." />
         <column name="column_names" type="LONGVARCHAR" required="true"  description="The column names defined on the table. The column names are stored in comma-separated values (CSV) format." />
@@ -545,7 +545,7 @@
         <column name="target_node_group_id" type="VARCHAR" size="50" required="true" description="The destination node group for the filter." />
         <column name="target_catalog_name" type="VARCHAR" size="50"  description="Optional name for the catalog the configured table is in." />
         <column name="target_schema_name" type="VARCHAR" size="50"  description="Optional name for the schema a configured table is in." />
-        <column name="target_table_name" type="VARCHAR" size="50" required="true"  description="The name of the target table that will trigger the bsh filter." />
+        <column name="target_table_name" type="VARCHAR" size="150" required="true"  description="The name of the target table that will trigger the bsh filter." />
         <column name="filter_on_update" type="BOOLEANINT" size="1" required="true" default="1"  description="Whether or not the filter should apply on an update." />
         <column name="filter_on_insert" type="BOOLEANINT" size="1" required="true" default="1"  description="Whether or not the filter should apply on an insert." />
         <column name="filter_on_delete" type="BOOLEANINT" size="1" required="true" default="1"  description="Whether or not the filter should apply on a delete." />
symmetric-schema.xml.diff (10,573 bytes)   

tregnago

2012-09-03 15:33

reporter  

PostgreSqlDdlBuilder.java.diff (869 bytes)   
Index: src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlDdlBuilder.java
===================================================================
--- src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlDdlBuilder.java	(revision 6229)
+++ src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlDdlBuilder.java	(working copy)
@@ -35,6 +35,7 @@
 import org.jumpmind.db.model.Database;
 import org.jumpmind.db.model.IIndex;
 import org.jumpmind.db.model.Table;
 import org.jumpmind.db.platform.AbstractDdlBuilder;
 
 /*
@@ -46,7 +47,7 @@
         // this is the default length though it might be changed when building
         // PostgreSQL
         // in file src/include/postgres_ext.h
-        databaseInfo.setMaxIdentifierLength(31);
+        databaseInfo.setMaxIdentifierLength(63);
 
         databaseInfo.setRequiresSavePointsInTransaction(true);
 

tregnago

2012-09-03 15:38

reporter   ~0000079

I've attached a patch to solve the issue. In PostgreSqlDdlBuilder I've updated the name length costant according to the default used in the latests PostgreSQL versions. ( ref.: http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS )

Related Changesets

SymmetricDS: master 16c09cdb

2012-09-05 15:20:52

chenson

Details Diff
0000792: Support tables with names greater than 50 chars Affected Issues
0000792
mod - symmetric-core/src/main/resources/symmetric-schema.xml Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlDdlBuilder.java Diff File

Issue History

Date Modified Username Field Change
2012-08-28 16:05 tregnago New Issue
2012-08-30 15:01 chenson Assigned To => chenson
2012-08-30 15:01 chenson Status new => assigned
2012-08-30 15:01 chenson Target Version => 3.1.4
2012-08-30 15:02 chenson Product Version 3.1.3 =>
2012-08-30 15:03 chenson Note Added: 0000074
2012-08-30 15:16 tregnago Note Added: 0000076
2012-09-03 15:33 tregnago File Added: symmetric-schema.xml.diff
2012-09-03 15:33 tregnago File Added: PostgreSqlDdlBuilder.java.diff
2012-09-03 15:38 tregnago Note Added: 0000079
2012-09-05 19:19 chenson Summary Tables with names greater than 50 chars => Support tables with names greater than 50 chars
2012-09-06 00:40 chenson Status assigned => resolved
2012-09-06 00:40 chenson Fixed in Version => 3.1.4
2012-09-06 00:40 chenson Resolution open => fixed
2012-09-07 23:53 chenson Status resolved => closed
2014-02-03 01:52 Changeset attached => SymmetricDS trunk r6240
2015-07-31 01:49 chenson Changeset attached => SymmetricDS master 16c09cdb