How to resolve the Oracle ORA-01408 error on Liferay

How to resolve the Oracle ORA-01408 error on Liferay

IT WORK FINE INTO MY LOCAL ENVIRONMENT - Nessun errore Oracle ORA-01408Develop “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.

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:

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 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.

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.

Antonio Musarra

I began my journey into the world of computing from an Olivetti M24 PC ( bought by my father for his work. Day after day, quickly taking control until … Now doing business consulting for projects in the enterprise application development using web-oriented technologies such as J2EE, Web Services, ESB, TIBCO, PHP.

You may also like...