4.2.c on WS2k8, Tomcat-7 & SQL Server 2005: db config issues...?

[oofdachile]

Hi, folks –

We need to prove that we could run Alfresco in a production environment if we wish to move away from a current, commercial ECM solution.

I set up & tested a Tomcat-7 server on Windows Server 2008r2 (ssl, LDAP authentication from AD, etc) and then created a database in a SQL Server 2005 rdbms, along with a new dbo user with "SQL Server Authentication", which I also tested.  Then I tried to follow the "Installing" // "Installing Alfresco Enterprise" // "Installing Alfresco on Tomcat" instructions here:

http://docs.alfresco.com/4.1/index.jsp

… in the absence of finding instructions for Community 4.2 WAR & Tomcat.  I also used the guidelines in the alfresco-global.properties file to configure Alfresco for a SQL Server database and a dir.root pointing into a CIFS share whose folders are tested as writable by the user running Tomcat.

First question:  where are the best instructions for WAR installs of Alfresco 4.2.x on top of existing Tomcat servers?

When I start Tomcat, its deployments of Alfresco are awash in error messages that start like this:

INFO: Starting service Catalina
Jun 20, 2013 2:31:52 PM org.apache.catalina.core.StandardEngine startInternal
INFO: Starting Servlet Engine: Apache Tomcat/7.0.39
Jun 20, 2013 2:31:52 PM org.apache.catalina.startup.HostConfig deployWAR
INFO: Deploying web application archive D:\Tomcat\7.x\webapps\alfresco.war
Jun 20, 2013 2:32:19 PM org.apache.catalina.startup.TaglibUriRule body
INFO: TLD skipped. URI: http://java.sun.com/jsp/jstl/core is already defined
Jun 20, 2013 2:32:19 PM org.apache.catalina.core.StandardContext addApplicationListener
INFO: The listener "org.apache.myfaces.webapp.StartupServletContextListener" is already configured for this context. The duplicate definition has been ignored.
Jun 20, 2013 2:32:19 PM org.apache.catalina.authenticator.AuthenticatorBase startInternal
FINE: No SingleSignOn Valve is present
Jun 20, 2013 2:32:21 PM org.apache.catalina.core.ApplicationContext log
INFO: Initializing Spring root WebApplicationContext
2013-06-20 14:32:34,363  ERROR [web.context.ContextLoader] [localhost-startStop-1] Context initialization failed
 org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'tenantService' defined in file [D:\Tomcat\7.x\webapps\alfresco\WEB-INF\classes\alfresco\mt\mt-context.xml]: Cannot resolve reference to bean 'tenantAdminDAO' while setting bean property 'tenantAdminDAO'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'tenantAdminDAO' defined in class path resource [alfresco/dao/dao-context.xml]: Cannot resolve reference to bean 'repoSqlSessionTemplate' while setting bean property 'sqlSessionTemplate'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'repoSqlSessionTemplate' defined in class path resource [alfresco/ibatis/ibatis-context.xml]: Cannot resolve reference to bean 'repoSqlSessionFactory' while setting constructor argument; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'repoSqlSessionFactory' defined in class path resource [alfresco/ibatis/ibatis-context.xml]: Invocation of init method failed; nested exception is org.springframework.core.NestedIOException: Failed to parse config resource: class path resource [alfresco/ibatis/alfresco-SqlMapConfig.xml]; nested exception is org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: org.apache.ibatis.builder.BuilderException: Failed to get resource: alfresco/ibatis/#resource.dialect#/activities-insert-SqlMap.xml
        at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:328)
        at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1325)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1086)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517)
        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:580)
        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:895)
        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:425)
        at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:276)
        at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:197)
        at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:47)
        at org.alfresco.web.app.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:63)
        at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4887)
        at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5381)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
        at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)
        at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)
        at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:633)
        at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:977)
        at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1655)
        at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
        at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
        at java.util.concurrent.FutureTask.run(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
        at java.lang.Thread.run(Unknown Source)
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'tenantAdminDAO' defined in class path resource [alfresco/dao/dao-context.xml]: Cannot resolve reference to bean 'repoSqlSessionTemplate' while setting bean property 'sqlSessionTemplate'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'repoSqlSessionTemplate' defined in class path resource [alfresco/ibatis/ibatis-context.xml]: Cannot resolve reference to bean 'repoSqlSessionFactory' while setting constructor argument; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'repoSqlSessionFactory' defined in class path resource [alfresco/ibatis/ibatis-context.xml]: Invocation of init method failed; nested exception is org.springframework.core.NestedIOException: Failed to parse config resource: class path resource  [alfresco/ibatis/alfresco-SqlMapConfig.xml]; nested exception is org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause:   org.apache.ibatis.builder.BuilderException: Failed to get resource: alfresco/ibatis/#resource.dialect#/activities-insert-SqlMap.xml
        at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:328)
        at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1325)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1086)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517)
        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.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:322)
        … 30 more
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

… and end (really, start, if I understand the "Caused By…" statements in the messages) with:

