Alfresco 1.4 and MySQL configuration problems

[kleegrewe_c]

Hello,

I have installed alfresco 1.4.0. The software runs fine with the preconfigured hsqldb, but does not start with mysql. I edited the custom-db-connection.properties file so that it should work with mysql and I created a mysql database named alfresco and granted the rights as in extras/database/mysql/db_setup.sql, but starting alfreso results in a bundle of sql exceptions (see below)

Are there any suggestions how to overcome this problem?

thanks in advance Christian

———-8<——————————-
16:10:45,678 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script: /usr/local/alfresco/tomcat/temp/Alfresco/AlfrescoSchemaCreate-org.hibernate.dialect.HSQLDialect-18321.sql
16:10:45,815 ERROR [org.alfresco.repo.domain.schema.SchemaBootstrap] Statement execution failed:
   SQL:  create table JBPM_ACTION ( ID_ bigint generated by default as identity (start with 1), class char(1) not null, NAME_ varchar(255), ISPROPAGATIONALLOWED_ bit, ACTIONEXPRESSION_ varchar(255), ISASYNC_ bit, REFERENCEDACTION_ bigint, ACTIONDELEGATION_ bigint, EVENT_ bigint, PROCESSDEFINITION_ bigint, TIMERNAME_ varchar(255), DUEDATE_ varchar(255), REPEAT_ varchar(255), TRANSITIONNAME_ varchar(255), TIMERACTION_ bigint, EXPRESSION_ varchar(4000), EVENTINDEX_ integer, EXCEPTIONHANDLER_ bigint, EXCEPTIONHANDLERINDEX_ integer, primary key (ID_) )
   Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'generated by default as identity (start with 1), class char(1) not null, NAME_ v' at line 1
   File: /usr/local/alfresco/tomcat/temp/Alfresco/AlfrescoSchemaCreate-org.hibernate.dialect.HSQLDialect-18321.sql
   Line: 23
16:10:45,854 ERROR [org.springframework.web.context.ContextLoader] Context initialization failed
org.alfresco.error.AlfrescoRuntimeException: Schema auto-update failed
   at org.alfresco.repo.domain.schema.SchemaBootstrap.onBootstrap(SchemaBootstrap.java:613)
   at org.alfresco.util.AbstractLifecycleBean.onApplicationEvent(AbstractLifecycleBean.java:54)
   at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:45)
   at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:225)
   at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:323)
   at org.springframework.web.context.support.AbstractRefreshableWebApplicationContext.refresh(AbstractRefreshableWebApplicationContext.java:134)
   at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:246)
   at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:184)
   at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:49)
   at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3692)
   at org.apache.catalina.core.StandardContext.start(StandardContext.java:4127)
   at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:759)
   at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:739)
   at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:524)
   at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:804)
   at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:693)
   at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:472)
   at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1118)
   at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:310)
   at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1020)
   at org.apache.catalina.core.StandardHost.start(StandardHost.java:718)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1012)
   at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:442)
   at org.apache.catalina.core.StandardService.start(StandardService.java:450)
   at org.apache.catalina.core.StandardServer.start(StandardServer.java:680)
   at org.apache.catalina.startup.Catalina.start(Catalina.java:536)
   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:585)
   at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:275)
   at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:413)
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'generated by default as identity (start with 1), class char(1) not null, NAME_ v' at line 1
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:3047)
   at com.mysql.jdbc.Statement.execute(Statement.java:692)
   at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:261)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.executeStatement(SchemaBootstrap.java:552)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.executeScriptFile(SchemaBootstrap.java:527)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.updateSchema(SchemaBootstrap.java:317)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.onBootstrap(SchemaBootstrap.java:598)
   … 32 more
16:10:45,858 ERROR [org.apache.catalina.core.ContainerBase.[Catalina].[localhost].[/alfresco]] Exception sending context initialized event to listener instance of class org.springframework.web.context.ContextLoaderListener
org.alfresco.error.AlfrescoRuntimeException: Schema auto-update failed
   at org.alfresco.repo.domain.schema.SchemaBootstrap.onBootstrap(SchemaBootstrap.java:613)
   at org.alfresco.util.AbstractLifecycleBean.onApplicationEvent(AbstractLifecycleBean.java:54)
   at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:45)
   at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:225)
   at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:323)
   at org.springframework.web.context.support.AbstractRefreshableWebApplicationContext.refresh(AbstractRefreshableWebApplicationContext.java:134)
   at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:246)
   at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:184)
   at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:49)
   at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3692)
   at org.apache.catalina.core.StandardContext.start(StandardContext.java:4127)
   at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:759)
   at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:739)
   at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:524)
   at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:804)
   at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:693)
   at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:472)
   at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1118)
   at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:310)
   at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1020)
   at org.apache.catalina.core.StandardHost.start(StandardHost.java:718)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1012)
   at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:442)
   at org.apache.catalina.core.StandardService.start(StandardService.java:450)
   at org.apache.catalina.core.StandardServer.start(StandardServer.java:680)
   at org.apache.catalina.startup.Catalina.start(Catalina.java:536)
   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:585)
   at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:275)
   at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:413)
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'generated by default as identity (start with 1), class char(1) not null, NAME_ v' at line 1
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:3047)
   at com.mysql.jdbc.Statement.execute(Statement.java:692)
   at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:261)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.executeStatement(SchemaBootstrap.java:552)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.executeScriptFile(SchemaBootstrap.java:527)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.updateSchema(SchemaBootstrap.java:317)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.onBootstrap(SchemaBootstrap.java:598)
   … 32 more
