View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0006470SymmetricDSImprovementpublic2024-06-04 08:012024-10-30 17:50
Reporterazarubkin Assigned Topmarzullo  
Prioritynormal 
Status confirmedResolutionopen 
Product Version3.15.5 
Summary0006470: Deletes are sent after the schema on reload
DescriptionWhen I reload the node, I noticed that database tables schema is sent first, then (if configured so) are the commands to delete all data from them. This is less than optimal, because as a part of process of reconfiguring table schema, the target node first saves the data to the temporary table, then recreates the table and copies the data back. This is not needed if the data is going to be deleted anyway.
TagsNo tags attached.

Activities

cquamme

2024-06-05 17:32

developer   ~0002459

What database platform and version are you using?
Would you be able to attach the table definition of the table with the problem as well.

azarubkin

2024-10-08 11:00

reporter   ~0002497

I'm syncing MS SQL to SQLite, MS SQL's version is 2005, SQLite's version is 3.40.1. I'll post the table definition later.

emiller

2024-10-09 17:53

developer   ~0002498

SymmetricDS runs the create before running the delete because of the scenario where the table is being created for the first time. In this scenario, running the delete first would cause an error.

SymmetricDS recreates the table and copies the data back when it thinks that altering the table isn't possible. It's possible that there is a bug that caused SymmetricDS to think that the table needs altered, when it really doesn't. The table definition would help us reproduce the bug, if there is one.

How are you telling SymmetricDS to send the table schema, and how are you telling it to delete the data? Are you using sym_table_reload_request, or are you using the initial.load.create.first and initial.load.delete.first parameters? If you're using sym_table_reload_request, are you using delete_first or before_custom_sql to tell it to delete the data?

azarubkin

2024-10-16 14:10

reporter   ~0002504

I'm not using sym_table_reload_request. I just do `symadmin reload-node`, or `symadmin send-schema`+`symadmin reload-table`. Initial.load.create.first and initial.load.delete first are set to true in symmetric.properties file. I'm attaching the part of the log. The table is recreated on each reload.
reload-table.txt (12,973 bytes)    
2024-10-16 16:44:22,949 INFO [edoc-001] [DefaultDatabaseWriter] [edoc-001-dataloader-10312] About to create table using the following definition: <?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor">
	<table name="people">
		<column name="id" primaryKey="true" primaryKeySeq="1" required="true" type="INTEGER" size="10" autoIncrement="true">
			<platform-column name="mssql2005" type="int identity" size="10"/>
		</column>
		<column name="surname" type="VARCHAR" size="20">
			<platform-column name="mssql2005" type="varchar" size="20"/>
		</column>
		<column name="first_name" type="VARCHAR" size="20">
			<platform-column name="mssql2005" type="varchar" size="20"/>
		</column>
		<column name="second_name" type="VARCHAR" size="20">
			<platform-column name="mssql2005" type="varchar" size="20"/>
		</column>
		<column name="traits" type="VARCHAR" size="50">
			<platform-column name="mssql2005" type="varchar" size="50"/>
		</column>
		<column name="message" type="VARCHAR" size="50">
			<platform-column name="mssql2005" type="varchar" size="50"/>
		</column>
		<column name="photofile" type="VARCHAR" size="50">
			<platform-column name="mssql2005" type="varchar" size="50"/>
		</column>
		<column name="regime_id" type="INTEGER" size="10">
			<platform-column name="mssql2005" type="int" size="10"/>
		</column>
		<column name="spec_regime_id" type="INTEGER" size="10">
			<platform-column name="mssql2005" type="int" size="10"/>
		</column>
		<column name="has_bag" required="true" type="BIT" size="1" default="0">
			<platform-column name="mssql2005" type="bit" size="1" default="0"/>
		</column>
		<column name="has_bicycle" required="true" type="BIT" size="1" default="0">
			<platform-column name="mssql2005" type="bit" size="1" default="0"/>
		</column>
		<column name="has_car_id" type="CHAR" size="15">
			<platform-column name="mssql2005" type="char" size="15"/>
		</column>
		<column name="passport_series" type="VARCHAR" size="10">
			<platform-column name="mssql2005" type="varchar" size="10"/>
		</column>
		<column name="passport_number" type="VARCHAR" size="10">
			<platform-column name="mssql2005" type="varchar" size="10"/>
		</column>
		<column name="passport_scancopy" type="VARCHAR" size="50">
			<platform-column name="mssql2005" type="varchar" size="50"/>
		</column>
		<column name="birthdate" type="TIMESTAMP">
			<platform-column name="mssql2005" type="smalldatetime"/>
		</column>
		<column name="1st_cat_pass" required="true" type="BIT" size="1" default="0">
			<platform-column name="mssql2005" type="bit" size="1" default="0"/>
		</column>
		<column name="instrument" type="VARCHAR" size="500">
			<platform-column name="mssql2005" type="varchar" size="500"/>
		</column>
		<column name="has_folder" required="true" type="BIT" size="1" default="0">
			<platform-column name="mssql2005" type="bit" size="1" default="0"/>
		</column>
		<column name="personal_data_consent" required="true" type="BIT" size="1" default="0">
			<platform-column name="mssql2005" type="bit" size="1" default="0"/>
		</column>
		<column name="knows_behavior_rules" required="true" type="BIT" size="1" default="0">
			<platform-column name="mssql2005" type="bit" size="1" default="0"/>
		</column>
		<index name="IX_people_passport">
			<index-column name="passport_series"/>
			<index-column name="passport_number"/>
		</index>
	</table>
