topic Re: problem upgrading from 2.1 to 3 (alf_usage_delta) in Alfresco Archive

problem upgrading from 2.1 to 3 (alf_usage_delta)

steve_tekell — Mon, 29 Dec 2008 18:19:57 GMT

While I have db.schema.update=true, it's not creating at least one table that it needs during and an upgrade from 2.1CE to Labs 3c with MySQL.So it fails saying "Table 'alfresco.alf_usage_delta' doesn't exist."It looks like there's no 3.0 dbscripts, so it's a problem with the 2.2 dbscripts. upgra

Re: problem upgrading from 2.1 to 3 (alf_usage_delta)

steve_tekell — Tue, 30 Dec 2008 19:15:47 GMT

well I have made it to a new but similar error. Now it fails because a column is missing, commit_time_ms in alf_transaction.
Is it possible that db.schema.update=true isn't fully working?
Or how can this column be missing?
I don't see any script to create it, what should it be?

11:19:59,847  ERROR [domain.schema.SchemaBootstrap] Statement execution failed:
   SQL: ALTER TABLE alf_transaction
   DROP INDEX FKB8761A3A9AE340B7, DROP FOREIGN KEY FKB8761A3A9AE340B7,
   ADD INDEX fk_alf_txn_svr (server_id),
   ADD CONSTRAINT fk_alf_txn_svr FOREIGN KEY (server_id) REFERENCES alf_server (id),
   ADD INDEX idx_alf_txn_ctms (commit_time_ms)

   Error: Key column 'commit_time_ms' doesn't exist in table
   File: /usr/local/tomcat/temp/Alfresco/AlfrescoSchemaUpdate-org.hibernate.dialect.MySQLInnoDBDialect-41024.sql
   Line: 1,000
11:19:59,849  ERROR [domain.schema.SchemaBootstrap] Schema auto-update failed
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Key column 'commit_time_ms' doesn't exist in table
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: problem upgrading from 2.1 to 3 (alf_usage_delta)

steve_tekell — Tue, 30 Dec 2008 21:33:34 GMT

I create an issue for this.
https://issues.alfresco.com/jira/browse/ALFCOM-2268

Re: problem upgrading from 2.1 to 3 (alf_usage_delta)

steve_tekell — Wed, 31 Dec 2008 00:54:02 GMT

here's some fixes/workarounds to get past these errors:

Changes to sql scripts in
/alfresco/dbscripts/upgrade

/2.2/org.hibernate.dialect.MySQLInnoDBDialect/upgrade-from-2.1.sql
– add create for missing alf_usage_delta
CREATE TABLE IF NOT EXISTS alf_usage_delta
(
   id BIGINT NOT NULL AUTO_INCREMENT,
   version BIGINT NOT NULL,
   node_id BIGINT NOT NULL,
   delta_size BIGINT NOT NULL,
   PRIMARY KEY (id)
) TYPE=InnoDB;
– add missing column
ALTER TABLE `alf_transaction` ADD COLUMN `commit_time_ms` bigint(20) default NULL;
ALTER TABLE `alf_transaction` ADD INDEX `idx_commit_time_ms` (`commit_time_ms`);

/2.1/org.hibernate.dialect.Dialect/AlfrescoSchemaUpdate-2.1-RemoveWcmSubmittedAspect.sql
– remove DELETEs with 'WHERE qname ='
– rename *_new to *