MySQL Database on Azure FAQ

Service consulting

Is the amount of storage space for data backups limited?

Data backups do not count toward your storage limit.

Is there a limit to the number of databases on a single server?

Users can create multiple databases on a single MySQL server, and there is no limit to the number of databases that can be created. However, multiple databases share server resources, and performance requirements rise as the number of databases grows. We recommend that you create multiple MySQL servers.

What are the current limitations of MySQL Database on Azure?

For more information about limitations, see service limitations of MySQL Database on Azure.

Why doesn’t MySQL Database on Azure support databases in MyISAM format?

The product R&D team has studied and analyzed this issue several times. They ultimately decided not to support it, for the following reasons:

  • MyISAM has flaws in terms of data integrity protection, and these flaws could cause data to be damaged or even lost. Moreover, because the flaws are primarily design faults, there is no way to repair them without breaking compatibility.
  • Because the MyISAM input/output (I/O) operations are not the optimal solution for storage on Azure, MyISAM offers little performance advantage over InnoDB.
  • MyISAM requires significant manual repair work if data is damaged, and it cannot be adapted to a platform as a service (PaaS) operating model.
  • The cost of migrating from MyISAM to InnoDB is minimal. In most applications, migration requires only changes to the table creation code.
  • The development of MySQL is also moving toward InnoDB. For example, MySQL 5.7 doesn’t use MyISAM at all, and the system database has also been transitioned to InnoDB.

Why is the default size for new, empty database servers set to 530 MB? Why is the displayed amount of storage space that's used for the database larger than the amount of storage space that's actually used?

For performance reasons, we use two 256-MB log files for new database-instance configurations. Consequently, the storage space usage figure that you see in the Azure portal includes the size of the log files. However, the size of the log files does not change during usage.

Can I use commands to set permissions on MySQL Database on Azure?

Yes. Although the Azure portal and PowerShell command line supports setting only read-write permissions for the entire database when you create users or databases, you can use the grant command to configure user permissions in greater detail.

What is the system time setting in MySQL Database on Azure? How can I change it?

The MySQL Database on Azure system time defaults to UTC, but you can update the time by configuring an offset with the Azure portal or PowerShell. For more information, see Change time zones on MySQL on Azure.

What functions does MySQL 5.7 provide? Can all these functions be used on MySQL Database on Azure?

MySQL Database on Azure is fully compatible with MySQL Community Edition. For more information about new functions in version 5.7, see the MySQL 5.7 Release Notes. MySQL Database on Azure is compatible with the vast majority of updated functions, but it does not yet support the following functions:

  • The replication function improvements in version 5.7. This is because the MySQL Database on Azure master-subordinate sync and replication functions make certain improvements to MySQL Database on Azure Services, rather than simply using MySQL replication technology.
  • InnoDB Buffer Pool Online Resize in version 5.7.
  • Query Rewrite Plugin.
  • InnoDB Transparent Page Level Compression.
  • Password expiration.

How do I create MySQL 5.7 instances in the Azure portal?

In the Azure portal, in the left pane, select New > Database > MySQL Databases on Azure > Create and then, under MySQL version, select 5.7.

Create MySQL 5.7

I already have a MySQL Database on Azure database instance, but it is version 5.6. How do I upgrade it to 5.7?

We suggest that you understand the changes in version 5.7 before you upgrade, and that you do not upgrade until you've tested the compatibility between application client and database. If you're in a production environment, we suggest that you upgrade the database when there are fewer operations to be processed by doing the following:

Step 1: (Optional) If you are worried about the impact of compatibility issues on the production environment after the upgrade, use the database restore function to restore the existing database to a new instance. For more information, see Backup and restore a MySQL Database on Azure. We suggest that you use the Backup Now feature to make a snapshot of the existing database server and then restore this snapshot to another new instance.

Step 2: Export the current database server data by using a MySQL management tool such as MySQL Workbench or a MySQL utility such as mysqldump.

mysqldump --databases <数据库名> --single-transaction --order-by-primary -r <备份文件名> --routines -h<服务器地址> -P<端口号> –u<用户名> -p<密码>

Step 3: Create a 5.7 instance and an account in the Azure portal. To do so, in the left pane of the Azure portal, select New, perform a quick create and then, under MySQL version, select version 5.7. Also, create accounts in the original database instance through the Azure portal.

Step 4: Import the data to the 5.7 instance with the common MySQL management tool Workbench or by using the SQL phrase source <backup file name>. After you've completed the preceding steps, you can switch the connection string of the application layer to a new server to start use.

How do I downgrade an MP service layer (MP1 or MP2) to an MS service layer (MS1-MS6)?