</database>
2024-10-16 16:44:22,955 INFO [edoc-001] [SqliteDatabasePlatform] [edoc-001-dataloader-10312] Running alter sql:
DROP TABLE IF EXISTS "people_";
CREATE TABLE "people_"(
    "id" INTEGER NOT NULL,
    "surname" VARCHAR,
    "first_name" VARCHAR,
    "second_name" VARCHAR,
    "traits" VARCHAR,
    "message" VARCHAR,
    "photofile" VARCHAR,
    "regime_id" INTEGER,
    "spec_regime_id" INTEGER,
    "has_bag" INTEGER DEFAULT (0) NOT NULL,
    "has_bicycle" INTEGER DEFAULT (0) NOT NULL,
    "has_car_id" VARCHAR,
    "passport_series" VARCHAR,
    "passport_number" VARCHAR,
    "passport_scancopy" VARCHAR,
    "birthdate" TIMESTAMP,
    "1st_cat_pass" INTEGER DEFAULT (0) NOT NULL,
    "instrument" VARCHAR,
    "has_folder" INTEGER DEFAULT (0) NOT NULL,
    "personal_data_consent" INTEGER DEFAULT (0) NOT NULL,
    "knows_behavior_rules" INTEGER DEFAULT (0) NOT NULL
);
INSERT INTO "people_" ("id","surname","first_name","second_name","traits","message","photofile","regime_id","spec_regime_id","has_bag","has_bicycle","has_car_id","passport_series","passport_number","passport_scancopy","birthdate","1st_cat_pass","instrument","has_folder","personal_data_consent","knows_behavior_rules") SELECT "id","surname","first_name","second_name","traits","message","photofile","regime_id","spec_regime_id","has_bag","has_bicycle","has_car_id","passport_series","passport_number","passport_scancopy","birthdate","1st_cat_pass","instrument","has_folder","personal_data_consent","knows_behavior_rules" FROM "people";
DROP TABLE IF EXISTS "people";
CREATE TABLE "people"(
    "id" INTEGER NOT NULL PRIMARY KEY  AUTOINCREMENT,
    "surname" VARCHAR,
    "first_name" VARCHAR,
    "second_name" VARCHAR,
    "traits" VARCHAR,
    "message" VARCHAR,
    "photofile" VARCHAR,
    "regime_id" INTEGER,
    "spec_regime_id" INTEGER,
    "has_bag" INTEGER DEFAULT (0) NOT NULL,
    "has_bicycle" INTEGER DEFAULT (0) NOT NULL,
    "has_car_id" VARCHAR,
    "passport_series" VARCHAR,
    "passport_number" VARCHAR,
    "passport_scancopy" VARCHAR,
    "birthdate" TIMESTAMP,
    "1st_cat_pass" INTEGER DEFAULT (0) NOT NULL,
    "instrument" VARCHAR,
    "has_folder" INTEGER DEFAULT (0) NOT NULL,
    "personal_data_consent" INTEGER DEFAULT (0) NOT NULL,
    "knows_behavior_rules" INTEGER DEFAULT (0) NOT NULL
);
CREATE INDEX "IX_people_passport" ON "people" ("passport_series", "passport_number");
INSERT INTO "people" ("id","surname","first_name","second_name","traits","message","photofile","regime_id","spec_regime_id","has_bag","has_bicycle","has_car_id","passport_series","passport_number","passport_scancopy","birthdate","1st_cat_pass","instrument","has_folder","personal_data_consent","knows_behavior_rules") SELECT "id","surname","first_name","second_name","traits","message","photofile","regime_id","spec_regime_id","has_bag","has_bicycle","has_car_id","passport_series","passport_number","passport_scancopy","birthdate","1st_cat_pass","instrument","has_folder","personal_data_consent","knows_behavior_rules" FROM "people_";
DROP TABLE IF EXISTS "people_";

