Alfresco 3.3 + Tomcat 6 + MySQL 5.5 - sql errors

[sns]

Hi,

Having installed Alfresco on a fresh freebsd machine with Tomcat 6 and MySQL 5.5 have found couple of troubles starting this webapp.

Main problems (for the moment) is that, as far as I can understand, some scripts/sql dialect commands are outdated.

Here's sample of my log file:

16:12:26,664 ERROR [org.alfresco.repo.domain.schema.SchemaBootstrap] Statement execution failed:
   SQL:     create table JBPM_ACTION (
        ID_ bigint not null auto_increment,
        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_ text,
        EVENTINDEX_ integer,
        EXCEPTIONHANDLER_ bigint,
        EXCEPTIONHANDLERINDEX_ integer,
        primary key (ID_)
    ) type=InnoDB
   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 'type=InnoDB' at line 22
   File: /usr/local/apache-tomcat-6.0/temp/Alfresco/AlfrescoSchema-MySQLInnoDBDialect-Update-61563.sql
   Line: 23
16:12:26,668 ERROR [org.alfresco.repo.domain.schema.SchemaBootstrap] Schema auto-update failed‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

As of 5.1 and higher versions of MySQL - 'type' definidion for table types is no more supported - it must be 'engine' instead. I have found all occurences of this mistake in static sql scripts, but this one is being build on the fly and I do not know which way to look to fix it.

On the other hand - if I disable schema auto update, as mentioned in some other post, some needed table is not created (during auto update?) and is not available. Here's a cut from my log file:

16:55:53,613 ERROR [org.springframework.web.context.ContextLoader] Context initialization failed
org.hibernate.exception.SQLGrammarException: could not load an entity: [org.alfresco.repo.attributes.GlobalAttributeEntryImpl#.avm_lock_table]
…..
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'alfresco.alf_global_attributes' doesn't exist
‍‍‍‍‍

Whoever will point me to the place in code I could fix it?

p.s. I think this should be fixed. MySQL is way too far from 5.0 now.

Thanks in advance!

[mikemayaka]

Hi,
I've tried installing Alfresco 3.4b on MySQL 5.5 and I get the "com.mysql.jdbc.exceptions.jdbc4.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 'type=InnoDB' at line 1" error. I edited all the creation scripts under "alfresco/dbscripts/create/org.hibernate.dialect.MySQLInnoDBDialect" replacing "Type" with "ENGINE"…the last script is auto-generated! Any solution from Alfresco on this?

[ssaravanan]

I experience this problem when trying to do an upgrade from 3.0 to 3.4a
I also experience the same issue with Alfresco 3.4a CE
I have the following errors

10:37:19,663  INFO  [domain.schema.SchemaBootstrap] Executing database script /opt/alfresco-3.4.a/tomcat/temp/Alfr
esco/AlfrescoSchema-MySQLInnoDBDialect-Update-1715635012673589626.sql (Copied from classpath:alfresco/dbscripts/up
grade/2.2/org.hibernate.dialect.MySQLInnoDBDialect/AlfrescoSchemaUpdate-Person.sql).
10:50:43,103  INFO  [domain.schema.SchemaBootstrap] Executing database script /opt/alfresco-3.4.a/tomcat/temp/Alfr
esco/AlfrescoSchema-MySQLInnoDBDialect-Update-8477093761482574813.sql (Copied from classpath:alfresco/dbscripts/cr
eate/org.hibernate.dialect.MySQLInnoDBDialect/AlfrescoCreate-LockTables.sql).
10:50:43,126  ERROR [domain.schema.SchemaBootstrap] Statement execution failed:
   SQL: CREATE TABLE alf_lock_resource
(
   id BIGINT NOT NULL AUTO_INCREMENT,
   version BIGINT NOT NULL,
   qname_ns_id BIGINT NOT NULL,
   qname_localname VARCHAR(255) NOT NULL,
   CONSTRAINT fk_alf_lockr_ns FOREIGN KEY (qname_ns_id) REFERENCES alf_namespace (id),
   PRIMARY KEY (id),
   UNIQUE INDEX idx_alf_lockr_key (qname_ns_id, qname_localname)
) TYPE=InnoDB
   Error: Table 'alf_lock_resource' already exists
   File: /opt/alfresco-3.4.a/tomcat/temp/Alfresco/AlfrescoSchema-MySQLInnoDBDialect-Update-8477093761482574813.sql
   Line: 19
10:50:43,128  ERROR [domain.schema.SchemaBootstrap] Schema auto-update failed
java.sql.SQLException: Table 'alf_lock_resource' already exists
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

My mysql shows

Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET storage_engine=INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql> show engines;
+————+———+—————————————————————-+
| Engine     | Support | Comment                                                        |
+————+———+—————————————————————-+
| MyISAM     | YES     | Default engine as of MySQL 3.23 with great performance         | 
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | 
| InnoDB     | DEFAULT | Supports transactions, row-level locking, and foreign keys     | 
| BerkeleyDB | YES     | Supports transactions and page-level locking                   | 
| BLACKHOLE  | NO      | /dev/null storage engine (anything you write to it disappears) | 
| EXAMPLE    | NO      | Example storage engine                                         | 
| ARCHIVE    | NO      | Archive storage engine                                         | 
| CSV        | NO      | CSV storage engine                                             | 
| ndbcluster | NO      | Clustered, fault-tolerant, memory-based tables                 | 
| FEDERATED  | NO      | Federated MySQL storage engine                                 | 
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | 
| ISAM       | NO      | Obsolete storage engine                                        | 
+————+———+—————————————————————-+
12 rows in set (0.00 sec)

mysql>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

mysql by default comes with MyISAM as the default engine for mysql
I changed it to InnoDB

SET storage_engine=INNODB;

[ssaravanan]

after changing to InnoDB, I get a totally different error

11:11:16,502  INFO  [domain.schema.SchemaBootstrap] Schema managed by database dialect org.hibernate.dialect.MySQLInnoDBDialect.
11:11:21,188  INFO  [domain.schema.SchemaBootstrap] Executing database script /opt/alfresco-3.4.a/tomcat/temp/Alfresco/AlfrescoSchema-MySQLInnoDBDialect-Update-3015101626237139735.sql (Copied from classpath:alfresco/dbscripts/create/org.hibernate.dialect.MySQLInnoDBDialect/AlfrescoCreate-LockTables.sql).
11:11:21,204  ERROR [domain.schema.SchemaBootstrap] Statement execution failed:
   SQL: CREATE TABLE alf_lock_resource
(
   id BIGINT NOT NULL AUTO_INCREMENT,
   version BIGINT NOT NULL,
   qname_ns_id BIGINT NOT NULL,
   qname_localname VARCHAR(255) NOT NULL,
   CONSTRAINT fk_alf_lockr_ns FOREIGN KEY (qname_ns_id) REFERENCES alf_namespace (id),
   PRIMARY KEY (id),
   UNIQUE INDEX idx_alf_lockr_key (qname_ns_id, qname_localname)
) TYPE=InnoDB
   Error: Table 'alf_lock_resource' already exists
   File: /opt/alfresco-3.4.a/tomcat/temp/Alfresco/AlfrescoSchema-MySQLInnoDBDialect-Update-3015101626237139735.sql
   Line: 19
11:11:21,206  ERROR [domain.schema.SchemaBootstrap] Schema auto-update failed
java.sql.SQLException: Table 'alf_lock_resource' already exists
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:2917)
   at com.mysql.jdbc.Statement.execute(Statement.java:529)
   at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
   at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.executeStatement(SchemaBootstrap.java:1215)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.executeScriptFile(SchemaBootstrap.java:1165)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.executeScriptUrl(SchemaBootstrap.java:897)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[janv]

