DLFileRank problem during upgrade

[Marco Ferretti, geändert vor 6 Jahren.]

Hi all,

I am trying to upgrade a portal (CE) from version 6.0.6 to 7. In order to minimize the headaches I am first upgrading to 6.2 (ga6) and then I will try the final upgrade.
While performing the (bundle) upgrade to 6.2, among other issues, I have this :

[localhost-startStop-1][BaseDB:106] ERROR: could not create unique index "ix_38f0315"_  Detail: Key (companyid, userid, fileentryid)=(10112, 92319, 0) is duplicated.: create unique index IX_38F0315 on DLFileRank (companyId, userId, fileE
ntryId); [Sanitized]

For the little knowledge that I have on the database structure I managed to identify the "problematic" files to belonging to a user in his own space : me.
I am not 100% sure as of how the duplication got in the database, one possible cause is that I use DAV functionalities to manipulate my files.
Anyways, the point here is : how do I get rid of the duplicated entries in dlfilerank table (in a safe way) ? What does the [Sanitized] message means ? As far as I understand it's trying to create a unique key in a table and it can't . The duplicated values are still there, so the message does not refer to the fact that it fixed the db and created the index ... right ?

Thanks in advance for any light you can shed on this.

Marco F.

[Alberto Chaparro, geändert vor 6 Jahren.]

Hi Marco,

The index which is failing is this one:
create index IX_38F0315 on DLFileRank (companyId, userId, fileEntryId);

To solve it, you can remove (using the API) the rows which violate this index in 6.0.6 before the upgrade.

Let us know if it solves your issue.

Regards.

[Marco Ferretti, geändert vor 6 Jahren.]

Alberto Chaparro:

Hi Marco,

The index which is failing is this one:
create index IX_38F0315 on DLFileRank (companyId, userId, fileEntryId);

To solve it, you can remove (using the API) the rows which violate this index in 6.0.6 before the upgrade.

Let us know if it solves your issue.

Regards.

Hi Alberto,

I was digging into the LF 6.0.6 database and, with much surprise, I found that the column fileEntryId is not part of the table dlfilerank :

 \d dlfilerank
              Table "public.dlfilerank"
   Column   |            Type             | Modifiers 
------------+-----------------------------+-----------
 filerankid | bigint                      | not null
 groupid    | bigint                      | 
 companyid  | bigint                      | 
 userid     | bigint                      | 
 createdate | timestamp without time zone | 
 folderid   | bigint                      | 
 name       | character varying(255)      | 
Indexes:
    "dlfilerank_pkey" PRIMARY KEY, btree (filerankid)
    "ix_ce705d48" UNIQUE, btree (companyid, userid, folderid, name)
    "ix_40b56512" btree (folderid, name)
    "ix_bafb116e" btree (groupid, userid)
    "ix_eed06670" btree (userid)

It looks like some other upgrade process is creating the column and setting it to a (incorrect?) value. Can you please confirm and eventually tell me where else I should take a look ?

TIA

Marco F.

[Marco Ferretti, geändert vor 6 Jahren.]

Ok, I have digged more in the upgrade code and it looks like the issue comes from the fact that there are 4 entries in DLFileRank that do not have a corresponding entry in DLFile.
When the upgrade process (com.liferay.portal.upgrade.v6_1_0) starts at some point it calls the UpgradeDocumentLibrary.updateFileRanks which

• scans the DLFileRank table
• for each quartet composed by groupId,fileRankId,folderId and name it looks for a corresponding entry in DLFile (calls UpgradeDocumentLibrary.getFileEntryId(long groupId, long folderId, String name) )
• if the entry is found then the value of fileentryid is inserted into the new column in DLFileRank.
• if the entry is not found then a value of 0 is returned

Please note that no warning whatsoever is logged in the last case scenario.

At this point my DLFileRank table contains duplicates by a key composed of companyid, userid, fileentryid and when the process tries to create the (unique) index the error is thrown and logged.

A quick (and dirty) way of mimicking the upgrade process and checking those lines without actually upgrading them is the following (Postgre)SQL bit :

with mydlfilerank as (
    select 
          a.companyid, a.userid, a.groupId, a.fileRankId, a.folderId, a.name, case when fileentryid is null then 0 else fileentryid end as fileentryid    
    from 
         DLFileRank a left join DLFileEntry b on 
                  b.groupid=a.groupid and b.folderid=a.folderid and b.name=a.name 
) 
select 
      companyid, userid, fileentryid,count(0) 
from 
     mydlfilerank 
group by 
     companyid, userid, fileentryid having count(0) > 1;

And to get the rows that are candidates to generate the problem :

with newdlfilerank as (
	select 
		a.companyid, a.userid, a.groupId, a.fileRankId, a.folderId, a.name, case when fileentryid is null then 0 else fileentryid end as fileentryid    
	from 
		DLFileRank a left join DLFileEntry b on b.groupid=a.groupid and b.folderid=a.folderid and b.name=a.name 
) 
select * from newdlfilerank where fileentryid = 0;

in my case there are 9 rows from DLFileRank that do not have a corresponding DLFileEntry. These 9 rows are not all bad (in this scenario), only 4 of them :

 companyid | userid | groupid | filerankid | folderid | name  | fileentryid 
-----------+--------+---------+------------+----------+-------+-------------
     10112 |  92319 |   92321 |     115519 |        0 | 5433  |           0
     10112 |  92319 |   92321 |     115520 |        0 | 5434  |           0
     10112 |  92319 |   92321 |     115529 |        0 | 5435  |           0
     10112 |  92319 |   80305 |     296481 |   101109 | 11021 |           0

Here's my question(s) :

• How can there be values in DLFileRank without reference to DLFile ?
• Since filerankid is nowere to be found but in DLFileRank (and its indexes), is it safe to select these values in DLFileRank and delete them (from the database) BEFORE running the upgrade process ?

[Marco Ferretti, geändert vor 6 Jahren.]

In case anyone is interested, at least in this particular case, running this sql delete query before the migration fixes my issue and so far I haven't seen any drawbacks :

delete from DLFileRank where filerankid in (with newdlfilerank as (select a.companyid, a.userid, a.groupId, a.fileRankId, a.folderId, a.name, case when fileentryid is null then 0 else fileentryid end as fileentryid    from DLFileRank a left join DLFileEntry b on b.groupid=a.groupid and b.folderid=a.folderid and b.name=a.name ) select filerankid from newdlfilerank where fileentryid = 0);