[FINERACT-723] Integration tests fail when the default tenant has a different time-zone than the system(s) running the application and database servers

• Type: Bug
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Affects Version/s: 1.1.0, 1.2.0, 1.3.0
• Labels: GSOC GSoC gsoc gsoc2019 gsoc2020
• Assignee: Unassigned
• Reporter: Vishwas Babu A J
• Created: 05/Mar/19 23:34
• Updated: 07/Sep/21 12:50

Description

Each tenant in Fineract has its timezone configured in the `tenants` table under the `fineractplatform-tenants` schema and the application should consider the same while carrying out any date/time related operations. This does not seem to be done consistently in the application and intermittent errors can be seen when the Application (tomcat) and database servers (MySQL) are run on systems with different timezones than that of the tenant.

Ex: Set the System timezone to "PDT". The default demo tenant is set to "Asia/Kolkata" and integration tests run on the same would fail intermittently.

Fixing the same would involve

• Ensuring that all dates created in the API / service layers are done in a tenant sensitive fashion by utilizing the methods present in the class org.apache.fineract.infrastructure.core.service.DateUtils.
• Ensure database operations do not use MySQL date functions. Ex:

@Override

    public Collection<SavingsAccountAnnualFeeData> retrieveChargesWithAnnualFeeDue() {

        final String sql = "select " + this.chargeDueMapper.schema() + " where sac.charge_due_date is not null and sac.charge_time_enum = ? "

                + " and sac.charge_due_date <= NOW() and sa.status_enum = ? ";




        return this.jdbcTemplate.query(sql, this.chargeDueMapper, new Object[] {ChargeTimeType.ANNUAL_FEE.getValue(), SavingsAccountStatusType.ACTIVE.getValue()});

    }

 in
org.apache.fineract.portfolio.savings.service.SavingsAccountChargeReadPlatformServiceImpl would cause issues with Timezones. We should be using something along the lines of

@Override
	public Collection<SavingsAccountAnnualFeeData> retrieveChargesWithAnnualFeeDue() {
	String currentdate = formatter.print(DateUtils.getLocalDateOfTenant());
	final String sql = "select " + this.chargeDueMapper.schema() + " where sac.charge_due_date is not null and sac.charge_time_enum = ? and sac.charge_due_date <= ? and sa.status_enum ? ";
	
	return this.jdbcTemplate.query(sql, this.chargeDueMapper, new Object[] {currentdate, ChargeTimeType.ANNUAL_FEE.getValue(), SavingsAccountStatusType.ACTIVE.getValue()});
	}

 

 

 

Issue Links

• blocks: FINERACT-852 ArrayIndexOutOfBoundsException in ClientSavingsIntegrationTest and SchedulerJobsTest
• blocks: FINERACT-885 ClientLoanIntegrationTest is (still) "flaky"
• is fixed by: FINERACT-1394 Partially Revert FIN-1112 "Replace ZoneId.systemDefault() with tenant's timezone"
• is related to: FINERACT-924 SchedulerJobsTestResults.testApplyAnnualFeeForSavingsJobOutcome
• relates to: FINERACT-901 Add serverTimezone=UTC (via JDBC URL?)
• links to: GitHub Pull Request #914
• links to: GitHub Pull Request #1068