Hi Emmanuel,
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?
Regards,
Tyler
-----Original Message-----
From: [email protected]
[mailto:[email protected]] 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]] 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
_______________________________________________
Sequoia mailing list
[email protected]
https://forge.continuent.org/mailman/listinfo/sequoia
