Intermittent 404 issues for customers who came from another DB engine

[erickgonzalez]

Parent Issue

#30000

Problem Statement

After getting the new logs, saw that the 404 was coming from the Identifier:

19/09/24 13:54:12:144 UTC] DEBUG business.IdentifierFactoryImpl: 404 Identifier found: Identifier [id=null, assetName=/phn-assets/phn-dynamic-pdfs/en/rif_invest_app_form_4_0_0.pdf, assetType=$$__404__CACHE_MISS__$$, parentPath=null, hostId=552d9ea5-a6c9-4cb2-bb0b-febdef8e35cf, sysPublishDate=null, sysExpireDate=null]

The code we use to get the identifier is the following:

core/dotCMS/src/main/java/com/dotmarketing/business/IdentifierFactoryImpl.java

Lines 145 to 176 in 5a7e977

	protected Identifier findByURI(final String siteId, final String uri) throws DotDataException {
	Identifier identifier = ic.getIdentifier(siteId, uri);
	if (identifier != null) {
	return check404(identifier);
	}
	final DotConnect dc = new DotConnect();
	final String parentPath = uri.substring(0, uri.lastIndexOf("/") + 1).toLowerCase();
	final String assetName = uri.substring(uri.lastIndexOf("/") + 1).toLowerCase();
	dc.setSQL("select * from identifier i where i.full_path_lc = ? and host_inode = ?");
	dc.addParam((parentPath + assetName).toLowerCase());
	dc.addParam(siteId);
	List<Identifier> results = null;
	results = TransformerLocator.createIdentifierTransformer(dc.loadObjectResults()).asList();
	if (results != null && !results.isEmpty()){
	identifier = results.get(0);
	}
	if(identifier==null || !InodeUtils.isSet(identifier.getId())) {
	identifier = build404(siteId,uri);
	}
	ic.addIdentifierToCache(identifier);
	return check404(identifier);
	}

Looking at the code, this column full_path_lc should be a function, not a column (at least in Postgres); for customers that were migrated from another DB engine, this was kept as a column and wasn't being filled.

Steps to Reproduce

Only Reproducible with customer data.

Acceptance Criteria

Pages and Files should return 200.

Create a UT that does the following:
1- Remove full_path_lc column:

ALTER TABLE identifier
DROP COLUMN full_path_lc;

2- Create the function if it doesn’t exists:

CREATE OR REPLACE FUNCTION full_path_lc(identifier) RETURNS text
    AS ' SELECT CASE WHEN $1.parent_path = ''/System folder'' then ''/'' else LOWER($1.parent_path || $1.asset_name) end; '
LANGUAGE SQL;

3- Create index (first check if exists):

CREATE UNIQUE INDEX idx_ident_uniq_asset_name on identifier (full_path_lc(identifier),host_inode);

dotCMS Version

22.03 LTS and 24.04 LTS.

Proposed Objective

Customer Success

Proposed Priority

Priority 1 - Show Stopper

External Links... Slack Conversations, Support Tickets, Figma Designs, etc.

https://helpdesk.dotcms.com/a/tickets/27067

Assumptions & Initiation Needs

No response

Quality Assurance Notes & Workarounds

No response

Sub-Tasks & Estimates

No response