Hi Jonathan,
What happened is that 1 request succeeded on primary but failed on
backup because of a foreign key constraint violation.
I guess that you have a single controller since you don't seem to have a
recovery log (in which case you will never be able to recover from a
failure).
There might be a problem related to the fact that you use
WaitForCompletion policy="first". In that case, queries are allowed to
process in parallel and if your backup backend was behind, it might be
possible that the insert with the foreign key violation executed before
the insert for the key completed. This case is handled within
transactions but I am not sure in autocommit mode.
Basically you have 2 options: set WaitForCompletion policy="all" or
sequence your key insert with the following inserts in the same transaction.
Let me know if that solves your issue,
Emmanuel
I’m not sure what I’m seeing here. Do I need to disable auto-commits?
Is the information below enough for a diagnosis? It’s a dump from the
controller output in WindowsXP. I’m running Sequoia 2.10.10 with
Apache Derby. I have one controller on the same machine as the
“primary” backend. I have one DB on the “primary” backend and one on
the “backup” backend. After launching our application, the database
should be populated with object information. The DB on the “primary”
backend is fine, but I have no data on the “backup” DB. The first
warning worries me. What’s going on there? Why is it aborting? Do I
need to have something in my configuration? I’m using very basic
configs. The following is my setup for the controller:
<RequestManager caseSensitiveParsing="true">
<RequestScheduler>
<RAIDb-1Scheduler level="passThrough"/>
</RequestScheduler>
<RequestCache>
<MetadataCache/>
<ParsingCache/>
<!-- <ResultCache granularity="table"/> -->
</RequestCache>
<LoadBalancer>
<RAIDb-1>
<WaitForCompletion policy="first"/>
<RAIDb-1-LeastPendingRequestsFirst/>
</RAIDb-1>
</LoadBalancer>
C:\swisslog\DB\Sequoia\bin>controller.bat
09:37:44,558 INFO controller.core.Controller Sequoia controller (2.10.10)
09:37:44,620 INFO controller.core.Controller Loading configuration
file: /C:/swisslog/DB/Sequoia/config/controller/con
troller.xml
09:37:44,667 INFO controller.core.Controller JMX is enabled
09:37:44,698 INFO controller.core.Controller Starting JMX server on
host: 172.24.25.60
09:37:44,698 WARN controller.core.Controller Could not parse RMI
Server Port 'null
09:37:44,980 INFO DatabaseBackend.TLCDATA.primary Adding connection
manager for virtual user "PTSDATA"
09:37:44,980 INFO DatabaseBackend.TLCDATA.backup Adding connection
manager for virtual user "PTSDATA"
09:37:45,042 INFO controller.RequestManager.TLCDATA Request manager
will parse requests with the following granularity
: TABLE
09:37:45,058 WARN controller.virtualdatabase.TLCDATA No recovery log
has been configured, enabling backend without che
ckpoint.
09:37:45,292 INFO DatabaseBackend.TLCDATA.primary Detected backend as:
Apache Derby
09:37:45,355 WARN DatabaseBackend.TLCDATA.primary
Statement.setCursorName not supported.
09:37:45,745 INFO controller.RequestManager.TLCDATA Setting new
virtual database schema.
09:37:45,761 INFO controller.RequestManager.TLCDATA Database backend
primary is now enabled
09:37:45,792 INFO DatabaseBackend.TLCDATA.backup Detected backend as:
Apache Derby
09:37:45,792 WARN DatabaseBackend.TLCDATA.backup
Statement.setCursorName not supported.
09:37:46,152 INFO controller.RequestManager.TLCDATA Virtual database
schema merged with new schema.
09:37:46,152 INFO controller.RequestManager.TLCDATA Database backend
backup is now enabled
09:37:46,199 INFO controller.core.Controller Adding VirtualDatabase
TLCDATA
09:37:46,199 INFO controller.core.Controller Loading backend
interceptor classes
09:37:46,199 INFO controller.core.Controller Loading frontend
interceptor classes
09:37:46,214 INFO controller.core.Controller Interceptors initialized
09:37:46,214 INFO controller.core.Controller Waiting for connections
on 0.0.0.0:25322
09:37:46,214 INFO controller.core.PingResponder Ping responder started
09:37:46,230 INFO controller.core.Controller Controller started on
2008.08.20 34 at 09:37:46 AM MDT
09:37:46,230 INFO controller.core.Controller Controller
172.24.25.60:25322 ready, listening to requests ...
09:42:44,706 WARN DatabaseBackend.TLCDATA.backup A worker thread was
still processing task Autocommit StatementExecute
UpdateTask 10 (INSERT INTO PTSDATA.EQUIPMENT (EQUIP_ID, SITE_ID,
ALIAS, EQUIP_DESC, EQUIP_TYPE, ZONE_ID, MODE) VALUES (
?, ?, ?, ?, ?, ?,
?)/<!%I|100|!%><!%I|1|!%><!%S|Blower100|!%><!%S||!%><!%S|Blower|!%><!%I|1|!%><!%S|AUTOMATIC|!%>),
abo
rting the request execution.
09:42:44,706 ERROR backend.DatabaseBackend.backup Request 'INSERT INTO
PTSDATA.PI_STATE (EQUIP_ID, ...' failed on backe
nd backup but 1 succeeded
(java.sql.SQLIntegrityConstraintViolationException: INSERT on table
'PI_STATE' caused a viola
tion of foreign key constraint 'FK_PI_STATE_1' for key (291,1). The
statement has been rolled back.)
09:42:44,706 ERROR backend.DatabaseBackend.backup Request 'INSERT INTO
PTSDATA.STATION_STATE (EQUIP...' failed on backe
nd backup but 1 succeeded
(java.sql.SQLIntegrityConstraintViolationException: INSERT on table
'STATION_STATE' caused a
violation of foreign key constraint 'FK_STATION_STATE_1' for key
(10,1). The statement has been rolled back.)
09:42:44,722 WARN backend.DatabaseBackend.backup Task execution failed
(java.sql.SQLException: Request 'INSERT INTO PT
SDATA.PI_STATE (EQUIP_ID, ...' failed on backend backup but 1
succeeded (java.sql.SQLIntegrityConstraintViolationExcept
ion: INSERT on table 'PI_STATE' caused a violation of foreign key
constraint 'FK_PI_STATE_1' for key (291,1). The stat
ement has been rolled back.))
09:42:44,738 WARN backend.DatabaseBackend.backup Task execution failed
(java.sql.SQLException: Request 'INSERT INTO PT
SDATA.STATION_STATE (EQUIP...' failed on backend backup but 1
succeeded (java.sql.SQLIntegrityConstraintViolationExcept
ion: INSERT on table 'STATION_STATE' caused a violation of foreign key
constraint 'FK_STATION_STATE_1' for key (10,1).
The statement has been rolled back.))
09:42:44,769 WARN DatabaseBackend.TLCDATA.backup Unable to cancel
execution of request
java.sql.SQLException: 'Statement' already closed.
at
org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown
Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
at org.apache.derby.client.am.Statement.cancel(Unknown Source)
at
org.continuent.sequoia.controller.loadbalancer.BackendTaskQueues.abortRemainingRequests(BackendTaskQueues.ja
va:280)
at
org.continuent.sequoia.controller.loadbalancer.BackendTaskQueues.abortRemainingRequests(BackendTaskQueues.ja
va:248)
at
org.continuent.sequoia.controller.backend.DatabaseBackend.terminateWorkerThreads(DatabaseBackend.java:3094)
at
org.continuent.sequoia.controller.loadbalancer.raidb1.RAIDb1.terminateThreadsAndConnections(RAIDb1.java:2282
)
at
org.continuent.sequoia.controller.loadbalancer.raidb1.RAIDb1.disableBackend(RAIDb1.java:2258)
at
org.continuent.sequoia.controller.loadbalancer.tasks.StatementExecuteUpdateTask.executeInAutoCommit(Statemen
tExecuteUpdateTask.java:250)
at
org.continuent.sequoia.controller.loadbalancer.tasks.StatementExecuteUpdateTask.executeTask(StatementExecute
UpdateTask.java:116)
at
org.continuent.sequoia.controller.loadbalancer.tasks.AbstractTask.execute(AbstractTask.java:141)
at
org.continuent.sequoia.controller.loadbalancer.BackendWorkerThread.run(BackendWorkerThread.java:197)
Caused by: org.apache.derby.client.am.SqlException: 'Statement'
already closed.
at
org.apache.derby.client.am.Statement.checkForClosedStatement(Unknown
Source)
... 10 more
09:42:44,816 WARN DatabaseBackend.TLCDATA.backup A worker thread was
still processing task Autocommit StatementExecute
UpdateTask 17 (INSERT INTO PTSDATA.STATION_STATE (EQUIP_ID, SITE_ID,
STATE_VAL, SEND_PRIORITY, RECV_PRIORITY, ASSIGNED,
PRESENT, FORWARDING, SENT, RECEIVED, FULL_COUNT, EVENT_COUNT) VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)/<!%I|10|!%><
!%I|1|!%><!%S|OFF|!%><!%I|0|!%><!%I|0|!%><!%I|0|!%><!%I|15|!%><!%N|4|!%><!%L|442381631577|!%><!%L|0|!%><!%L|53385928093
20448|!%><!%L|-3689348814741910324|!%>), aborting the request execution.
09:42:44,831 WARN DatabaseBackend.TLCDATA.backup Unable to remove task
Autocommit StatementExecuteUpdateTask 17 (INSER
T INTO PTSDATA.STATION_STATE (EQUIP_ID, SITE_ID, STATE_VAL,
SEND_PRIORITY, RECV_PRIORITY, ASSIGNED, PRESENT, FORWARDING
, SENT, RECEIVED, FULL_COUNT, EVENT_COUNT) VALUES (?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?)/<!%I|10|!%><!%I|1|!%><!%S|OFF|!%
><!%I|0|!%><!%I|0|!%><!%I|0|!%><!%I|15|!%><!%N|4|!%><!%L|442381631577|!%><!%L|0|!%><!%L|5338592809320448|!%><!%L|-36893
48814741910324|!%>) from pending request queue
09:42:44,847 WARN DatabaseBackend.TLCDATA.backup A worker thread was
still processing task Autocommit StatementExecute
UpdateTask 27 (INSERT INTO PTSDATA.PI_STATE (EQUIP_ID, SITE_ID,
STATE_VAL, RECV_PRIORITY, UPSTREAM_STA, FORWARDING) VAL
UES (?, ?, ?, ?, ?,
?)/<!%I|291|!%><!%I|1|!%><!%S|OFF|!%><!%I|1|!%><!%I|20|!%><!%N|4|!%>),
aborting the request executi
on.
09:42:44,847 WARN DatabaseBackend.TLCDATA.backup Unable to remove task
Autocommit StatementExecuteUpdateTask 27 (INSER
T INTO PTSDATA.PI_STATE (EQUIP_ID, SITE_ID, STATE_VAL, RECV_PRIORITY,
UPSTREAM_STA, FORWARDING) VALUES (?, ?, ?, ?, ?,
?)/<!%I|291|!%><!%I|1|!%><!%S|OFF|!%><!%I|1|!%><!%I|20|!%><!%N|4|!%>)
from pending request queue
09:42:44,863 INFO sequoia.controller.connection 4 connections freed on
"jdbc:derby://susd4test1:1527/TLCDATA"
09:42:44,863 WARN sequoia.controller.connection Some connections are
still active, destroying them.
09:42:44,925 ERROR backend.DatabaseBackend.backup Request 'INSERT INTO
PTSDATA.EQUIPMENT (EQUIP_ID,...' failed on backe
nd backup but 1 succeeded
(java.sql.SQLIntegrityConstraintViolationException: INSERT on table
'EQUIPMENT' caused a viol
ation of foreign key constraint 'FK_EQUIPMENT_1' for key (1,1). The
statement has been rolled back.)
09:42:44,925 ERROR DatabaseBackend.TLCDATA.backup Failed to remove
task Autocommit StatementExecuteUpdateTask 17 (INSER
T INTO PTSDATA.STATION_STATE (EQUIP_ID, SITE_ID, STATE_VAL,
SEND_PRIORITY, RECV_PRIORITY, ASSIGNED, PRESENT, FORWARDING
, SENT, RECEIVED, FULL_COUNT, EVENT_COUNT) VALUES (?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?)/<!%I|10|!%><!%I|1|!%><!%S|OFF|!%
><!%I|0|!%><!%I|0|!%><!%I|0|!%><!%I|15|!%><!%N|4|!%><!%L|442381631577|!%><!%L|0|!%><!%L|5338592809320448|!%><!%L|-36893
48814741910324|!%>) from []
09:42:44,925 WARN backend.DatabaseBackend.backup Task execution failed
(java.sql.SQLException: Request 'INSERT INTO PT
SDATA.EQUIPMENT (EQUIP_ID,...' failed on backend backup but 1
succeeded (java.sql.SQLIntegrityConstraintViolationExcept
ion: INSERT on table 'EQUIPMENT' caused a violation of foreign key
constraint 'FK_EQUIPMENT_1' for key (1,1). The stat
ement has been rolled back.))
09:42:44,941 ERROR DatabaseBackend.TLCDATA.backup Failed to remove
task Autocommit StatementExecuteUpdateTask 27 (INSER
T INTO PTSDATA.PI_STATE (EQUIP_ID, SITE_ID, STATE_VAL, RECV_PRIORITY,
UPSTREAM_STA, FORWARDING) VALUES (?, ?, ?, ?, ?,
?)/<!%I|291|!%><!%I|1|!%><!%S|OFF|!%><!%I|1|!%><!%I|20|!%><!%N|4|!%>)
from []
09:42:44,941 WARN DatabaseBackend.TLCDATA.backup Unable to remove task
Autocommit StatementExecuteUpdateTask 10 (INSER
T INTO PTSDATA.EQUIPMENT (EQUIP_ID, SITE_ID, ALIAS, EQUIP_DESC,
EQUIP_TYPE, ZONE_ID, MODE) VALUES (?, ?, ?, ?, ?, ?, ?)
/<!%I|100|!%><!%I|1|!%><!%S|Blower100|!%><!%S||!%><!%S|Blower|!%><!%I|1|!%><!%S|AUTOMATIC|!%>)
from pending request que
ue
09:42:44,972 ERROR DatabaseBackend.TLCDATA.backup Failed to remove
task Autocommit StatementExecuteUpdateTask 10 (INSER
T INTO PTSDATA.EQUIPMENT (EQUIP_ID, SITE_ID, ALIAS, EQUIP_DESC,
EQUIP_TYPE, ZONE_ID, MODE) VALUES (?, ?, ?, ?, ?, ?, ?)
/<!%I|100|!%><!%I|1|!%><!%S|Blower100|!%><!%S||!%><!%S|Blower|!%><!%I|1|!%><!%S|AUTOMATIC|!%>)
from []
Jonathan Stockho
Software Developer
Swisslog Healthcare Solutions
10825 E. 47th Avenue
Denver, CO 80239
Phone: 303-373-7814
FAX: 303-373-7872
e-mail: [EMAIL PROTECTED]
Internet: www.swisslog.com <http://www.swisslog.com>
------------------------------------------------------------------------------------
*About Swisslog *
Swisslog designs solutions in logistics automation that greatly reduce
the manual flow of materials and associated information within
healthcare facilities.
This message may contain legally privileged or confidential
information and is therefore addressed to the named persons only. The
recipient should inform the sender and delete this message, if he/she
is not named as addressee. The sender disclaims any and all liability
for the integrity and punctuality of this message. The sender has
activated an automatic virus scanning, but does not guarantee the
virus free transmission of this message.
------------------------------------------------------------------------
_______________________________________________
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
