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> 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> 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
