Database indexes are not recreated in fallback when upgrading

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 6.2.X EE, 7.0.X, Master
• Fix Version/s: 7.0.0 DXP FP47, 7.0.0 DXP SP8, 7.0.X, 7.1.0 Beta 1, 7.1.X, Master
• Component/s: Core Infrastructure > Service Builder, Database Upgrade Framework
• Labels:
  • QA-AUTOMATION
  • liferay-digital-enterprise-70-sp8
  • liferay-fixpack-de-47-7010
• Branch Version/s:
  7.0.x
• Backported to Branch:
  Committed
• Story Points: 5
• Fix Priority: 4
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/57963
• Bug Type: Database Upgrade
• QA Test Name:
  • LocalFile.CPDocumentsandmedia#MoveToRecycleBinDocument
  • LocalFile.CPDocumentsandmedia#ViewImageEditorControls
  • LocalFile.DocumentsadministrationUsecase#AddAndRemoveMetadataSets
  • LocalFile.DocumentsadministrationUsecase#AddDocumentTypeAndMetadata
  • LocalFile.DocumentsadministrationUsecase#UpdateDocumentTypeWithPermissions
  • LocalFile.DocumentsadministrationUsecase#ViewDateFieldRequiredInContract
• QA Test Score: 29

Reproducing steps for master or 7.0.x

1. Take a Liferay 6.2.10 installation with a database with Oracle. Make sure all indexes are present. (This might not be the case after portal-160 for a table like KaleoLog due to the present bug.)
2. Upgrade to master. (For some entities the upgrade goes to the fallback procedure of recreating the table and indexes.)
3. Check database indexes for the tables:

• KaleoLog
• LayoutPrototype
• ShoppingCart
• ShoppingOrder
• ShoppingOrderItem
• DDMStructure
• DDMTemplate
• JournalArticle
• CalendarBooking

Expected: Indexes are present.
Observed: (Not all) Indexes are not present.

Reproducing steps for 6.2.x or 6,2.10 with portal-160+

1. Start Liferay.
2. Check database indexes for the table KaleoLog.

Expected: Indexes are present.
Observed: Indexes are not present.

Notes

The problem comes from:

1. using the method com.liferay.portal.kernel.dao.db.DB#runSQLTemplateString(java.sql.Connection, java.lang.String, boolean, boolean) called in com.liferay.portal.upgrade.util.BaseUpgradeTableImpl#updateTable(java.sql.Connection, java.sql.Connection, boolean), which expects the last line of a sql to end in semicolon,
2. the sql sentences in the array TABLE_SQL_ADD_INDEXES in the classes *Table.java not ending in semicolon.