Import Database to Azure Database for MySQL or for MariaDB – real case (Got error 1 from storage engine, you need (at least one of) the SUPER privilege(s) for this operation)

Some time ago Microsoft launched Azure Database for MySQL and Azure Database for MariaDB, so we can use these databases as a Platform as a Service. We are not responsible for the operating system, database engine upgrades, security.

If you need to move your workloads to Azure, on you source server simply run a command like this:

mysqldump –single-transaction -u user_name -p database_name > dump.sql

This simply dump your MariaDB or Mysql database to a dump.sql file. After that, you can create Azure Database for MySQL or Azure Database for MariaDB in Azure and connect to it using GUI tool to manage database – MySQL Workbench (https://dev.mysql.com/downloads/workbench/). To download it just please use other downloads section that will not install local MySQL Database server, but just MySQL Workbench.

Just before installing MySQL Workbench please check if you have installed all prerequisites https://dev.mysql.com/resources/workbench_prerequisites.html.

Just after installing GUI please add new MySQL connection to your newly create Azure Database for MySQL or MariaDB.

Usually, To be compatible with your application you will need to disable SSL connection to your database and you must add Ip address that can connect to your database.

After you will be able to connect to the Azure database you are ready to import Database using Server / Data Import option. You have to create a new Default Target Schema.

If something went wrong you will see an error with the line, but usually, you will not receive so much information about this. So better option to import DB is open dump in New Query editor (File / New Query Tab) and load the dump here .

You can just start import by pressing Run .

If you do not create a database before, please do it using this command:

create database database_name;

use database_name;

Use command use database_name; – at first line of your dump, also.

If there is any error importing you will see it on Output pane:

So you can correct your dump and import again.

Here are my simple corrections:

  • Got error 1 from storage engine

CREATE TABLE `clients_log` ( … ) ENGINE=MyISAM AUTO_INCREMENT=12240 DEFAULT CHARSET=utf8    Error Code: 1030. Got error 1 from storage engine

It is just because MyISAM is not supported in Azure Database for MySQL, primarily due to the lack of transaction support which can potentially lead to data loss. This is one of the reasons MySQL switched over to InnoDB as the default.

So you need to replace all ENGINE=MyISAM with simply space (nothing).

  • Access denied; you need (at least one of) the SUPER privilege(s) for this operation

/*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`xxxx_prod`@`localhost` SQL SECURITY DEFINER */    Error Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation

You need to modify DEFINER from DEFINER =` xxxx_prod `@`localhost` to DEFINER =`your_username_just_before@_from_Azure_portal`@`%`

Having DB has several advantages and the most important is Intelligent Performance.