0004933: Invalid MySQL update statement that queries same table when starting load to multiple nodes - SymmetricDS

ID	Project	Category	View Status	Date Submitted	Last Update

0004933	SymmetricDS	Bug	public	2021-03-31 18:20	2021-04-28 19:10

Reporter	matthewwhittenham	Assigned To	elong
Priority	normal
Status	closed	Resolution	fixed
Product Version	3.12.7
Target Version	3.12.9	Fixed in Version	3.12.9

Summary	0004933: Invalid MySQL update statement that queries same table when starting load to multiple nodes
Description	I am getting the following SQLException: Failed to execute EXTRACT for node XXX and channel default. You can't specify target table 'sym_extract_request' for update in FROM clause when running SymmetricDS. My setup is a single SymmetricDS instance with 1 x "master" node, and 2 x "local" nodes - where the master node is the main node and the local nodes are in a separate node group together. My master node connects to an AWS Aurora MySQL database, using the MariaDB JDBC driver. The local nodes connect to SQLite Database files stored locally on my machine, using the standard SQLite JDBC driver.
Steps To Reproduce	Setup 3 x nodes - 1 x that connects to a MySQL type database using the MariaDB driver, and 2 x local SQLite databases using the SQLite JDBC driver. Populate the MySQL main database with some user tables (also add these tables to the SQLite nodes, or set initial.load.create.first=true), SymmetricDS tables and data for each of these (see insert_sample.sql attached for the data that I add to the main database). Then, run bin/sym and observe logging output for the error mentioned above.
Additional Information	Please see attached insert_sample.sql for my SymmetricDS configuration used, as well as create_user_tables.sql and create_user_tables_mysql.sql scripts to create equivalent tables on the local nodes, and master node respectively. Please also see my .properties files for each node, although url and login information for the Aurora MySQL database that I connect to has been omitted. Please also see the full log output from when I encountered this issue.
Tags	dialect: mysql/mariadb, initial/partial load

matthewwhittenham

2021-03-31 18:20

reporter

create_user_tables.sql (1,811 bytes)

CREATE TABLE "Clubs" (
	"short_club_name"	TEXT,
	"club_code"	TEXT NOT NULL UNIQUE,
	"long_club_name"	TEXT NOT NULL UNIQUE,
	"club_rep_name"	TEXT,
	"email_address"	TEXT,
	PRIMARY KEY("short_club_name")
);

CREATE TABLE "Events" (
	"event_number"	INTEGER,
	"distance"	INTEGER NOT NULL,
	"stroke"	TEXT NOT NULL,
	"sex"	TEXT NOT NULL,
	"no_of_swimmers"	INTEGER NOT NULL,
	"event_type"	TEXT NOT NULL,
	"no_of_lanes"	INTEGER NOT NULL,
	"seeding_method"	TEXT NOT NULL,
	"heat_events_number"	INTEGER,
	"entry_fee"	REAL NOT NULL,
	"time_interval"	INTEGER NOT NULL,
	"session_number"	INTEGER NOT NULL,
	PRIMARY KEY("event_number")
	FOREIGN KEY("heat_events_number") REFERENCES Events("event_number")
	FOREIGN KEY("session_number") REFERENCES Sessions("session_number")
);

CREATE TABLE "Meet" (
	"licence_number"	TEXT,
	"meet_name"	TEXT NOT NULL,
	"meet_type"	TEXT NOT NULL,
	"start_date"	TEXT NOT NULL,
	"end_date"	TEXT NOT NULL,
	"location"	TEXT NOT NULL,
	"course"	INTEGER NOT NULL,
	PRIMARY KEY("licence_number")
);

CREATE TABLE "Qualifying_Times" (
	"event_number"	INTEGER,
	"min_age"	INTEGER,
	"max_age"	INTEGER,
	"lower_time"	INTEGER,
	"upper_time"	INTEGER,
	PRIMARY KEY("event_number","min_age","max_age")
	FOREIGN KEY("event_number") REFERENCES Events("event_number")
);

CREATE TABLE "Sessions" (
	"session_number"	INTEGER,
	"date"	TEXT NOT NULL,
	"start_time"	INTEGER,
	PRIMARY KEY("session_number")
);

