Limitations in Azure Database for MySQL - Flexible Server
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.
- If you are looking for min/max values for server parameters like
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.
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
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;
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.
MySQL supports many storage engines. On Azure Database for MySQL flexible server, the following is the list of supported and unsupported storage engines:
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.
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 DEFINERcommands or use the
--skip-definercommand 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
SELECT ... INTO OUTFILE: Not supported in the service.
LOAD DATA INFILEis supported, but the
[LOCAL]parameter must be specified and directed to a UNC path (Azure storage mounted through SMB). Additionally, if you're using MySQL client version >= 8.0, you need to include
-–local-infile=1parameter in your connection string.
- For version MySQL 8.0 and above, below mentioned dynamic privileges are only supported.
Zone redundant HA
- This configuration can only be set during server create.
- Not supported in the Burstable compute tier.
- 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
- Not supported with read replica configurations (both source and replicas).
- 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.
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.