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.