Because MP service layers use different storage schema than do MS service layers, MySQL Database on Azure does not currently support directly downgrading from MP to MS. If you are certain that you need to perform this type of downgrade operation, first create a subordinate instance and then promote it to a master instance. The steps are shown below (a downgrade from MP1 to MS6 is given as an example; the downgrade operation for other service layers is similar).

The names of the resources involved in this example are provided in the following table:

Resource MP1 MS6
Resource group Default-MySQL-ChinaEast Default-MySQL-ChinaEast
Server mysqldemoserver mysqlmsserver
Location ChinaEast ChinaEast
Test database replicadb replicadb

Step 1: Ensure that there is at least one complete backup of the MP1 instance, and that the backup status is Ready. Depending on your settings, this process might take up to one day.

Daily backup ready

Step 2: Execute the PowerShell command to create an MS6 subordinate instance (read-only instance).

New-AzureRmResource -ResourceType "Microsoft.MySql/servers" -ResourceName mysqlmsserver -ApiVersion 2015-09-01 -ResourceGroupName Default-MySql-ChinaEast -Location ChinaEast -SkuObject @{name='MS6'} -Properties @{replicationMode='AzureSlave'; creationSource=@{server='mysqldemoserver';region='chinaEast'}; version = '5.6';}

Step 3: Wait until the latency between the subordinate instance (MS6) and the master instance (MP1) is sufficiently low (for example, less than 60 seconds), and then suspend all the client program’s write operations on the MP1 master instance.

Replica latency

Step 4: Create the replicadb database on the MP1 master instance, and wait until this test database has synced to the MS6 subordinate instance. Now the data on the two instances is fully synced.

Replica database synced

Step 5: Promote the MS6 subordinate instance to a master instance. For more information, see Promote subordinate instances.

Finally, you can modify the client program connection string so that it points to the new MS6 instance, and then delete the MP1 instance.

Note

In certain circumstances it might be inconvenient to modify the client program connection string. We have provided a process for your reference in which there is no need to modify the client program connection string. The following steps do not require you to modify the client program connection string, but they might temporarily cause downtime for upper layer applications (usually for a few minutes).

Step 6: Create a manual instant backup of the MS6 instance. For more information, see Backup and restore a MySQL Database on Azure.

Step 7: Delete the MP1 instance, and then restore the manual instant backup created in Step 6 to a new MS6 instance with the same name as the original MP1 instance (in this example, mysqldemoserver).

Finally, delete the temporary MS6 instance mysqldemoserver, and then restore the client program’s normal read and write operations on the MS6 instance.

Connection issues

I can’t connect to MySQL Database on Azure after I create a database. What should I do?

Consider using the following solutions:

  • Confirm that you have added your current IP address to the safe list. You can complete this process in the Azure portal by selecting Configure and then adding the current IP.
  • Confirm that the user name you’ve entered on the client side is in the instance name%username format. If you have checked both the preceding points but still can’t connect to the database, contact 21Vianet for technical support.

Why do timeouts frequently occur when I connect to the database?

The timeouts are caused by limitations in Azure Traffic Manager. You should manually set the server parameter in the Azure portal or in Windows PowerShell to any value from 60 to 240 seconds (120 is recommended). For instances that have been created since October, you don’t need to set the server parameter manually. We have already adjusted the default value to 120 seconds with a selectable range of 60-240. (This change only works only on instances created since October.)

Do I have too few concurrent connections for MySQL Database on Azure?

To ensure that connections are used fully and effectively, use connection pooling or persistent connections to connect to the database. For more information, see Connect efficiently to MySQL Database on Azure.

Why is access to MySQL Database on Azure sometimes fast and sometimes slow since I set up connection pooling?

This issue arises because the server configures a timeout mechanism, closing a connection that has been in an idle state for some time to free up resources. For this reason, you need to configure a verification system on the client to better maintain connection pooling and ensure access speeds for MySQL databases. This system is used to verify the effectiveness of connections and ensure that the allocated connections are all effective. For example, for Tomcat JDBC Connection Pool, refer to the TestOnBorrow settings in the JDBC Connection Pool official introduction document.

JDBC connection to MySQL on Azure reports IllegalArgumentException and the error is “URLDecoder: Illegal hex characters in escape (%) pattern - For input string: ...” What should I do?

This occurs because the percent sign (%) in the database user name was not correctly transferred to the connection string URL. Use the DriverManager.getConnection (URL, user name, password) function and type the user name and password in a transfer where the function automatically processes certain special characters. Alternatively, you can perform a manual transfer by replacing the "%" in the URL string with "%25".

Safety issues

I noticed that the database server address is a public endpoint. Does this mean that the access request goes through the internet before it reaches the server if my app visits a database in the same datacenter?