16:10:45,861 ERROR [org.apache.catalina.core.ContainerBase.[Catalina].[localhost].[/alfresco]] Exception sending context initialized event to listener instance of class org.alfresco.web.app.ContextListener
org.alfresco.error.AlfrescoRuntimeException: Schema auto-update failed
   at org.alfresco.repo.domain.schema.SchemaBootstrap.onBootstrap(SchemaBootstrap.java:613)
   at org.alfresco.util.AbstractLifecycleBean.onApplicationEvent(AbstractLifecycleBean.java:54)
   at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:45)
   at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:225)
   at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:323)
   at org.springframework.web.context.support.AbstractRefreshableWebApplicationContext.refresh(AbstractRefreshableWebApplicationContext.java:134)
   at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:246)
   at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:184)
   at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:49)
   at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3692)
   at org.apache.catalina.core.StandardContext.start(StandardContext.java:4127)
   at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:759)
   at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:739)
   at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:524)
   at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:804)
   at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:693)
   at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:472)
   at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1118)
   at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:310)
   at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1020)
   at org.apache.catalina.core.StandardHost.start(StandardHost.java:718)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1012)
   at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:442)
   at org.apache.catalina.core.StandardService.start(StandardService.java:450)
   at org.apache.catalina.core.StandardServer.start(StandardServer.java:680)
   at org.apache.catalina.startup.Catalina.start(Catalina.java:536)
   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:585)
   at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:275)
   at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:413)
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'generated by default as identity (start with 1), class char(1) not null, NAME_ v' at line 1
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:3047)
   at com.mysql.jdbc.Statement.execute(Statement.java:692)
   at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:261)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.executeStatement(SchemaBootstrap.java:552)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.executeScriptFile(SchemaBootstrap.java:527)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.updateSchema(SchemaBootstrap.java:317)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.onBootstrap(SchemaBootstrap.java:598)
   … 32 more

[kevinr]

Follow the instructions in the MSQL_readme that says what to do - you need to change the hibernate dialect in Alfresco to use MySQL instead of HSQL.

Kev

[kleegrewe_c]

Thanks a lot, it is working now.

Christian

[tamax]

I'm sorry, I am trying to solve the same problem but I can't find the MSQL_readme file  where is it ?

Well, actually there is something I don't understand :
I deployed Alfresco 1.4 + MySQL under Liferay 4.1.2 under WinXP and everything worked very fine.
Now I've migrated to Linux Ubuntu 6.10 and Liferay 4.2 and Alfresco doesn't want to run anymore (I used the Alfresco war provided with Liferay 4.2), and I followed exactly the same steps  : I can't login…(I know that the MySQL server is running because Liferay works with it)

I hope that this solution may help me…
So please can you tell where this file is ?

[kleegrewe_c]

there is a file named README_mysql.txt in the alfresco root directory. This should contain a description of the alfresco configuration necessary for mysql usage.

Christian

[tamax]

I must be blind, but there is no README_mysql.txt file in the Alfresco root directory, I have a README_war.txt file where it is explained how to install Alfresco and MySQL, and I carefully followed these steps but it does not work : no tables are created in MySQL server !! And moreover, even if it is working with HSQL, I can't login (as I explained above), that's why I'm trying to change my database, to check if the problem comes from the database (but I have big doubts…).

The thing is I have nothing in log files, I don't know what to do…
And it worked under windows !!  :cry:

[kleegrewe_c]

Here is the contents of my README_mysql.txt. My configuration is running fine and I didn't encounter any login problem.

—————- 8< ————README_mysql.txt ———————-

Optional Install of MySQL
————————-

Alfresco is pre-configured to use the HSQL database, but can easily be
configured to use other databases to use other databases, please visit
the forums and wiki at http://www.alfresco.com for guidelines.

Alfresco requires MySQL 4.1 or higher.

- Browse to http://dev.mysql.com/downloads
- Download the appropriate Linux option
- Install once downloaded
- Use Typical setup type
- You may skip the MySQL.com sign-up
- Configure using options appropriate to required use
  - choose UTF8 Character set
  - include MySQL in path
- Test MySQL is installed and running by opening a command prompt and entering:
  'mysql -u root -p'
- When prompted, give the password you set during installation.  If no errors
  are reported, then it is installed and running.  Enter 'quit' to exit.

Create Database
—————

Navigate to the 'alfresco/extras/databases/mysql' folder and run 'db_setup.bat'. 

This creates a MySQL database named 'alfresco' with a user account and password
of 'alfresco'.  If db_setup fails, this may be because the MySQL service is not
running or that the mysql command cannot be found.  Either correct this or setup
the Alfresco database and user manually by loading the 'db_setup.sql' file into
MySQL, for example, 'mysql -u root -p <db_setup.sql'.

Configure Alfresco
——————

To convert the default installation to MySQL, you simple need to remove 3 files
from the tomcat/shared/classes/alfresco/extension directory that are set to use
HSQL.  Those files are:

- custom-db-and-data-context.xml
- custom-db-connection.properties
- custom-hibernate-dialect.properties

——————— 8< ———————END OF FILE ——————-

As far as I remember you do not have to remove the custom-*.properties files but edit them in a way, that hsqldb configuration is replaced by mysql configuration (# uncoment lines ..)

regards Christian

[tamax]

Thx for your quick reply, I'll try it tomorrow morning (I'm not at work right now)…

But I'm still curious to know why it does not work in my linux environment…
If somebody faced this problem once, please tell me how you solved it, and if I'm going the right way…

Regards,
Maxime.

[tamax]

Well I removed these 3 files as I didn't know how to edit them and now it works, so fine !!
thx again.