How to resolve the Oracle ORA-01408 error on Liferay

Develop “happily” on Liferay 7 / DXP and everything works correctly on your local environment. When the fateful moment of deployment in different environments from your own, someone calls you telling you that your bundle is not working and the log shows this error:

ORA-01408: esiste già un indice per questa lista di colonne: create index IX_1118FBD3 on lbro_horse_Horse (horseId);

Your first answer is: but my local environment works! and on the other hand you feel like answering: yes, but you have not installed database oracles on your local environment.

A few days ago I found myself in this scene; to be able to understand the cause and find the solution without upsetting everything was not easy at all.

1. We face the damn ORA-01408

The Oracle ORA-01408 error when running the CREATE INDEX statement indicates that there is already an index for the specified column. The SQL code for the database is generated by Liferay, including that of index management, ergo, error fault is Liferay. In fact Liferay (and especially the Service Builder) has nothing to do with it, we will see why and how to solve this hateful and blocking problem.

Let’s consider the description of the Horse entity defined within the service.xml file. Line 10 defines the entity’s primary key, and the line block 33-35 defines the HorseIds finder, which allows you to get a collection of Horse objects by specifying a horseId list.

<?xml version="1.0"?>
<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 7.0.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_7_0_0.dtd">

<service-builder package-path="it.dontesta.labs.liferay.lrbo16.servicebuilder">
	<namespace>LRBO_HORSE</namespace>
	<entity local-service="true" name="Horse" remote-service="true" uuid="true">

		<!-- PK fields -->

		<column name="horseId" primary="true" type="long" />

		<!-- Group instance -->


		<!-- Audit fields -->

		<!-- Other fields -->

		<column name="name" type="String" />
		<column name="age" type="int" />
		<column name="gender" type="String" />
		<column name="kind" type="String" />
		<column name="mantle" type="String" />

		<!-- Order -->

		<order by="asc">
			<order-column name="name" />
		</order>

		<!-- Finder methods -->

		<finder name="HorseIds" return-type="Collection">
			<finder-column name="horseId" arrayable-operator="OR" comparator="="/>
		</finder>

		<finder name="Name" return-type="Collection">
			<finder-column name="name" />
		</finder>

	</entity>
</service-builder>

For our finder, the build service process generates the appropriate index creation instructions. As defined in this specific service.xml file, the file indexes.sql has the following content:

create index IX_1CD9D1BA on LRBO_HORSE_Horse (age);
create index IX_1118FBD3 on LRBO_HORSE_Horse (horseId);
create index IX_943F5AD4 on LRBO_HORSE_Horse (name[$COLUMN_LENGTH:75$]);
create index IX_12A69589 on LRBO_HORSE_Horse (uuid_[$COLUMN_LENGTH:75$], companyId);
create unique index IX_16B8EA4B on LRBO_HORSE_Horse (uuid_[$COLUMN_LENGTH:75$], groupId);

Line 2 shows the index creation statement for the horseId column, which in this case is also the primary key of the Horse entity. Be aware of the index name specified in line 2 statement (IX_1118FBD3), that’s exactly what mentioned in the error returned by Oracle.

The content of the indexes.sql file has a single index creation for the horseId column. At this point the question is spontaneous: who creates the index for the horseId column?

Let’s remember that the horseId column is defined as a primary key, and in these cases, the database implicitly creates a unique index for the key; then it is permissible by the database to lift the exception ORA-01408.

2. We solve the damn ORA-01408

Very well! Now that we are aware of the cause of the error ORA-01408, the next step is to find a simple solution to apply to solve the problem. I’m sure all of you will be asking:

It is possible that Liferay has not thought about the way to solve this problem? Possible that there is no way to avoid the creation of indexes?

Unfortunately Liferay official documentation does not answer our questions, but do not despair, because the DTD service.xml has the answer inside.

<!--
The name value specifies the name of the finder method.

The return-type value specifies the return type of the finder. Valid values are
"Collection" or the name of the entity. If the value is "Collection", then this
finder returns a list of entities. If the value is the name of the entity, then
this finder returns at most one entity.

If the unique value is true, then the finder must return a unique entity.

If the db-index value is true, then the service will automatically generate a
SQL index for this finder. The default value is true.
-->
<!ATTLIST finder
	name CDATA #REQUIRED
	return-type CDATA #REQUIRED
	unique CDATA #IMPLIED
	where CDATA #IMPLIED
	db-index CDATA #IMPLIED
>

The db-index attribute on the finder element is the answer to our problem. The default value is true, we will add the db-index attribute to the finder element by setting the value to false, launching immediately after the build service process (but after deleting the old indexes.sql file). Examining the indexes.sql file right after running the build service process, we find with great happiness that the index creation for the horseId column has disappeared.

create index IX_1CD9D1BA on LRBO_HORSE_Horse (age);
create index IX_943F5AD4 on LRBO_HORSE_Horse (name[$COLUMN_LENGTH:75$]);
create index IX_12A69589 on LRBO_HORSE_Horse (uuid_[$COLUMN_LENGTH:75$], companyId);
create unique index IX_16B8EA4B on LRBO_HORSE_Horse (uuid_[$COLUMN_LENGTH:75$], groupId);

At this point we commit our bundle “little man” (eg Jenkins) responsible for deployment in environments with Oracle database, with the certainty that everything will end well.