Rows Inserts And Deleted Immediately

[Santhosh]

Hello Sja Team..

Im using sja for linux 5.18

Immediately after inserting the rows, some rows are get deleted. I have gone thru a number of posts about the same issue. THe tables never had timestamp primary key, doesn;t differ in character sets. I couldn't find a reason for deleting the rows from the target server

Here is the log

Table SrcRows TgtRows Inserted Updated Deleted

========================= ======= ======= ======== ======= =======

`property_master` 620077 614752 5752 55906 359

`property_broker` 39138 38745 393 0 0

`property_foreclosure_loan` 86553 85539 1015 0 0

`property_lender` 182571 182126 445 5 0

`property_sales_history` 73808 72489 1322 0 0

`property_contact` 553047 545394 7655 0 1

`property_trustee` 119692 118310 1382 0 0

`property_tax` 101286 100264 1022 0 39

`property_foreclosure` 224541 222999 1542 0 0

THe schema follows

CREATE TABLE `rbtool`.`property_master` (

`PROPERTY_ID` bigint(20) NOT NULL auto_increment,

`USER_ID` bigint(20) NOT NULL,

`COUNTY_ID` mediumint(9) default NULL,

`PROPERTY_TYPE_NAME` varchar(50) default NULL,

`STATE_ID` smallint(3) default NULL,

`PROPERTY_STATUS_NAME` varchar(30) default NULL,

`RESIDENCE_TYPE_ID` smallint(3) default NULL,

`PROPERTY_STREET_DISPLAY_ADDRESS` varchar(100) default NULL,

`PROPERTY_STREET_HIDDEN_ADDRESS` varchar(100) default NULL,

`PROPERTY_CITY` varchar(50) default NULL,

`PROPERTY_ZIP` mediumint(9) default NULL,

`PROPERTY_BEDS` float(9,3) default NULL,

`PROPERTY_BATHS` float(9,3) default NULL,

`PROPERTY_SQUARE_FEET` float default NULL,

`PROPERTY_LOT_SIZE` float default NULL,

`PROPERTY_LOT_HEIGHT` float default NULL,

`PROPERTY_LOT_WIDTH` float default NULL,

`PROPERTY_FLOORS` smallint(3) default NULL,

`PROPERTY_MARKET_PRICE` double default NULL,

`PROPERTY_ESTIMATE` double default NULL,

`PROPERTY_ESTIMATE_FROM` double default NULL,

`PROPERTY_ESTIMATE_TO` double default NULL,

`PROPERTY_DESCRIPTION` varchar(5000) default NULL,

`PROPERTY_GARAGE_SIZE` float default NULL,

`PROPERTY_PARCEL_NUMBER` varchar(50) default NULL,

`PROPERTY_DOC_NUMBER` varchar(50) default NULL,

`PROPERTY_YEAR_BUILT` mediumint(4) default NULL,

`PROPERTY_ROOMS` mediumint(5) default NULL,

`PROPERTY_BDG_PRICE_SQFT` float default NULL,

`PROPERTY_LOT_PRICE_SQFT` float default NULL,

`PROPERTY_TOTAL_DUE_AMOUNT` double default NULL,

`PROPERTY_DEADLINE` varchar(50) default NULL,

`PROPERTY_APPRAISED_VALUE` double default NULL,

`PROPERTY_APPRAISAL_DATE` datetime default NULL,

`PROPERTY_ESCROW_AMOUNT` double default NULL,

`PROPERTY_INSURED` tinyint(1) NOT NULL default '0',

`PROPERTY_INSURED_VALUE` double NOT NULL default '0',

`PROPERTY_COOLING` varchar(50) default NULL,

`PROPERTY_WATER` varchar(50) default NULL,

`PROPERTY_SEWAGE` varchar(50) default NULL,

`PROPERTY_HEATING_SYSTEM` varchar(50) default NULL,

`PROPERTY_GARAGE` varchar(50) default NULL,

`PROPERTY_ELECTRIC` varchar(50) default NULL,

`PROPERTY_BASEMENT` varchar(50) default NULL,

`PROPERTY_FOUNDATION` varchar(50) default NULL,

`PROPERTY_POOL` varchar(50) default NULL,

`PROPERTY_GAS` varchar(50) default NULL,

`PROPERTY_HOA` varchar(50) default NULL,

`ACTIVE_FLAG` tinyint(1) default '1',

`REGISTRY` varchar(100) default NULL,

`ENTERED_DATE` datetime default NULL,

`SOURCE_INDEX` mediumint(9) default NULL,

`TAX_ENTERED` tinyint(1) NOT NULL default '0',

`LISTEDDATE` datetime default NULL,

`FORECLOSURE_ENTERED` tinyint(1) NOT NULL default '0',

`FORECLOSURE_LOAN_ENTERED` tinyint(1) NOT NULL default '0',

`TRUSTEE_ENTERED` tinyint(1) NOT NULL default '0',

`LENDER_ENTERED` tinyint(1) NOT NULL default '0',

`PHOTO_ENTERED` tinyint(1) NOT NULL default '0',

`CONTACT_ENTERED` tinyint(1) NOT NULL default '0',

`SALES_HISTORY_ENTERED` tinyint(1) NOT NULL default '0',

`BROKER_ENTERED` tinyint(1) NOT NULL default '0',

PRIMARY KEY (`PROPERTY_ID`),

KEY `PROPERTY_STATUS_NAME` (`PROPERTY_STATUS_NAME`),

KEY `PROPERTY_TYPE_NAME` (`PROPERTY_TYPE_NAME`),

KEY `COUNTY_ID` (`COUNTY_ID`),

KEY `RESIDENCE_TYPE_ID` (`RESIDENCE_TYPE_ID`),

KEY `STATE_ID` (`STATE_ID`),

KEY `PROPERTY_ZIP` (`PROPERTY_ZIP`),

KEY `PROPERTY_CITY` (`PROPERTY_CITY`),

KEY `PROPERTY_STREET_DISPLAY_ADDRESS` (`PROPERTY_STREET_DISPLAY_ADDRESS`),

KEY `ACTIVE_FLAG` (`ACTIVE_FLAG`),

CONSTRAINT `PROPERTY_MASTER_ibfk_1` FOREIGN KEY (`RESIDENCE_TYPE_ID`) REFERENCES `residence_type_master` (`RESIDENCE_TYPE_ID`),

CONSTRAINT `PROPERTY_MASTER_ibfk_2` FOREIGN KEY (`PROPERTY_STATUS_NAME`) REFERENCES `property_status_master` (`PROPERTY_STATUS_NAME`),

CONSTRAINT `PROPERTY_MASTER_ibfk_3` FOREIGN KEY (`STATE_ID`) REFERENCES `state_master` (`STATE_ID`),

CONSTRAINT `PROPERTY_MASTER_ibfk_4` FOREIGN KEY (`PROPERTY_TYPE_NAME`) REFERENCES `property_type_master` (`PROPERTY_TYPE_NAME`),

CONSTRAINT `PROPERTY_MASTER_ibfk_5` FOREIGN KEY (`COUNTY_ID`) REFERENCES `county_master` (`COUNTY_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `rbtool`.`property_tax` (

`PROPERTY_ID` bigint(20) NOT NULL,

`TAX_ACCOUNT_NO` varchar(50) default NULL,

`TAX_LAND_TOTAL` double default NULL,

`TAX_IMPROVEMENTS` float default NULL,

`TAX_ASSESSED_TOTAL` double default NULL,

PRIMARY KEY (`PROPERTY_ID`),

CONSTRAINT `PROPERTY_TAX_ibfk_1` FOREIGN KEY (`PROPERTY_ID`) REFERENCES `property_master` (`PROPERTY_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `rbtool`.`property_foreclosure` (

`PROPERTY_ID` bigint(20) NOT NULL,

`FC_DEFAULT_AMOUNT` double default NULL,

`FC_RECORDED_DATE` datetime default NULL,

`FC_LAST_PAYMENT` float default NULL,

`FC_OPENING_BID` double default NULL,

`FC_REFEREE` varchar(50) default NULL,

`FC_STATUS_NAME` varchar(10) default NULL,

`FC_JUDGEMENT_DATE` datetime default NULL,

`FC_AUCTION_DATE` datetime default NULL,

`FC_AUCTION_TIME` varchar(20) default NULL,

`FC_AUCTION_ADDRESS` varchar(100) default NULL,

`FC_AUCTION_CITY` varchar(50) default NULL,

`FC_DOCKET_NUMBER` varchar(50) default NULL,

PRIMARY KEY (`PROPERTY_ID`),

KEY `FC_STATUS_NAME` (`FC_STATUS_NAME`),

CONSTRAINT `PROPERTY_FORECLOSURE_ibfk_1` FOREIGN KEY (`PROPERTY_ID`) REFERENCES `property_master` (`PROPERTY_ID`),

CONSTRAINT `PROPERTY_FORECLOSURE_ibfk_2` FOREIGN KEY (`FC_STATUS_NAME`) REFERENCES `fc_status_master` (`FC_STATUS_NAME`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `rbtool`.`property_contact` (

`PROPERTY_ID` bigint(20) NOT NULL,

`CONTACT_NAME` varchar(100) default NULL,

`CONTACT_STREET_ADDRESS` varchar(100) default NULL,

`CONTACT_CITY` varchar(50) default NULL,

`STATE_ID` smallint(3) default NULL,

`CONTACT_ZIP` mediumint(9) default NULL,

`CONTACT_PHONE` varchar(20) default NULL,

`CONTACT_EMAIL` varchar(64) default NULL,

PRIMARY KEY (`PROPERTY_ID`),

KEY `PROPERTY_CONTACT_ibfk_1` (`STATE_ID`),

CONSTRAINT `PROPERTY_CONTACT_ibfk_1` FOREIGN KEY (`STATE_ID`) REFERENCES `state_master` (`STATE_ID`),

CONSTRAINT `PROPERTY_CONTACT_ibfk_2` FOREIGN KEY (`PROPERTY_ID`) REFERENCES `property_master` (`PROPERTY_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

[peterlaursen]

we will certainly look into this Monday morning.

It would be a help is you attached a small dump with data to reproduce the porblem.

You can create a ticket if you do not want to expose the data in public.

[Santhosh]

peterlaursen wrote on Nov 18 2006, 09:11 PM:

we will certainly look into this Monday morning.

It would be a help is you attached a small dump with data to reproduce the porblem.

You can create a ticket if you do not want to expose the data in public.

Please get me the steps to create a ticket. I didn't find a place to raise ticket

[peterlaursen]

I think we found this issue now!

Problem is that there is a small time-slice between UPDATES and DELETES.  If there is activity in between and if for instance there is some counter in the table that counts how many times a topic has been opened, checksums will change in between.  Actually we have been able to reproduce with this Forum itself.

We will have to reuse the checksums, generate some snapshot of (parts of) the table or find some similar solution.

It may take some days to find and implement the optimal solution …

[peterlaursen]

Please try this:

http://www.webyog.com/downloads/betas/not_…QLyog523Ent.exe

With this build we have reversed the order or INSERTs, UPDATES and DELETES.

It will now perform those in the order DELETE -> INSERT -> UPDATE.

This will ensure that if some 'trigger code' in some application changes data (typically a 'counter' row) while the sync is running (between DELETEs and INSERTs) the target database will still have all rows existing on the source at the time the last checksum was collected.

It may display a log message like

Code:

DELETES INSERTS UPDATES
============================
37    42         7

.. in case that such 'trigger code' has been active (also if there actually was nothing to be deleted on target). Actually the rows affected will first be DELETEd on the target and next the updated row on source will be INSERTEd on target. So with this example this 'application trigger code' was active on 37 rows while the sync was running and 5 new rows and 7 updated rows were found.

[Author]

Posts