This project has moved. For the latest updates, please go here.
2
Vote

Problem when Importing Views

description

I tried to backup a database and then to restore it.
During restoring process, I have an error during the view importing process that states that another view is not existing.
Looking in the db it seems that, while in the importing table process the dll considers the priority of the tables, during the importing process of the viewes it thake it from the first to the last, without considering nested views.

comments

lanit wrote Jan 4, 2016 at 7:24 AM

I have same error

adriancs wrote Jan 7, 2016 at 1:37 AM

Hi, this problem has been reported before.
but, I currently don't have the idea of how to sort the VIEW in the correct order where they depend on each other.
I need to spend some time to study it.

adriancs wrote Jan 9, 2016 at 9:23 AM

Can you please try it by using MySqlDump.exe?

If MySqlDump.exe is able to sort the VIEW in correct order, this means within MySQL server itself there is a way to figure out the dependencies among the VIEWs themselves.

stigbuhl wrote Jan 21, 2016 at 9:00 AM

After backup of my database.
It looks almost perfectly, but can I get a "CREATE TABLE IF NOT EXISTS" before the views like:
Mysql WorkBench do it like this:
CREATE TABLE IF NOT EXISTS
CREATE TABLE IF NOT EXISTS `calendarviewfordata` (......
CREATE TABLE IF NOT EXISTS `calendarviewforsearch` (......
......

-- 
-- Dumping views
-- 
DROP TABLE IF EXISTS `calendarviewfordata`;
DROP VIEW IF EXISTS `calendarviewfordata`;
CREATE ALGORITHM=MERGE SQL SECURITY DEFINER VIEW `calendarviewfordata` AS.....

DROP TABLE IF EXISTS `calendarviewforsearch`;
DROP VIEW IF EXISTS `calendarviewforsearch`;
CREATE ALGORITHM=MERGE SQL SECURITY DEFINER VIEW `calendarviewforsearch` AS.....
.......
I refer to different views in the first calendarviewfordata.

stigbuhl wrote Jan 21, 2016 at 10:48 AM

Hallo again
I have made a small fix there put Create table in.
public static BackupDatabase (string constring, string fileDestination)
{
        using (var conn = new MySqlConnection(constring))
                {
                    using (var cmd = new MySqlCommand())
                    {
                        using (var mb = new MySqlBackup(cmd))
                        {
                            cmd.Connection = conn;
                            conn.Open();
                            mb.ExportToFile(fileDestination);
                            conn.Close();
                        }
                    }
                }
        SortViews(tempFileDestination);
}


public static void SortViews(string fileDestination)
        {
            var fileContents = System.IO.File.ReadAllText(fileDestination);

            var newText = new List<string>();
            fileContents = fileContents.Replace("-- Dumping views", "-- Dumping views" + Environment.NewLine + @"-- SortViews CREATE TABLE IF NOT EXISTS");
            var f = fileContents.Split(';');
            var views = f.Where(y => y.Contains("SQL SECURITY DEFINER"));
            foreach (var v in views)
            {
                var s = v.Split('`');
                var tableName = s[1];
                var count = 0;
                var createTableView = @"
                CREATE TABLE IF NOT EXISTS `" + tableName + @"` (";
                var komma = "";
                foreach (var collumName in s)
                {
                    if (collumName == " AS ")
                    {
                        var name = s[count + 1];
                        createTableView += komma + "`" + name + "` INT";
                        komma = ", ";
                    }

                    count++;
                }
                createTableView += ");";
                newText.Add(createTableView);

            }
            if (newText.Count() != 0)
            {
                fileContents = fileContents.Replace("-- SortViews CREATE TABLE IF NOT EXISTS", "-- SortViews CREATE TABLE IF NOT EXISTS" + Environment.NewLine + Environment.NewLine + string.Join(Environment.NewLine, newText));
            }
            System.IO.File.WriteAllText(fileDestination, fileContents);
            
        }

netclick wrote Mar 20, 2016 at 3:13 PM

Hi,

is this problem solved?

I've the same issue. When a view depends on another view it gives an error.

Kind regards.

netclick wrote Mar 20, 2016 at 5:32 PM

I've changed the solution of stigbuhl to work like the export of Mysql Workbench.
Remove the
    /// <summary>
    /// .
    /// </summary>
    /// <param name="fileDestination"></param>
    public bool SortViews(string fileDestination)
    {
        try
        {
            var stringtempview = "";

            //Apro la connessione
            using (MySqlConnection conn = new MySqlConnection(ConnectionStringBuilder.GetConnectionString(true)))
            {
                List<String> tablenames = new List<String>();

                //Preparo il comando
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    //Associo la connessione al comando da eseguire.
                    cmd.Connection = conn;

                    //Apro la connessione
                    conn.Open();

                    //Cambio l'SQL mode per gestire un problema con gli apici.
                    cmd.CommandText = "SHOW FULL TABLES IN " + ConfigurationManager.AppSettings["DatabaseName"]  + " WHERE TABLE_TYPE LIKE 'VIEW';";
                    var reader = cmd.ExecuteReader();

                    //Recupero il nome delle viste nello schema.
                    while (reader.Read())
                    {
                        tablenames.Add(reader.GetString(0));
                    }

                    //Chiudo il reader.
                    reader.Close();

                    //Itero le viste trovate per ricercare le colonne.
                    foreach (var tablename in tablenames)
                    {
                        stringtempview += "--" + Environment.NewLine;
                        stringtempview += "-- Temporary view structure for view `" + tablename + "`" + Environment.NewLine;
                        stringtempview += "--" + Environment.NewLine + Environment.NewLine;

                        stringtempview += "DROP TABLE IF EXISTS `" + tablename + "`;" + Environment.NewLine + Environment.NewLine;
                        stringtempview += "DROP VIEW IF EXISTS `" + tablename + "`;" + Environment.NewLine + Environment.NewLine;
                        stringtempview += "CREATE VIEW `" + tablename + "` AS SELECT " + Environment.NewLine;

                        cmd.CommandText = "SHOW COLUMNS FROM `" + ConfigurationManager.AppSettings["DatabaseName"]  + "`.`" + tablename + "`;";
                        reader = cmd.ExecuteReader();

                        List<String> columns = new List<String>();

                        //Itero le colonne.
                        while (reader.Read())
                        {
                            columns.Add(reader.GetString(0));
                        }

                        var counter = 1;
                        foreach (var column in columns)
                        {
                            stringtempview += "1 AS `" + column + "`";

                            if (counter < columns.Count)
                            {
                                stringtempview += ", " + Environment.NewLine;
                            }
                            else
                            {
                                stringtempview += "; " + Environment.NewLine;
                            }

                            counter++;
                        }

                        stringtempview += Environment.NewLine + Environment.NewLine;

                        //Chiudo il reader.
                        reader.Close();
                    }

                    //Chiudo il reader.
                    reader.Close();

                    //Chiudo la connessione
                    conn.Close();
                }
            }

            //Leggo il file generato.
            var fileContents = File.ReadAllText(fileDestination);
            fileContents = fileContents.Replace("-- Dumping views", "-- Dumping views" + Environment.NewLine + @"-- SortViews CREATE TABLE IF NOT EXISTS");

            //Aggiungo la parte nuova.
            if (!String.IsNullOrEmpty(stringtempview))
            {
                fileContents = fileContents.Replace("-- SortViews CREATE TABLE IF NOT EXISTS", "-- SortViews CREATE TABLE IF NOT EXISTS" + Environment.NewLine + Environment.NewLine + stringtempview);
            }

            File.WriteAllText(fileDestination, fileContents);

            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }

adriancs wrote Mar 21, 2016 at 6:16 AM

Ok, I'll have a on this on this weekend.

ryanframes wrote Jun 23, 2016 at 6:14 AM

is this bug fixed ?
bc i still have this problem , i downloaded from Nuget Package in VS 2010,
there's something weird, when i check it in References it says packages\MysqlBackUp.Net.2.0.9.4\lib\Net40\MySqlBackup.dll but the Version is still 2.0.9.2...

ryanframes wrote Jun 23, 2016 at 6:22 AM

oh, nvm, it's fixed,,