activemq-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From janne postilista <jannepostilis...@gmail.com>
Subject Re: Problem creating tables for SQL Server 2008
Date Mon, 23 May 2011 12:05:19 GMT
If there's no separate DDL except for the java code, it is difficult
to create the tables manually, first I would need to decipher plenty
of stuff like:

83	                "CREATE TABLE " + getFullMessageTableName() + "(" +
"ID " + sequenceDataType + " NOT NULL"
84	                    + ", CONTAINER " + containerNameDataType + ",
MSGID_PROD " + msgIdDataType + ", MSGID_SEQ "
85	                    + sequenceDataType + ", EXPIRATION " +
longDataType + ", MSG "

Am I correct in interpreting the stack traces (Incorrect syntax the
keyword 'PRIMARY' + 'ACTIVEMQ_ACKS' already has a primary key defined
on it= as:

- for some reason activemq first creates ACTIVEMQ_ACKS, then tries to
drop it's primary key and add a new composite key
- dropping primary key fails for some syntax problem (I am not sql
server expert...)
- and hence adding the composite key fails
- if I can (after activemq automatically tries to create the tables,
and fails in the last 2 steps) manually update database so that
ACTIVEMQ_ACKS has primary key as PRIMARY KEY (CONTAINER, CLIENT_ID,
SUB_NAME, PRIORITY), the tables will be 100% as they should be

?

On Mon, May 23, 2011 at 12:40 PM, Gary Tully <gary.tully@gmail.com> wrote:
> There is a createTablesOnStartup attribute on the
> jdbcPersistenceAdapter that you can set to false to disable creation.
>
> The DDL is in the the source:
> http://svn.apache.org/viewvc/activemq/trunk/activemq-core/src/main/java/org/apache/activemq/store/jdbc/Statements.java?view=markup
>
> But the problem is we need to include some dynamic sql to determine
> the primary key before we can do the drop table.
>
> This is a known issue but it has not been fully resolved.
>
> I have reopened https://issues.apache.org/jira/browse/AMQ-3075 to track it.
>
>
> On 20 May 2011 07:35, janne postilista <jannepostilistat@gmail.com> wrote:
>> Anyone? Does the table generation always have to be done by ActiveMQ
>> when it starts, or can it be done manually?
>>
>> On Thu, May 19, 2011 at 5:20 PM, janne postilista
>> <jannepostilistat@gmail.com> wrote:
>>> It seems activeMQ creates the tables (ACTIVEMQ_ACKS, LOCK, etc) when
>>> it starts up. Can you create the tables manually? Where is the DDL for
>>> that?
>>>
>>> I have a local SQL Server 2008 that I want to configure ActiveMQ to
>>> use. My activemq.xml configuration is:
>>>
>>>     <persistenceAdapter>
>>>       <jdbcPersistenceAdapter dataSource="#sqlserver-ds"/>
>>>    </persistenceAdapter>
>>>
>>>  <bean id="sqlserver-ds"
>>> class="org.apache.commons.dbcp.BasicDataSource"
>>> destroy-method="close">
>>>    <property name="driverClassName"
>>> value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
>>>    <property name="url"
>>> value="jdbc:sqlserver://localhost:1433;databaseName=activemq;integratedSecurity=true"/>
>>>    <property name="maxActive" value="20"/>
>>>    <property name="poolPreparedStatements" value="true"/>
>>>  </bean>
>>>
>>> When I start up activemq, table creation shows errors:
>>>
>>>  INFO | Using Persistence Adapter:
>>> JDBCPersistenceAdapter(org.apache.commons.dbcp.BasicDataSource@3e026eea)
>>>  INFO | Database adapter driver override recognized for :
>>> [microsoft_sql_server_jdbc_driver_3_0] - adapter: class
>>> org.apache.activemq.store.jdbc.adapter.TransactJDBCAdapter
>>>  WARN | Could not create JDBC tables; they could already exist.
>>> Failure was: ALTER TABLE ACTIVEMQ_ACKS DROP PRIMARY KEY Message:
>>> Incorrect syntax near the keyword 'PRIMARY'. SQLState: S0001 Vendor
>>> cod
>>> e: 156
>>>  WARN | Failure details: Incorrect syntax near the keyword 'PRIMARY'.
>>> com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near
>>> the keyword 'PRIMARY'.
>>>        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
>>>        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
>>>        at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:775)
>>>        at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:676)
>>>        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
>>>        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
>>>        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
>>>        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
>>>        at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:649)
>>>        at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
>>>        at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
>>>        at org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter.doCreateTables(DefaultJDBCAdapter.java:101)
>>>        at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.start(JDBCPersistenceAdapter.java:272)
>>>        at org.apache.activemq.broker.BrokerService.start(BrokerService.java:493)
>>>        at org.apache.activemq.xbean.XBeanBrokerService.afterPropertiesSet(XBeanBrokerService.java:60)
>>>        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>>        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>>>        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>>>        at java.lang.reflect.Method.invoke(Method.java:597)
>>>        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1536)
>>>        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1477)
>>>        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1409)
>>>        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
>>>        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
>>>        at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
>>>        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
>>>        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
>>>        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
>>>        at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:574)
>>>        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:895)
>>>        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:425)
>>>        at org.apache.xbean.spring.context.ResourceXmlApplicationContext.<init>(ResourceXmlApplicationContext.java:64)
>>>        at org.apache.xbean.spring.context.ResourceXmlApplicationContext.<init>(ResourceXmlApplicationContext.java:52)
>>>        at org.apache.activemq.xbean.XBeanBrokerFactory$1.<init>(XBeanBrokerFactory.java:114)
>>>        at org.apache.activemq.xbean.XBeanBrokerFactory.createApplicationContext(XBeanBrokerFactory.java:114)
>>>        at org.apache.activemq.xbean.XBeanBrokerFactory.createBroker(XBeanBrokerFactory.java:70)
>>>        at org.apache.activemq.broker.BrokerFactory.createBroker(BrokerFactory.java:71)
>>>        at org.apache.activemq.broker.BrokerFactory.createBroker(BrokerFactory.java:54)
>>>        at org.apache.activemq.console.command.StartCommand.startBroker(StartCommand.java:115)
>>>        at org.apache.activemq.console.command.StartCommand.runTask(StartCommand.java:74)
>>>        at org.apache.activemq.console.command.AbstractCommand.execute(AbstractCommand.java:57)
>>>        at org.apache.activemq.console.command.ShellCommand.runTask(ShellCommand.java:143)
>>>        at org.apache.activemq.console.command.AbstractCommand.execute(AbstractCommand.java:57)
>>>        at org.apache.activemq.console.command.ShellCommand.main(ShellCommand.java:85)
>>>        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>>        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>>>        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>>>        at java.lang.reflect.Method.invoke(Method.java:597)
>>>        at org.apache.activemq.console.Main.runTaskClass(Main.java:251)
>>>        at org.apache.activemq.console.Main.main(Main.java:107)
>>>  WARN | Could not create JDBC tables; they could already exist.
>>> Failure was: ALTER TABLE ACTIVEMQ_ACKS ADD PRIMARY KEY (CONTAINER,
>>> CLIENT_ID, SUB_NAME, PRIORITY) Message: Table 'ACTIVEMQ_ACKS' already
>>>  has a primary key defined on it. SQLState: S0000 Vendor code: 1779
>>>  WARN | Failure details: Table 'ACTIVEMQ_ACKS' already has a primary
>>> key defined on it.
>>> com.microsoft.sqlserver.jdbc.SQLServerException: Table 'ACTIVEMQ_ACKS'
>>> already has a primary key defined on it.
>>>        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
>>>        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
>>>        at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:775)
>>>        at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:676)
>>>        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
>>>        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
>>>        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
>>>        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
>>>        at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:649)
>>>        at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
>>>        at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
>>>        at org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter.doCreateTables(DefaultJDBCAdapter.java:101)
>>>        at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.start(JDBCPersistenceAdapter.java:272)
>>>        at org.apache.activemq.broker.BrokerService.start(BrokerService.java:493)
>>>        at org.apache.activemq.xbean.XBeanBrokerService.afterPropertiesSet(XBeanBrokerService.java:60)
>>>        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>>        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>>>        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>>>        at java.lang.reflect.Method.invoke(Method.java:597)
>>>        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1536)
>>>        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1477)
>>>        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1409)
>>>        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
>>>        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
>>>        at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
>>>        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
>>>        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
>>>        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
>>>        at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:574)
>>>        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:895)
>>>        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:425)
>>>        at org.apache.xbean.spring.context.ResourceXmlApplicationContext.<init>(ResourceXmlApplicationContext.java:64)
>>>        at org.apache.xbean.spring.context.ResourceXmlApplicationContext.<init>(ResourceXmlApplicationContext.java:52)
>>>        at org.apache.activemq.xbean.XBeanBrokerFactory$1.<init>(XBeanBrokerFactory.java:114)
>>>        at org.apache.activemq.xbean.XBeanBrokerFactory.createApplicationContext(XBeanBrokerFactory.java:114)
>>>        at org.apache.activemq.xbean.XBeanBrokerFactory.createBroker(XBeanBrokerFactory.java:70)
>>>        at org.apache.activemq.broker.BrokerFactory.createBroker(BrokerFactory.java:71)
>>>        at org.apache.activemq.broker.BrokerFactory.createBroker(BrokerFactory.java:54)
>>>        at org.apache.activemq.console.command.StartCommand.startBroker(StartCommand.java:115)
>>>        at org.apache.activemq.console.command.StartCommand.runTask(StartCommand.java:74)
>>>        at org.apache.activemq.console.command.AbstractCommand.execute(AbstractCommand.java:57)
>>>        at org.apache.activemq.console.command.ShellCommand.runTask(ShellCommand.java:143)
>>>        at org.apache.activemq.console.command.AbstractCommand.execute(AbstractCommand.java:57)
>>>        at org.apache.activemq.console.command.ShellCommand.main(ShellCommand.java:85)
>>>        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>>        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>>>        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>>>        at java.lang.reflect.Method.invoke(Method.java:597)
>>>        at org.apache.activemq.console.Main.runTaskClass(Main.java:251)
>>>        at org.apache.activemq.console.Main.main(Main.java:107)
>>>  INFO | Database lock driver override recognized for :
>>> [microsoft_sql_server_jdbc_driver_3_0] - adapter: class
>>> org.apache.activemq.store.jdbc.adapter.TransactDatabaseLocker
>>>  INFO | Attempting to acquire the exclusive lock to become the Master broker
>>>  INFO | Becoming the master on dataSource:
>>> org.apache.commons.dbcp.BasicDataSource@3e026eea
>>>
>>> So it seems failed when creating the tables. I do see tables
>>> dbo.ACTIVEMQ_ACKS, _LOCK and _MSGS generated but has the error caused
>>> some problem (or can it be safely ignored)?
>>>
>>
>
>
>
> --
> http://blog.garytully.com
> http://fusesource.com
>

Mime
View raw message