Triggers creation

Nov 21, 2014 at 10:47 AM
Dear All Sage Men

I am facing a problem with triggers in the backup file. The problem is weird because it happens random and I cannot reporduce it everytime. Also the symptoms of problem are different: once the triggers for the tables are not created at all, in other case the triggers have their name prefixed with the name of the source database, so during restore process they are being created in source database, not the target one. And yet sometimes triggers are created OK. In all cases I have been testing the same database but on different servers. Do anyone has an ides where the problem can be located?
I am using version 1.5.7.0, vs 2008, vb.net, windows 7 pro.
By the way, I have tried to download the newest upgrade but then I am not able to compile the project because the parameter on New is no more the connection string but the conenction itself despite the documentation. So this way it didn't work either.

I would appreciate very much any assistance
Greetings to all
Robert
Coordinator
Nov 21, 2014 at 11:09 AM
Which version of MySqlBackup.NET are you using?
Nov 21, 2014 at 11:39 AM
I am using version 1.5.7.0, 2013-01-24
Coordinator
Nov 21, 2014 at 9:00 PM
Hi, robsib,

You are strongly recommended to use the latest version.
The latest version is far more stable than the 1.5.7.
Many enhancements and improvements have been made :)
Nov 22, 2014 at 8:47 AM
Hello

Thanx for the prompt answer. But the problem is that I have already tried this solution and it didn't work. Maybe I do something wrong. I have downloaded the latest version, described as 2.0.6. I ahve extracted the MySqlBackup.dll and replaced with it the old one in my project. Unfortunately I am not able to complie it. First of all it lloks like the partameter for NEW() is not anymore the connection string but the MySqlCommand. And then I get several error messages about not defined properties, like the ones below:

Error 84 Too many arguments to 'Public Sub New()'.
Error 85 'FileName' is not a member of 'MySql.Data.MySqlClient.ExportInformations'. 1725 17 PasAdmin
Error 86 'Export' is not a member of 'MySql.Data.MySqlClient.MySqlBackup'. 1726 17 PasAdmin
Error 87 'FileName' is not a member of 'MySql.Data.MySqlClient.ExportInformations'. 1728 77 PasAdmin
Error 88 'DatabaseInfo' is not a member of 'MySql.Data.MySqlClient.MySqlBackup'. 1729 66 PasAdmin
Error 89 'FileName' is not a member of 'MySql.Data.MySqlClient.ImportInformations'. 1734 21 PasAdmin
Error 90 'Import' is not a member of 'MySql.Data.MySqlClient.MySqlBackup'. 1736 25 PasAdmin
Error 91 'FileName' is not a member of 'MySql.Data.MySqlClient.ExportInformations'. 1741 85 PasAdmin
Error 92 'DatabaseInfo' is not a member of 'MySql.Data.MySqlClient.MySqlBackup'. 1742 84 PasAdmin
Error 93 'FileName' is not a member of 'MySql.Data.MySqlClient.ExportInformations'. 1745 73 PasAdmin
Error 94 'DatabaseInfo' is not a member of 'MySql.Data.MySqlClient.MySqlBackup'. 1746 60 PasAdmin
Error 96 Too many arguments to 'Public Sub New()'. 1798 52 PasAdmin
Error 97 'FileName' is not a member of 'MySql.Data.MySqlClient.ExportInformations'. 1799 13 PasAdmin
Error 98 'Export' is not a member of 'MySql.Data.MySqlClient.MySqlBackup'. 1800 13 PasAdmin
Error 99 'FileName' is not a member of 'MySql.Data.MySqlClient.ExportInformations'. 1801 72 PasAdmin
Error 100 'DatabaseInfo' is not a member of 'MySql.Data.MySqlClient.MySqlBackup'. 1802 60 PasAdmin

Any idea what do I do wrong?

Greetings
Robert
Coordinator
Nov 22, 2014 at 9:18 AM
show me your code, how do you export and import database with MySqlBackup.NET.
Nov 22, 2014 at 12:37 PM
Here is my piece of code:


Imports System.IO
Imports System.Xml
Imports MySql.Data.MySqlClient
.
.
.

Region "Backup"

