You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@activemq.apache.org by He...@systema.com on 2022/03/14 10:52:11 UTC

Problem with MSSQL lease lock

Hello Community,

after updating my ActivMQ (+OS, Java, MSSQL lease lock DB)
I get this message in the activemq.log: 

022-03-14 11:32:13,646 | WARN  | Could not create JDBC tables; they could 
already exist. Failure was: CREATE TABLE ACTIVEMQ_MSGS(ID BIGINT NOT NULL, 
CONTAINER VARCHAR(250) NOT NULL, MSGID_PROD VARCHAR(250), MSGID_SEQ 
BIGINT, EXPIRATION BIGINT, MSG BLOB, PRIMARY KEY ( ID ) ) Message: Column, 
parameter, or variable #6: Cannot find data type BLOB. SQLState: S0006 
Vendor code: 2715 | 
org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter | main
2022-03-14 11:32:13,647 | WARN  | Failure details: Column, parameter, or 
variable #6: Cannot find data type BLOB. | 
org.apache.activemq.store.jdbc.JDBCPersistenceAdapter | main


I find on the Microsoft page: "BLOB types can be used with varbinary(max)"
But how can I fix the Statement in ActiveMQ 5-16-4?
Or can it be overridden by config?

Best Regards 

Herbert

Re: Problem with MSSQL lease lock

Posted by Jean-Baptiste Onofré <jb...@nanthrax.net>.
Hi,

According to the log, it seems you are using DefaultJDBCAdaptor. You have
to use MS SQL compliant adapter:

org.apache.activemq.store.jdbc.adapter.TransactJDBCAdapter

Regards
JB

On Mon, Mar 14, 2022 at 11:52 AM <He...@systema.com> wrote:

> Hello Community,
>
> after updating my ActivMQ (+OS, Java, MSSQL lease lock DB)
> I get this message in the activemq.log:
>
> 022-03-14 11:32:13,646 | WARN  | Could not create JDBC tables; they could
> already exist. Failure was: CREATE TABLE ACTIVEMQ_MSGS(ID BIGINT NOT NULL,
> CONTAINER VARCHAR(250) NOT NULL, MSGID_PROD VARCHAR(250), MSGID_SEQ BIGINT,
> EXPIRATION BIGINT, MSG BLOB, PRIMARY KEY ( ID ) ) Message: Column,
> parameter, or variable #6: Cannot find data type BLOB. SQLState: S0006
> Vendor code: 2715 |
> org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter | main
> 2022-03-14 11:32:13,647 | WARN  | Failure details: Column, parameter, or
> variable #6: Cannot find data type BLOB. |
> org.apache.activemq.store.jdbc.JDBCPersistenceAdapter | main
>
>
> I find on the Microsoft page: "BLOB types can be used with varbinary(max)"
> But how can I fix the Statement in ActiveMQ 5-16-4?
> Or can it be overridden by config?
>
> Best Regards
>
> Herbert
> ------------------------------
>
>
> *Herbert Helmstreit*Dipl.-Phys.
> Software Engineer
>
> [image: SYSTEMA Logo] <https://www.systema.com>
>
> Phone: +49 941 / 7 83 92 36
> Fax: +49 351 / 88 24 772
>
> Herbert.Helmstreit@systema.com | www.systema.com
>
> [image: LinkedIn] <https://www.linkedin.com/company/systema-gmbh/>[image:
> Facebook] <https://de-de.facebook.com/SYSTEMA.automation/>[image: XING]
> <https://www.xing.com/pages/systemagmbh>
>
> SYSTEMA Systementwicklung Dipl.-Inf. Manfred Austen GmbH
> Schikanederstraße 2b - Posthof | 93053 Regensburg
> HRB 11256 Amtsgericht Dresden | USt.-ID DE 159 607 786
> Geschäftsführer: Manfred Austen, CEO und Dr. Ulf Martin, COO
>
>

Antwort: Re: Problem with MSSQL lease lock

Posted by He...@systema.com.
Hello Tim,

thank you for comming back to my question.
We have SQL server 19
Unfortunatelly I have changed too much at a time.
But after reading the doc / sources I came to the following workaround:

           <persistenceAdapter>
                        <jdbcPersistenceAdapter 
dataSource="#mssql-data-source" lockKeepAlivePeriod="5000">
                                  <statements>
                                        <statements binaryDataType 
="varbinary(max)"/>
                                  </statements>
                                <locker>
                                        <lease-database-locker 
maxAllowableDiffFromDBTime="1000" lockAcquireSleepInterval="10000"/>
                                </locker>
                         </jdbcPersistenceAdapter> 
                </persistenceAdapter> 

The statement section was missing in the old variant.
But the database bean definition stayed all the same

   <!--
        This bean is the MS-SQL 19 DataSource used as persistence adapter
        with the MS jdbc driver mssql-jdbc-7.2.2.jre8.jar and OpenJDK10
     -->
     <bean id="mssql-data-source" 