CREATE TABLE "Swimmers" (
	"asa_number"	INTEGER,
	"forename"	TEXT NOT NULL,
	"surname"	TEXT NOT NULL,
	"nickname"	TEXT,
	"dob"	TEXT,
	"age"	INTEGER NOT NULL,
	"sex"	TEXT NOT NULL,
	"short_club_name"	TEXT NOT NULL,
	"disability_status"	TEXT,
	PRIMARY KEY("asa_number")
	FOREIGN KEY("short_club_name") REFERENCES Clubs("short_club_name")
);

create_user_tables.sql (1,811 bytes)

create_user_tables_mysql.sql (1,765 bytes)

CREATE TABLE Clubs (
	short_club_name	VARCHAR(12),
	club_code	VARCHAR(4) NOT NULL,
	long_club_name	VARCHAR(30) NOT NULL,
	club_rep_name	TEXT,
	email_address	TEXT,
	PRIMARY KEY(short_club_name),
	UNIQUE (club_code),
	UNIQUE (long_club_name)
);

CREATE TABLE Sessions (
	session_number	INTEGER,
	date	TEXT NOT NULL,
	start_time	INTEGER,
	PRIMARY KEY(session_number)
);

CREATE TABLE Events (
	event_number	INTEGER,
	distance	INTEGER NOT NULL,
	stroke	TEXT NOT NULL,
	sex	TEXT NOT NULL,
	no_of_swimmers	INTEGER NOT NULL,
	event_type	TEXT NOT NULL,
	no_of_lanes	INTEGER NOT NULL,
	seeding_method	TEXT NOT NULL,
	heat_events_number	INTEGER,
	entry_fee	REAL(5,2) NOT NULL,
	time_interval	INTEGER NOT NULL,
	session_number	INTEGER NOT NULL,
	PRIMARY KEY(event_number),
	FOREIGN KEY(heat_events_number) REFERENCES Events(event_number),
	FOREIGN KEY(session_number) REFERENCES Sessions(session_number)
);

CREATE TABLE Meet (
	licence_number	VARCHAR(30),
	meet_name	TEXT NOT NULL,
	meet_type	TEXT NOT NULL,
	start_date	TEXT NOT NULL,
	end_date	TEXT NOT NULL,
	location	TEXT NOT NULL,
	course	INTEGER NOT NULL,
	PRIMARY KEY(licence_number)
);

CREATE TABLE Qualifying_Times (
	event_number	INTEGER,
	min_age	INTEGER,
	max_age	INTEGER,
	lower_time	INTEGER,
	upper_time	INTEGER,
	PRIMARY KEY(event_number, min_age, max_age),
	FOREIGN KEY(event_number) REFERENCES Events(event_number)
);

CREATE TABLE Swimmers (
	asa_number	INTEGER,
	forename	TEXT NOT NULL,
	surname	TEXT NOT NULL,
	nickname	TEXT,
	dob	TEXT,
	age	INTEGER NOT NULL,
	sex	TEXT NOT NULL,
	short_club_name	VARCHAR(12) NOT NULL,
	disability_status	TEXT,
	PRIMARY KEY(asa_number),
	FOREIGN KEY(short_club_name) REFERENCES Clubs(short_club_name)
);

create_user_tables_mysql.sql (1,765 bytes)

insert_sample_mysql.sql (14,598 bytes)

--
-- Licensed to JumpMind Inc under one or more contributor
-- license agreements.  See the NOTICE file distributed
-- with this work for additional information regarding
-- copyright ownership.  JumpMind Inc licenses this file
-- to you under the GNU General Public License, version 3.0 (GPLv3)
-- (the "License"); you may not use this file except in compliance
-- with the License.
--
-- You should have received a copy of the GNU General Public License,
-- version 3.0 (GPLv3) along with this library; if not, see
-- <http://www.gnu.org/licenses/>.
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.


------------------------------------------------------------------------------
-- Sample Data
------------------------------------------------------------------------------

-- General Meet Setup
INSERT INTO Meet (licence_number, meet_name, meet_type, start_date, end_date, location, course) VALUES ("3SE7357", "Test_Meet", "Age_Group_Open", "2021-03-27", "2021-02-28", "Prince Regent Swimming Complex", 0);

