View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0003295SymmetricDSBugpublic2017-10-29 23:212022-07-12 20:01
Reporterrlanewala Assigned Toelong  
Prioritylow 
Status closedResolutionfixed 
Product Version3.8.30 
Target Version3.13.6Fixed in Version3.13.6 
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.

Tagsddl/schema, dialect: sql-server

Activities

hanes

2018-10-17 14:06

developer   ~0001276

Last edited: 2018-10-17 14: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 18: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 23:21 rlanewala New Issue
2017-10-29 23:21 rlanewala Tag Attached: mssql
2018-10-17 14:06 hanes Status new => confirmed
2018-10-17 14:06 hanes Note Added: 0001276
2018-10-17 14: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 14:07 hanes Note Edited: 0001276 View Revisions
2018-10-17 18:27 hanes Target Version => 3.10.0
2018-10-17 18:27 hanes Note Added: 0001283
2018-10-29 16:55 hanes Priority urgent => low
2019-03-14 12:39 admin Target Version 3.10.0 => 3.10.1
2019-04-23 13:46 elong Target Version 3.10.1 => 3.10.2
2019-04-23 20:49 admin Tag Renamed mssql => dialect: mssql
2019-04-24 12:50 admin Tag Renamed dialect: mssql => dialect: sql-server
2019-05-08 12:51 admin Target Version 3.10.2 => 3.10.3
2019-06-24 17:45 elong Target Version 3.10.3 => 3.10.4
2019-08-09 19:41 elong Target Version 3.10.4 => 3.10.5
2019-10-31 14:59 admin Target Version 3.10.5 => 3.10.6
2019-11-14 19:47 elong Target Version 3.10.6 => 3.10.7
2019-12-09 15:25 admin Target Version 3.10.7 => 3.10.8
2020-01-02 14:40 admin Target Version 3.10.8 => 3.10.9
2020-01-22 19:03 elong Target Version 3.10.9 => 3.10.10
2020-02-14 13:33 elong Tag Attached: ddl/schema
2020-02-14 13:46 elong Target Version 3.10.10 => 3.10.11
2020-03-16 20:14 admin Target Version 3.10.11 => 3.10.12
2020-05-26 12:28 elong Target Version 3.10.12 => 3.11.10
2020-06-24 20:14 elong Target Version 3.11.10 => 3.12.2
2020-07-20 13:06 elong Target Version 3.12.2 => 3.12.3
2020-08-31 19:47 elong Target Version 3.12.3 => 3.12.4
2020-09-23 15:32 admin Target Version 3.12.4 => 3.12.5
2020-11-09 13:45 admin Target Version 3.12.5 => 3.12.6
2021-01-08 20:42 admin Target Version 3.12.6 => 3.12.7
2021-02-25 13:01 admin Target Version 3.12.7 => 3.12.8
2021-03-30 19:13 admin Target Version 3.12.8 => 3.12.9
2021-04-28 14:41 admin Target Version 3.12.9 => 3.12.10
2021-05-28 11:54 admin Target Version 3.12.10 => 3.12.11
2021-07-13 12:43 admin Target Version 3.12.11 => 3.12.12
2021-11-03 12:11 elong Target Version 3.12.12 => 3.12.13
2021-12-11 17:11 admin Target Version 3.12.13 => 3.12.14
2021-12-20 13:55 admin Target Version 3.12.14 => 3.12.15
2022-01-13 13:34 elong Target Version 3.12.15 => 3.14.0
2022-07-08 20:51 elong Assigned To => elong
2022-07-08 20:51 elong Status confirmed => resolved
2022-07-08 20:51 elong Resolution open => fixed
2022-07-08 20:51 elong Fixed in Version => 3.13.6
2022-07-08 20:51 elong Target Version 3.14.0 => 3.13.6
2022-07-12 20:01 admin Status resolved => closed