class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
                <property name="driverClassName" 
value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
                .....
                <property name="poolPreparedStatements" value="true"/>
     </bean> 

A little bit strange....


Herbert





Von:    "Tim Bain" <tb...@alumni.duke.edu>
An:     "ActiveMQ Users" <us...@activemq.apache.org>
Datum:  14.03.2022 14:01
Betreff:        Re: Problem with MSSQL lease lock



Interestingly, AMQ-6904 defined the SQL Server adapter but didn't use it 
in
https://git-wip-us.apache.org/repos/asf?p=activemq.git;a=tree;f=activemq-jdbc-store/src/main/resources/META-INF/services/org/apache/activemq/store/jdbc
.
Would you be willing to do some JAR surgery to edit the SQL Server files
from that directory to reference SqlServerJDBCAdapter rather than
TransactJDBCAdapter? I'd guess only the 6.1 file needs a change, but maybe
try them one by one to determine the minimum set?

Also, what SQL Server version are you using?

Tim

On Mon, Mar 14, 2022, 6:42 AM Tim Bain <tb...@alumni.duke.edu> wrote:

> Support for SQL Server (including the use of varbinary) was added in
> 5.15.11 and 5.16.0 under AMQ-6904 (
> https://issues.apache.org/jira/browse/AMQ-6904), but the broker is using
> DefaultJDBCAdapter instead. Can you show your config?
>
> Tim
>
> On Mon, Mar 14, 2022, 4:52 AM <He...@systema.com> wrote:
>
>> Hello Community,
>>
>> after updating my ActivMQ (+OS, Java, MSSQL lease lock DB)
>> I get this message in the activemq.log:
>>
>> 022-03-14 11:32:13,646 | WARN  | Could not create JDBC tables; they 
could
>> already exist. Failure was: CREATE TABLE ACTIVEMQ_MSGS(ID BIGINT NOT 
NULL,
>> CONTAINER VARCHAR(250) NOT NULL, MSGID_PROD VARCHAR(250), MSGID_SEQ 
BIGINT,
>> EXPIRATION BIGINT, MSG BLOB, PRIMARY KEY ( ID ) ) Message: Column,
>> parameter, or variable #6: Cannot find data type BLOB. SQLState: S0006
>> Vendor code: 2715 |
>> org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter | main
>> 2022-03-14 11:32:13,647 | WARN  | Failure details: Column, parameter, 
or
>> variable #6: Cannot find data type BLOB. |
>> org.apache.activemq.store.jdbc.JDBCPersistenceAdapter | main
>>
>>
>> I find on the Microsoft page: "BLOB types can be used with 
varbinary(max)"
>> But how can I fix the Statement in ActiveMQ 5-16-4?
>> Or can it be overridden by config?
>>
>> Best Regards
>>
>> Herbert
>> ------------------------------
>>
>>
>> *Herbert Helmstreit*Dipl.-Phys.
>> Software Engineer
>>
>> [image: SYSTEMA Logo] <https://www.systema.com>
>>
>> Phone: +49 941 / 7 83 92 36
>> Fax: +49 351 / 88 24 772
>>
>> Herbert.Helmstreit@systema.com | www.systema.com
>>
>> [image: LinkedIn] <https://www.linkedin.com/company/systema-gmbh/
>[image:
>> Facebook] <https://de-de.facebook.com/SYSTEMA.automation/>[image: XING]
>> <https://www.xing.com/pages/systemagmbh>
>>
>> SYSTEMA Systementwicklung Dipl.-Inf. Manfred Austen GmbH
>> Schikanederstraße 2b - Posthof | 93053 Regensburg
>> HRB 11256 Amtsgericht Dresden | USt.-ID DE 159 607 786
>> Geschäftsführer: Manfred Austen, CEO und Dr. Ulf Martin, COO
>>
>>

Re: Problem with MSSQL lease lock

Posted by Tim Bain <tb...@alumni.duke.edu>.
Interestingly, AMQ-6904 defined the SQL Server adapter but didn't use it in
https://git-wip-us.apache.org/repos/asf?p=activemq.git;a=tree;f=activemq-jdbc-store/src/main/resources/META-INF/services/org/apache/activemq/store/jdbc.
Would you be willing to do some JAR surgery to edit the SQL Server files
from that directory to reference SqlServerJDBCAdapter rather than
TransactJDBCAdapter? I'd guess only the 6.1 file needs a change, but maybe
try them one by one to determine the minimum set?

Also, what SQL Server version are you using?

Tim

On Mon, Mar 14, 2022, 6:42 AM Tim Bain <tb...@alumni.duke.edu> wrote:

> Support for SQL Server (including the use of varbinary) was added in
> 5.15.11 and 5.16.0 under AMQ-6904 (
> https://issues.apache.org/jira/browse/AMQ-6904), but the broker is using
> DefaultJDBCAdapter instead. Can you show your config?
>
> Tim
>
> On Mon, Mar 14, 2022, 4:52 AM <He...@systema.com> wrote:
>
>> Hello Community,
>>
>> after updating my ActivMQ (+OS, Java, MSSQL lease lock DB)
>> I get this message in the activemq.log:
>>
>> 022-03-14 11:32:13,646 | WARN  | Could not create JDBC tables; they could
>> already exist. Failure was: CREATE TABLE ACTIVEMQ_MSGS(ID BIGINT NOT NULL,
>> CONTAINER VARCHAR(250) NOT NULL, MSGID_PROD VARCHAR(250), MSGID_SEQ BIGINT,
>> EXPIRATION BIGINT, MSG BLOB, PRIMARY KEY ( ID ) ) Message: Column,
>> parameter, or variable #6: Cannot find data type BLOB. SQLState: S0006
>> Vendor code: 2715 |
>> org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter | main
>> 2022-03-14 11:32:13,647 | WARN  | Failure details: Column, parameter, or
>> variable #6: Cannot find data type BLOB. |
>> org.apache.activemq.store.jdbc.JDBCPersistenceAdapter | main
>>
>>
>> I find on the Microsoft page: "BLOB types can be used with varbinary(max)"
>> But how can I fix the Statement in ActiveMQ 5-16-4?
>> Or can it be overridden by config?
>>
>> Best Regards
>>
>> Herbert
>> ------------------------------
>>
>>
>> *Herbert Helmstreit*Dipl.-Phys.
>> Software Engineer
>>
>> [image: SYSTEMA Logo] <https://www.systema.com>
>>
>> Phone: +49 941 / 7 83 92 36
>> Fax: +49 351 / 88 24 772
>>
>> Herbert.Helmstreit@systema.com | www.systema.com
>>
>> [image: LinkedIn] <https://www.linkedin.com/company/systema-gmbh/>[image:
>> Facebook] <https://de-de.facebook.com/SYSTEMA.automation/>[image: XING]
>> <https://www.xing.com/pages/systemagmbh>
>>
>> SYSTEMA Systementwicklung Dipl.-Inf. Manfred Austen GmbH
>> Schikanederstraße 2b - Posthof | 93053 Regensburg
>> HRB 11256 Amtsgericht Dresden | USt.-ID DE 159 607 786
>> Geschäftsführer: Manfred Austen, CEO und Dr. Ulf Martin, COO
>>
>>

Re: Problem with MSSQL lease lock

Posted by Tim Bain <tb...@alumni.duke.edu>.
Support for SQL Server (including the use of varbinary) was added in
5.15.11 and 5.16.0 under AMQ-6904 (
https://issues.apache.org/jira/browse/AMQ-6904), but the broker is using
DefaultJDBCAdapter instead. Can you show your config?

Tim

On Mon, Mar 14, 2022, 4:52 AM <He...@systema.com> wrote:

> Hello Community,
>
> after updating my ActivMQ (+OS, Java, MSSQL lease lock DB)
> I get this message in the activemq.log:
>
> 022-03-14 11:32:13,646 | WARN  | Could not create JDBC tables; they could
> already exist. Failure was: CREATE TABLE ACTIVEMQ_MSGS(ID BIGINT NOT NULL,
> CONTAINER VARCHAR(250) NOT NULL, MSGID_PROD VARCHAR(250), MSGID_SEQ BIGINT,
> EXPIRATION BIGINT, MSG BLOB, PRIMARY KEY ( ID ) ) Message: Column,
> parameter, or variable #6: Cannot find data type BLOB. SQLState: S0006
> Vendor code: 2715 |
> org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter | main
> 2022-03-14 11:32:13,647 | WARN  | Failure details: Column, parameter, or
> variable #6: Cannot find data type BLOB. |
> org.apache.activemq.store.jdbc.JDBCPersistenceAdapter | main
>
>
> I find on the Microsoft page: "BLOB types can be used with varbinary(max)"
> But how can I fix the Statement in ActiveMQ 5-16-4?
> Or can it be overridden by config?
>
> Best Regards
>
> Herbert
> ------------------------------
>
>
> *Herbert Helmstreit*Dipl.-Phys.
> Software Engineer
>
> [image: SYSTEMA Logo] <https://www.systema.com>
>
> Phone: +49 941 / 7 83 92 36
> Fax: +49 351 / 88 24 772
>
> Herbert.Helmstreit@systema.com | www.systema.com
>
> [image: LinkedIn] <https://www.linkedin.com/company/systema-gmbh/>[image:
> Facebook] <https://de-de.facebook.com/SYSTEMA.automation/>[image: XING]
> <https://www.xing.com/pages/systemagmbh>
>
> SYSTEMA Systementwicklung Dipl.-Inf. Manfred Austen GmbH
> Schikanederstraße 2b - Posthof | 93053 Regensburg
> HRB 11256 Amtsgericht Dresden | USt.-ID DE 159 607 786
> Geschäftsführer: Manfred Austen, CEO und Dr. Ulf Martin, COO
>
>