2024-10-16 16:44:23,314 INFO [edoc-001] [DefaultDatabaseWriter] [edoc-001-dataloader-10312] About to create table using the following definition: <?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor">
	<table name="people">
		<column name="id" primaryKey="true" primaryKeySeq="1" required="true" type="INTEGER" size="10" autoIncrement="true">
			<platform-column name="mssql2005" type="int identity" size="10"/>
		</column>
		<column name="surname" type="VARCHAR" size="20">
			<platform-column name="mssql2005" type="varchar" size="20"/>
		</column>
		<column name="first_name" type="VARCHAR" size="20">
			<platform-column name="mssql2005" type="varchar" size="20"/>
		</column>
		<column name="second_name" type="VARCHAR" size="20">
			<platform-column name="mssql2005" type="varchar" size="20"/>
		</column>
		<column name="traits" type="VARCHAR" size="50">
			<platform-column name="mssql2005" type="varchar" size="50"/>
		</column>
		<column name="message" type="VARCHAR" size="50">
			<platform-column name="mssql2005" type="varchar" size="50"/>
		</column>
		<column name="photofile" type="VARCHAR" size="50">
			<platform-column name="mssql2005" type="varchar" size="50"/>
		</column>
		<column name="regime_id" type="INTEGER" size="10">
			<platform-column name="mssql2005" type="int" size="10"/>
		</column>
		<column name="spec_regime_id" type="INTEGER" size="10">
			<platform-column name="mssql2005" type="int" size="10"/>
		</column>
		<column name="has_bag" required="true" type="BIT" size="1" default="0">
			<platform-column name="mssql2005" type="bit" size="1" default="0"/>
		</column>
		<column name="has_bicycle" required="true" type="BIT" size="1" default="0">
			<platform-column name="mssql2005" type="bit" size="1" default="0"/>
		</column>
		<column name="has_car_id" type="CHAR" size="15">
			<platform-column name="mssql2005" type="char" size="15"/>
		</column>
		<column name="passport_series" type="VARCHAR" size="10">
			<platform-column name="mssql2005" type="varchar" size="10"/>
		</column>
		<column name="passport_number" type="VARCHAR" size="10">
			<platform-column name="mssql2005" type="varchar" size="10"/>
		</column>
		<column name="passport_scancopy" type="VARCHAR" size="50">
			<platform-column name="mssql2005" type="varchar" size="50"/>
		</column>
		<column name="birthdate" type="TIMESTAMP">
			<platform-column name="mssql2005" type="smalldatetime"/>
		</column>
		<column name="1st_cat_pass" required="true" type="BIT" size="1" default="0">
			<platform-column name="mssql2005" type="bit" size="1" default="0"/>
		</column>
		<column name="instrument" type="VARCHAR" size="500">
			<platform-column name="mssql2005" type="varchar" size="500"/>
		</column>
		<column name="has_folder" required="true" type="BIT" size="1" default="0">
			<platform-column name="mssql2005" type="bit" size="1" default="0"/>
		</column>
		<column name="personal_data_consent" required="true" type="BIT" size="1" default="0">
			<platform-column name="mssql2005" type="bit" size="1" default="0"/>
		</column>
		<column name="knows_behavior_rules" required="true" type="BIT" size="1" default="0">
			<platform-column name="mssql2005" type="bit" size="1" default="0"/>
		</column>
		<index name="IX_people_passport">
			<index-column name="passport_series"/>
			<index-column name="passport_number"/>
		</index>
	</table>
