Update to MySQL 5.7
Description
Environment
Activity
Tim Anderson February 21, 2021 at 11:28 PMEdited
The scope of the JIRA has been reduced to focus on what is required to support MySQL 5.7 without changing sql-mode. This has meant changes to queries and reports. The hibernate dialect has been retained to avoid migration issues. Updating the schema generation has been raised in
Updated reports:
Customer Acquisition Report.jrxml
Customer Product Sales Report.jrxml
Customer Retention Report.jrxml
Duplicate Customers-Patients.jrxml
Duplicate Customers-Patients subreport.jrxml
Export Customer Addresses.jrxml
Export Customer Addresses for Sales.jrxml
Export Customer Email Addresses.jrxml
Export Customer Email Addresses for Sales.jrxml
Export Customer Phone Numbers.jrxml
Export Customer Phone Numbers for Sales.jrxml
Debtors - Current.jrxml
Debtors - Historical.jrxml
Practice Summary_OutstandingDebtors.jrxml
Patient Deceased Report.jrxml
Product List Report.jrxml
Product Price List Report.jrxml
Stock Reorder Report.jrxml
Stock Valuation Report.jrxml
Stocktake Export Report.jrxml
Stocktake List Report.jrxml
Note that the query required to determine a single preferred contact is somewhat involved (e.g. see Export Customer Addresses.jrxml), so the contact functions have been updated to accept a customer identifier. These can be used in JasperReports using EVALUATE() e.g.:
Tim Anderson February 10, 2021 at 10:45 PM
There are a few issues with upgrading the dialect, at least for the purposes of schema generation:
datetime changes to datetime(6) which requires migration for existing sites. On the > 100GB sites, this will be time consuming
the hibernate3-maven-plugin which is used to ensure the schema is generated identically to previous releases fails to generate 'ON DELETE CASCADE' clauses when the org.hibernate.dialect.MySQL57InnoDBDialect is used. Not yet clear why this is the case, but the dependencies required in openvpms-release to get it to run at all may be the culprit:
Tim Anderson January 21, 2019 at 12:34 AMEdited
Hibernate has been updated to 5.3.6 as part of OBF-255. This no longer requires the dialect to be set.
The following sql-mode appears to work:
This is the default sql-mode for 5.7 with ONLY_FULL_GROUP_BY removed.
At the moment, we cannot support MySQL 5.7 without setting the sql_mode in mysqld.cnf:
Failure to set this will cause exceptions like:
To support it natively queries and reports needs to be updated to correctly use group by clauses