Rediger

Limitations in Azure Database for MySQL - Flexible Server

  • Artikel

APPLIES TO: Azure Database for MySQL - Flexible Server

This article describes limitations in Azure Database for MySQL flexible server. General limitations in the MySQL database engine are also applicable. If you'd like to learn about resource limitations (compute, memory, storage), see the compute and storage article.

Server parameters

Note

  • If you are looking for min/max values for server parameters like max_connections and innodb_buffer_pool_size, this information has moved to the server parameters concepts server parameters article.
  • lower_case_table_names value can only be set to 1 in Azure Database for MySQL flexible server.

Azure Database for MySQL flexible server supports tuning the values of server parameters. Some parameters' min and max values (ex. max_connections, join_buffer_size, query_cache_size) are determined by the compute tier and before you compute the size of the server. Refer to server parameters for more information about these limits.

Generated Invisible Primary Keys

For MySQL version 8.0 and above, Generated Invisible Primary Keys(GIPK) is enabled by default for all Azure Database for MySQL flexible server instances. MySQL 8.0+ servers add the invisible column my_row_id to the tables and a primary key on that column, where the InnoDB table is created without an explicit primary key. For this reason, you can't create a table having a column named my_row_id unless the table creation statement also specifies an explicit primary key. Learn more. By default, GIPKs are shown in the output of SHOW CREATE TABLE, SHOW COLUMNS, and SHOW INDEX, and are visible in the Information Schema COLUMNS and STATISTICS tables. For more details on GIPK and its use cases with Data-in-Replication in Azure Database for MySQL flexible server, refer to GIPK with Data-in-Replication.

Steps to disable GIPK

  • You can update the value of server parameter sql_generate_invisible_primary_key to 'OFF' by following steps mentioned on how to update any server parameter from Azure portal or by using Azure CLI.

  • Or you can connect to your Azure Database for MySQL flexible server instances and run the following command.

mysql> SET sql_generate_invisible_primary_key=OFF;

lower_case_table_names

In Azure Database for MySQL flexible server, the default value for lower_case_table_names is 1 for MySQL version 5.7. If you need to adjust this setting, we recommend reaching out to our support team for guidance. It's important to understand that once parameter value changed to 2, it's not allowed to revert from 2 back to 1.

For MySQL version 8.0, please note that changing the lower_case_table_names setting after the server is initialized is prohibited. Learn more. In Azure Database for MySQL flexible server version 8.0, the default value for lower_case_table_names is 1. If you wish to modify this parameter to 2, we suggest creating a MySQL 5.7 server, contacting our support team for assistance with the change, and later, if needed, you can upgrade the server to version 8.0.

Storage engines

MySQL supports many storage engines. On Azure Database for MySQL flexible server, the following is the list of supported and unsupported storage engines:

Supported

Unsupported

Privileges & data manipulation support

Many server parameters and settings can inadvertently degrade server performance or negate the ACID properties of the MySQL server. This service doesn't expose multiple roles to maintain the service integrity and SLA at a product level.

The MySQL service doesn't allow direct access to the underlying file system. Some data manipulation commands aren't supported.

Unsupported

The following are unsupported:

  • DBA role: Restricted. Alternatively, you can use the administrator user (created during the new server creation), which allows you to perform most of DDL and DML statements.
  • Below static privileges are restricted.
  • BACKUP_ADMIN privilege: Granting BACKUP_ADMIN privilege isn't supported for taking backups using any utility tools. Refer Supported section for list of supported dynamic privileges.
  • DEFINER: Requires super privileges to create and is restricted. If importing data using a backup, manually remove the CREATE DEFINER commands or use the --skip-definer command when performing a mysqlpump.
  • System databases: The mysql system database is read-only and used to support various PaaS functionalities. You can't make changes to the mysql system database.
  • SELECT ... INTO OUTFILE: Not supported in the service.

Supported

Functional limitations

Zone redundant HA

  • This configuration can only be set during server create.
  • Not supported in the Burstable compute tier.

Network

  • Connectivity method can't be changed after creating the server. If the server is created with Private access (virtual network Integration), it can't be changed to Public access (allowed IP addresses) after creation, and vice versa

Stop/start operation

  • Not supported with read replica configurations (both source and replicas).

Scale operations

  • Decreasing server storage provisioned isn't supported.

Server version upgrades

  • Automated migration between major database engine versions isn't supported. If you want to upgrade the major version, take a dump and restore to a server created with the new engine version.

Restore a server

  • With point-in-time restore, new servers are created with the same compute and storage configurations as the source server it's based on. The newly restored server's compute can be scaled down after the server is created.

Feature comparisons

Not all features available in Azure Database for MySQL single server are available in Azure Database for MySQL flexible server.

For the complete list of feature comparisons between Azure Database for MySQL single server and Azure Database for MySQL flexible server, refer to choosing the right MySQL Server option in Azure.

Next steps