Find missing rows

Jan 1, 2015 at 1:55 PM
Is it possible to find missing rows using MySqlBackup?

We have a cloud application which uses its main database online, but we want to provide a failover database on client level as well.

To maintain data traffic i'd like to find missing rows (eg compare cloud tables with local tables) and use MySqlBackup to insert those missing rows with the same primary key (id).

Both databases will have exactly the same schemas so that's not an issue.

I tried this using DataTables and MySqlDataAdapter, but I couldn't find a way to "override" the primary key (id) on insert. This is important because some tables are like "temporary" tables, which means rows can get removed and thus the primary key wil differ.

I hope this is possible, great lib!
Coordinator
Jan 3, 2015 at 12:22 AM
What we are going to use is
INSERT IGNORE INTO table1(id,name,tel.....)VALUES(1,'JOHN','23784743',.........);
This SQL will be ignored if the key exists.

This function is currently not available in current release.
and I'm doing it.

Hopefully it can done before February.
Coordinator
Jan 25, 2015 at 10:04 AM
Hello, this problem is now resolved with the new release of V2.0.7.

You can use ExportInfo.RowsExportMode = RowsDataExportMode.InsertIgnore

by using InsertIgnore, the query will be skipped if the primary existed and INSERT if it is not existed.

Below is the code example:
string file = "C:\\backup.sql";

using (MySqlConnection conn = new MySqlConnection(textBox1.Text))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        conn.Open();
        cmd.Connection = conn;

        MySqlBackup mb = new MySqlBackup(cmd);
        mb.ExportInfo.RowsExportMode = RowsDataExportMode.InsertIgnore;
        mb.ExportToFile(file);

        conn.Close();
    }
}