problem upgrading from 2.1 to 3 (alf_usage_delta)

[steve_tekell]

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.  upgrade-from-2.1.sql assumes that alf_usage_delta exists and there's no create script for it.  I guess I'll just create the table and see how far it gets.


The error:

10:08:54,775  ERROR [domain.schema.SchemaBootstrap] Statement execution failed:
   SQL: INSERT INTO t_alf_usage_delta
   (
      id, version,
      node_id,
      delta_size
   )
   SELECT
      ud.id, 1,
      ud.node_id,
      ud.delta_size
   FROM
      alf_usage_delta ud

   Error: Table 'alfresco.alf_usage_delta' doesn't exist
   File: /usr/local/tomcat/temp/Alfresco/AlfrescoSchemaUpdate-org.hibernate.dialect.MySQLInnoDBDialect-8962.sql
   Line: 441
10:08:54,777  ERROR [domain.schema.SchemaBootstrap] Schema auto-update failed
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'alfresco.alf_usage_delta' doesn't exist
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[steve_tekell]

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

[steve_tekell]

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

[steve_tekell]

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 *