topic Re: Alfresco 3.3 + Tomcat 6 + MySQL 5.5 - sql errors in Alfresco Archive

Alfresco 3.3 + Tomcat 6 + MySQL 5.5 - sql errors

sns — Thu, 06 May 2010 13:21:02 GMT

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 [o

Re: Alfresco 3.3 + Tomcat 6 + MySQL 5.5 - sql errors

mikemayaka — Thu, 21 Oct 2010 08:16:56 GMT

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?

Re: Alfresco 3.3 + Tomcat 6 + MySQL 5.5 - sql errors

ssaravanan — Thu, 21 Oct 2010 09:42:25 GMT

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;

Re: Alfresco 3.3 + Tomcat 6 + MySQL 5.5 - sql errors

ssaravanan — Thu, 21 Oct 2010 10:00:41 GMT

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)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Alfresco 3.3 + Tomcat 6 + MySQL 5.5 - sql errors

janv — Mon, 25 Oct 2010 09:08:37 GMT

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

Re: Alfresco 3.3 + Tomcat 6 + MySQL 5.5 - sql errors

ssaravanan — Mon, 25 Oct 2010 09:27:38 GMT

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

Re: Alfresco 3.3 + Tomcat 6 + MySQL 5.5 - sql errors

janv — Mon, 25 Oct 2010 09:33:47 GMT

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

Re: Alfresco 3.3 + Tomcat 6 + MySQL 5.5 - sql errors

sunnysharma — Tue, 26 Jul 2011 08:24:13 GMT

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.

Re: Alfresco 3.3 + Tomcat 6 + MySQL 5.5 - sql errors

janv — Fri, 29 Jul 2011 15:01:51 GMT

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