Migrating Wordpress from DreamHost to Azure Wordpress Resource via UpdraftPlus "wp_options table does not exist..." error

Question

Goal
Our goal is to migrate a wordpress site from DreamHost to an Azure Wordpress resource.

Process and Errors
We installed UpdraftPlus (pro version) on both builds. We utilized it's Send a backup to another site -> Recieve a backup from a remote site feature. During the database restoration it failed and gave the following error wp_options table does not exists....

We ended up downloading the database back up file from UpdraftPlus. The top portion looked like this:

# WordPress MySQL database backup
# Created by UpdraftPlus version 2.16.27.24 (https://updraftplus.com)
# WordPress Version: 5.5, running on PHP 7.4.2 (Apache), MySQL 5.7.28-log
# Backup of: http://example.com
# Home URL: http://example.com
# Content URL: http://example.com/wp-content
# Uploads URL: http://example.com/wp-content/uploads
# Table prefix: wp_
# Filtered table prefix: wp_
# Site info: multisite=0
# Site info: end

# Generated: Tuesday 25. August 2020 19:52 UTC
# Hostname: mysql.example.com
# Database: `mydatabasename_com`
# --------------------------------------------------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40101 SET foreign_key_checks = 0 */;

# Table: `wp_options`
# Approximate rows expected in table: 551

# Delete any existing table `wp_options`

DROP TABLE IF EXISTS `wp_options`;

# Table structure of table `wp_options`

CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `option_value` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `autoload` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`),
  KEY `autoload` (`autoload`)
) ENGINE=MyISAM AUTO_INCREMENT=595049 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

# Data contents of table `wp_options`
 
INSERT INTO `wp_options` VALUES 
  (1, 'siteurl', 'http://example.com', 'yes'),
  (2, 'home', 'http://example.com', 'yes'),
  (3, 'blogname', 'My Blog Name', 'yes'),
...

We took that script and used Workbench to query the Azure MySQL database. Unfortunately it came up with the same error: wp_options table does not exists....

The first command in the backup query script is to drop the current wp_options table (if it exists). The the following command is a create query that recreates the the wp_options table. This command silently fails. The next command, which inserts values into the wp_options table, is the one causing the error shown. There's no wp_options table for it to insert in to.

Summary and Question
Currently the MySQL create query is failing and causing an error. How do we transfer the database successfully?

Answer 1

We downloaded a database back up via UpdraftPlus from the Azure Wordpress. And the problem became pretty clear. Take a look at the Storage Engine and Collation of the create query:

CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(191) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `option_value` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `autoload` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`),
  KEY `autoload` (`autoload`)
) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=214 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

On DreamHost, we edited every database table to have the same storage engine and collation as the Azure MySQL database tables. We just opened up good old phpMyAdmin and adjusted each table via table options, like so:

Just to be clear that's changing:
Storage Engine:
MyISAM -> InnoDB
Collation:
utf8mb4_unicode_ci -> utf8mb4_unicode_520_ci

We then took a database export directly from phpMyAdmin and ran it through Workbench. Viola! The whole database transferred succesfully.

Actual Problem
I'm not sure exactly why this wouldn't work initially, but... My speculation is the Azure MySQL database is in a Windows environment and the DreamHost MySQL database is in a Linux environment. So somewhere there are probably database configurations that don't line up.