No. The Azure datacenter’s network routing deduces that this is one of its own addresses and directly routes the request to the IP address via the datacenter’s internal network. This way of routing is more secure, so there is less concern about third parties monitoring query requests or results. However, if your app and database are not in the same datacenter, database query requests and results do go through the internet. In this situation, we recommend that you use Secure Sockets Layer (SSL) to ensure the privacy of data transfers. For more information about SSL links, see Safe SSL access to MySQL Database on Azure.

Client compatibility issues

MySQL Database on Azure uses MySQL Community Edition and is compatible with common MySQL management tools. During the actual operation and maintenance of MySQL Database on Azure, you might encounter compatibility issues with some versions of some clients. These issues are summarized in the next two sections.

Connection problems when you use Workbench 6.3.5 to connect to MySQL Database on Azure

By default, Workbench 6.3.5 selects SSL connection and uses “TLS-DHE-RSA-WITH-AES-256-CBC-SHA” for encryption. However, our proxy server does not currently support identification, which causes connectivity problems between MySQL Database on Azure and Workbench 6.3.5. As a workaround, configure the use of SSL certificates by doing the following:

  1. Download the certificate.
  2. In workbench 6.3.5, under SSL CA File, locate the certificate, and then enter AES256-SHA in the SSL Cipher box, as shown in the following figure:

Workbench 6.3.5SSL connection methods

You can also select versions earlier than 6.3.5, which do not have this problem.

Connection problems when you use SQLyog to connect to MySQL Database on Azure

If the user name is longer than 16 characters, this client automatically truncates it to the first 16 characters. This causes connection issues. Use the latest version of the SQLyog client, such as MySQL Workbench.

Master-subordinate replication

I want to change the performance version of the master instance. For example, I might want to go up from MS4 to MS5 or go down from MS6 to MS5. Will the version for subordinate instances be updated as well?

The performance version for subordinate instances will not change with that of the master instance. However, you can change the performance version of subordinate instances separately.

I want to use the master-subordinate replication function, but my database instance is on version 5.5. What should I do?

Master-subordinate replication supports only version 5.6 and later. You can manually upgrade the database implementation to version 5.6 or 5.7 first, and then use the replicate function.

There is sometimes a very high latency between my master and subordinate instances (for example, more than five minutes). What causes this?

High latency between master and subordinate instances can be caused by any of the following situations:

  • If you have not set up a primary key, replication will require a full table scan of the subordinate instance, which can severely affect performance. Individual full table update operations can even cause the subordinate instance to jam.
  • The subordinate instance also needs a lot of time to perform the update when a large amount of data is being updated, which can jam replication and cause delays.
  • If the read-only instance configuration is too low, it will not be possible to perform updates as fast as on the master instance.
  • Large amounts of read operations from the subordinate instance will affect the simultaneous implementation of updates generated on the master instance.

How can I eliminate high latency between the master and subordinate instances?

It is essential to set up a primary key for the database. Not doing so might result in replication slowing down or even jamming up.

Upgrading the performance version of read-only instances can directly increase throughput for read-only instances. This allows updates to be performed more quickly and speeds up the synchronization process, while also enabling rapid responses for read operations.

If a particular query phrase is very time-sensitive, it will fail to meet requirements no matter how much it is shortened, so you can run the query on the master database. However, this process means that you need to specify a particular query phrase to run on a particular instance.

Service layer change issues

How do I downgrade an MP service layer (MP1 or MP2) to an MS service layer (MS1-MS6)?

Because MP service layers use different storage schema to MS service layers, MySQL Database on Azure does not currently support directly downgrading from MP to MS. If you are certain that you need to perform this type of downgrade operation, you can do so by first creating a subordinate instance and then promoting it to a master instance. The specific steps are shown here. A downgrade from MP1 to MS6 is given as an example; the downgrade operation for other service layers is similar.

The names of the resources involved in this example are provided in the following table:

Resource MP1 MS6
Resource group Default-MySQL-ChinaEast Default-MySQL-ChinaEast
Server mysqldemoserver mysqlmsserver
Location ChinaEast ChinaEast
Test database replicadb replicadb

Step 1: Ensure that there is at least one complete backup of the MP1 instance, and that the backup status is Ready. Depending on your settings, this process might take up to one day.

Daily backup ready

Step 2: Execute the PowerShell command to create an MS6 subordinate instance (read-only instance).

New-AzureRmResource -ResourceType "Microsoft.MySql/servers" -ResourceName mysqlmsserver -ApiVersion 2015-09-01 -ResourceGroupName Default-MySql-ChinaEast -Location ChinaEast -SkuObject @{name='MS6'} -Properties @{replicationMode='AzureSlave'; creationSource=@{server='mysqldemoserver';region='chinaEast'}; version = '5.6';}

Step 3: Wait until the latency between the subordinate instance (MS6) and the master instance (MP1) is sufficiently low (for example, less than 60 seconds), and then suspend all the client program’s write operations on the MP1 master instance.

