#MySQL Database on Azure FAQ

• Service consulting
• Connection issues
• Security consulting
• Client compatibility issues
• Master-Slave Replication FAQ
• Service layer change issues
• Other issues

##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, seeservice limitations of MySQL Database on Azure.

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

The product R&D team studied and analyzed this issue on several occasions, but ultimately decided not to support it. The reasons include the following:

1. MyISAM has flaws in terms of data integrity protection, and these flaws could cause database data to be damaged or even lost. Moreover, because these flaws are primarily due to design faults, there is no way to repair them without breaking compatibility.
2. Because the MyISAM input/output (I/O) operations are not the optimal solution for storage on Azure, MyISAM offers little performance advantage over InnoDB.
3. MyISAM requires significant manual repair work if data are damaged, and it cannot be adapted to a platform as a service (PaaS) operating model.
4. The cost of migrating from MyISAM to InnoDB is minimal. In most applications, migration requires only changes to the table creation code.
5. The development of MySQL is also moving towards 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 used for the database larger than the amount of storage space 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 will not change during usage.

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

Yes. While the Management Portal and PowerShell command line only support setting 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 set to in MySQL Database on Azure? How can I change it?

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

What functions does MySQL 5.7 provide? Can you use all these functions on MySQL Database on Azure?

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

• MySQL Database on Azure does not currently support the replication function improvements in version 5.7. This is because MySQL Database on Azure’s 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 to create MySQL 5.7 instances on the management portal?

Click on New in the menu on the left of the Management Portal, click on Database > , click on MySQL Databases on Azure, press Create, and then select 5.7 in the MySQL version section.

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 the compatibility between application client and database is tested. In case of 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 upgrading, use the database restore function to restore the existing database to a new instance. See “Backup and restore of MySQL Database on Azure” for details. We suggest that you to 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 with 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 Management Portal: Click on New in the menu on the left of the Management Portal, perform a quick create, and select version 5.7 in the MySQL version section. 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 ”. After the preceding steps are completed, 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 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, it can be accomplished by first creating a subordinate instance and then promoting it to a master instance. The specific 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 given in the table below:

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.

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 of the client program’s write operations on the MP1 master instance.

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.

Step 5: Promote the MS6 subordinate instance to a master instance. (Refer to: 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.

Step 6: Create a manual instant backup of the MS6 instance. (See: Backup and restore a MySQL Database on Azure)

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

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?

Here are solutions to some common causes:

1. 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.
2. Confirm that the user name you’ve entered on the client side is in the instance name%username format. If you have checked both of the preceding points above 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 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 used, 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 is 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’s URL. Use the DriverManager.getConnection (URL, user name, password) function and type the user name and password into 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 below:

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 Databse 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:

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, sich 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?

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

I want to use the master-suborindate 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:

1. 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.
2. 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.
3. If the read-only instance configuration is too low, it will not be possible to perform updates as fast as on the master instance.
4. Large amounts of read operations from the suborindate 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?

1. 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.
2. 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.
3. 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, it can be accomplished by first creating a subordinate instance and then promoting it to a master instance. The specific 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 given in the table below:

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.

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 of the client program’s write operations on the MP1 master instance.

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.

Step 5: Promote the MS6 subordinate instance to a master instance. (Refer to: 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.

Step 6: Create a manual instant backup of the MS6 instance. (See: Backup and restore a MySQL Database on Azure)

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

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. If 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 figure below.

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 (see Service limitations), causing an error. To resolve this error, delete DEFINER from the statement and use the default current user.

###Given that then 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? A: 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. In order to enable a new database to support Emoji icons, do the following:

1. Modify the existing database configuration

In the Management Portal, go to the MySQL Database on Azure database page, select the database you wish to modify, click on Edit character set, select utf8mb4, then press Confirm.

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.

###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):

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

2. Create a new table

We recommend that you use a DBMS tool (for examplem, 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.

4. Transfer data in original table to the new table

We recommend that you use MySQL statements:INSERT INTO table_b (…) SELECT … FROM table_a;

5. Original DROP table

We recommend that you use MySQL statements:DROP TABLE table_a;

6. 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 nowsupport Emoji through a table without changing the application.