Share via

MySql Flexible server upgrade from 5.7 to 8.0 fails without error information

Jan Buijnsters 20 Reputation points
2024-12-04T14:26:49.2866667+00:00

We are trying to do a major version upgrade of Azure MySQL flexible server 5.7 -> 8.0.
I have removed all application databases for troubleshooting the issue.
The azure validation results show a successful result.
User's image

I get back the following error without any extra information, except to check database compatability with the upgrade checker:
{"code":"DeploymentFailed","target":"/subscriptions/{SUBSCRIPTION}/resourceGroups/{RG}/Microsoft.Resources/deployments/UpgradeMySqlFlexibleServer_68kdkdo1566bafe3","message":"At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/arm-deployment-operations for usage details.","details":[{"code":"ResourceDeploymentFailure","target":"/subscriptions/{SUBSCRIPTION}/resourceGroups/{RESOURCE_GROUP}/providers/Microsoft.DBforMySQL/flexibleServers/tmp-jbui-devtest-mysql-upgrade","message":"The resource write operation failed to complete successfully, because it reached terminal provisioning state 'Failed'."}]}

Checking DB compatiblity with the mysql 8.0 upgrade checker gives us back in total 4 errors:
"id": "routinesSyntaxCheck", "title": "MySQL 8.0 syntax check for routine-like objects", "status": "OK", "description": "The following objects did not pass a syntax check with the latest MySQL 8.0 grammar. A common reason is that they reference names that conflict with new reserved keywords. You must update these routine definitions and `quote` any such references before upgrading.", "documentationLink": "https://dev.mysql.com/doc/refman/en/keywords.html", "detectedProblems": [{ "level": "Error", "dbObject": "mysql.az_replication_change_master", "description": "at line 3,4255: unexpected token 'REPLICATION'"},{ "level": "Error", "dbObject": "mysql.az_replication_change_master_with_gtid", "description": "at line 3,4251: unexpected token 'REPLICATION'"},{ "level": "Error", "dbObject": "mysql.az_replication_remove_master", "description": "at line 3,2370: unexpected token 'REPLICATION'"},{ "level": "Error", "dbObject": "mysql.az_update_replica_information", "description": "at line 3,1525: unexpected token 'REPLICATION'"}]

Based on the note in the major version upgrade documentation, I am assuming these 4 errors can be safely ignored, is this correct?
https://zcusa.951200.xyz/en-us/azure/mysql/flexible-server/how-to-upgrade#prerequisites
"""
When you use Oracle's official tool to check schema compatibility, you might encounter some warnings indicating unexpected tokens in stored procedures, such as: mysql.az_replication_change_master - at line 3,4255: unexpected token 'REPLICATION' mysql.az_add_action_history - PROCEDURE uses obsolete NO_AUTO_CREATE_USER sql_mode You can safely ignore these warnings. They refer to built-in stored procedures prefixed with mysql., which are used to support Azure MySQL features. These warnings do not affect the functionality of your database.
"""

The upgrade also fails when performed via the az cli.

As I get no extra error information back this issue proves to be difficult to solve from our side alone.
Could you possibly give me any direction what our next steps should be?

Thanks,
Kind regards,
Jan

Azure Database for MySQL
Azure Database for MySQL
An Azure managed MySQL database service for app development and deployment.
874 questions
0 comments
Accepted answer
  1. Vijayalaxmi Kattimani 740 Reputation points Microsoft Vendor
    2024-12-04T17:09:10.31+00:00

    Hi @Jan Buijnsters,

    Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

    We would like to inform you that, The error indicates that the upgrade attempt of your Azure MySQL Flexible Server failed during the resource deployment stage. This failure suggests potential issues related to server settings, or compatibility.

    Kindly note that below mentioned important points:

    • Duration of downtime varies based on the size of the database instance and the number of tables it contains.
    • When initiating a major version upgrade for Azure Database for MySQL flexible server via Rest API or SDK, please avoid modifying other properties of the service in the same request. The simultaneous changes are not permitted and might lead to unintended results or request failure. Please conduct property modifications in separate operations post-upgrade completion.
    • Some workloads might not exhibit enhanced performance after upgrading from 5.7 to 8.0. We suggest that you evaluate the performance of your workload by first creating a replica server (as a test server), then promoting it to a standalone server and then running the workload on the test server prior to implementing the upgrade in a production environment.
    • Upgrading the major MySQL version is irreversible. Your deployment might fail if validation identifies that the server is configured with any features that are removed or deprecated. You can make necessary configuration changes on the server and try the upgrade again.
    • Read Replicas with MySQL version 5.7 should be upgraded before Primary Server for replication to be compatible between different MySQL versions, read more on Replication Compatibility between MySQL versions.
    • Before upgrading your production servers, it's now easier and more efficient with our built-in Validate feature in the Azure portal. This tool pre-checks your database schema's compatibility with MySQL 8.0, highlighting potential issues. While we offer this convenient option, we also strongly recommend you use the official Oracle MySQL Upgrade checker tool to test your database schema compatibility and perform necessary regression test to verify application compatibility with features removed/deprecated in the new MySQL version.

    Note: When you use Oracle's official tool to check schema compatibility, you might encounter some warnings indicating unexpected tokens in stored procedures, such as: mysql.az_replication_change_master - at line 3,4255: unexpected token 'REPLICATION' mysql.az_add_action_history - PROCEDURE uses obsolete NO_AUTO_CREATE_USER sql_mode You can safely ignore these warnings. They refer to built-in stored procedures prefixed with mysql., which are used to support Azure MySQL features. These warnings do not affect the functionality of your database.

    To link two servers and start replication, login to the target replica server in the Azure Database for MySQL service and set the external instance as the source server. This is done by using the mysql.az_replication_change_master or mysql.az_replication_change_master_with_gtid stored procedure on the Azure Database for MySQL server.

    To remove the relationship between source and replica server, use the following stored procedure: CALL mysql.az_replication_remove_master;

    Please refer to the below mentioned links for more information.

    https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-deprecations

    https://zcusa.951200.xyz/en-us/azure/mysql/flexible-server/how-to-data-in-replication?tabs=bash%2Ccommand-line

    https://dev.classmethod.jp/articles/mysql-flexible-server-major-version-upgrade/

    I hope, This response will address your query and helped you to overcome on your challenges.

    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jan Buijnsters 20 Reputation points
    2024-12-23T08:40:41.2333333+00:00

    There is an upgrade compatibility issue with the server parameter audit_log_events -> CONNECTION_V2.
    Make sure to switch it over to e.g. option CONNECTION.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.