First published on MSDN on Jun 08, 2017
Microsoft has recently released the preview of
Azure Database for MySQL
. We noticed that customers who are migrating from other MySQL servers have been facing the error:
"Got error 1 from storage engine"
While indicated to be a storage capacity issue on a MySQL server when researching this error, in Azure Database for MySQL this error is most commonly seen when the MyISAM
storage engine
is being used. Currently, MyISAM is not supported on Azure Database for MySQL. In this scenario, you will need to modify the tables to utilize a supported engine such as InnoDB. This is the engine that Azure Database for MySQL uses by default.
This article
Converting MyISAM to InnoDB
will help you understand the implications of changing from MyISAM and the suggested conversion steps to InnoDB. The most common method is to alter the existing table to utilize the InnoDB engine:
[code language="sql"]ALTER TABLE table_name ENGINE=InnoDB;[/code]
You can identify the tables in your database using MyISAM with this query:
[code language="sql"]SELECT TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'dbname' AND engine = 'MyISAM';[/code]
After converted, you will need to create a new dump of the database and try the import again.
If you already have the dump of the database or are unable to make changes to the source database, another approach is by editing the dump file with your favorite editor. You will want to find all instances of MyISAM in the dump file and replace with InnoDB. An example using vi in a Unix environment:
:%s/MyISAM/InnoDB/gc
This will allow a global search and confirmation of each change (drop the c to apply it for all instances without confirming each change).
With the edited dump file you will now be able to import without the previously observed error. In both methods, it is recommended to test this change before implementing into production.
In regards to support for MyISAM, see the
comment
from JasonH@MSFT in May 2017, at the end of article “What is Azure Database for MySQL? Service Introduction”:
“MyISAM support has been asked for several times, and has currently been denied in the feature feedback here because of lack of data consistency (think ACID principles):
https://feedback.azure.com/forums/597982-azure-database-for-mysql/suggestions/19271050-add-...
MyISAM has other limitations that don't make it right for this kind of service right now:
https://dba.stackexchange.com/questions/1/what-are-the-main-differences-between-innodb-and-...
“