Public Sub PU_SUB_DoBackupRestore(ByVal ParDoBackup As Boolean, ByVal ParSchema As String, ByVal ParCompanyName As String, ByVal ParFolderName As String, ByVal ParFileName As String)
    Dim LO_BOL_OK As Boolean = False
    PUB_STR_FolderCustomDataTransfer = ParCompanyName + Path.DirectorySeparatorChar + PUB_CON_FolderNameDataTransfer + Path.DirectorySeparatorChar
    Dim LO_STR_ConnectionString As String
    LO_STR_ConnectionString = "server=" + MySql_Connection.Host + ";"
    LO_STR_ConnectionString = LO_STR_ConnectionString + "user=" + MySql_Connection.User + ";"
    LO_STR_ConnectionString = LO_STR_ConnectionString + "pwd=" + MySql_Connection.Password + ";"
    LO_STR_ConnectionString = LO_STR_ConnectionString + "database=" + ParSchema + ";"
    Dim LO_STR_FileName As String
    If ParDoBackup Then
        Dim LO_OBJ_SaveFileDialog As New System.Windows.Forms.SaveFileDialog
        With LO_OBJ_SaveFileDialog
            .Title = "Save Backup File"
            .Filter = "Select where to save the backup file (*.sql)|*.sql|" + "All Files" + "(*.*)|*.*"
            Try
                If ParFolderName = "" Then
                    .InitialDirectory = PUB_STR_FolderCustomDataTransfer
                Else
                    .InitialDirectory = ParFolderName
                End If

            Catch ex As Exception
                .InitialDirectory = FileIO.SpecialDirectories.MyDocuments
            End Try
            If .InitialDirectory = "" Then
                .InitialDirectory = FileIO.SpecialDirectories.MyDocuments
            End If
            .FileName = ParSchema + "_company_database_backup_" + System.DateTime.Today.ToShortDateString
            If .ShowDialog() = Windows.Forms.DialogResult.OK Then
                LO_STR_FileName = .FileName
                LO_BOL_OK = True
            End If
        End With
    Else
        Dim LO_OBJ_OpenFileDialog As New OpenFileDialog()
        If ParFolderName = "" Then
            LO_OBJ_OpenFileDialog.InitialDirectory = PUB_STR_FolderCustomDataTransfer
        Else
            LO_OBJ_OpenFileDialog.InitialDirectory = ParFolderName
        End If
        If ParFileName <> "" Then
            LO_OBJ_OpenFileDialog.FileName = ParFileName
        End If
        LO_OBJ_OpenFileDialog.Filter = "Select the valid backup SQL (*.sql)|*.sql|" + "All Files" + "(*.*)|*.*"
        LO_OBJ_OpenFileDialog.FilterIndex = 0
        LO_OBJ_OpenFileDialog.Title = "Locate the SQL File with backup"
        LO_OBJ_OpenFileDialog.RestoreDirectory = True
        If LO_OBJ_OpenFileDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            LO_STR_FileName = LO_OBJ_OpenFileDialog.FileName
            If Not System.IO.File.Exists(LO_STR_FileName) Then
                LO_STR_FileName = ""
            Else
                LO_BOL_OK = True
            End If
        End If

    End If
    If LO_BOL_OK Then
        Dim LO_OBJ_MyBackup As New MySqlBackup(LO_STR_ConnectionString)
        If ParDoBackup Then
            LO_OBJ_MyBackup.ExportInfo.FileName = LO_STR_FileName
            LO_OBJ_MyBackup.Export()
            MessageBox.Show("Backup finished successfully&" + vbCrLf + LO_OBJ_MyBackup.ExportInfo.FileName + vbCrLf + _
                             "&Exported Tables Number : &" + LO_OBJ_MyBackup.DatabaseInfo.Tables.Count.ToString, "Info", _
                             MessageBoxButtons.OK, MessageBoxIcon.Information)
        Else
            If MessageBox.Show("Do you really want to overwrite all existing data with the restored ones?", "Backup Restore Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) = DialogResult.Yes Then
                Dim LO_STR_ErrorMessage As String = ""
                LO_OBJ_MyBackup.ImportInfo.FileName = LO_STR_FileName
                Try
                    LO_OBJ_MyBackup.Import()
                Catch ex As Exception
                    LO_STR_ErrorMessage = ex.Message
                End Try
                If LO_STR_ErrorMessage = "" Then
                    MessageBox.Show("Import finished successfully&" + vbCrLf + LO_OBJ_MyBackup.ExportInfo.FileName + vbCrLf + _
                                               "&Imported Tables Number : &" + LO_OBJ_MyBackup.DatabaseInfo.Tables.Count.ToString, "Info", _
                                               MessageBoxButtons.OK, MessageBoxIcon.Information)
                Else
                    MessageBox.Show("Import finished &" + vbCrLf + LO_OBJ_MyBackup.ExportInfo.FileName + vbCrLf + _
                       "&Imported Tables Number : &" + LO_OBJ_MyBackup.DatabaseInfo.Tables.Count.ToString + vbCrLf + vbCrLf + _
                       "There have been some errors, please verify the data" + vbCrLf + LO_STR_ErrorMessage, "Info", _
                       MessageBoxButtons.OK, MessageBoxIcon.Error)
                End If
            End If
        End If
    End If
End Sub


Public Sub PU_SUB_DoBackupSystemDatabase(ByVal ParSchema As String, ByVal ParFolderName As String)
    Dim LO_BOL_OK As Boolean = False
    Dim LO_STR_ConnectionString As String
    LO_STR_ConnectionString = "server=" + MySql_Connection.Host + ";"
    LO_STR_ConnectionString = LO_STR_ConnectionString + "user=" + MySql_Connection.User + ";"
    LO_STR_ConnectionString = LO_STR_ConnectionString + "pwd=" + MySql_Connection.Password + ";"
    LO_STR_ConnectionString = LO_STR_ConnectionString + "database=" + ParSchema + ";"
    Dim LO_STR_FileName As String
    Dim LO_OBJ_SaveFileDialog As New System.Windows.Forms.SaveFileDialog
    With LO_OBJ_SaveFileDialog
        .Title = "Save Backup File"
        .Filter = "Select where to save the backup file (*.sql)|*.sql|" + "All Files" + "(*.*)|*.*"
        Try
            If ParFolderName = "" Then
                .InitialDirectory = PUB_STR_FolderCustomDataTransfer
            Else
                .InitialDirectory = ParFolderName
            End If

        Catch ex As Exception
            .InitialDirectory = FileIO.SpecialDirectories.MyDocuments
        End Try
        If .InitialDirectory = "" Then
            .InitialDirectory = FileIO.SpecialDirectories.MyDocuments
        End If
        .FileName = ParSchema + "_system_database_backup_" + System.DateTime.Today.ToShortDateString
        If .ShowDialog() = Windows.Forms.DialogResult.OK Then
            LO_STR_FileName = .FileName
            LO_BOL_OK = True
        End If
    End With
    If LO_BOL_OK Then
        Dim LO_OBJ_MyBackup As New MySqlBackup(LO_STR_ConnectionString)
        LO_OBJ_MyBackup.ExportInfo.FileName = LO_STR_FileName
        LO_OBJ_MyBackup.Export()
        MessageBox.Show("Backup finished succesfully&" + vbCrLf + LO_OBJ_MyBackup.ExportInfo.FileName + vbCrLf + _
                        "&Exported Tables Numer : &" + LO_OBJ_MyBackup.DatabaseInfo.Tables.Count.ToString, "Info", _
                         MessageBoxButtons.OK, MessageBoxIcon.Information)
    End If
End Sub

To read the connection string parameters I am using public Public MySql_Connection As MySqlConnection which is coming from Imports Devart.Data.MySql
Coordinator
Nov 23, 2014 at 6:02 AM
Edited Nov 23, 2014 at 6:16 AM
here is the simplified code by using the latest version of MySqlBackup.NET v2.0.6
Sub DoBackupAndRestore()
    Dim ParFile As String = "D:\dump.sql"
    Dim ConString As String = "server=localhost;user=root;pwd=1234;database=test;"
    Backup(ConString, ParFile)
    MessageBox.Show("backup completed.")
    Restore(ConString, ParFile)
    MessageBox.Show("restore completed.")
End Sub

Sub Backup(ByVal ParConnectionString As String, ByVal ParFile As String)
    Dim conn As New MySqlConnection(ParConnectionString)
    Try
        conn.Open()
        Dim cmd As New MySqlCommand()
        cmd.Connection = conn
        Dim mb As New MySqlBackup(cmd)
        mb.ExportToFile(ParFile)
    Catch ex As Exception
        conn.Close()
        MessageBox.Show(ex.ToString())
    End Try
    conn.Close()
End Sub

Sub Restore(ByVal ParConnectionString As String, ByVal ParFile As String)
    Dim conn As New MySqlConnection(ParConnectionString)
    Try
        conn.Open()
        Dim cmd As New MySqlCommand()
        cmd.Connection = conn
        Dim mb As New MySqlBackup(cmd)
        mb.ImportFromFile(ParFile)
    Catch ex As Exception
        conn.Close()
        MessageBox.Show(ex.ToString())
    End Try
    conn.Close()
End Sub
Marked as answer by robsib on 11/23/2014 at 5:23 AM
Nov 23, 2014 at 12:22 PM
Thanx for your prompt answer and the code example. I have implemennted the code and, having included MySqlData.dll version 6.9.5.0 it started to work. However while restoring the database I got an error "packets larger than max_allowed_packet are not allowed". I have found your issue https://mysqlbackupnet.codeplex.com/discussions/405917 where you say that this problem has been solved, but despite the fact that I have downloaded the latest version it still occurs. I have implemeneted the suggested solution "SET GLOBAL max_allowed_packet = 102410241024;" and it works fine now.

Thanx for help
Robert
Coordinator
Nov 23, 2014 at 11:10 PM
Edited Nov 23, 2014 at 11:23 PM
You are welcome :)

In the new version, I have dropped the function of
SET GLOBAL max_allowed_packet = 1024*1024*1024";
This is because to make this command to have effect, the connection is forced to close and reopen.
Not every programmer wants to close it automatically as developers might do something to the connection before starting the backup or restore process. Therefore, closing and reopening the connection is not recommended to be done automatically and internally.

The internal process of MySqlBackup.NET should not interrupt the connection state.

For more info about max_allowed_packet, you may read more at the Documentation - FAQ - Section 5. Error - Packets larger than max_allowed_packet are not allowed.