I've tried installing Alfresco 3.4b on MySQL 5.5 and I get the "com.mysql.jdbc.exceptions.jdbc4.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 'type=InnoDB' at line 1" error

For those of you who would like to dev/test against the latest MySQL release candidate (at time of writing, 5.5.6 rc) I've now fixed the above issue on HEAD (r23228) to enable Alfresco (+ JBPM auto-generated SQL) to bootstrap against the MySQL 5.5 rc. Please let us know if you see any specific repeatable differences (performance or otherwise) compared to latest MySQL 5.1 (at time of writing 5.1.51).

Regards,
Jan

[ssaravanan]

Thanks Janv.
I'm using mysql Server version: 5.0.77 .
http://forums.alfresco.com/en/viewtopic.php?f=8&t=35280#p102492
My upgrade fails as you can see on the above post…
So upgrade to Mysql Server version: 5.5.6 rc would hopefully fix my issue or
wait till the release of 3.4b where the fix might be available, is that right or have I missed out anything ?
Thanks Janv

[janv]

Hi ssaravanan,

I can't comment on your specific issue which seem to be unrelated to this original post (which is all about bootstrap on 5.5).

Please log your issue(s) in a separate post … you should be able to solve them before trying to use a 5.5 release candidate.

Thanks,
Jan

[sunnysharma]

I also faced the same problem.

16:12:26,664 ERROR [org.alfresco.repo.domain.schema.SchemaBootstrap] Statement execution failed:
   SQL:     create table JBPM_ACTION (
        ID_ bigint not null auto_increment,
        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_ text,
        EVENTINDEX_ integer,
        EXCEPTIONHANDLER_ bigint,
        EXCEPTIONHANDLERINDEX_ integer,
        primary key (ID_)
    ) type=InnoDB
   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 'type=InnoDB' at line 22
   File: /usr/local/apache-tomcat-6.0/temp/Alfresco/AlfrescoSchema-MySQLInnoDBDialect-Update-61563.sql
   Line: 23
16:12:26,668 ERROR [org.alfresco.repo.domain.schema.SchemaBootstrap] Schema auto-update failed

For me it worked like this: Take the backup of the structure of the db for the earlier installation of Alfresco version 3.3, which is running on MySQL 5.1.
Import this in MySQL 5.5. And now Alfresco should start without any problem.

[janv]

Just an FYI - this issue is also resolved for Alfresco 3.4 (3.4b or higher) which can be installed directly against MySQL 5.5 or MySQL 5.1.

Regards,
Jan