Return-Path: X-Original-To: apmail-activemq-users-archive@www.apache.org Delivered-To: apmail-activemq-users-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 078944CD9 for ; Mon, 23 May 2011 12:05:48 +0000 (UTC) Received: (qmail 7934 invoked by uid 500); 23 May 2011 12:05:47 -0000 Delivered-To: apmail-activemq-users-archive@activemq.apache.org Received: (qmail 7904 invoked by uid 500); 23 May 2011 12:05:47 -0000 Mailing-List: contact users-help@activemq.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: users@activemq.apache.org Delivered-To: mailing list users@activemq.apache.org Received: (qmail 7896 invoked by uid 99); 23 May 2011 12:05:47 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 23 May 2011 12:05:47 +0000 X-ASF-Spam-Status: No, hits=-0.7 required=5.0 tests=FREEMAIL_FROM,RCVD_IN_DNSWL_LOW,RFC_ABUSE_POST,SPF_PASS,T_TO_NO_BRKTS_FREEMAIL X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of jannepostilistat@gmail.com designates 209.85.212.43 as permitted sender) Received: from [209.85.212.43] (HELO mail-vw0-f43.google.com) (209.85.212.43) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 23 May 2011 12:05:40 +0000 Received: by vws10 with SMTP id 10so5424531vws.2 for ; Mon, 23 May 2011 05:05:19 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:date :message-id:subject:from:to:content-type:content-transfer-encoding; bh=B6xkgLC+iOccZJPvQpz3sDAnyoOvlx6SO7znDzTjxVk=; b=QvI5YN53llr1bGUrDFTyoWpfnpGndOYgnYRCBK9uALNGeqZEP4hYODjiFKm2OZ82M9 /ibL5ypuNoZq8ph0MuZgshbyDm2BlzRnTNu+OvuwDcD+IJB4GskiwNtYCSRyt2Pte8d2 p3eNmskFmBDki7wJyndBENHLOzD2HKbinNbgA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type:content-transfer-encoding; b=kDouT+EM8EYGY3zC3PvM97yKTpbORFhDr1q49vys9FWv6F6SZZVKnwM4irMe7cwca/ mgd/PuwK9tft2VYy5HLZwb0wG6afpRFtjFV1gT68OvVgSaU43dzlERcKC1pb9AM7hx9b k+Pj/wsKsv3bJtLYDzmCAajw+lMepYnIGO1+0= MIME-Version: 1.0 Received: by 10.52.0.9 with SMTP id 9mr3198220vda.278.1306152319646; Mon, 23 May 2011 05:05:19 -0700 (PDT) Received: by 10.52.159.161 with HTTP; Mon, 23 May 2011 05:05:19 -0700 (PDT) In-Reply-To: References: Date: Mon, 23 May 2011 15:05:19 +0300 Message-ID: Subject: Re: Problem creating tables for SQL Server 2008 From: janne postilista To: users@activemq.apache.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Checked: Checked by ClamAV on apache.org 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=3D 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 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/o= rg/apache/activemq/store/jdbc/Statements.java?view=3Dmarkup > > 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 i= t. > > > On 20 May 2011 07:35, janne postilista 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 >> 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: >>> >>> =A0 =A0 >>> =A0 =A0 =A0 >>> =A0 =A0 >>> >>> =A0>> class=3D"org.apache.commons.dbcp.BasicDataSource" >>> destroy-method=3D"close"> >>> =A0 =A0>> value=3D"com.microsoft.sqlserver.jdbc.SQLServerDriver"/> >>> =A0 =A0>> value=3D"jdbc:sqlserver://localhost:1433;databaseName=3Dactivemq;integr= atedSecurity=3Dtrue"/> >>> =A0 =A0 >>> =A0 =A0 >>> =A0 >>> >>> When I start up activemq, table creation shows errors: >>> >>> =A0INFO | Using Persistence Adapter: >>> JDBCPersistenceAdapter(org.apache.commons.dbcp.BasicDataSource@3e026eea= ) >>> =A0INFO | Database adapter driver override recognized for : >>> [microsoft_sql_server_jdbc_driver_3_0] - adapter: class >>> org.apache.activemq.store.jdbc.adapter.TransactJDBCAdapter >>> =A0WARN | 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 >>> =A0WARN | Failure details: Incorrect syntax near the keyword 'PRIMARY'. >>> com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near >>> the keyword 'PRIMARY'. >>> =A0 =A0 =A0 =A0at com.microsoft.sqlserver.jdbc.SQLServerException.makeF= romDatabaseError(SQLServerException.java:197) >>> =A0 =A0 =A0 =A0at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNe= xtResult(SQLServerStatement.java:1493) >>> =A0 =A0 =A0 =A0at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExe= cuteStatement(SQLServerStatement.java:775) >>> =A0 =A0 =A0 =A0at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtE= xecCmd.doExecute(SQLServerStatement.java:676) >>> =A0 =A0 =A0 =A0at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuf= fer.java:4575) >>> =A0 =A0 =A0 =A0at com.microsoft.sqlserver.jdbc.SQLServerConnection.exec= uteCommand(SQLServerConnection.java:1400) >>> =A0 =A0 =A0 =A0at com.microsoft.sqlserver.jdbc.SQLServerStatement.execu= teCommand(SQLServerStatement.java:179) >>> =A0 =A0 =A0 =A0at com.microsoft.sqlserver.jdbc.SQLServerStatement.execu= teStatement(SQLServerStatement.java:154) >>> =A0 =A0 =A0 =A0at com.microsoft.sqlserver.jdbc.SQLServerStatement.execu= te(SQLServerStatement.java:649) >>> =A0 =A0 =A0 =A0at org.apache.commons.dbcp.DelegatingStatement.execute(D= elegatingStatement.java:264) >>> =A0 =A0 =A0 =A0at org.apache.commons.dbcp.DelegatingStatement.execute(D= elegatingStatement.java:264) >>> =A0 =A0 =A0 =A0at org.apache.activemq.store.jdbc.adapter.DefaultJDBCAda= pter.doCreateTables(DefaultJDBCAdapter.java:101) >>> =A0 =A0 =A0 =A0at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter= .start(JDBCPersistenceAdapter.java:272) >>> =A0 =A0 =A0 =A0at org.apache.activemq.broker.BrokerService.start(Broker= Service.java:493) >>> =A0 =A0 =A0 =A0at org.apache.activemq.xbean.XBeanBrokerService.afterPro= pertiesSet(XBeanBrokerService.java:60) >>> =A0 =A0 =A0 =A0at sun.reflect.NativeMethodAccessorImpl.invoke0(Native M= ethod) >>> =A0 =A0 =A0 =A0at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMet= hodAccessorImpl.java:39) >>> =A0 =A0 =A0 =A0at sun.reflect.DelegatingMethodAccessorImpl.invoke(Deleg= atingMethodAccessorImpl.java:25) >>> =A0 =A0 =A0 =A0at java.lang.reflect.Method.invoke(Method.java:597) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.AbstractAut= owireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanF= actory.java:1536) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.AbstractAut= owireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactor= y.java:1477) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.AbstractAut= owireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.j= ava:1409) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.AbstractAut= owireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.jav= a:519) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.AbstractAut= owireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:= 456) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.AbstractBea= nFactory$1.getObject(AbstractBeanFactory.java:291) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.DefaultSing= letonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.AbstractBea= nFactory.doGetBean(AbstractBeanFactory.java:288) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.AbstractBea= nFactory.getBean(AbstractBeanFactory.java:190) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.DefaultList= ableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:57= 4) >>> =A0 =A0 =A0 =A0at org.springframework.context.support.AbstractApplicati= onContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:8= 95) >>> =A0 =A0 =A0 =A0at org.springframework.context.support.AbstractApplicati= onContext.refresh(AbstractApplicationContext.java:425) >>> =A0 =A0 =A0 =A0at org.apache.xbean.spring.context.ResourceXmlApplicatio= nContext.(ResourceXmlApplicationContext.java:64) >>> =A0 =A0 =A0 =A0at org.apache.xbean.spring.context.ResourceXmlApplicatio= nContext.(ResourceXmlApplicationContext.java:52) >>> =A0 =A0 =A0 =A0at org.apache.activemq.xbean.XBeanBrokerFactory$1.= (XBeanBrokerFactory.java:114) >>> =A0 =A0 =A0 =A0at org.apache.activemq.xbean.XBeanBrokerFactory.createAp= plicationContext(XBeanBrokerFactory.java:114) >>> =A0 =A0 =A0 =A0at org.apache.activemq.xbean.XBeanBrokerFactory.createBr= oker(XBeanBrokerFactory.java:70) >>> =A0 =A0 =A0 =A0at org.apache.activemq.broker.BrokerFactory.createBroker= (BrokerFactory.java:71) >>> =A0 =A0 =A0 =A0at org.apache.activemq.broker.BrokerFactory.createBroker= (BrokerFactory.java:54) >>> =A0 =A0 =A0 =A0at org.apache.activemq.console.command.StartCommand.star= tBroker(StartCommand.java:115) >>> =A0 =A0 =A0 =A0at org.apache.activemq.console.command.StartCommand.runT= ask(StartCommand.java:74) >>> =A0 =A0 =A0 =A0at org.apache.activemq.console.command.AbstractCommand.e= xecute(AbstractCommand.java:57) >>> =A0 =A0 =A0 =A0at org.apache.activemq.console.command.ShellCommand.runT= ask(ShellCommand.java:143) >>> =A0 =A0 =A0 =A0at org.apache.activemq.console.command.AbstractCommand.e= xecute(AbstractCommand.java:57) >>> =A0 =A0 =A0 =A0at org.apache.activemq.console.command.ShellCommand.main= (ShellCommand.java:85) >>> =A0 =A0 =A0 =A0at sun.reflect.NativeMethodAccessorImpl.invoke0(Native M= ethod) >>> =A0 =A0 =A0 =A0at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMet= hodAccessorImpl.java:39) >>> =A0 =A0 =A0 =A0at sun.reflect.DelegatingMethodAccessorImpl.invoke(Deleg= atingMethodAccessorImpl.java:25) >>> =A0 =A0 =A0 =A0at java.lang.reflect.Method.invoke(Method.java:597) >>> =A0 =A0 =A0 =A0at org.apache.activemq.console.Main.runTaskClass(Main.ja= va:251) >>> =A0 =A0 =A0 =A0at org.apache.activemq.console.Main.main(Main.java:107) >>> =A0WARN | 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 >>> =A0has a primary key defined on it. SQLState: S0000 Vendor code: 1779 >>> =A0WARN | 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. >>> =A0 =A0 =A0 =A0at com.microsoft.sqlserver.jdbc.SQLServerException.makeF= romDatabaseError(SQLServerException.java:197) >>> =A0 =A0 =A0 =A0at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNe= xtResult(SQLServerStatement.java:1493) >>> =A0 =A0 =A0 =A0at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExe= cuteStatement(SQLServerStatement.java:775) >>> =A0 =A0 =A0 =A0at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtE= xecCmd.doExecute(SQLServerStatement.java:676) >>> =A0 =A0 =A0 =A0at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuf= fer.java:4575) >>> =A0 =A0 =A0 =A0at com.microsoft.sqlserver.jdbc.SQLServerConnection.exec= uteCommand(SQLServerConnection.java:1400) >>> =A0 =A0 =A0 =A0at com.microsoft.sqlserver.jdbc.SQLServerStatement.execu= teCommand(SQLServerStatement.java:179) >>> =A0 =A0 =A0 =A0at com.microsoft.sqlserver.jdbc.SQLServerStatement.execu= teStatement(SQLServerStatement.java:154) >>> =A0 =A0 =A0 =A0at com.microsoft.sqlserver.jdbc.SQLServerStatement.execu= te(SQLServerStatement.java:649) >>> =A0 =A0 =A0 =A0at org.apache.commons.dbcp.DelegatingStatement.execute(D= elegatingStatement.java:264) >>> =A0 =A0 =A0 =A0at org.apache.commons.dbcp.DelegatingStatement.execute(D= elegatingStatement.java:264) >>> =A0 =A0 =A0 =A0at org.apache.activemq.store.jdbc.adapter.DefaultJDBCAda= pter.doCreateTables(DefaultJDBCAdapter.java:101) >>> =A0 =A0 =A0 =A0at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter= .start(JDBCPersistenceAdapter.java:272) >>> =A0 =A0 =A0 =A0at org.apache.activemq.broker.BrokerService.start(Broker= Service.java:493) >>> =A0 =A0 =A0 =A0at org.apache.activemq.xbean.XBeanBrokerService.afterPro= pertiesSet(XBeanBrokerService.java:60) >>> =A0 =A0 =A0 =A0at sun.reflect.NativeMethodAccessorImpl.invoke0(Native M= ethod) >>> =A0 =A0 =A0 =A0at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMet= hodAccessorImpl.java:39) >>> =A0 =A0 =A0 =A0at sun.reflect.DelegatingMethodAccessorImpl.invoke(Deleg= atingMethodAccessorImpl.java:25) >>> =A0 =A0 =A0 =A0at java.lang.reflect.Method.invoke(Method.java:597) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.AbstractAut= owireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanF= actory.java:1536) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.AbstractAut= owireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactor= y.java:1477) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.AbstractAut= owireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.j= ava:1409) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.AbstractAut= owireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.jav= a:519) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.AbstractAut= owireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:= 456) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.AbstractBea= nFactory$1.getObject(AbstractBeanFactory.java:291) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.DefaultSing= letonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.AbstractBea= nFactory.doGetBean(AbstractBeanFactory.java:288) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.AbstractBea= nFactory.getBean(AbstractBeanFactory.java:190) >>> =A0 =A0 =A0 =A0at org.springframework.beans.factory.support.DefaultList= ableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:57= 4) >>> =A0 =A0 =A0 =A0at org.springframework.context.support.AbstractApplicati= onContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:8= 95) >>> =A0 =A0 =A0 =A0at org.springframework.context.support.AbstractApplicati= onContext.refresh(AbstractApplicationContext.java:425) >>> =A0 =A0 =A0 =A0at org.apache.xbean.spring.context.ResourceXmlApplicatio= nContext.(ResourceXmlApplicationContext.java:64) >>> =A0 =A0 =A0 =A0at org.apache.xbean.spring.context.ResourceXmlApplicatio= nContext.(ResourceXmlApplicationContext.java:52) >>> =A0 =A0 =A0 =A0at org.apache.activemq.xbean.XBeanBrokerFactory$1.= (XBeanBrokerFactory.java:114) >>> =A0 =A0 =A0 =A0at org.apache.activemq.xbean.XBeanBrokerFactory.createAp= plicationContext(XBeanBrokerFactory.java:114) >>> =A0 =A0 =A0 =A0at org.apache.activemq.xbean.XBeanBrokerFactory.createBr= oker(XBeanBrokerFactory.java:70) >>> =A0 =A0 =A0 =A0at org.apache.activemq.broker.BrokerFactory.createBroker= (BrokerFactory.java:71) >>> =A0 =A0 =A0 =A0at org.apache.activemq.broker.BrokerFactory.createBroker= (BrokerFactory.java:54) >>> =A0 =A0 =A0 =A0at org.apache.activemq.console.command.StartCommand.star= tBroker(StartCommand.java:115) >>> =A0 =A0 =A0 =A0at org.apache.activemq.console.command.StartCommand.runT= ask(StartCommand.java:74) >>> =A0 =A0 =A0 =A0at org.apache.activemq.console.command.AbstractCommand.e= xecute(AbstractCommand.java:57) >>> =A0 =A0 =A0 =A0at org.apache.activemq.console.command.ShellCommand.runT= ask(ShellCommand.java:143) >>> =A0 =A0 =A0 =A0at org.apache.activemq.console.command.AbstractCommand.e= xecute(AbstractCommand.java:57) >>> =A0 =A0 =A0 =A0at org.apache.activemq.console.command.ShellCommand.main= (ShellCommand.java:85) >>> =A0 =A0 =A0 =A0at sun.reflect.NativeMethodAccessorImpl.invoke0(Native M= ethod) >>> =A0 =A0 =A0 =A0at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMet= hodAccessorImpl.java:39) >>> =A0 =A0 =A0 =A0at sun.reflect.DelegatingMethodAccessorImpl.invoke(Deleg= atingMethodAccessorImpl.java:25) >>> =A0 =A0 =A0 =A0at java.lang.reflect.Method.invoke(Method.java:597) >>> =A0 =A0 =A0 =A0at org.apache.activemq.console.Main.runTaskClass(Main.ja= va:251) >>> =A0 =A0 =A0 =A0at org.apache.activemq.console.Main.main(Main.java:107) >>> =A0INFO | Database lock driver override recognized for : >>> [microsoft_sql_server_jdbc_driver_3_0] - adapter: class >>> org.apache.activemq.store.jdbc.adapter.TransactDatabaseLocker >>> =A0INFO | Attempting to acquire the exclusive lock to become the Master= broker >>> =A0INFO | 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 >