Thanks, Emmanuel. The wait policy appears to have been the culprit. Yes, we only have one controller and no backup at the moment. We want to look at simply keeping a backup and a secondary DB incase the first one fails at the moment, with a roadmap of full recoverability and online backups. I'm trying to keep things simple for now just to get going.
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Emmanuel Cecchet Sent: Wednesday, August 20, 2008 10:17 AM To: Sequoia general mailing list Subject: Re: [Sequoia] Can someone help with this error? 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 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