Replica latency

Step 4: Create the replicadb database on the MP1 master instance and wait until this test database has synced to the MS6 subordinate instance. Now the data on the two instances is fully synced.

Replica database synced

Step 5: Promote the MS6 subordinate instance to a master instance. For more information, see Promote subordinate instances.

Finally, you can modify the client program connection string so that it points to the new MS6 instance, and then delete the MP1 instance.

Note

In certain situations, it might be inconvenient to modify the client program connection string. We have provided a process for your reference in which there is no need to modify the client program connection string. The following steps do not require you to modify the client program connection string, but they might temporarily cause downtime for upper layer applications (usually for a few minutes).

Step 6: Create a manual instant backup of the MS6 instance. For more information, see Backup and restore a MySQL Database on Azure.

Step 7: Delete the MP1 instance, and then restore the manual instant backup created in Step 6 to a new MS6 instance with the same name as the original MP1 instance (in this example, mysqldemoserver).

Finally, delete the temporary MS6 instance mysqldemoserver, and then restore the client program’s normal read and write operations on the MS6 instance.

Other issues

Questions about the mysql.exe command-line tool

Command “status” returns the wrong server version number

After the mysql.exe command-line tool gets connected with the MySQL Database on Azure database, if you’re using the command “status” to view the server version, a wrong version number will be returned. For example, if the version of server you created is 5.7, the version returned by the command status is 5.7, as shown in the figure below.

Command “status” returns wrong result

If you’re using the mysql.exe command-line tool to view the server version number, please use the command show variables like '%version%'; (double quotation marks not contained), as shown in the following figure:

show command returns correct result

Common issues with database migration

An error message saying “Access denied; you need (at least one of) the Super privilege(s) for this operation” is reported during the TRIGGER, PROCEDURE, VIEW, FUNCTION, or EVENT import process.

Check to see whether the statement reporting the error uses DEFINER and displays users other than the current user (for example DEFINER=user@host). In this case, MySQL requires Super privileges to execute this statement. MySQL Database on Azure does not provide user Super privileges, a condition that causes an error. To resolve the error, delete DEFINER from the statement and use the default current user. For more information, see Service limitations.

Given that the MySQL Database on Azure portal supports only configuring user read/write privileges for the entire database, will the migration still succeed if my existing database has more detailed user privilege settings?

Yes. Although the Azure portal and Windows PowerShell/REST API supports only setting read/write privileges for the entire database when you create users or databases, you can use the grant command to fine-tune user privilege settings.

How do I upgrade the database from version 5.5 to version 5.6?

We do not currently support one-key upgrades. The way around this is to export the data from the original version 5.5 instance by using mysqldump, create a new version 5.6 database server instance, and then import the data. After it passes compatibility testing, you can migrate the application to the version 5.6 instance.

If your original database is the generation environment or you cannot accept any downtime, you can manually create a snapshot of the original database, restore it to a completely new instance, and then perform the migration and upgrade on the restored instance. This sequence will reduce the impact on the original database.

Problems with emoji support

How do I enable a new database to support emoji icons?

When you create a database on MySQL Database on Azure, the database adopts the UTF8 character set by default unless otherwise specified. Because the UTF8 character set on MySQL supports at most 3-byte encoding, the emoji icons that are encoded by 4 bytes cannot be inserted into the table. To enable a new database to support emoji icons, do the following:

Step 1: Modify the existing database configuration. In the Azure portal, go to the MySQL Database on Azure database page, select the database you want to modify, select Edit character set, select utf8mb4, and then select Confirm.

Change the current database configuration

Step 2: Create a database with character set of utf8mb4. When you go to the database page to create the database, select utf8mb4 as the character set for the database.

Create a database with character set of utf8mb4

How do I enable an existing database to support emoji icons?

Existing databases that don’t use the character set utf8mb4 cannot support emoji icons by directly setting the character set as utf8mb4. However, you can create a new table and then import the data from the original table to this new one to indirectly realize the support to emoji. Do the following (command line omitted):

Step 1: Modify the character set settings for an existing database (as above).

Step 2: Create a new table. We recommend that you use a DBMS tool (for example, Workbench) to create a new table, providing the name of this new table as table_b (the name of original table is table_a). The structure of table_b should be identical to that of table_a.

Step 3: Transfer data in from the original table to the new table. We recommend that you use MySQL statements: INSERT INTO table_b (…) SELECT … FROM table_a;.

Step 4: Original DROP table. We recommend that you use MySQL statements:DROP TABLE table_a;.

Step 5: Retitle the new table with the name of original table. We recommend that you use MySQL statements:RENAME table_b TO table_a;.

The database can now support emoji through a table without changing the application.