Oops! My bad! Thanks Manu. Robert
On 1/22/09 8:45 PM, "Emmanuel Cecchet" <[email protected]> wrote: Hi Robert, The original message said: > >> I got the following errors on a single Sequoia controller with two backend nodes: So it looks like a single controller configuration and the distributed request log will be empty. Best, Emmanuel > > > Just out of curiosity, have you enabled the distributed request log > and if so what do you see in it? This call should be distributed to > both controllers. > > Cheers, Robert > > > On 1/22/09 5:04 PM, "Emmanuel Cecchet" <[email protected]> wrote: > > Hi Tyler, > > We implemented the fix as suggested in SQLDialect and the logs > seem to > > indicate that the call is being made appropriately, we can see > the following > > in the full_cluster.log: > > > > Statement.executeQuery() detected a stored procedure ({call > > nextval('hibernate_sequence')}/) remapping the call to > > CallableStatement.executeQuery() > > > > However the end result is the same, one of the sequences is getting > > incremented. Any ideas? > > > Unless your force the JDBC connection to read-only, the call should be > broadcast. > If you enable the logs in Postgres, do you see the request being > sent to > only one backend? > You can also set the DatabaseBackend logger to DEBUG in > log4j.properties > to track the calls and make sure they are sent to both backends. > How do you make sure that only one sequence has been updated? > Are you sure that your backup/restore mechanism properly set sequences > originally? > > Thanks for your feedback, > Emmanuel > > -----Original Message----- > > From: [email protected] > > [mailto:[email protected]] > > <mailto:[email protected]%5d><mailto:[email protected]%5d> > On Behalf > Of Emmanuel > > Cecchet > > Sent: Friday, 23 January 2009 2:15 AM > > To: Sequoia general mailing list > > Subject: Re: [Sequoia] duplicate key value violates unique constraint > > > > Hi Tyler, > > > > The easy fix is to change the SQL to access your sequences by a 'CALL > > nextval()' or 'CALL setval()' so that Sequoia treats it as a stored > > procedure call and synchronizes it properly. > > The other option, is to modify the SequoiaRequestRegExp regular > > expression (in package org.continuent.sequoia.controller.requests). > > Select statements that need to be broadcast are defined in that > regular > > expression: > > private static final String SELECT_FOR_UPDATE_PATTERN_STRING > > > = ".*select.*\\s+for\\s+update\\s*.*"; > > You can modify it so that every occurrence of setval or nextval would > > also force the select statement to be broadcast. Something like: > > private static final String SELECT_FOR_UPDATE_PATTERN_STRING > > > = > ".*select.*\\s+(nextval\\s*[(]|setval\\s*[(]|for\\s+(update)\\s*).*"; > > > > You can compile the modified class or create a new class that > need to be > > referenced from your config/controller.properties file in: > > > controller.factory=org.continuent.sequoia.controller.core.SequoiaControllerF > > actory > > > > Note that this setting is controller-wide which is in fact a design > > problem in the current Sequoia 2.10. I am moving these settings > to the > > virtual database configuration in Sequoia 4 so that you can have a > > Postgres dialect or a MySQL dialect specified for a particular > database > > and have different setting co-exist inside the same controller. > > > > Hope this helps, > > Emmanuel > > > > > > > >> Thanks for your reply. We managed to get to the root of the > problem that > >> caused the key violations. The reason is that we are using Postgres > >> > > Sequence > > > >> to generate an incremented serial number. The problem is that > the queries > >> that set the serial number uses "select" which only gets > executed by one > >> backend node. Here is a dump of queries to recreate the scenario: > >> > >> -- > >> -- Create sequence and set value > >> -- > >> CREATE SEQUENCE test_sequence > >> INCREMENT BY 1 > >> NO MAXVALUE > >> NO MINVALUE > >> CACHE 1; > >> > >> -- > >> -- Set starting value for sequence > >> -- > >> SELECT setval('test_sequence', 1000, true); > >> > >> -- > >> -- Test with increment of node (from controller). It returns 1001 > >> -- > >> SELECT nextval('test_sequence'); > >> > >> -- > >> -- Test with a select on each node, one node returns 1001, the other > >> > > returns > > > >> 1 > >> -- > >> SELECT * from test_sequence; > >> > >> Is there anyway we can make Sequoia to pass the select queries > to both > >> nodes? or is there any better solutions for this scenario? We > are happy to > >> sacrifice a bit of performance to get consistent data. > >> > >> Regards, > >> Tyler > >> > >> > >> > >> -----Original Message----- > >> From: [email protected] > >> [mailto:[email protected]] > > <mailto:[email protected]%5d><mailto:[email protected]%5d> > On Behalf > Of Emmanuel > >> Cecchet > >> Sent: Tuesday, 20 January 2009 1:36 PM > >> To: Sequoia general mailing list > >> Subject: Re: [Sequoia] duplicate key value violates unique > constraint > >> > >> Hi Tyler, > >> > >> From the messages you sent, it looks like the request fails > >> consistently on both backends. So it looks like the behavior is > >> consistent and you might have a race condition in your > application that > >> can lead to the foreign key violation. > >> If the problem only occurs under a certain load, the delay > introduced by > >> Sequoia might exacerbate some synchronization issues in the > application. > >> You might be able to reproduce the issue with a standalone > database if > >> you artificially load the node where the database is running. > >> > >> Hope this helps, > >> Emmanuel > >> > >> > >> > >> > >>> I got the following errors on a single Sequoia controller with two > >>> backend nodes: > >>> > >>> 009-01-20 09:59:48,302 INFO controller.loadbalancer.RAIDb1 write > >>> request 48348 failed: > >>> > >>> Backend ABDR - BackendWorkerThread for backend 'cluster2' with > RAIDb > >>> level:1 failed (ERROR: insert or update on table "contactdetails" > >>> violates f > >>> > >>> oreign key constraint "fk5cbbce22494239c6" > >>> > >>> Detail: Key (emailaddress)=(569596) is not present in table > >>> "emailaddress".) > >>> > >>> Backend ABDR - BackendWorkerThread for backend 'cluster1' with > RAIDb > >>> level:1 failed (ERROR: insert or update on table "contactdetails" > >>> violates f > >>> > >>> oreign key constraint "fk5cbbce22494239c6" > >>> > >>> Detail: Key (emailaddress)=(569596) is not present in table > >>> "emailaddress".) > >>> > >>> 2009-01-20 09:59:48,303 INFO > >>> virtualdatabase.VirtualDatabaseWorkerThread.ABDR Error during > command > >>> execution (write request 48348 failed: > >>> > >>> Backend ABDR - BackendWorkerThread for backend 'cluster2' with > RAIDb > >>> level:1 failed (ERROR: insert or update on table "contactdetails" > >>> violates f > >>> > >>> oreign key constraint "fk5cbbce22494239c6" > >>> > >>> Detail: Key (emailaddress)=(569596) is not present in table > >>> "emailaddress".) > >>> > >>> Backend ABDR - BackendWorkerThread for backend 'cluster1' with > RAIDb > >>> level:1 failed (ERROR: insert or update on table "contactdetails" > >>> violates f > >>> > >>> oreign key constraint "fk5cbbce22494239c6" > >>> > >>> Detail: Key (emailaddress)=(569596) is not present in table > >>> "emailaddress".) > >>> > >>> It appeared to me that it is a result of previous query not > executed > >>> on one node before the next query gets executed. This error happens > >>> randomly. > >>> > >>> we managed to repeat this problem only when the server is running > >>> under certain load. Also, FYI, WaitForCompletion policy is > already set > >>> to "all". > >>> > >>> Any help is appreciated. > >>> > >>> Tyler > >>> > >>> > ------------------------------------------------------------------------ > >>> > >>> _______________________________________________ > >>> Sequoia mailing list > >>> [email protected] > >>> https://forge.continuent.org/mailman/listinfo/sequoia > >>> > >>> > >> > >> > > > > > > > > > -- > Emmanuel Cecchet > FTO @ Frog Thinker > Open Source Development & Consulting > -- > Web: http://www.frogthinker.org > email: [email protected] > Skype: emmanuel_cecchet > > _______________________________________________ > Sequoia mailing list > [email protected] > https://forge.continuent.org/mailman/listinfo/sequoia > > > > -- > Robert Hodges, CTO, Continuent, Inc. > Email: [email protected] > Mobile: +1-510-501-3728 Skype: hodgesrm > ------------------------------------------------------------------------ > > _______________________________________________ > Sequoia mailing list > [email protected] > https://forge.continuent.org/mailman/listinfo/sequoia -- Emmanuel Cecchet FTO @ Frog Thinker Open Source Development & Consulting -- Web: http://www.frogthinker.org email: [email protected] Skype: emmanuel_cecchet _______________________________________________ Sequoia mailing list [email protected] https://forge.continuent.org/mailman/listinfo/sequoia -- Robert Hodges, CTO, Continuent, Inc. Email: [email protected] Mobile: +1-510-501-3728 Skype: hodgesrm
_______________________________________________ Sequoia mailing list [email protected] https://forge.continuent.org/mailman/listinfo/sequoia
