Mysql.Data and Azure Mysql error 'Table 'mysql.proc' doesn't exist'

When I try to execute the stored procedure against Azure MySQL version 8.0.15 server using following code I get the error saying 'Table 'mysql.proc' doesn't exist'. This error does not happen with older versions. some search says to use MySQL_upgrade but I don't get that to work against azure MySQL. How do I overcome this error? Everything works fine when I connect to local MySQL version which is same as azure hosted.

Sample code

static async Task Main(string[] args)
    {
        var builder = new MySqlConnectionStringBuilder
        {
            Server = "myserver.mysql.database.azure.com",
            Database = "mydb",
            UserID = "myserver@myserver",
            Password = "password",
            SslMode = MySqlSslMode.Prefered,
        };

        using (var conn = new MySqlConnection(builder.ConnectionString))
        {
            Console.WriteLine("Opening connection");
            await conn.OpenAsync();

            using (var command = conn.CreateCommand())
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "prc_myprocedure";

                var reader = await command.ExecuteReaderAsync(CommandBehavior.Default);
                while (reader.Read())
                {
                    Console.WriteLine(reader.GetValue(0));
                }
            }

            Console.WriteLine("Closing connection");
        }

        Console.WriteLine("Press RETURN to exit");
        Console.ReadLine();
    }
}

Complete error:

Unhandled exception. MySql.Data.MySqlClient.MySqlException (0x80004005): Table 'mysql.proc' doesn't exist
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.SchemaProvider.GetProcedures(String[] restrictions)
   at MySql.Data.MySqlClient.ISSchemaProvider.GetProcedures(String[] restrictions)
   at MySql.Data.MySqlClient.ISSchemaProvider.GetSchemaInternal(String collection, String[] restrictions)
   at MySql.Data.MySqlClient.SchemaProvider.GetSchema(String collection, String[] restrictions)
   at MySql.Data.MySqlClient.MySqlConnection.GetSchemaCollection(String collectionName, String[] restrictionValues)
   at MySql.Data.MySqlClient.ProcedureCache.GetProcData(MySqlConnection connection, String spName)
   at MySql.Data.MySqlClient.ProcedureCache.AddNew(MySqlConnection connection, String spName)
   at MySql.Data.MySqlClient.ProcedureCache.GetProcedure(MySqlConnection conn, String spName, String cacheKey)
   at MySql.Data.MySqlClient.StoredProcedure.GetParameters(String procName)
   at MySql.Data.MySqlClient.StoredProcedure.CheckParameters(String spName)
   at MySql.Data.MySqlClient.StoredProcedure.Resolve(Boolean preparing)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
   at AzureMySqlExample.MySqlUpdate.Main(String[] args) in C:\Users\sammo\source\repos\ConsoleApp1\Program.cs:line 31
   at AzureMySqlExample.MySqlUpdate.<Main>(String[] args)

ISSUE: You are getting the below error because when you start a connection to Azure MySQL, the gateway of Azure MySQL will first send back a greeting package with a default server version "5.6.42.0 MySQL Community Server (GPL)". This make the MySQL.Data driver treat the server version as 5.6. But actually it is 8.0 and in MySQL 8.0, the table mysql.proc not exists anymore.

Workaround: You can use ProxySQL to fix the version issue:

1. Set up ProxySQL for Azure MySQL https://techcommunity.microsoft.com/t5/Azure-Database-for-MySQL/Load-balance-read-replicas-using-ProxySQL-in-Azure-Database-for/ba-p/880042

2. Update the server version in ProxySQL Connecting to ProxySQL with admin account, run the following commands.

a. mysql –u admin –padmin -h 127.0.0.1 -P 6032

b. UPDATE global_variables SET variable_value='' WHERE variable_name='mysql-server_version';

c. load mysql variables to runtime;

d. save mysql variable to disk;