What do I need to know about the Oracle B2C Service 18A: MySQL 5.7 Upgrade - General Announcement?
MySQL, Updates, 18A and newer
Audience: Oracle B2C Service (OSvC) Site Owners and Administrators
With the release of Oracle B2C Service 18A, we will begin migrating production sites to use MySQL version 5.7.17 or later. This migration allows Oracle B2C Service to continue to provide you with a secure and flexible platform for your unique customization and integration requirements.
Any site on Oracle B2C Service February 2015 or later automatically qualifies for the MySQL migration. Sites on versions older than February 2015 (or that are End of Life) must update to Oracle B2C Service May 2017 (or newer) prior to migrating to MySQL 5.7.
Site owners/administrators will be contacted in advance of the MySQL migration. If you have any questions about the migration please contact the Oracle B2C Service Support Team.
As an Oracle B2C Service site owner, administrator or developer, you should be aware of some of the incompatibilities between older MySQL versions and MySQL 5.7. These topics are especially relevant for sites with ROQL queries, custom reports with specific ordering requirements, or any legacy Direct SQL queries. Important: Critical report executions should be tested on a migrated test site prior to the MySQL migration of the production site. This list is not intended to be an exhaustive list of all improvements to 5.7 but represent the most probable items that may affect queries:
Unquoted date values in queries will not work in 5.7. For example, the commands below would return values in pre-5.7 MySQL versions based on the dates in the query (where stat_date is of type timestamp):
There are multiple changes in the DATE and TIMESTAMP data types. Some of the date and time functions are no longer supported in MySQL 5.7. Please review the MySQL documentation for details.
String to Number conversion precision has been improved: As of MySQL 5.5.3, the server includes "dtoa", a library for conversion between strings and numbers by David M. Gay. This library has improved the conversion from string to DECIMAL, FLOAT and DOUBLE values. Queries using these functions could return different precision values in the results.
Queries without an explicit 'ORDER BY' clause might return the results in a different order when executed in MySQL 5.7. Queries that require order precision should always use an 'order by' clause to be deterministic.
From version 5.7, queries using DISTINCT are required to have all the ORDER BY fields in the SELECT list. This requirement was not enforced in prior versions of MySQL.
For example, query below would fail with "ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'foo.file' which is not in SELECT list; this is incompatible with DISTINCT":
This list is not intended to be an exhaustive list of all changes to 5.7. Please refer to the MySQL documentation for details.
For additional information regarding the use of MySQL reserved words in custom fields, custom object classes, and attributes, see Answer ID 11797: MySQL Reserved Keywords. If there are any questions or concerns please reach out to the Oracle Cloud Customer Connect Discussion Forums.