View Issue Details

IDProjectCategoryView StatusLast Update
0003295SymmetricDSBugpublic2018-10-29 12:55
ReporterrlanewalaAssigned To 
Prioritylow 
Status confirmedResolutionopen 
Product Version3.8.30 
Target Version3.10.0Fixed in Version 
Summary0003295: Sync from MySql to MSSql initial load - table create fails on Decimal Precision when MySQL precision is more than 38
DescriptionWhen trying to setup and initial load data from MySql to SQL SERVER 2012. The initial load fails on creating table with error "Column or parameter 0000007: Specified column precision 40 is greater than the maximum precision of 38."

MySql Table:
Running alter sql:
SET quoted_identifier on;
SET quoted_identifier on;
CREATE TABLE "calcbillmonthtotal_savings"(
    "school" INT NOT NULL,
    "resource" VARCHAR(11) NOT NULL,
    "unit_of_measure" VARCHAR(5) NOT NULL,
    "month" DATETIME NOT NULL,
    "students" INT NOT NULL,
    "actual_cost" DECIMAL(34,4),
    "actual_usage" DECIMAL(40,4),
    "actual_peak_usage" DECIMAL(40,4),
    "actual_offpeak_usage" DECIMAL(40,4),
    "actual_usage_per_student" DECIMAL(42,12),
    "baseline_start" DATETIME NOT NULL,
    "baseline_end" DATETIME NOT NULL,
    "baseline_months" INT NOT NULL,
    "historic_cost_per_unit" DECIMAL(20,12) DEFAULT 0.000000000000 NOT NULL,
    "baseline_usage_per_student" DECIMAL(20,12),
    "historic_cost" DECIMAL(60,16) DEFAULT 0.0000000000000000 NOT NULL,
    "usage_saving" DECIMAL(53,12),
    "usage_saving_per_student" DECIMAL(43,12),
    "cost_saving" DECIMAL(65,24) DEFAULT 0.000000000000000000000000 NOT NULL,
    "cost_saving_per_student" DECIMAL(63,24) DEFAULT 0.000000000000000000000000 NOT NULL,
    "emission_saving" DECIMAL(63,18),
    PRIMARY KEY ("school", "resource", "unit_of_measure", "month", "students", "actual_cost", "actual_usage", "actual_peak_usage", "actual_offpeak_usage", "actual_usage_per_student", "baseline_start", "baseline_end", "baseline_months", "historic_cost_per_unit", "baseline_usage_per_student", "historic_cost", "usage_saving", "usage_saving_per_student", "cost_saving", "cost_saving_per_student", "emission_saving")
);


How to configure or setup Sync successfully?
Steps To Reproduce* Setup an Server => Client sync from MySql to Sql Server 2012
* initiate a initial load.
* Fails when creating the table that has Decimal and precision greater than 38.

Activities

hanes

2018-10-17 10:06

developer   ~0001276

Last edited: 2018-10-17 10:07

View 2 revisions

Confirmed that the issue still exists in 3.9.14.
AcknowledgeService - The outgoing batch 001-10 failed: [S1000,2750] Column or parameter 0000007: Specified column precision 40 is greater than the maximum precision of 38.

Caused by: java.sql.SQLException: Column or parameter 0000007: Specified column precision 40 is greater than the maximum precision of 38.


The question remains though, what is the correct solution? The data on the MySQL side could be larger than that on the SQLServer side.

hanes

2018-10-17 14:27

developer   ~0001283

SymmetricDS should create the field as a 38 but should definitely issue a warning in the log about possible issues due to the drop of precision at the target schema.

Issue History

Date Modified Username Field Change
2017-10-29 19:21 rlanewala New Issue
2017-10-29 19:21 rlanewala Tag Attached: mssql
2018-10-17 10:06 hanes Status new => confirmed
2018-10-17 10:06 hanes Note Added: 0001276
2018-10-17 10:07 hanes Summary Sync from MySql to MSSql initial load - table create fails on Decimal Precision => Sync from MySql to MSSql initial load - table create fails on Decimal Precision when MySQL precision is more than 38
2018-10-17 10:07 hanes Note Edited: 0001276 View Revisions
2018-10-17 14:27 hanes Target Version => 3.10.0
2018-10-17 14:27 hanes Note Added: 0001283
2018-10-29 12:55 hanes Priority urgent => low