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