Caused by: org.springframework.core.NestedIOException: Failed to parse config resource: class path resource [alfresco/ibatis/alfresco-SqlMapConfig.xml]; nested exception is org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: org.apache.ibatis.builder.BuilderException: Failed to get resource: alfresco/ibatis/#resource.dialect#/activities-insert-SqlMap.xml
        at org.alfresco.ibatis.HierarchicalSqlSessionFactoryBean.buildSqlSessionFactory(HierarchicalSqlSessionFactoryBean.java:248)
        at org.alfresco.ibatis.HierarchicalSqlSessionFactoryBean.afterPropertiesSet(HierarchicalSqlSessionFactoryBean.java:98)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1477)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1417)
        … 59 more
Caused by: org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: org.apache.ibatis.builder.BuilderException: Failed to get resource: alfresco/ibatis/#resource.dialect#/activities-insert-SqlMap.xml
        at org.alfresco.ibatis.HierarchicalXMLConfigBuilder.parseConfiguration(HierarchicalXMLConfigBuilder.java:128)
        at org.alfresco.ibatis.HierarchicalXMLConfigBuilder.parse(HierarchicalXMLConfigBuilder.java:110)
        at org.alfresco.ibatis.HierarchicalSqlSessionFactoryBean.buildSqlSessionFactory(HierarchicalSqlSessionFactoryBean.java:246)
        … 62 more
Caused by: org.apache.ibatis.builder.BuilderException: Failed to get resource: alfresco/ibatis/#resource.dialect#/activities-insert-SqlMap.xml
        at org.alfresco.ibatis.HierarchicalXMLConfigBuilder.mapperElement(HierarchicalXMLConfigBuilder.java:306)
        at org.alfresco.ibatis.HierarchicalXMLConfigBuilder.parseConfiguration(HierarchicalXMLConfigBuilder.java:124)
        … 64 more
Jun 20, 2013 2:32:34 PM org.apache.catalina.core.StandardContext listenerStart
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

… which I think means that Alfresco doesn't like the database configuration in the alfresco-global.properties file:

db.driver=net.sourceforge.jtds.jdbc.Driver
db.username=theAlfrescoDBO
db.password=theAfrescoDBOsPassword
db.host=theServerName
db.port=1433
db.name=alf_DigitalAssets_Dev1
db.url=jdbc:jtds:sqlserver://${db.host}:${db.port}/${db.name}
db.txn.isolation=4096
db.pool.validate.query=select 1
‍‍‍‍‍‍‍‍‍‍‍

… as listed here:  http://wiki.alfresco.com/wiki/Database_Configuration

I copied the jTDS .jar driver to %CATALINA_BASE%\lib, and I did the "alter database…" command on this database.

Would some kind soul help me understand what I have not yet done correctly, please?

Thanks!

Don

[abarisone]

HI,
maybe you're experiencing some issues because Alfresco is not supported with Tomcat 7.
According to the Alfresco Compatibility matrix http://www.alfresco.com/services/subscription/supported-platforms you should have:
- Windows Server 2008 R2 X64
- MS SQL Server 2008 R2
- Tomcat 6.0.35

Regards,
Andrea

[kaynezhang]

That is not tomcat 7 's problem,alfresco can be run on tomcat 7,It seems that alfresco didn't use hibernate dialiect to load ibatis mapping file correctly.
sqlserver is only supported by enterprise edition,make sure under alfresco\WEB-INF\classes\alfresco\ibatis you'll see a folder named  "org.hibernate.dialect.SQLServerDialect"

Then edit file alfresco-global.properties and add hibernate.dialect=org.hibernate.dialect.SQLServerDialect to the end and restart.

[oofdachile]

Hi, folks –

A statement like Alfresco Community 4.x "does not support Micosoft SQL Server" leaves me wondering what exactly is meant:  was I on my own to create the right properties in alfresco-global.properties?  More times than not, problems like these have to do with injecting my local pizza shop order information into a product's config file, rather than actual issues with the product itself, and so I wondered if I was just doing something wrong when trying to figure this out.

So, ok, the scoop is that in the 4.x Community releases of Alfresco, the SQL Server versions of the database scripts which create database tables (and probably do a bunch of other SQL-Server-y stuff) are just not included.  You can check this for yourself by looking here:

%CATALINA_BASE%\webapps\alfresco\WEB-INF\classes\alfresco\dbscripts\create

Here (and other places, too, no doubt) there is support for only MySQL and Postgres.  Addons like these …

https://addons.alfresco.com/addons/oracle-and-sql-server-support-alfresco-community
https://addons.alfresco.com/addons/sqlserver-community

… appear to add these needed pieces, though the first doesn't seem to have SQL Server hooks in it (though, hey, what do I know?) and the second entry appears to be a null pointer these days.  Perhaps a lot could be learned from studying the Oracle portion of the first file, though.

  So the big quesitons seem to be these:

<ul>
<li> Does ANYONE have an actual copy of that second addon?
<li> If the Oracle/SQLServer addon can indeed be used outright for SQL Server, does anyone know how?
</ul>

Thanks for taking the time to reply, Andrea and Kaynezhang!

Don

[kaynezhang]

Only Alfresco Enterprise endition support Micosoft SQL Server,Oracle addon can't  be used for SQL Server.
It seems someone had develop an amp for sqlserver2008 called ""alfresco community support for microsoft sqlserver2008".

[oofdachile]

Thanks, kaynezhang.  The AMP that you found for us is for Alfresco 3.4, but comparing that to the Oracle AMP should make it possible to create a complete package that works with 4.2.d.  Hooray!