On 6/1/2010 2:16 PM, Steve Singer wrote:
> Jason Culverhouse wrote:
>> Steve,
>> I tracked it down to a TRUNCATE TABLE on company_zip_updated.
>> This table is just a transient record keeping table, items get inserted,
>> processed and deleted.
>>
>> The question is, would a drop set on the set that contains the table repair
>> the problem?
>> i.e, will this remove the statements that update that table from the log
>> since the SET is removed?
>> OR
>> should I consider the EXECUTE SCRIPT ONLY ON to remove the offending rows in
>> that table on the replicas?
>
> A DROP SET won't fix your problem because the problem insert will still
> be sl_log. (In the future we might want to change things somehow so
> the drop set deletes the pending data?)
Ah right, forgot about that one.
Yes, but that also requires that some events move somewhat out of
bounds. Which is a rather dramatic change in the overall design.
Jan
>
> Scott's idea of doing a TRUNCATE on both ends makes sense. The
> TRUNCATE doesn't even need to be inside of the execute script (since the
> _deny() trigger won't fire)
>
>
>
>>
>> I goal is to avoid replicating the whole database again.
>> Jason
>>
>> On Jun 1, 2010, at 9:48 AM, Steve Singer wrote:
>>
>>> Jason Culverhouse wrote:
>>>> I have a problem where my replication is hung, I don't really know where
>>>> to start.... This table is in set "38", most everything is in set "1", it
>>>> looks like set 38 isn't "caught up" in the ordering.
>>>> Any Idea's on how to repair this? Is this problem because the set's are
>>>> not merged? Can I merge the sets? Version is slony1-1.2.15 on postgres 8.3
>>> Your problem appears to be that a row being inserted into your replica is
>>> already on your replica so it is failing. This is bad and I am very
>>> curious to know how your cluster got into this state.
>>>
>>> Have you mean making any schema changes to this cluster. Was EXECUTE
>>> SCRIPT used or was it not used?
>>>
>>> Have you tried making any data changes through EXECUTE SCRIPT (ie would
>>> someone have run a EXECUTE SCRIPT (..ONLY ON ..) the replica that inserst
>>> data into your company_zip_updated table).
>>>
>>> Options for fixing it include
>>>
>>> -Dropping this node from replication and rebuilding the replica. Depending
>>> on the size of your data this might be the simplest.
>>>
>>> -Deleting the offending row of company_zip_updated from the replica using
>>> EXECUTE SCRIPT ONLY ON and letting replication progress. I will warn you
>>> that this is treating the visible symptom only and does not address the
>>> cause. This could make things worse for you (it is hard to say without
>>> knowing what the actual problem is)
>>>
>>>
>>>
>>>
>>>
>>>
>>>> 2010-06-01 09:11:53 PDT DEBUG2 syncThread: new sl_action_seq 1 - SYNC
>>>> 574847
>>>> 2010-06-01 09:11:53 PDT DEBUG2 remoteWorkerThread_50: forward confirm
>>>> 60,574847 received by 50
>>>> 2010-06-01 09:11:53 PDT DEBUG2 remoteWorkerThread_40: SYNC 33381140
>>>> processing
>>>> 2010-06-01 09:11:53 PDT DEBUG2 remoteWorkerThread_40: syncing set 38 with
>>>> 4 table(s) from provider 40
>>>> 2010-06-01 09:11:53 PDT DEBUG2 ssy_action_list length: 0
>>>> 2010-06-01 09:11:53 PDT DEBUG2 remoteWorkerThread_40: syncing set 1 with
>>>> 139 table(s) from provider 40
>>>> 2010-06-01 09:11:53 PDT DEBUG2 ssy_action_list length: 0
>>>> 2010-06-01 09:11:53 PDT DEBUG2 remoteWorkerThread_40: current local
>>>> log_status is 0
>>>> 2010-06-01 09:11:53 PDT DEBUG2 remoteWorkerThread_40_40: current remote
>>>> log_status = 1
>>>> 2010-06-01 09:11:53 PDT DEBUG2 remoteHelperThread_40_40: 0.008 seconds
>>>> delay for first row
>>>> 2010-06-01 09:11:53 PDT ERROR remoteWorkerThread_40: "update only
>>>> "public"."review" set zipcode_id='80203' where id='1026409';
>>>> update only "public"."review" set zipcode_id='80203' where id='1026401';
>>>> update only "public"."review" set zipcode_id='80203' where id='1008795';
>>>> update only "public"."review" set zipcode_id='80203' where id='1008048';
>>>> update only "public"."review" set zipcode_id='80203' where id='1007445';
>>>> insert into "public"."company_zip_updated" (id,company_id,zipcode_id)
>>>> values ('1205','30149282','80122');
>>>> update only "public"."merchant" set zipcode_id='80203' where id='905955';
>>>> update only "public"."advertisement" set zipcode_id='80203' where
>>>> id='1467390';
>>>> update only "public"."advertisement" set zipcode_id='80203' where
>>>> id='1375973';
>>>> update only "public"."advertisement" set zipcode_id='80203' where
>>>> id='1389545';
>>>> " ERROR: duplicate key value violates unique constraint
>>>> "company_zip_updated_company_id_key"
>>>> - qualification was: where log_origin = 40 and ( (
>>>> log_tableid in (158,159,160,161)
>>>> and (log_xid < '622094999')
>>>> and (log_xid >= '622094987')
>>>> ) or (
>>>> log_tableid in
>>>> (124,121,117,118,6,22,23,25,26,31,32,43,44,45,46,55,56,57,59,78,115,79,106,114,116,3,4,5,9,11,12,13,14,15,1,7,2,8,16,17,27,28,29,33,34,36,39,40,41,42,47,48,49,50,51,52,53,54,61,63,64,65,69,71,72,74,75,76,77,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,107,108,109,110,111,112,113,119,120,123,122,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157)
>>>> and (log_xid < '622094999')
>>>> and (log_xid >= '622094987')
>>>> ) )
>>>> 2010-06-01 09:11:53 PDT DEBUG2 remoteHelperThread_40_40: 0.016 seconds
>>>> until close cursor
>>>> 2010-06-01 09:11:53 PDT DEBUG2 remoteHelperThread_40_40: inserts=1
>>>> updates=79 deletes=0
>>>> 2010-06-01 09:11:53 PDT ERROR remoteWorkerThread_40: SYNC aborted
>>>> Here is the table definition
>>>> Table "public.company_zip_updated"
>>>> Column | Type | Modifiers
>>>>
>>>> ------------+---------+------------------------------------------------------------------
>>>> id | integer | not null default
>>>> nextval('company_zip_updated_id_seq'::regclass)
>>>> company_id | integer | not null
>>>> zipcode_id | integer | not null
>>>> Indexes:
>>>> "company_zip_updated_pkey" PRIMARY KEY, btree (id)
>>>> "company_zip_updated_company_id_key" UNIQUE, btree (company_id)
>>>> Foreign-key constraints:
>>>> "company_zip_updated_company_id_fkey" FOREIGN KEY (company_id)
>>>> REFERENCES company(id) ON DELETE CASCADE
>>>> "company_zip_updated_zipcode_id_fkey" FOREIGN KEY (zipcode_id)
>>>> REFERENCES zipcode(zip) ON DELETE CASCADE
>>>> Triggers:
>>>> _mc_cluster_logtrigger_161 AFTER INSERT OR DELETE OR UPDATE ON
>>>> company_zip_updated FOR EACH ROW EXECUTE PROCEDURE
>>>> _mc_cluster.logtrigger('_mc_cluster', '161', 'kvv')
>>>> _______________________________________________
>>>> Slony1-general mailing list
>>>> [email protected]
>>>> http://lists.slony.info/mailman/listinfo/slony1-general
>>>
>>> --
>>> Steve Singer
>>> Afilias Canada
>>> Data Services Developer
>>> 416-673-1142
>>
>
>
--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin
_______________________________________________
Slony1-general mailing list
[email protected]
http://lists.slony.info/mailman/listinfo/slony1-general
