Upgrade from 6.1: Duplicated data in DDMTemplate table prevents index IX_E6DFAB84 creation when using Oracle database

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 6.2.X EE, 7.0.X, 7.1.X, 7.2.X, Master
• Fix Version/s: 7.2.10 DXP FP8, 7.2.10.2 DXP SP3, 7.2.X, 7.3.4 CE GA5, 7.3.10 DXP GA1, Master
• Component/s: Dynamic Data Mapping > Upgrades
• Labels:
  • ee-ts
  • liferay-dxp-72-sp3
  • liferay-fixpack-dxp-8-7210
  • pt-upgrade-review
• Branch Version/s:
  7.2.x
• Backported to Branch:
  Committed
• Fix Priority: 3
• Git Pull Request: https://github.com/leoadb/liferay-portal/pull/978

Duplicated data in DDMTemplate table prevents index IX_E6DFAB84 during upgrade from 6.1 to 6.2, 7.0 or 7.1.

11:17:26,323 WARN  [localhost-startStop-1][BaseDB:108] ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found_: create unique index IX_E6DFAB84 on DDMTemplate (groupId, classNameId, templateKey); [Sanitized]

The root cause of the issue is new templateKey column is added in 6.2 but it is partially initialized in UpgradeDynamicDataMapping.java, we are only processing DDMTemplate entries with language = xsd, for more information, see LPS-41936 and LPS-41975

This issue is only reproduced in Oracle database as it doesn't allow creating a unique index with more than one NULL value in a column.
Other databases as postgresql or mysql allows it, so the error is not produced.

Steps to reproduce

1. Install Liferay 6.1 GA2
2. Create a DDL data definition
3. Create two list templates in the DDL data definition. the issue is only reproduced if you use list templates
4. Upgrade the Liferay installation:
   • Direct upgrade from 6.1 to master or 7.2 is not supported
   • You have to stop in a intermediate version, executing two upgrade steps
   • For example:
     • Option 1: 6.1 => 6.2 and 6.2 => master
     • Option 2: 6.1 => 7.1 and 7.1 => master
5. Check DDMTemplate table and the upgrade log file:
   • Expected behavior: Unique index IX_E6DFAB84 is created in DDMTemplate
   • Wrong behavior: Unique index IX_E6DFAB84 is not created in DDMTemplate and following warning is written to log file

     11:17:26,046 INFO  [localhost-startStop-1][BaseDB:100] create unique index IX_E6DFAB84 on DDMTemplate (groupId, classNameId, templateKey);
     11:17:26,323 WARN  [localhost-startStop-1][BaseDB:108] ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found_: create unique index IX_E6DFAB84 on DDMTemplate (groupId, classNameId, templateKey); [Sanitized]

Note: as we have to execute two upgrade process, the ORA-01452: cannot CREATE UNIQUE INDEX; warning will be produced in the first upgrade that starts in 6.1, but it can safely ignored because index are recreated in all upgrade operations. If the warning is not shown in second upgrade that ends in master, it will be fine.