Technical Description of the issue. If the creation of the required indexes for metadata queries fail, metadata queries will be extremely slow or will timeout, and the root cause would be difficult to detect.
During an upgrade, the failure to create the index is being reported as a warning, but buried under a patch temporary report. Here is the warning in the log:
2015-02-12 13:53:58,860 WARN [ domain.schema.SchemaBootstrap ] [ localhost-startStop-1 ] Schema validation found 1 potential problems, results written to: J:\alf424\tomcat\temp\Alfresco\Alfresco-AlfrescoOracle9Dialect-Validation-Post-Upgrade-alf_-3227840462705701270.txt
The temporary file contains the actual error. It could have something like the following:
Error report -
SQL Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP 01652. 00000 - "unable to extend temp segment by %s in tablespace %s"
It is not possible for users to discern that the message should be linked to the performance problem that is affecting them.
Although strictly speaking Alfresco is reporting the error, it is difficult to interpret. A high level of knowledge of the database and indexes is required in order to understand the implications of the error buried in the db diagnostic file. We cannot expect users to make sense out of that error.
If the upgrade script failed, and the metadata queries were enabled, maybe we should not let Alfresco to start at all.
Customers Description of the problem.
"The following query is timing out from node browser with cmis strict in share:
SELECT cmis:objectId, cmis:name FROM gg:patExternalDocument WHERE gg:patDocumentStorageId = '05c93943-9a05-485f-a41d-d7912335647d'
The query does complete from our custom code but it takes roughly 5-20 minutes to complete."
"I don't know if Oracle reported success [ creating the indexes ] to Alfresco or not. ... If the patch is supposed to have applied some indexes and they are not applied and yet all indications in Alfresco are that the patch was applied; that is, in my opinion, a bug. "
Steps to reproduce
To demonstrate the issue, it is necessary to prevent the creation of the necessary indexes. In summary I did the following:
1. Created a 4.1.9 environment.
2. Added an index manually on alf_node_properties, which I named IDX_ALF_NPROP_S_TEST, so the upgrade script would fail trying to add the same index.
3. Set system.metadata-query-indexes.ignored=false in alfresco-global.properties
4. Upgraded to version 4.2.4
Alfresco should produce an ERROR message in the log, indicating that it was not possible to create the indices required for the correct performance of metadata queries.
If the indexes were not created, and system.metadata-query-indexes.ignored=false, then application of the patch should be considered as failed, and the bootstrap should be stopped.
A warning is generated on the logs ("Schema validation found 1 potential problems"), and a temporary file is created (e.g., "J:\alf424\tomcat\temp\Alfresco\Alfresco-AlfrescoOracle9Dialect-Validation-Post-Upgrade-alf_-3227840462705701270.txt").
Alfresco starts without problems.
Analysis to date
Customer business impact / priority / urgency. Customer has already diagnosed and solved the problem manually. However, this issue could be difficult to detect for users upgrading to version 4.2 and willing to take advantage of metadata queries.
Ideal Fix Version. 4.2.N