View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0004354SymmetricDSBugpublic2020-04-17 12:282020-05-26 16:37
ReporterStrDevelopment Assigned Toelong  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.11.0 
Target Version3.11.9Fixed in Version3.11.9 
Summary0004354: MsSql - create trigger query fails when keys contain spaces
DescriptionWe use Microsoft Dynamics Nav.
Most of the NAV table and fieldnames contain spaces......
When the symmetric server starts/updates, triggers are (re)created.
If the key(s) contain spaces the query will fail because double qoutes are missing in one line of the create trigger query.
Steps To ReproduceImport DB.xml via Dbimport.
Add to sym_channel, sym_trigger and sym_trigger_router.
The logs will show it fails after a few seconds.

if you run the trigger query manually with double quotes, it will work, untill you reboot or add another trigger.
Additional InformationLine 17 of create_trigger_query.sql is
if (1=1 AND ( UPDATE(Vendor No_) OR UPDATE(Item No_) OR UPDATE(Variant Code) ) ) <----- Created on start and not working

but it should be
if (1=1 AND ( UPDATE("Vendor No_") OR UPDATE("Item No_") OR UPDATE("Variant Code") ) ) <----- Working
Tagsdialect: sql-server, trigger

Relationships

related to 0003901 closedpmarzullo MSSQL: Updating multiple rows in source table results in reversed rows in destination table 

Activities

StrDevelopment

2020-04-17 12:28

reporter  

create_trigger_query.sql (9,929 bytes)
DB.xml (4,269 bytes)    
<database xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="dbexport">
        <table name="TABLENAME$Item Vendor">
                <column name="timestamp" required="true" type="BINARY" size="8">
                        <platform-column name="mssql2008" type="timestamp"/>
                </column>
                <column name="Vendor No_" primaryKey="true" required="true" type="NVARCHAR" size="20">
                        <platform-column name="mssql2008" type="nvarchar" size="20"/>
                </column>
                <column name="Item No_" primaryKey="true" required="true" type="NVARCHAR" size="20">
                        <platform-column name="mssql2008" type="nvarchar" size="20"/>
                </column>
                <column name="Variant Code" primaryKey="true" required="true" type="NVARCHAR" size="10">
                        <platform-column name="mssql2008" type="nvarchar" size="10"/>
                </column>
                <column name="Lead Time Calculation" required="true" type="VARCHAR" size="32">
                        <platform-column name="mssql2008" type="varchar" size="32"/>
                </column>
                <column name="Vendor Item No_" required="true" type="NVARCHAR" size="20">
                        <platform-column name="mssql2008" type="nvarchar" size="20"/>
                </column>
                <column name="Inventory" required="true" type="DECIMAL" size="38,20">
                        <platform-column name="mssql2008" type="decimal" size="38" decimalDigits="20"/>
                </column>
                <column name="Previous Stock Quantity" required="true" type="DECIMAL" size="38,20">
                        <platform-column name="mssql2008" type="decimal" size="38" decimalDigits="20"/>
                </column>
                <column name="Last Modification Date" required="true" type="TIMESTAMP" size="23,3">
                        <platform-column name="mssql2008" type="datetime" size="23" decimalDigits="3"/>
                </column>
                <column name="Preferred supplier" required="true" type="TINYINT" size="3">
                        <platform-column name="mssql2008" type="tinyint" size="3"/>
                </column>
                <column name="PPD" required="true" type="DECIMAL" size="38,20">
                        <platform-column name="mssql2008" type="decimal" size="38" decimalDigits="20"/>
                </column>
                <column name="Previous PPD" required="true" type="DECIMAL" size="38,20">
                        <platform-column name="mssql2008" type="decimal" size="38" decimalDigits="20"/>
                </column>
                <column name="Producer" required="true" type="TINYINT" size="3">
                        <platform-column name="mssql2008" type="tinyint" size="3"/>
                </column>
                <column name="Status" required="true" type="INTEGER" size="10">
                        <platform-column name="mssql2008" type="int" size="10"/>
                </column>
                <unique name="$1">
                        <unique-column name="Item No_"/>
                        <unique-column name="Variant Code"/>
                        <unique-column name="Vendor No_"/>
                </unique>
                <unique name="$2">
                        <unique-column name="Vendor No_"/>
                        <unique-column name="Vendor Item No_"/>
                        <unique-column name="Item No_"/>
                        <unique-column name="Variant Code"/>
                </unique>
                <unique name="$3">
                        <unique-column name="Inventory"/>
                        <unique-column name="Vendor No_"/>
                        <unique-column name="Item No_"/>
                        <unique-column name="Variant Code"/>
                </unique>
                <unique name="$4">
                        <unique-column name="PPD"/>
                        <unique-column name="Vendor No_"/>
                        <unique-column name="Item No_"/>
                        <unique-column name="Variant Code"/>
                </unique>
        </table>
</database>
DB.xml (4,269 bytes)   
trigger_fail.log (3,607 bytes)

StrDevelopment

2020-04-17 16:01

reporter   ~0001701

After a little search, i maybe found the line which is causing our problem.

https://github.com/JumpMind/symmetric-ds/blob/5a2342d8a2e39468acea5d367b696da8355c5468/symmetric-core/src/main/java/org/jumpmind/symmetric/db/AbstractTriggerTemplate.java#L1257

Hope this helps

elong

2020-04-23 18:59

developer   ~0001709

Thanks for reporting. This patch is working for us:
http://www.jumpmind.com/downloads/symmetricds/patches/3.11/patch-0004354.jar

Related Changesets

SymmetricDS: 3.11 7db65c57

2020-04-23 14:58:32

admin

Details Diff 		0004354: MsSql - create trigger fails when keys contain spaces Affected Issues
0004354
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/db/AbstractTriggerTemplate.java Diff File

Issue History

Date Modified Username Field Change
2020-04-17 12:28 StrDevelopment New Issue
2020-04-17 12:28 StrDevelopment File Added: create_trigger_query.sql
2020-04-17 12:28 StrDevelopment File Added: DB.xml
2020-04-17 12:28 StrDevelopment File Added: trigger_fail.log
2020-04-17 12:28 StrDevelopment Tag Attached: trigger
2020-04-17 16:01 StrDevelopment Note Added: 0001701
2020-04-23 18:56 elong Relationship added related to 0003901
2020-04-23 18:56 elong Tag Attached: dialect: sql-server
2020-04-23 18:57 elong Assigned To => elong
2020-04-23 18:57 elong Status new => assigned
2020-04-23 18:57 elong Product Version 3.11.7 => 3.11.0
2020-04-23 18:57 elong Target Version => 3.11.9
2020-04-23 18:57 elong Description Updated View Revisions
2020-04-23 18:57 elong Steps to Reproduce Updated View Revisions
2020-04-23 18:57 elong Additional Information Updated View Revisions
2020-04-23 18:59 elong Note Added: 0001709
2020-04-23 19:00 admin Changeset attached => SymmetricDS 3.11 7db65c57
2020-04-23 19:00 elong Status assigned => resolved
2020-04-23 19:00 elong Resolution open => fixed
2020-04-23 19:00 elong Fixed in Version => 3.11.9
2020-05-26 16:37 elong Status resolved => closed