There should be a single statements element with attributes, so something like:
<persistenceAdapter>
<jdbcPersistenceAdapter dataDirectory=".." lockKeepAlivePeriod="..">
<statements>
<statements
lockCreateStatement="SELECT * FROM
ACTIVEMQ_LOCK FOR UPDATE WAIT 300"
stringIdDataType=".." >
</statements>
</statements>
</jdbcPersistenceAdapter>
</persistenceAdapter>
On 28 May 2012 07:15, Alex Hooper <ahoo...@bmjgroup.com> wrote:
> Gary Tully uttered:
>
>> you can set a specific statement string via the setter on statements
>> element.
>>
>> eg: peek for lockCreateStatement in the schema
>>
>> and have a look at
>> http://fusesource.com/docs/broker/5.4/persistence/JDBC-Customize.html
>>
>
> Hm, yes -- that does look spot on. However, when I try
>
> <persistenceAdapter>
>
> <jdbcPersistenceAdapter brokerName="prod-s01"
> dataDirectory="${activemq.base}/data" dataSource="#oracle-ds"
> useDatabaseLock="true" lockKeepAlivePeriod="10">
> <statements>
> <statements stringIdDataType="VARCHAR(128)" />
> <statements lockCreateStatement="SELECT * FROM ACTIVEMQ_LOCK FOR
> UPDATE WAIT 300" />
> </statements>
> </jdbcPersistenceAdapter>
> </persistenceAdapter>
>
>
> Then activemq won't start up and the log stops at:
>
> 2012-05-28 05:55:45,554 | DEBUG | Found XML schema
> [http://www.springframework.org/schema/beans/spring-beans-2.0.xsd] in
> classpath: org/springframework/beans/factory/xml/spring-beans-2.0.xsd |
> org.springframework.beans.factory.xml.PluggableSchemaResolver | main
> 2012-05-28 05:55:45,629 | TRACE | Trying to resolve XML entity with public
> id [null] and system id
> [http://activemq.apache.org/schema/core/activemq-core.xsd] |
> org.springframework.beans.factory.xml.PluggableSchemaResolver | main
> 2012-05-28 05:55:45,645 | DEBUG | Found XML schema
> [http://activemq.apache.org/schema/core/activemq-core.xsd] in classpath:
> activemq.xsd | org.springframework.beans.factory.xml.PluggableSchemaResolver
> | main
>
> Which leaves me at a bit of a loss. The config looks right according to the
> schema, and I can't find any examples.
>
> For info, if I remove the lockCreateStatement line, it starts up normally:
>
> 2012-05-25 15:16:33,498 | DEBUG | Could not create JDBC tables; The message
> tabl
> e already existed. Failure was: ALTER TABLE ACTIVEMQ_MSGS ADD PRIORITY
> NUMBER Me
> ssage: ORA-01430: column being added already exists in table
> SQLState: 72000 Vendor code: 1430 |
> org.apache.activemq.store.jdbc.adapter.Defa
> ultJDBCAdapter | main
> 2012-05-25 15:16:33,498 | DEBUG | Executing SQL: CREATE INDEX
> ACTIVEMQ_MSGS_PIDX ON ACTIVEMQ_MSGS (PRIORITY) |
> org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter | main
> 2012-05-25 15:16:33,528 | DEBUG | Could not create JDBC tables; The message
> table already existed. Failure was: CREATE INDEX ACTIVEMQ_MSGS_PIDX ON
> ACTIVEMQ_MSGS (PRIORITY) Message: ORA-00955: name is already used by an
> existing object
> SQLState: 42000 Vendor code: 955 |
> org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter | main
> 2012-05-25 15:16:33,528 | DEBUG | Executing SQL: ALTER TABLE ACTIVEMQ_ACKS
> ADD PRIORITY NUMBER DEFAULT 5 NOT NULL |
> org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter | main
> 2012-05-25 15:16:33,546 | DEBUG | Could not create JDBC tables; The message
> table already existed. Failure was: ALTER TABLE ACTIVEMQ_ACKS ADD PRIORITY
> NUMBER DEFAULT 5 NOT NULL Message: ORA-01430: column being added already
> exists in table
> SQLState: 72000 Vendor code: 1430 |
> org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter | main
> 2012-05-25 15:16:33,546 | DEBUG | Executing SQL: ALTER TABLE ACTIVEMQ_ACKS
> DROP PRIMARY KEY | org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter
> | main
> 2012-05-25 15:16:33,694 | DEBUG | Executing SQL: ALTER TABLE ACTIVEMQ_ACKS
> ADD PRIMARY KEY (CONTAINER, CLIENT_ID, SUB_NAME, PRIORITY) |
> org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter | main
> 2012-05-25 15:16:33,782 | INFO | Database lock driver override not found
> for : [oracle_jdbc_driver]. Will use default implementation. |
> org.apache.activemq.store.jdbc.JDBCPersistenceAdapter | main
> 2012-05-25 15:16:33,784 | DEBUG | Using default JDBC Locker:
> org.apache.activemq.store.jdbc.DefaultDatabaseLocker@2d35da43 |
> org.apache.activemq.store.jdbc.JDBCPersistenceAdapter | main
> 2012-05-25 15:16:33,784 | INFO | Attempting to acquire the exclusive lock
> to become the Master broker |
> org.apache.activemq.store.jdbc.DefaultDatabaseLocker | main
> 2012-05-25 15:16:33,784 | DEBUG | Locking Query is SELECT * FROM
> ACTIVEMQ_LOCK FOR UPDATE |
> org.apache.activemq.store.jdbc.DefaultDatabaseLocker | main
>
> Cheers,
>
>
> Alex.
>
>
>
>
>> On 25 May 2012 15:17, Alex Hooper <ahoo...@bmjgroup.com> wrote:
>>>
>>> Gary Tully uttered:
>>>
>>>> the keepAlive kicks in after the start() has successfully obtained the
>>>> lock, so a slave should just block, but a master should check the lock
>>>> status every period. By default it does an update using the connection
>>>> that has a pending transaction.
>>>
>>>
>>> Ah, right, the keepAlive is for extant locks, not for keeping-alive
>>> connections that are waiting for a lock. Which is, in hindsight, exactly
>>> what the name suggests.
>>>
>>>
>>>> It may be that that update has no need to hit the server till a
>>>> commit... not sure. May depend on the driver. But it should be
>>>> sufficient to validate the jdbc connection.
>>>
>>>
>>> I cannot see a way to validate the jdbc connection while the slave is
>>> blocked waiting for a response to its SELECT FOR UPDATE as the validation
>>> can only be done on idle connections and the connection is not idle, it
>>> is
>>> actively waiting for a response to its query. Even removeAbandoned won't
>>> touch it, as . . . it hasn't been abandoned.
>>>
>>>
>>>> Have peek at the source:
>>>>
>>>>
>>>> http://svn.apache.org/viewvc/activemq/trunk/activemq-core/src/main/java/org/apache/activemq/store/jdbc/DefaultDatabaseLocker.java?view=markup
>>>>
>>> Yes, sorry -- I should have done this earlier. But it's so long since
>>> I've
>>> coded Java that I assumed finding the right bit would take me a
>>> geological
>>> age.
>>>
>>> Looking at that nice while loop, it occurs to me that another approach
>>> that
>>> would work, would be to alter the SQL used to grab the lock to so that it
>>> won't wait indefinitely, eg:
>>>
>>> SELECT * FROM ACTIVEMQ_LOCK FOR UPDATE WAIT 300
>>>
>>> From looking at
>>> http://activemq.apache.org/schema/core/activemq-core-5.5.0.xsd, I cannot
>>> see
>>> a way to supply this SQL in config. Do you happen to know whether this is
>>> possible?
>>>
>>> Meanwhile, I shall get onto our hosting company about the half-open
>>> connection.
>>>
>>> Thanks again fro your help,
>>>
>>> Alex.
>>>
>>>
>>>
>>>
>>>> On 25 May 2012 13:36, Alex Hooper <ahoo...@bmjgroup.com> wrote:
>>>>>
>>>>> Gary Tully uttered:
>>>>> [snip]
>>>>>
>>>>>> In your setup, it is odd that the dropped connection does not cause
>>>>>> the lock keepAlive to fail and the broker to terminate. It should,
>>>>>> unless there are tcp level options that need to kick in to see the
>>>>>> half close. Or some connection pool config that can pick up on the
>>>>>> failure, there are some validate options on commons jdbc pool that
>>>>>> could help there.
>>>>>>
>>>>> [snip]
>>>>>
>>>>>> Hopefully the above will help, but start with determining why in your
>>>>>> current setup, the lock keepalive is not triggering for you when the
>>>>>> connection is dropped because that is a little odd. unless you have
>>>>>> the
>>>>>>
>>>>>>
>>>>>> org.apache.activemq.store.jdbc.JDBCPersistenceAdapter#setLockKeepAlivePeriod
>>>>>> = 0.
>>>>>>
>>>>> How exactly does the lock keepalive mechanism work? I'm explicitly set
>>>>> it
>>>>> in
>>>>> the xml config now:
>>>>>
>>>>> <jdbcPersistenceAdapter brokerName="prod-s01"
>>>>> dataDirectory="${activemq.base}/data" dataSource="#oracle-ds"
>>>>> useDatabaseLock="true" lockKeepAlivePeriod="10">
>>>>> <statements>
>>>>> <statements stringIdDataType="VARCHAR(128)" />
>>>>> </statements>
>>>>> </jdbcPersistenceAdapter>
>>>>>
>>>>> But once the instance has started and issued its initial
>>>>> lock-requesting
>>>>> query, there is no further TCP activity at all. Maybe I've
>>>>> misunderstood
>>>>> the
>>>>> intent of this function; that's far from unlikely.
>>>>>
>>>>> Alex.
>>>>>
>>>>>
>>>>>
>>>>>> On 24 May 2012 11:45, Alex Hooper <ahoo...@bmjgroup.com> wrote:
>>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> We are running activemq 5.5.1 in an active/passive failover
>>>>>>> configuration
>>>>>>> with JDBC Persistence to an Oracle backend. The default strategy for
>>>>>>> determining whether the current master has failed is for the
>>>>>>> secondary
>>>>>>> server to attempt to get a lock on the database table, waiting
>>>>>>> indefinitely
>>>>>>> for the lock to be granted.
>>>>>>>
>>>>>>> This is not working (at least in our context) as, after a relatively
>>>>>>> short
>>>>>>> time in operation (a handful of hours at most) the connection to
>>>>>>> Oracle
>>>>>>> is
>>>>>>> dropped. Activemq doesn't notice this, so the secondary sits there
>>>>>>> happily
>>>>>>> waiting for a lock it can now never get and, in the event of a
>>>>>>> failure,
>>>>>>> won't serve any clients as it is not a master.
>>>>>>>
>>>>>>> Is there some way to change the decision mechanism to, eg, a polling
>>>>>>> strategy? Or can anyone suggest another resolution to this problem?
>>>>>>>
>>>>>>> Alex.
>>>>>>> --
>>>>>>> Alex Hooper
>>>>>>> Operations Team Leader, BMJ Group, BMA House, London WC1H 9JR
>>>>>>> Tel: +44 (0) 20 7383 6049
>>>>>>> http://group.bmj.com/
>>>>>>>
>>>>>>>
>>>>>>> _______________________________________________________________________
>>>>>>> The BMJ Group is one of the world's most trusted providers of medical
>>>>>>> information for doctors, researchers, health care workers and
>>>>>>> patients
>>>>>>> group.bmj.com. This email and any attachments are confidential. If
>>>>>>> you
>>>>>>> have received this email in error, please delete it and kindly notify
>>>>>>> us.
>>>>>>> If the email contains personal views then the BMJ Group accepts no
>>>>>>> responsibility for these statements. The recipient should check this
>>>>>>> email
>>>>>>> and attachments for viruses because the BMJ Group accepts no
>>>>>>> liability
>>>>>>> for
>>>>>>> any damage caused by viruses. Emails sent or received by the BMJ
>>>>>>> Group
>>>>>>> may
>>>>>>> be monitored for size, traffic, distribution and content. BMJ
>>>>>>> Publishing
>>>>>>> Group Limited trading as BMJ Group. A private limited company,
>>>>>>> registered
>>>>>>> in England and Wales under registration number 03102371. Registered
>>>>>>> office:
>>>>>>> BMA House, Tavistock Square, London WC1H 9JR, UK.
>>>>>>>
>>>>>>> _______________________________________________________________________
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>> --
>>>>> Alex Hooper
>>>>> Operations Team Leader, BMJ Group, BMA House, London WC1H 9JR
>>>>> Tel: +44 (0) 20 7383 6049
>>>>> http://group.bmj.com/
>>>>>
>>>>> _______________________________________________________________________
>>>>> The BMJ Group is one of the world's most trusted providers of medical
>>>>> information for doctors, researchers, health care workers and patients
>>>>> group.bmj.com. This email and any attachments are confidential. If
>>>>> you
>>>>> have received this email in error, please delete it and kindly notify
>>>>> us.
>>>>> If the email contains personal views then the BMJ Group accepts no
>>>>> responsibility for these statements. The recipient should check this
>>>>> email
>>>>> and attachments for viruses because the BMJ Group accepts no liability
>>>>> for
>>>>> any damage caused by viruses. Emails sent or received by the BMJ Group
>>>>> may
>>>>> be monitored for size, traffic, distribution and content. BMJ
>>>>> Publishing
>>>>> Group Limited trading as BMJ Group. A private limited company,
>>>>> registered
>>>>> in England and Wales under registration number 03102371. Registered
>>>>> office:
>>>>> BMA House, Tavistock Square, London WC1H 9JR, UK.
>>>>> _______________________________________________________________________
>>>>
>>>>
>>>>
>>>>
>>>
>>> --
>>> Alex Hooper
>>> Operations Team Leader, BMJ Group, BMA House, London WC1H 9JR
>>> Tel: +44 (0) 20 7383 6049
>>> http://group.bmj.com/
>>>
>>> _______________________________________________________________________
>>> The BMJ Group is one of the world's most trusted providers of medical
>>> information for doctors, researchers, health care workers and patients
>>> group.bmj.com. This email and any attachments are confidential. If you
>>> have received this email in error, please delete it and kindly notify us.
>>> If the email contains personal views then the BMJ Group accepts no
>>> responsibility for these statements. The recipient should check this
>>> email
>>> and attachments for viruses because the BMJ Group accepts no liability
>>> for
>>> any damage caused by viruses. Emails sent or received by the BMJ Group
>>> may
>>> be monitored for size, traffic, distribution and content. BMJ Publishing
>>> Group Limited trading as BMJ Group. A private limited company,
>>> registered
>>> in England and Wales under registration number 03102371. Registered
>>> office:
>>> BMA House, Tavistock Square, London WC1H 9JR, UK.
>>> _______________________________________________________________________
>>
>>
>>
>>
>
>
> --
> Alex Hooper
> Operations Team Leader, BMJ Group, BMA House, London WC1H 9JR
> Tel: +44 (0) 20 7383 6049
> http://group.bmj.com/
>
> _______________________________________________________________________
> The BMJ Group is one of the world's most trusted providers of medical
> information for doctors, researchers, health care workers and patients
> group.bmj.com. This email and any attachments are confidential. If you
> have received this email in error, please delete it and kindly notify us.
> If the email contains personal views then the BMJ Group accepts no
> responsibility for these statements. The recipient should check this email
> and attachments for viruses because the BMJ Group accepts no liability for
> any damage caused by viruses. Emails sent or received by the BMJ Group may
> be monitored for size, traffic, distribution and content. BMJ Publishing
> Group Limited trading as BMJ Group. A private limited company, registered
> in England and Wales under registration number 03102371. Registered office:
> BMA House, Tavistock Square, London WC1H 9JR, UK.
> _______________________________________________________________________
--
http://fusesource.com
http://blog.garytully.com
