掲示板
DLFileRank problem during upgrade
6年前 に Marco Ferretti によって更新されました。
DLFileRank problem during upgrade
Regular Member 投稿: 100 参加年月日: 10/10/04 最新の投稿
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 :
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.
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.
6年前 に Alberto Chaparro によって更新されました。
RE: DLFileRank problem during upgrade
Liferay Master 投稿: 549 参加年月日: 11/04/25 最新の投稿
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.
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.
6年前 に Marco Ferretti によって更新されました。
RE: DLFileRank problem during upgrade
Regular Member 投稿: 100 参加年月日: 10/10/04 最新の投稿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.
6年前 に Marco Ferretti によって更新されました。
RE: DLFileRank problem during upgrade
Regular Member 投稿: 100 参加年月日: 10/10/04 最新の投稿
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
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 :
And to get the rows that are candidates to generate the problem :
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 :
Here's my question(s) :
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 ?
6年前 に Marco Ferretti によって更新されました。
RE: DLFileRank problem during upgrade (回答)
Regular Member 投稿: 100 参加年月日: 10/10/04 最新の投稿
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);