Skip Navigation
Expand
Oracle B2C Service 18A: MySQL 5.7 Upgrade - General Announcement
Answer ID 9429   |   Last Review Date 04/09/2019

What do I need to know about the Oracle B2C Service 18A: MySQL 5.7 Upgrade - General Announcement?

Environment:

MySQL, Updates, 18A and newer
Audience: Oracle B2C Service (OSvC) Site Owners and Administrators 

Resolution:

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.  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:

 1. 

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):

a. mysql> INSERT INTO foo (source, stat_date) VALUES ('Input', 2016-03-01);

b. mysql> SELECT id FROM foo WHERE stat_date LIKE 2008-04-16;

But to make above queries work in MySQL 5.7 you need to add quotes to date values like this:

a. mysql> INSERT INTO foo (source, stat_date) VALUES ('Input', '2016-03-01');

b. mysql> SELECT id FROM foo WHERE stat_date LIKE '2008-04-16';

 2. 

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.

 3. 

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.

 4.

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.  

 5.

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":

a. mysql> SELECT DISTINCT(image_id) FROM foo INNER JOIN bar ON id = image_id WHERE category_id = 12345 ORDER BY foo.file ASC;

To resolve this issue set foo.file in the select clause:

a. mysql> SELECT DISTINCT(image_id), foo.file FROM foo INNER JOIN bar ON id = image_id WHERE category_id = 12345 ORDER BY foo.file ASC;


This list is not intended to be an exhaustive list of all changes to 5.7. Please refer to the MySQL documentation for details. If there are any questions or concerns please reach out to the Oracle Cloud Customer Connect Discussion Forums.

Available Languages for this Answer:

Notify Me
The page will refresh upon submission. Any pending input will be lost.