-- Add Dummy Club
insert into Clubs (short_club_name, club_code, long_club_name, club_rep_name, email_address)
values ('Portsmouth N', 'PORS', 'Portsmouth Northsea SC', 'Name One', 'name.one@gmail.com');

-- Add Dummy Swimmers
insert into Swimmers (asa_number, forename, surname, dob, age, sex, short_club_name)
values (839484, 'Matthew', 'Whittenham', '1998-11-20', 22, 0, 'Portsmouth N');
insert into Swimmers (asa_number, forename, surname, dob, age, sex, short_club_name)
values (999999, 'Emma', 'Whittenham', '2002-11-20', 18, 1, 'Portsmouth N');



------------------------------------------------------------------------------
-- Clear and load SymmetricDS Configuration
------------------------------------------------------------------------------

delete from sym_trigger_router;
delete from sym_trigger;
delete from sym_router;
delete from sym_channel where channel_id in ('sale_transaction', 'item');
delete from sym_node_group_link;
delete from sym_node_group;
delete from sym_node_host;
delete from sym_node_identity;
delete from sym_node_security;
delete from sym_node;

------------------------------------------------------------------------------
-- Channels
------------------------------------------------------------------------------

-- Channel "meet" for meet & session tables
insert into sym_channel 
(channel_id, processing_order, max_batch_size, enabled, description)
values('meet', 1, 100000, 1, 'channel for meet and session tables');

insert into sym_channel 
(channel_id, processing_order, max_batch_size, enabled, description)
values('events', 2, 100000, 1, 'channel for events & qualifying times tables');

insert into sym_channel 
(channel_id, processing_order, max_batch_size, enabled, description)
values('swimmers', 3, 100000, 1, 'channel for swimmers and clubs');





-- -- Channel "item" for tables related to items for purchase
-- insert into sym_channel 
-- (channel_id, processing_order, max_batch_size, enabled, description)
-- values('item', 1, 100000, 1, 'Item and pricing data');

------------------------------------------------------------------------------
-- Node Groups
------------------------------------------------------------------------------

insert into sym_node_group (node_group_id) values ('master');
insert into sym_node_group (node_group_id) values ('node');

------------------------------------------------------------------------------
-- Node Group Links
------------------------------------------------------------------------------

-- Master sends changes to Nodes when Master pushes to Nodes. 
insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('master', 'node', 'P');

-- Nodes send changes to Master when Nodes push to Master
insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('node', 'master', 'P');

------------------------------------------------------------------------------
-- Triggers
------------------------------------------------------------------------------

-- Triggers for tables on "all" channel
insert into sym_trigger 
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('Meet','Meet','meet',current_timestamp,current_timestamp);

insert into sym_trigger 
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('Sessions','Sessions','meet',current_timestamp,current_timestamp);

insert into sym_trigger 
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('Events','Events','events',current_timestamp,current_timestamp);

insert into sym_trigger 
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('Qualifying_Times','Qualifying_Times','events',current_timestamp,current_timestamp);

insert into sym_trigger 
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('Clubs','Clubs','swimmers',current_timestamp,current_timestamp);

insert into sym_trigger 
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('Swimmers','Swimmers','swimmers',current_timestamp,current_timestamp);

-- Triggers for tables on "all" channel - initial load only

-- insert into sym_trigger 
-- (trigger_id,source_table_name,channel_id, sync_on_insert, sync_on_update, sync_on_delete,last_update_time,create_time)
-- values('Meet_Initial','Meet','all',0,0,0,current_timestamp,current_timestamp);

-- insert into sym_trigger 
-- (trigger_id,source_table_name,channel_id, sync_on_insert, sync_on_update, sync_on_delete,last_update_time,create_time)
-- values('Clubs_Initial','Clubs','all',0,0,0,current_timestamp,current_timestamp);

-- insert into sym_trigger 
-- (trigger_id,source_table_name,channel_id, sync_on_insert, sync_on_update, sync_on_delete,last_update_time,create_time)
-- values('Events_Initial','Events','all',0,0,0,current_timestamp,current_timestamp);

