Create table/view without checking if referred table/view exists

Question

I have a MySql Database connected to a VB.Net application. All I want is to create the database automatically using my application. I currently use MySqlBackup.Net available in NuGet Packages to accomplish the task.

The method I use to restore/create the backup is as below:

   Try
            Dim connstring As String = "Server=" & Host & ";Port=" & Port & ";User=" & Username & ";Password=" & Password
            Using mySqlConnection As New MySqlConnection() With {.ConnectionString = connstring}
                If mySqlConnection.State <> ConnectionState.Open Then mySqlConnection.Open()
                Using cmd As MySqlCommand = New MySqlCommand
                    cmd.Connection = mySqlConnection
                    Using mb As MySqlBackup = New MySqlBackup(cmd)                     
                        mb.ImportInfo.TargetDatabase = DatabaseName
                        mb.ImportInfo.IgnoreSqlError = True
                        mb.ImportInfo.DatabaseDefaultCharSet = "latin1"
                        mb.ImportInfo.ErrorLogFile = "err.txt"
                        mb.ImportFromFile(ExportFileName)
                        Dim err = mb.LastError
                        If err Is Nothing Then
                            r.Passed = True
                            r.Result = "New database successfully created!"
                        Else                            
                            r.Passed = False
                            r.Result = "An error occured while trying to initialize database: " & err.ToString
                        End If

                    End Using
                End Using
            End Using
        Catch ex As MysqlException
            r.Passed = False
            r.Result = "Unable to create new database! " & ex.Message
        End Try

The problem now lies when I want to import a file that has been dumped from MySql Workbench or any other program. When I run the exported file from Workbench directly in any DB management software, it completes successfully but raises an error when I import it using my application.

When I export the schema using other software the problem arises when a table/view to be created has a reference to another table/view which hasn't been created yet. I have tried running it as a transaction but not yet successful.

When I import from a file dumped from Workbench I get the following error:

MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5

A quick view of the dumped file is as follows:

-- MySQL dump 10.13  Distrib 8.0.21, for Win64 (x86_64)
--
-- Host: localhost    Database: test_db
-- ------------------------------------------------------
-- Server version   8.0.21

/*!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 */;
/*!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=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Temporary view structure for view `view_accounts_expenses`
--

DROP TABLE IF EXISTS view_accounts_expenses;
/*!50001 DROP VIEW IF EXISTS view_accounts_expenses*/;

For the other software e.g Navicat, the problem is when I create i.e. table_1 and it has a reference to table_2. Having in mind on export/dumping, the tables/views are exported in alphabetical order hence a problem in executing the code.

Answer 1

After all workaround, the only solution I got is to export the schema using MySqlbackup.Net or Navicat then arranging the tables in order of their creation. First created, first to be exported. Then the import now works amazing.