Upgrade Alfresco 2.0.0 to 3 Labs Stable

[nmaupu]

Hi everybody,

I am currently trying to upgrade from Alfresco community 2.0.0 to Alfresco 3 Labs.
I read and followed articles from alfresco wiki : http://wiki.alfresco.com/wiki/General_Upgrade_Process but problems occured. Here is what I did and where I stuck.

Environment :
  - Centos
  - Mysql 5
  - JBoss 4.0.5.GA
  - Alfresco 2.0.0 - no plugin installed
  - Quite big repository (~ 60Go)

First, I tried to upgrade from 2.0.0 :
  - Cold backup (data + sql dump)
  - Alfresco war upgrade to 3 Labs
  - Changing index.recovery.mode to FULL and db.schema.update to true
  - Restarting JBoss and waiting
  - Upgrade failed with the following error :

17:50:41,935 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script /tmp/Alfresco/AlfrescoSchemaUpdate-org.hibernate.dialect.MySQLInnoDBDialect-26168.sql (Copied from classpath:alfresco/dbscripts/upgrade/2.1/org.hibernate.dialect.MySQLInnoDBDialect/AlfrescoSchemaUpdate-2.1-NotNullColumns.sql).
17:50:41,938 INFO  [org.hibernate.dialect.Dialect] Using dialect: org.hibernate.dialect.MySQLInnoDBDialect
17:50:41,939 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script /tmp/Alfresco/AlfrescoSchemaUpdate-org.hibernate.dialect.MySQLInnoDBDialect-26169.sql (Copied from classpath:alfresco/dbscripts/upgrade/2.2/org.hibernate.dialect.MySQLInnoDBDialect/AlfrescoSchemaUpdate-2.2-ACL.sql).
17:50:42,470 INFO  [org.hibernate.dialect.Dialect] Using dialect: org.hibernate.dialect.MySQLInnoDBDialect
17:50:42,472 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script /tmp/Alfresco/AlfrescoSchemaUpdate-org.hibernate.dialect.MySQLInnoDBDialect-26170.sql (Copied from classpath:alfresco/dbscripts/upgrade/2.2/org.hibernate.dialect.MySQLInnoDBDialect/upgrade-0-create-missing-tables.sql).
17:50:42,517 INFO  [org.hibernate.dialect.Dialect] Using dialect: org.hibernate.dialect.MySQLInnoDBDialect
17:50:42,519 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script /tmp/Alfresco/AlfrescoSchemaUpdate-org.hibernate.dialect.MySQLInnoDBDialect-26171.sql (Copied from classpath:alfresco/dbscripts/upgrade/2.2/org.hibernate.dialect.MySQLInnoDBDialect/upgrade-from-2.1.sql).
20:58:00,689 ERROR [org.alfresco.repo.domain.schema.SchemaBootstrap] Statement execution failed:
   SQL: INSERT INTO alf_locale (locale_str)
   SELECT DISTINCT(ma.mkey)
      FROM alf_node_properties np
      JOIN alf_attributes a1 ON (np.attribute_value = a1.id)
      JOIN alf_map_attribute_entries ma ON (ma.map_id = a1.id)

   Error: Table 'alfresco.alf_attributes' doesn't exist
   File: /tmp/Alfresco/AlfrescoSchemaUpdate-org.hibernate.dialect.MySQLInnoDBDialect-26171.sql
   Line: 735
20:58:00,691 ERROR [org.alfresco.repo.domain.schema.SchemaBootstrap] Schema auto-update failed
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'alfresco.alf_attributes' doesn't exist
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2941)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3243)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3172)
        at com.mysql.jdbc.Statement.execute(Statement.java:706)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I thought there is a big gap between alfresco 2.0.0 and alfresco 3 so I also tried to upgrade from 2.0.0 to 2.9.0 and from 2.9.0 to 3 Labs Stable :
  - Restoring cold backup to 2.0.0 and restarting JBoss to check if everything is ok and stopping JBoss
  - Upgrading Alfresco war to 2.9.0
  - Changing index.recovery.mode to FULL and db.schema.update to true
  - Starting JBoss
  - Upgrade successfull (not so long, 10 - 20 min)

Upgrade finish successfully with a message that say Alfresco version is 2.9.0 and schema is 2.0.0, is it normal ?
Anyway, I continue with upgrading to Alfresco Labs 3 Stable :
  - Upgrading war to 3 Labs Stable
  - Changing index.recovery.mode to FULL and db.schema.update to true
  - Starting JBoss
  - After 12 hours, it failed with error :