-- insert into sym_trigger 
-- (trigger_id,source_table_name,channel_id, sync_on_insert, sync_on_update, sync_on_delete,last_update_time,create_time)
-- values('Qualifying_Times_Initial','Qualifying_Times','all',0,0,0,current_timestamp,current_timestamp);

-- insert into sym_trigger 
-- (trigger_id,source_table_name,channel_id, sync_on_insert, sync_on_update, sync_on_delete,last_update_time,create_time)
-- values('Sessions_Initial','Sessions','all',0,0,0,current_timestamp,current_timestamp);

-- insert into sym_trigger 
-- (trigger_id,source_table_name,channel_id, sync_on_insert, sync_on_update, sync_on_delete,last_update_time,create_time)
-- values('Swimmers_Initial','Swimmers','all',0,0,0,current_timestamp,current_timestamp);


------------------------------------------------------------------------------
-- Routers
------------------------------------------------------------------------------

-- Default router sends all data from master to nodes
insert into sym_router 
(router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
values('master_2_nodes', 'master', 'node', 'default',current_timestamp, current_timestamp);

-- Default router sends all data from nodes to master
insert into sym_router 
(router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
values('nodes_2_master', 'node', 'master', 'default',current_timestamp, current_timestamp);

-- -- Column match router will subset data from corp to specific store
-- insert into sym_router 
-- (router_id,source_node_group_id,target_node_group_id,router_type,router_expression,create_time,last_update_time)
-- values('corp_2_one_store', 'corp', 'store', 'column','STORE_ID=:EXTERNAL_ID or OLD_STORE_ID=:EXTERNAL_ID',current_timestamp, current_timestamp);


------------------------------------------------------------------------------
-- Trigger Routers
------------------------------------------------------------------------------

-- Sends Meet table data from master to nodes
insert into sym_trigger_router 
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('Meet','master_2_nodes', 100, current_timestamp, current_timestamp);

insert into sym_trigger_router 
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('Meet','nodes_2_master', 100, current_timestamp, current_timestamp);

insert into sym_trigger_router 
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('Clubs','master_2_nodes', 100, current_timestamp, current_timestamp);

insert into sym_trigger_router 
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('Clubs','nodes_2_master', 100, current_timestamp, current_timestamp);

insert into sym_trigger_router 
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('Events','master_2_nodes', 100, current_timestamp, current_timestamp);

insert into sym_trigger_router 
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('Events','nodes_2_master', 100, current_timestamp, current_timestamp);

insert into sym_trigger_router 
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('Qualifying_Times','master_2_nodes', 100, current_timestamp, current_timestamp);

insert into sym_trigger_router 
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('Qualifying_Times','nodes_2_master', 100, current_timestamp, current_timestamp);

insert into sym_trigger_router 
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('Sessions','master_2_nodes', 100, current_timestamp, current_timestamp);

insert into sym_trigger_router 
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('Sessions','nodes_2_master', 100, current_timestamp, current_timestamp);

insert into sym_trigger_router 
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('Swimmers','master_2_nodes', 100, current_timestamp, current_timestamp);

insert into sym_trigger_router 
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('Swimmers','nodes_2_master', 100, current_timestamp, current_timestamp);

-- Initial Load: 

-- insert into sym_trigger_router 
-- (trigger_id,router_id,initial_load_order,last_update_time,create_time)
-- values('Meet_Initial','master_2_nodes', 100, current_timestamp, current_timestamp);

-- insert into sym_trigger_router 
-- (trigger_id,router_id,initial_load_order,last_update_time,create_time)
-- values('Meet_Initial','nodes_2_master', 100, current_timestamp, current_timestamp);

-- insert into sym_trigger_router 
-- (trigger_id,router_id,initial_load_order,last_update_time,create_time)
-- values('Clubs_Initial','master_2_nodes', 100, current_timestamp, current_timestamp);

-- insert into sym_trigger_router 
-- (trigger_id,router_id,initial_load_order,last_update_time,create_time)
-- values('Clubs_Initial','nodes_2_master', 100, current_timestamp, current_timestamp);

-- insert into sym_trigger_router 
-- (trigger_id,router_id,initial_load_order,last_update_time,create_time)
-- values('Events_Initial','master_2_nodes', 100, current_timestamp, current_timestamp);

-- insert into sym_trigger_router 
-- (trigger_id,router_id,initial_load_order,last_update_time,create_time)
-- values('Events_Initial','nodes_2_master', 100, current_timestamp, current_timestamp);

-- insert into sym_trigger_router 
-- (trigger_id,router_id,initial_load_order,last_update_time,create_time)
-- values('Qualifying_Times_Initial','master_2_nodes', 100, current_timestamp, current_timestamp);

-- insert into sym_trigger_router 
-- (trigger_id,router_id,initial_load_order,last_update_time,create_time)
-- values('Qualifying_Times_Initial','nodes_2_master', 100, current_timestamp, current_timestamp);

-- insert into sym_trigger_router 
-- (trigger_id,router_id,initial_load_order,last_update_time,create_time)
-- values('Sessions_Initial','master_2_nodes', 100, current_timestamp, current_timestamp);

-- insert into sym_trigger_router 
-- (trigger_id,router_id,initial_load_order,last_update_time,create_time)
-- values('Sessions_Initial','nodes_2_master', 100, current_timestamp, current_timestamp);

-- insert into sym_trigger_router 
-- (trigger_id,router_id,initial_load_order,last_update_time,create_time)
-- values('Swimmers_Initial','master_2_nodes', 100, current_timestamp, current_timestamp);

-- insert into sym_trigger_router 
-- (trigger_id,router_id,initial_load_order,last_update_time,create_time)
-- values('Swimmers_Initial','nodes_2_master', 100, current_timestamp, current_timestamp);


-- -- Send all items to all stores
-- insert into sym_trigger_router 
-- (trigger_id,router_id,initial_load_order,last_update_time,create_time)
-- values('item','corp_2_store', 100, current_timestamp, current_timestamp);

-- -- Send item prices to associated stores
-- insert into sym_trigger_router 
-- (trigger_id,router_id,initial_load_order,initial_load_select,last_update_time,create_time)
-- values('item_selling_price','corp_2_one_store',100,'store_id=''$(externalId)''',current_timestamp,current_timestamp);

-- -- Send all sales transactions to corp
-- insert into sym_trigger_router 
-- (trigger_id,router_id,initial_load_order,last_update_time,create_time)
-- values('sale_transaction','store_2_corp', 200, current_timestamp, current_timestamp);

-- insert into sym_trigger_router 
-- (trigger_id,router_id,initial_load_order,last_update_time,create_time)
-- values('sale_return_line_item','store_2_corp', 200, current_timestamp, current_timestamp);

-- -- Send all sales transactions to store during initial load
-- insert into sym_trigger_router 
-- (trigger_id,router_id,initial_load_order,last_update_time,create_time)
-- values('sale_transaction_corp','corp_2_store', 200, current_timestamp, current_timestamp);

-- insert into sym_trigger_router 
-- (trigger_id,router_id,initial_load_order,last_update_time,create_time)
-- values('sale_return_line_item_corp','corp_2_store', 200, current_timestamp, current_timestamp);

insert_sample_mysql.sql (14,598 bytes)

node-001.properties (3,428 bytes)

#
# Licensed to JumpMind Inc under one or more contributor
# license agreements.  See the NOTICE file distributed
# with this work for additional information regarding
# copyright ownership.  JumpMind Inc licenses this file
# to you under the GNU General Public License, version 3.0 (GPLv3)
# (the "License"); you may not use this file except in compliance
# with the License.
#
# You should have received a copy of the GNU General Public License,
# version 3.0 (GPLv3) along with this library; if not, see
# <http://www.gnu.org/licenses/>.
#
# Unless required by applicable law or agreed to in writing,
# software distributed under the License is distributed on an
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
# KIND, either express or implied.  See the License for the
# specific language governing permissions and limitations
# under the License.
#

# Friendly name to refer to this node from command line
engine.name=node-001

# The class name for the JDBC Driver
#db.driver=com.mysql.jdbc.Driver
#db.driver=oracle.jdbc.driver.OracleDriver
#db.driver=org.postgresql.Driver
#db.driver=org.apache.derby.jdbc.EmbeddedDriver
#db.driver=org.hsqldb.jdbcDriver
#db.driver=net.sourceforge.jtds.jdbc.Driver
#db.driver=com.ibm.db2.jcc.DB2Driver
#db.driver=com.informix.jdbc.IfxDriver
#db.driver=org.firebirdsql.jdbc.FBDriver
#db.driver=interbase.interclient.Driver
db.driver=org.sqlite.JDBC
#db.driver=com.sybase.jdbc4.jdbc.SybDriver
#db.driver=com.nuodb.jdbc.Driver
#db.driver=org.h2.Driver

# The JDBC URL used to connect to the database
#db.url=jdbc:mysql://localhost/store001?tinyInt1isBit=false
#db.url=jdbc:oracle:thin:@127.0.0.1:1521:store001
#db.url=jdbc:postgresql://localhost/store001?stringtype=unspecified
#db.url=jdbc:derby:store001;create=true
#db.url=jdbc:hsqldb:file:store001;shutdown=true
#db.url=jdbc:jtds:sqlserver://localhost:1433/store001;useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880
#db.url=jdbc:db2://localhost/store001
#db.url=jdbc:informix-sqli://localhost:9088/store001:INFORMIXSERVER=ol_ids_1150_1
#db.url=jdbc:firebirdsql:localhost:/var/lib/firebird/data/databasename
#db.url=jdbc:interbase://localhost//opt/interbase/data/store001.gdb
db.url=jdbc:sqlite:C:/Users/matth/OneDrive - University of Sussex/University of Sussex/Final Year Project/symmetric-server-3.12.7-SQLiteTest/symmetric-server-3.12.7/db/node1.db
#db.url=jdbc:sybase:Tds:localhost:5000/databasename
#db.url=jdbc:com.nuodb://localhost/database?schema=database
#db.url=jdbc:h2:store001;AUTO_SERVER=TRUE;LOCK_TIMEOUT=60000

# The database user that SymmetricDS should use.
db.user=

# The database password
db.password=

# This node will contact the root node's sync.url to register itself.
registration.url=http://localhost:31415/sync/master-000

# Node group this node belongs to, which defines what it will sync with who.
# Must match the sym_node_group configuration in database.
group.id=node

# External ID for this node, which is any unique identifier you want to use.
external.id=001

# How to run routing (in millis), which puts changes into batches.
job.routing.period.time.ms=5000

# How often to run push (in millis), which sends changes to other nodes.
job.push.period.time.ms=10000

# How often to run pull (in millis), which receives changes from other nodes.
job.pull.period.time.ms=10000

#initial.load.create.first=true

node-001.properties (3,428 bytes)

node-002.properties (3,428 bytes)

#
# Licensed to JumpMind Inc under one or more contributor
# license agreements.  See the NOTICE file distributed
# with this work for additional information regarding
# copyright ownership.  JumpMind Inc licenses this file
# to you under the GNU General Public License, version 3.0 (GPLv3)
# (the "License"); you may not use this file except in compliance
# with the License.
#
# You should have received a copy of the GNU General Public License,
# version 3.0 (GPLv3) along with this library; if not, see
# <http://www.gnu.org/licenses/>.
#
# Unless required by applicable law or agreed to in writing,
# software distributed under the License is distributed on an
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
# KIND, either express or implied.  See the License for the
# specific language governing permissions and limitations
# under the License.
#

# Friendly name to refer to this node from command line
engine.name=node-002

# The class name for the JDBC Driver
#db.driver=com.mysql.jdbc.Driver
#db.driver=oracle.jdbc.driver.OracleDriver
#db.driver=org.postgresql.Driver
#db.driver=org.apache.derby.jdbc.EmbeddedDriver
#db.driver=org.hsqldb.jdbcDriver
#db.driver=net.sourceforge.jtds.jdbc.Driver
#db.driver=com.ibm.db2.jcc.DB2Driver
#db.driver=com.informix.jdbc.IfxDriver
#db.driver=org.firebirdsql.jdbc.FBDriver
#db.driver=interbase.interclient.Driver
db.driver=org.sqlite.JDBC
#db.driver=com.sybase.jdbc4.jdbc.SybDriver
#db.driver=com.nuodb.jdbc.Driver
#db.driver=org.h2.Driver

# The JDBC URL used to connect to the database
#db.url=jdbc:mysql://localhost/store001?tinyInt1isBit=false
#db.url=jdbc:oracle:thin:@127.0.0.1:1521:store001
#db.url=jdbc:postgresql://localhost/store001?stringtype=unspecified
#db.url=jdbc:derby:store001;create=true
#db.url=jdbc:hsqldb:file:store001;shutdown=true
#db.url=jdbc:jtds:sqlserver://localhost:1433/store001;useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880
#db.url=jdbc:db2://localhost/store001
#db.url=jdbc:informix-sqli://localhost:9088/store001:INFORMIXSERVER=ol_ids_1150_1
#db.url=jdbc:firebirdsql:localhost:/var/lib/firebird/data/databasename
#db.url=jdbc:interbase://localhost//opt/interbase/data/store001.gdb
db.url=jdbc:sqlite:C:/Users/matth/OneDrive - University of Sussex/University of Sussex/Final Year Project/symmetric-server-3.12.7-SQLiteTest/symmetric-server-3.12.7/db/node2.db
#db.url=jdbc:sybase:Tds:localhost:5000/databasename
#db.url=jdbc:com.nuodb://localhost/database?schema=database
#db.url=jdbc:h2:store001;AUTO_SERVER=TRUE;LOCK_TIMEOUT=60000

# The database user that SymmetricDS should use.
db.user=

# The database password
db.password=

# This node will contact the root node's sync.url to register itself.
registration.url=http://localhost:31415/sync/master-000

# Node group this node belongs to, which defines what it will sync with who.
# Must match the sym_node_group configuration in database.
group.id=node

# External ID for this node, which is any unique identifier you want to use.
external.id=002

# How to run routing (in millis), which puts changes into batches.
job.routing.period.time.ms=5000

# How often to run push (in millis), which sends changes to other nodes.
job.push.period.time.ms=10000

# How often to run pull (in millis), which receives changes from other nodes.
job.pull.period.time.ms=10000

#initial.load.create.first=true

node-002.properties (3,428 bytes)

symmetric.log (263,749 bytes)

master-000.properties (3,738 bytes)

#
# Licensed to JumpMind Inc under one or more contributor
# license agreements.  See the NOTICE file distributed
# with this work for additional information regarding
# copyright ownership.  JumpMind Inc licenses this file
# to you under the GNU General Public License, version 3.0 (GPLv3)
# (the "License"); you may not use this file except in compliance
# with the License.
#
# You should have received a copy of the GNU General Public License,
# version 3.0 (GPLv3) along with this library; if not, see
# <http://www.gnu.org/licenses/>.
#
# Unless required by applicable law or agreed to in writing,
# software distributed under the License is distributed on an
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
# KIND, either express or implied.  See the License for the
# specific language governing permissions and limitations
# under the License.
#

# Friendly name to refer to this node from command line
engine.name=master-000

# The class name for the JDBC Driver
#db.driver=com.mysql.jdbc.Driver
#db.driver=oracle.jdbc.driver.OracleDriver
#db.driver=org.postgresql.Driver
#db.driver=org.apache.derby.jdbc.EmbeddedDriver
#db.driver=org.hsqldb.jdbcDriver
#db.driver=net.sourceforge.jtds.jdbc.Driver
#db.driver=com.ibm.db2.jcc.DB2Driver
#db.driver=com.informix.jdbc.IfxDriver
#db.driver=org.firebirdsql.jdbc.FBDriver
#db.driver=interbase.interclient.Driver
#db.driver=org.sqlite.JDBC
#db.driver=com.sybase.jdbc4.jdbc.SybDriver
#db.driver=com.nuodb.jdbc.Driver
#db.driver=org.h2.Driver
db.driver=org.mariadb.jdbc.Driver

# The JDBC URL used to connect to the database
#db.url=jdbc:mysql://localhost/store001?tinyInt1isBit=false
#db.url=jdbc:oracle:thin:@127.0.0.1:1521:store001
#db.url=jdbc:postgresql://localhost/store001?stringtype=unspecified
#db.url=jdbc:derby:store001;create=true
#db.url=jdbc:hsqldb:file:store001;shutdown=true
#db.url=jdbc:jtds:sqlserver://localhost:1433/store001;useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880
#db.url=jdbc:db2://localhost/store001
#db.url=jdbc:informix-sqli://localhost:9088/store001:INFORMIXSERVER=ol_ids_1150_1
#db.url=jdbc:firebirdsql:localhost:/var/lib/firebird/data/databasename
#db.url=jdbc:interbase://localhost//opt/interbase/data/store001.gdb
#db.url=jdbc:sqlite:C:/Users/matth/OneDrive - University of Sussex/University of Sussex/Final Year Project/symmetric-server-3.12.7-SQLiteTest/symmetric-server-3.12.7/db/master.db
#db.url=jdbc:sybase:Tds:localhost:5000/databasename
#db.url=jdbc:com.nuodb://localhost/database?schema=database
#db.url=jdbc:h2:store001;AUTO_SERVER=TRUE;LOCK_TIMEOUT=60000
db.url=jdbc:mariadb:aurora//  #DB INFORMATION HAS BEEN OMITTED

# The database user that SymmetricDS should use.
db.user= #DB INFORMATION HAS BEEN OMITTED

# The database password
db.password=   #DB INFORMATION HAS BEEN OMITTED

# This node will contact the root node's sync.url to register itself.
registration.url=

# Sync URL where other nodes can contact this node to push/pull data or register.
sync.url=http://localhost:31415/sync/master-000

# Node group this node belongs to, which defines what it will sync with who.
# Must match the sym_node_group configuration in database.
group.id=master

# External ID for this node, which is any unique identifier you want to use.
external.id=000

# How to run routing (in millis), which puts changes into batches.
job.routing.period.time.ms=5000

# How often to run push (in millis), which sends changes to other nodes.
job.push.period.time.ms=10000

# How often to run pull (in millis), which receives changes from other nodes.
job.pull.period.time.ms=10000

auto.registration=true
auto.reload=true
#initial.load.create.first=true

master-000.properties (3,738 bytes)

SymmetricDS: 3.12 c06c96f0 2021-04-23 14:52:46 admin Details Diff	0004933: SQLException Error when running SymmetricDS	Affected Issues 0004933
	mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/DataExtractorService.java	Diff File
	mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/DataExtractorServiceSqlMap.java	Diff File

Date Modified	Username	Field	Change
2021-03-31 18:20	matthewwhittenham	New Issue
2021-03-31 18:20	matthewwhittenham	File Added: create_user_tables.sql
2021-03-31 18:20	matthewwhittenham	File Added: create_user_tables_mysql.sql
2021-03-31 18:20	matthewwhittenham	File Added: insert_sample_mysql.sql
2021-03-31 18:20	matthewwhittenham	File Added: node-001.properties
2021-03-31 18:20	matthewwhittenham	File Added: node-002.properties
2021-03-31 18:20	matthewwhittenham	File Added: symmetric.log
2021-03-31 18:20	matthewwhittenham	File Added: master-000.properties
2021-04-23 14:53	elong	Assigned To	=> elong
2021-04-23 14:53	elong	Status	new => resolved
2021-04-23 14:53	elong	Resolution	open => fixed
2021-04-23 14:53	elong	Fixed in Version	=> 3.12.9
2021-04-23 14:53	elong	Target Version	=> 3.12.9
2021-04-23 14:53	elong	Description Updated	View Revisions
2021-04-23 15:00	admin	Changeset attached	=> SymmetricDS 3.12 c06c96f0
2021-04-27 18:16	elong	Summary	SQLException Error when running SymmetricDS => Invalid MySQL update statement that queries same table when starting load to multiple nodes
2021-04-27 18:16	elong	Tag Attached: dialect: mysql/mariadb
2021-04-27 18:16	elong	Tag Attached: initial/partial load
2021-04-28 19:10	admin	Status	resolved => closed

View Issue Details

Activities

Related Changesets

Issue History