Mysql and SQLGrammarException

[gael_poffet]

Hi all,
I'm tying to configure Alfresco Labs3 stable on a Debian Lenny server. Everything is working fine when I'm using Derby but as soon as I change the config to use MySQL Alfresco is not starting.
I got this error:

14:19:24,352  INFO  [domain.schema.SchemaBootstrap] Schema managed by database dialect org.hibernate.dialect.MySQLInnoDBDialect.
14:19:43,365  INFO  [domain.schema.SchemaBootstrap] No changes were made to the schema.
org.hibernate.exception.SQLGrammarException: could not execute query
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.loader.Loader.doList(Loader.java:2216)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
        at org.hibernate.loader.Loader.list(Loader.java:2099)
        at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
        at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
        at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
…
Caused by: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "limit" at line 1, column 355.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Here is my config:

# MySQL dialect (default)
#
hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect^M
‍‍‍‍‍

and

# Database configuration
db.schema.update=true
db.schema.update.lockRetryCount=24
db.schema.update.lockRetryWaitSeconds=5
db.driver=org.gjt.mm.mysql.Driver
db.name=alfresco
db.url=jdbc:mysql:///${db.name}
db.username=alfresco
db.password=alfresco
db.pool.initial=10
db.pool.max=40
db.txn.isolation=-1
‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The mysql server version is

mysql  Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2‍

If someone can help me it would be appreciated as I'm getting realy mad…

Thanks

[thewheel]

I have the same problem with openSUSE11.0
It works (great) with de derby database but won't start with MySQL
Also: during installation there is no question about the database to use although the installation manual say's so….

Any help would be appreciated.

The Wheel

Ok, this is really dumm…

In 'custum-hibernate-dialect.properties' the "#" in front of the mysql line should not be removed, but the whole string "#mysql#" has to be removed!!!

After deleting everyting in "/opt/Alfresco/alf_data/" it now works for me!!!!

[zshay]

Gael,

Do you have multiple lines commented out in your hibernate properties file?  I saw references to both HQL and derby in your error trace.  Not sure if it is allowed to specify multiple database sources…so I would double check that you only have one type enabled.  "custom-repository.properties" and "custom-hibernate-dialect.properties" are both good places to check for this configuration.

Best regards,
Zach