</database>
2024-10-16 16:44:23,315 INFO [edoc-001] [SqliteDatabasePlatform] [edoc-001-dataloader-10312] Running alter sql:
DROP TABLE IF EXISTS "people_";
CREATE TABLE "people_"(
    "id" INTEGER NOT NULL,
    "surname" VARCHAR,
    "first_name" VARCHAR,
    "second_name" VARCHAR,
    "traits" VARCHAR,
    "message" VARCHAR,
    "photofile" VARCHAR,
    "regime_id" INTEGER,
    "spec_regime_id" INTEGER,
    "has_bag" INTEGER DEFAULT (0) NOT NULL,
    "has_bicycle" INTEGER DEFAULT (0) NOT NULL,
    "has_car_id" VARCHAR,
    "passport_series" VARCHAR,
    "passport_number" VARCHAR,
    "passport_scancopy" VARCHAR,
    "birthdate" TIMESTAMP,
    "1st_cat_pass" INTEGER DEFAULT (0) NOT NULL,
    "instrument" VARCHAR,
    "has_folder" INTEGER DEFAULT (0) NOT NULL,
    "personal_data_consent" INTEGER DEFAULT (0) NOT NULL,
    "knows_behavior_rules" INTEGER DEFAULT (0) NOT NULL
);
INSERT INTO "people_" ("id","surname","first_name","second_name","traits","message","photofile","regime_id","spec_regime_id","has_bag","has_bicycle","has_car_id","passport_series","passport_number","passport_scancopy","birthdate","1st_cat_pass","instrument","has_folder","personal_data_consent","knows_behavior_rules") SELECT "id","surname","first_name","second_name","traits","message","photofile","regime_id","spec_regime_id","has_bag","has_bicycle","has_car_id","passport_series","passport_number","passport_scancopy","birthdate","1st_cat_pass","instrument","has_folder","personal_data_consent","knows_behavior_rules" FROM "people";
DROP TABLE IF EXISTS "people";
CREATE TABLE "people"(
    "id" INTEGER NOT NULL PRIMARY KEY  AUTOINCREMENT,
    "surname" VARCHAR,
    "first_name" VARCHAR,
    "second_name" VARCHAR,
    "traits" VARCHAR,
    "message" VARCHAR,
    "photofile" VARCHAR,
    "regime_id" INTEGER,
    "spec_regime_id" INTEGER,
    "has_bag" INTEGER DEFAULT (0) NOT NULL,
    "has_bicycle" INTEGER DEFAULT (0) NOT NULL,
    "has_car_id" VARCHAR,
    "passport_series" VARCHAR,
    "passport_number" VARCHAR,
    "passport_scancopy" VARCHAR,
    "birthdate" TIMESTAMP,
    "1st_cat_pass" INTEGER DEFAULT (0) NOT NULL,
    "instrument" VARCHAR,
    "has_folder" INTEGER DEFAULT (0) NOT NULL,
    "personal_data_consent" INTEGER DEFAULT (0) NOT NULL,
    "knows_behavior_rules" INTEGER DEFAULT (0) NOT NULL
);
CREATE INDEX "IX_people_passport" ON "people" ("passport_series", "passport_number");
INSERT INTO "people" ("id","surname","first_name","second_name","traits","message","photofile","regime_id","spec_regime_id","has_bag","has_bicycle","has_car_id","passport_series","passport_number","passport_scancopy","birthdate","1st_cat_pass","instrument","has_folder","personal_data_consent","knows_behavior_rules") SELECT "id","surname","first_name","second_name","traits","message","photofile","regime_id","spec_regime_id","has_bag","has_bicycle","has_car_id","passport_series","passport_number","passport_scancopy","birthdate","1st_cat_pass","instrument","has_folder","personal_data_consent","knows_behavior_rules" FROM "people_";
DROP TABLE IF EXISTS "people_";
reload-table.txt (12,973 bytes)   

azarubkin

2024-10-16 14:11

reporter   ~0002505

Please also note that the table is created twice per reload.

azarubkin

2024-10-17 12:07

reporter   ~0002506

The table definition:

USE [guard]
GO

