Using in the Azure Database for MySQL server
Got the error 1 when I tried to migrate the database
ERROR 1030 (HY000) at line Got error 1 from storage engine
Why?
https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/06/08/azure-database-for-mysql-cant-restore-database-with-error-got-error-1-from-storage-engine/
"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:
1
|
ALTER TABLE table_name ENGINE=InnoDB;
|
You can identify the tables in your database using MyISAM with this query:
1
2
|
SELECT TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'dbname' AND engine = 'MyISAM';
|
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”:
Repair. MySQL Storage Engine – How to Convert MyISAM to InnoDB
https://kinsta.com/knowledgebase/convert-myisam-to-innodb/
Step 2
Do a quick scan or sort of the “Type” column and you can see which Storage Engine types your tables are using. In this example below, you can see that two of the tables are still using MyISAM.
Find MyISAM tables
Alternatively, you could run a query to see if any myISAM tables exist. Replace ‘database’ with your database name.
SELECT TABLE_NAME,
ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database' and ENGINE = 'myISAM'
Convert MyISAM to InnoDB with phpMyAdminYou can convert MyISAM to InnoDB fairly easily. This example is below is using the wp_comments table. Simply run the ALTER command to convert it to InnoDB storage engine. Note: We always recommend backing up your MySQL database before running any operations on it.
ALTER TABLE wp_comments ENGINE=InnoDB;
Ensure you are running MySQL 5.6.4 or higher otherwise, you might run into issues where full-text indexing is not supported yet by InnoDB. If you are a Kinsta client you don’t need to worry about this.
Alternatively, you can also convert them manually with phpMyAdmin. Simply click on the myISAM table, click into the “Operations” tab, and change the storage engine.
Convert MyISAM to InnoDB