16:38:34,557 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script /tmp/Alfresco/Alfresco
SchemaUpdate-org.hibernate.dialect.MySQLInnoDBDialect-42924.sql (Copied from classpath:alfresco/dbscripts/upgrade/2.2
/org.hibernate.dialect.MySQLInnoDBDialect/AlfrescoSchemaUpdate-2.2-ACL.sql).
16:38:34,918 INFO  [org.hibernate.dialect.Dialect] Using dialect: org.hibernate.dialect.MySQLInnoDBDialect
16:38:34,920 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script /tmp/Alfresco/Alfresco
SchemaUpdate-org.hibernate.dialect.MySQLInnoDBDialect-42925.sql (Copied from classpath:alfresco/dbscripts/upgrade/2.2
/org.hibernate.dialect.MySQLInnoDBDialect/upgrade-0-create-missing-tables.sql).
16:38:34,945 INFO  [org.hibernate.dialect.Dialect] Using dialect: org.hibernate.dialect.MySQLInnoDBDialect
16:38:34,947 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script /tmp/Alfresco/Alfresco
SchemaUpdate-org.hibernate.dialect.MySQLInnoDBDialect-42926.sql (Copied from classpath:alfresco/dbscripts/upgrade/2.2
/org.hibernate.dialect.MySQLInnoDBDialect/upgrade-from-2.1.sql).
11:35:14,035 ERROR [org.alfresco.repo.domain.schema.SchemaBootstrap] Statement execution failed:
   SQL: INSERT INTO t_alf_node_properties
   (
      node_id, qname_id, locale_id, list_index,
      actual_type_n, persisted_type_n,
      boolean_value, long_value, float_value, double_value,
      string_value,
      serializable_value
   )
   SELECT
      np.node_id, tqn.qname_id, 1, -1,
      ptypes_actual.type_id, ptypes_persisted.type_id,
      np.boolean_value, np.long_value, np.float_value, np.double_value,
      np.string_value,
      np.serializable_value
   FROM
      alf_node_properties np
      JOIN t_qnames tqn ON (np.qname = tqn.qname)
      JOIN t_prop_types ptypes_actual ON (ptypes_actual.type_name = np.actual_type)
      JOIN t_prop_types ptypes_persisted ON (ptypes_persisted.type_name = np.persisted_type)
   WHERE
      np.attribute_value IS NULL

   Error: Data truncation: Data too long for column 'string_value' at row 2445
   File: /tmp/Alfresco/AlfrescoSchemaUpdate-org.hibernate.dialect.MySQLInnoDBDialect-42926.sql
   Line: 793
11:35:14,036 ERROR [org.alfresco.repo.domain.schema.SchemaBootstrap] Schema auto-update failed
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'string_value' at row 2445
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2939)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3243)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3172)
        at com.mysql.jdbc.Statement.execute(Statement.java:706)
        at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:261)
        at org.alfresco.repo.domain.schema.SchemaBootstrap.executeStatement(SchemaBootstrap.java:944)
        at org.alfresco.repo.domain.schema.SchemaBootstrap.executeScriptFile(SchemaBootstrap.java:913)
        at org.alfresco.repo.domain.schema.SchemaBootstrap.executeScriptUrl(SchemaBootstrap.java:768)
        at org.alfresco.repo.domain.schema.SchemaBootstrap.checkSchemaPatchScripts(SchemaBootstrap.java:739)
        at org.alfresco.repo.domain.schema.SchemaBootstrap.updateSchema(SchemaBootstrap.java:641)
        at org.alfresco.repo.domain.schema.SchemaBootstrap.onBootstrap(SchemaBootstrap.java:1123)
        at org.alfresco.util.AbstractLifecycleBean.onApplicationEvent(AbstractLifecycleBean.java:62)
        at org.springframework.context.event.SimpleApplicationEventMulticaster$1.run(SimpleApplicationEventMulticaste
r.java:77)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I check this field in DB, it is of type text. So it should not be too short …
I googled around and saw some versions of mysql (5.0.x) can failed with this error when inserting a special char (like ç, à, ù, etc …) with some encoding.

I was in latin1 when it failed. So, I restart the whole process in utf-8 and it failed with the same error.

Is somebody have an idea on how I can upgrade from Alfresco 2.0.0 to Alfresco 3 Labs with a 60Go repository ?

Thanks,
NMaupu

[dsandy]

Hello,
did you have any luck with this?  I am having the same problems and have spent a long time (1 week) attempting to get this to work…

cheers
Damien