/****** Object: Table [dbo].[people] Script Date: 17.10.2024 15:07:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[people](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [surname] [varchar](20) NULL,
    [first_name] [varchar](20) NULL,
    [second_name] [varchar](20) NULL,
    [traits] [varchar](50) NULL,
    [message] [varchar](50) NULL,
    [photofile] [varchar](50) NULL,
    [regime_id] [int] NULL,
    [spec_regime_id] [int] NULL,
    [has_bag] [bit] NOT NULL,
    [has_bicycle] [bit] NOT NULL,
    [has_car_id] [char](15) NULL,
    [passport_series] [varchar](10) NULL,
    [passport_number] [varchar](10) NULL,
    [passport_scancopy] [varchar](50) NULL,
    [birthdate] [smalldatetime] NULL,
    [1st_cat_pass] [bit] NOT NULL,
    [instrument] [varchar](500) NULL,
    [has_folder] [bit] NOT NULL,
    [personal_data_consent] [bit] NOT NULL,
    [knows_behavior_rules] [bit] NOT NULL,
 CONSTRAINT [PK_people] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[people] ADD CONSTRAINT [DF_people_has_bag] DEFAULT (0) FOR [has_bag]
GO

ALTER TABLE [dbo].[people] ADD CONSTRAINT [DF_people_has_bicycle] DEFAULT (0) FOR [has_bicycle]
GO

ALTER TABLE [dbo].[people] ADD CONSTRAINT [DF_people_sp] DEFAULT ((0)) FOR [1st_cat_pass]
GO

ALTER TABLE [dbo].[people] ADD CONSTRAINT [DF_people_has_folder] DEFAULT ((0)) FOR [has_folder]
GO

ALTER TABLE [dbo].[people] ADD CONSTRAINT [DF_people_personal_data_consent] DEFAULT ((0)) FOR [personal_data_consent]
GO

ALTER TABLE [dbo].[people] ADD CONSTRAINT [DF_people_knows_behavior_rules] DEFAULT ((0)) FOR [knows_behavior_rules]
GO

ALTER TABLE [dbo].[people] WITH CHECK ADD CONSTRAINT [FK_people_regimes] FOREIGN KEY([regime_id])
REFERENCES [dbo].[regimes] ([regime_id])
GO

ALTER TABLE [dbo].[people] CHECK CONSTRAINT [FK_people_regimes]
GO

ALTER TABLE [dbo].[people] WITH CHECK ADD CONSTRAINT [FK_people_regimes2] FOREIGN KEY([spec_regime_id])
REFERENCES [dbo].[regimes] ([regime_id])
GO

ALTER TABLE [dbo].[people] CHECK CONSTRAINT [FK_people_regimes2]
GO

ALTER TABLE [dbo].[people] WITH CHECK ADD CONSTRAINT [CK_people_birthdate_correct] CHECK (([birthdate]=CONVERT([char](8),[birthdate],(112))))
GO

ALTER TABLE [dbo].[people] CHECK CONSTRAINT [CK_people_birthdate_correct]
GO

ALTER TABLE [dbo].[people] WITH CHECK ADD CONSTRAINT [CK_people_either_bag_or_folder] CHECK (([has_bag]=(0) OR [has_folder]=(0)))
GO

ALTER TABLE [dbo].[people] CHECK CONSTRAINT [CK_people_either_bag_or_folder]
GO

pmarzullo

2024-10-30 17:49

developer   ~0002517

The migration is occurring because it thinks that the default value is changing:
2024-10-30 13:46:43,799 INFO [client] [ModelComparator] [client-dataloader-13] The has_bag column on the people table changed default value from 0 to 0
2024-10-30 13:46:43,799 INFO [client] [ModelComparator] [client-dataloader-13] The has_bicycle column on the people table changed default value from 0 to 0
2024-10-30 13:46:43,799 INFO [client] [ModelComparator] [client-dataloader-13] The 1st_cat_pass column on the people table changed default value from 0 to 0
2024-10-30 13:46:43,799 INFO [client] [ModelComparator] [client-dataloader-13] The has_folder column on the people table changed default value from 0 to 0
2024-10-30 13:46:43,799 INFO [client] [ModelComparator] [client-dataloader-13] The personal_data_consent column on the people table changed default value from 0 to 0
2024-10-30 13:46:43,799 INFO [client] [ModelComparator] [client-dataloader-13] The knows_behavior_rules column on the people table changed default value from 0 to 0

Issue History

Date Modified Username Field Change
2024-06-04 08:01 azarubkin New Issue
2024-06-05 17:32 cquamme Note Added: 0002459
2024-06-05 17:33 cquamme Status new => feedback
2024-10-08 11:00 azarubkin Note Added: 0002497
2024-10-08 11:00 azarubkin Status feedback => new
2024-10-09 17:53 emiller Note Added: 0002498
2024-10-09 17:54 emiller Status new => feedback
2024-10-16 14:10 azarubkin Note Added: 0002504
2024-10-16 14:10 azarubkin File Added: reload-table.txt
2024-10-16 14:10 azarubkin Status feedback => new
2024-10-16 14:11 azarubkin Note Added: 0002505
2024-10-17 12:07 azarubkin Note Added: 0002506
2024-10-30 17:49 pmarzullo Note Added: 0002517
2024-10-30 17:50 pmarzullo Assigned To => pmarzullo
2024-10-30 17:50 pmarzullo Status new => confirmed