Azure MySQL Single Server database import failure

Question

Hi,

So we are currently in process of upgrading our JIRA MySQL databases from 5.7 to 8.0 and I finally was able to figure out how to export/import data via Workbench/manually/mysqldump/mysqlpump no problem, but I am unable to simply import a database into the newly created MySQL server database 8.0 no matter what I try...

Today I deleted and re-created the MySQL Server 8.0 database, create a simple database "guest", created a simple table "test", exported it via various conditions:

• Export to Dump Project Folder | EVERYTHING UNCHECKED, Dump Structure and Data / Data Only / Structure Only (tried every option), then Import via the same settings
• Export to Dump Project Folder | EVERYTHING CHECKED, Dump Structure and Data / Data Only / Structure Only (tried every option), then Import via the same settings
  I get:
  20:42:55 Restoring guest (test)
  Running: "C:\Program Files\MySQL\MySQL Workbench 8.0\mysql.exe" --defaults-file="C:\Users\MYLOCALUSERNAMEACCOUNT\AppData\Local\Temp\2\tmp39x1bevs.cnf" --protocol=tcp --host=DBONAZURE.mysql.database.azure.com --user=DBADMINACCOUNTONAZURE@DBONAZURE --port=3306 --default-character-set=utf8 --comments --database=guest < "C:\Users\MYLOCALUSERNAMEACCOUNT\Desktop\Dumps\ByFolder\newserv (2)\guest_test.sql"
  Operation failed with exitcode 1
  20:42:55 Import of C:\Users\akamenevadmin\Desktop\Dumps\ByFolder\newserv (2) has finished with 1 errors

When I am trying to do it via the mysqlpump (which is working on our current mysql 5.7 server, I was successfully to export data, delete it on the server, re-import and everything is working) and EXPORT is working fine, but IMPORT shows that it is complete in like a second or two, but nothing is imported on the server.... no errors
This is the result I get from the command line mysqlpump import attempt:

mysqlpump --host=DBONAZURE.mysql.database.azure.com --port=3306 --default-character-set=utf8 --user=DBADMINACCOUNTONAZURE@DBONAZURE --password=DBADMINPASSWORD --protocol=tcp --databases confluence < C:\Users\MYLOCALUSERNAMEACCOUNT\Desktop\Dumps\1-by-1\confluenceDEV2.sql

mysqlpump: [Warning] Using a password on the command line interface can be insecure.
-- Dump created by MySQL pump utility, version: 8.0.30, Win64 (x86_64)
-- Dump start time: Mon Aug 1 19:06:20 2022
-- Server version: 5.6.47

SET @OLD_UNIQUE_CHECKS=@@unique _CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@alenzi _MODE;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET @@SESSION.SQL_LOG_BIN= 0;
SET @OLD_TIME_ZONE=@@TIME _ZONE;
SET TIME_ZONE='+00:00';
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8mb3;
CREATE DATABASE /!32312 IF NOT EXISTS/ confluence /*!40100 DEFAULT CHARACTER SET latin1 */;
SET TIME_ZONE=@OLD_TIME_ZONE;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET SQL_MODE=@OLD_SQL_MODE;
-- Dump end time: Mon Aug 1 19:06:20 2022
Dump completed in 163

I had issues using mysqldump on the old server, but mysqlpump worked fine. I still decided to try the mysqldump on the new server just in case and have a similar results, export seem to be working, but import shows no errors, but no data is imported... even if after export, I do not remove the database, but remove the "test" table, after import it does not show that table...
mysqldump --host=DBONAZURE.mysql.database.azure.com --port=3306 --default-character-set=utf8 --user=DBADMINACCOUNTONAZURE@DBONAZURE --password=DBADMINPASSWORD --protocol=tcp --column-statistics=FALSE --routines --events --no-create-info=TRUE --skip-triggers guest < C:\Users\MYLOCALUSERNAMEACCOUNT\Desktop\Dumps\1-by-1\guest-dv03-reimport.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.
-- MySQL dump 10.13 Distrib 8.0.30, for Win64 (x86_64)
--
-- Host: DBONAZURE.mysql.database.azure.com Database: guest
-- ------------------------------------------------------
-- Server version 5.6.47.0

/*!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 /;
/!50503 SET NAMES utf8 /;
/!40103 SET @OLD_TIME_ZONE=@@TIME _ZONE /;
/!40103 SET TIME_ZONE='+00:00' /;
/!40014 SET @OLD_UNIQUE_CHECKS=@@unique _CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@alenzi _MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /;
/!40111 SET @OLD_SQL_NOTES=@@alenzi _NOTES, SQL_NOTES=0 */;

--
-- Dumping events for database 'guest'
--

--
-- Dumping routines for database 'guest'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /;
/!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-08-01 20:59:09

So I have no idea what else to try here and why a simply export/import dump of a nearly empty database with just 1 empty table is not working... Appreciate any assistance on this!

Answer 1

This has been resolved, MySQL Workbench had to be launched via Run as administrator lol... there were a bunch of other issues as well, but that's a different story.