Problem during upgrade 3.1 -> 3.3.3 with db scheme & oracle

[jpfi]

hi guys,

I'm currently trying to upgrade a 3.1.0 to 3.3.3 (both enterprise). (yub, i know that this path ist not directly supported…but my prob is not related to upgrade path).
(ACT #15024-24128)
DB-settings are done in alfresco-global.properties, I also set hibernate.default_schema:
db.username=myname
…
hibernate.default_schema=myname

When starting the 3.3.3 system org.alfresco.repo.domain.schema.SchemaBootstrap is executed & test if my scheme exists by invoking countAppliedPatches-method.
In my case a NoSchemaException seems to be thrown & SchemaBootstrap turns into create-mode.

private boolean updateSchema(Configuration cfg, Session session, Connection connection) throws Exception
    {
        boolean create = false;
        try
        {
            countAppliedPatches(cfg, connection);
        }
        catch (NoSchemaException e)
        {
            create = true;
        }
        // Get the dialect
        final Dialect dialect = Dialect.getDialect(cfg.getProperties());
        String dialectStr = dialect.getClass().getSimpleName();

        if (create)
        {
            // execute pre-create scripts (not patches)
            for (String scriptUrl : this.preCreateScriptUrls)
            {
                executeScriptUrl(cfg, connection, scriptUrl);
            }
            // the applied patch table is missing - we assume that all other tables are missing
            // perform a full update using Hibernate-generated statements
            File tempFile = TempFileProvider.createTempFile("AlfrescoSchema-" + dialectStr + "-Update-", ".sql");
            SchemaBootstrap.dumpSchemaCreate(cfg, tempFile);
            executeScriptFile(cfg, connection, tempFile, null);
            // execute post-create scripts (not patches)
            for (String scriptUrl : this.postCreateScriptUrls)
            {
                executeScriptUrl(cfg, connection, scriptUrl);
            }
        }
        else
        {…
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

My DB has more than 1 DB scheme. But each scheme has dedicated user…Any hints?

best, jan

[jpfi]

Hi,
I also tried hibernate.default_schema=ALFRESCO as described here http://wiki.alfresco.com/wiki/Database_Configuration#Oracle_example

both configs are leading to the same result:

 [domain.schema.SchemaBootstrap] Executing database script /srv/tomcat/200/temp/Alfresco/AlfrescoSchema-AlfrescoOracle9Dialect-Update-7715176739865162957.sql (Copied from classpath:alfresco/dbscripts/create/3.3/org.alfresco.repo.domain.hibernate.dialect.AlfrescoOracle9Dialect/AlfrescoCreate-3.3-RepoTables.sql).
16:34:20,723  ERROR [domain.schema.SchemaBootstrap] Statement execution failed:
   SQL: CREATE TABLE alf_applied_patch
(
    id VARCHAR2(64) NOT NULL,
    description VARCHAR2(1024),
    fixes_from_schema NUMBER(10,0),
    fixes_to_schema NUMBER(10,0),
    applied_to_schema NUMBER(10,0),
    target_schema NUMBER(10,0),
    applied_on_date TIMESTAMP,
    applied_to_server VARCHAR2(64),
    was_executed NUMBER(1,0),
    succeeded NUMBER(1,0),
    report VARCHAR2(1024),
    PRIMARY KEY (id)
)
   Error: ORA-00955: name is already used by an existing object

   File: /srv/tomcat/200/temp/Alfresco/AlfrescoSchema-AlfrescoOracle9Dialect-Update-7715176739865162957.sql
   Line: 24
16:34:20,724  ERROR [domain.schema.SchemaBootstrap] Schema auto-update failed
java.sql.SQLException: ORA-00955: name is already used by an existing object

   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
   at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
   at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
   at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:957)
   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
   at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1696)
   at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1662)
   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:1195)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.executeScriptFile(SchemaBootstrap.java:1145)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.executeScriptUrl(SchemaBootstrap.java:877)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.updateSchema(SchemaBootstrap.java:732)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.onBootstrap(SchemaBootstrap.java:1355)
   at org.springframework.extensions.surf.util.AbstractLifecycleBean.onApplicationEvent(AbstractLifecycleBean.java:56)
   at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:97)
   at org.alfresco.repo.management.SafeApplicationEventMulticaster.multicastEvent(SafeApplicationEventMulticaster.java:78)
   at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:294)
   at org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:858)
   at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:419)
   at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:261)
   at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:192)
   at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:47)
   at org.alfresco.web.app.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:63)
   at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3843)
   at org.apache.catalina.core.StandardContext.start(StandardContext.java:4342)
   at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
   at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
   at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:525)
   at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:830)
   at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:719)
   at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:490)
   at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1149)
   at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:311)
   at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:117)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
   at org.apache.catalina.core.StandardHost.start(StandardHost.java:719)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
   at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
   at org.apache.catalina.core.StandardService.start(StandardService.java:516)
   at org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
   at org.apache.catalina.startup.Catalina.start(Catalina.java:578)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:288)
   at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:413)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

best jan

[derek]

Ticket noted.

[jpfi]

Hi,
ok, removing hibernate.default_schema runs upgrade script as it should be.
best, jan