Import views issues when restore

[ikari84]

Hi, first of all, thank you very much for your hard work. I have been using the tool and its amazing.

But I have issues, when restore database with views by dependency errors (view does not exist). I fixed it adding code for create temporary tables as views (before to export views). I work it in version 2.0.9.2

I trying it in the new version, and it have the same issues to import views.

PD: Sorry for my bad english XD.
PD2: I attach the source code
MySqlBackup.zip

[adriancs2]

Hi, I have downloaded the code that you uploaded. I can see that the following 2 new blocks of code are added before export view to avoid the error of dependency views.

void Export_Tables_View()
{
	StringBuilder _sqlCreateTemporary;
	if (!ExportInfo.ExportViews || _database.Views.Count == 0)
		return;
	Export_WriteComment("");
	Export_WriteComment("Dumping tables temporary");
	Export_WriteComment("");
	textWriter.WriteLine();
	foreach (MySqlView view in _database.Views)
	{
		if (stopProcess)
			return;
		_sqlCreateTemporary = new StringBuilder();
		//_sqlCreateTemporary.AppendFormat("DROP TABLE IF EXISTS `{0}`;",view.Name);
		_sqlCreateTemporary.AppendFormat("SHOW CREATE TABLE `{0}`;", view.Name);

		QueryExpress.ExecuteScalarStr(Command, string.Format("DROP TABLE IF EXISTS `{0}`;", view.Name));
		QueryExpress.ExecuteScalarStr(Command, string.Format("CREATE TEMPORARY TABLE `{0}` AS SELECT * FROM `{0}` LIMIT 0,1;", view.Name));
		System.Data.DataTable dt = QueryExpress.GetTable(Command, _sqlCreateTemporary.ToString());
		foreach(System.Data.DataRow tmp in dt.Rows)
		{
			Export_WriteComment(string.Format("Dumping structure for {0}",view.Name));
			Export_WriteComment("Creating temporary table to overcome VIEW dependency errors");
			Export_WriteComment("");
			Export_WriteLine(ReFormatTableView(tmp["Create Table"].ToString()) + "; ");
			textWriter.WriteLine();
		}
		//QueryExpress.ExecuteScalarStr(Command, string.Format("DROP TABLE IF EXISTS `{0}`;",view.Name));
	}
}

string ReFormatTableView(string cText)
{
	cText = cText.Replace(" CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,", ",");
	cText = cText.Replace(" CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL", "");
	cText = cText.Replace(" COLLATE utf8_spanish_ci DEFAULT NULL,", ",");
	cText = cText.Replace(" COLLATE utf8_spanish_ci DEFAULT NULL", "");
	cText = cText.Replace(" unsigned NOT NULL DEFAULT 0,", ",");
	cText = cText.Replace(" unsigned NOT NULL DEFAULT 0", "");
	cText = cText.Replace(" unsigned NOT NULL,", ",");
	cText = cText.Replace(" unsigned NOT NULL", "");            
	cText = cText.Replace(" NOT NULL DEFAULT 0.0000,", ",");
	cText = cText.Replace(" NOT NULL DEFAULT 0.0000", "");
	cText = cText.Replace(" NOT NULL DEFAULT 0.00,", ",");
	cText = cText.Replace(" NOT NULL DEFAULT 0.00", "");
	cText = cText.Replace(" COLLATE utf8_spanish_ci NOT NULL,", ",");
	cText = cText.Replace(" COLLATE utf8_spanish_ci NOT NULL", "");
	cText = cText.Replace(" CHARACTER SET utf8mb4 NOT NULL DEFAULT '',", ",");
	cText = cText.Replace(" CHARACTER SET utf8mb4 NOT NULL DEFAULT ''", "");
	cText = cText.Replace(" CHARACTER SET utf8mb4 DEFAULT NULL,", ",");
	cText = cText.Replace(" CHARACTER SET utf8mb4 DEFAULT NULL", "");
	cText = cText.Replace(" NOT NULL DEFAULT 0,", ",");
	cText = cText.Replace(" NOT NULL DEFAULT 0", "");
	cText = cText.Replace(" DEFAULT NULL,", ",");
	cText = cText.Replace(" DEFAULT NULL", "");
	cText = cText.Replace("ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_spanish_ci", "ENGINE=MyISAM");
	cText = cText.Replace("ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci", "ENGINE=MyISAM");
	cText = cText.Replace(" COLLATE utf8_spanish_ci,", ",");
	cText = cText.Replace(" COLLATE utf8_spanish_ci", "");
	cText = cText.Replace(" unsigned,", ",");
	cText = cText.Replace(" unsigned", "");
	cText = cText.Replace(" NOT NULL,", ",");
	cText = cText.Replace(" NOT NULL", "");
	cText = cText.Replace(" CHARACTER SET utf8,", ",");
	cText = cText.Replace(" CHARACTER SET utf8", "");
	cText = cText.Replace(" TEMPORARY", "");
	cText = cText.Replace(")mb4,", "),");
	cText = cText.Replace(")mb4", ")");
	return cText;
}

Can you provide examples that will repeat the view dependency error?
Thanks

[ikari84]

Hi bro, sorry for late. I leave an example, showing these dependencies. It should be noted that the ReFormatTableView routine, I use to format the declarations of the fields because it throws me an error when importing them, since this one appears in MariaDB 10.2.16
TestDump 2018-07-14 114544.zip

[ikari84]

Hi, bro I changed the code by this:

        void Export_Tables_View()
        {
            //StringBuilder _sqlCreateTemporary;
            if (!ExportInfo.ExportViews || _database.Views.Count == 0)
                return;
            Export_WriteComment("");
            Export_WriteComment("Dumping tables temporary");
            Export_WriteComment("");
            textWriter.WriteLine();
            foreach (MySqlView view in _database.Views)
            {
                if (stopProcess)
                    return;
                
                    Export_WriteComment(string.Format("Dumping structure for {0}", view.Name));
                    Export_WriteComment("Creating temporary table to overcome VIEW dependency errors");
                    Export_WriteComment("");
                    Export_WriteLine(ReFormatTableView(view.Name));
                    textWriter.WriteLine();
            }
        }

So:

 string ReFormatTableView(string cViewName)
        {
            StringBuilder cText = new StringBuilder();
            System.Data.DataTable hColumns = QueryExpress.GetTable(Command, string.Format("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='{0}' AND TABLE_SCHEMA='{1}'", cViewName,_database.Name));
            cText.AppendFormat("CREATE TABLE `{0}` (\r\n",cViewName);
            foreach(System.Data.DataRow rcol in hColumns.Rows)
            {
                cText.AppendFormat("`{0}` {1},\r\n", rcol["COLUMN_NAME"].ToString(), rcol["COLUMN_TYPE"].ToString());
            }
            cText.AppendLine(") ENGINE=MyISAM;");
            cText = cText.Replace(" CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,", ",");
            cText = cText.Replace(" CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL", "");
            cText = cText.Replace(" COLLATE utf8_spanish_ci DEFAULT NULL,", ",");
            cText = cText.Replace(" COLLATE utf8_spanish_ci DEFAULT NULL", "");
            cText = cText.Replace(" unsigned NOT NULL DEFAULT 0,", ",");
            cText = cText.Replace(" unsigned NOT NULL DEFAULT 0", "");
            cText = cText.Replace(" unsigned NOT NULL,", ",");
            cText = cText.Replace(" unsigned NOT NULL", "");
            cText = cText.Replace(" NOT NULL DEFAULT 0.0000,", ",");
            cText = cText.Replace(" NOT NULL DEFAULT 0.0000", "");
            cText = cText.Replace(" NOT NULL DEFAULT 0.00,", ",");
            cText = cText.Replace(" NOT NULL DEFAULT 0.00", "");
            cText = cText.Replace(" COLLATE utf8_spanish_ci NOT NULL,", ",");
            cText = cText.Replace(" COLLATE utf8_spanish_ci NOT NULL", "");
            cText = cText.Replace(" CHARACTER SET utf8mb4 NOT NULL DEFAULT '',", ",");
            cText = cText.Replace(" CHARACTER SET utf8mb4 NOT NULL DEFAULT ''", "");
            cText = cText.Replace(" CHARACTER SET utf8mb4 DEFAULT NULL,", ",");
            cText = cText.Replace(" CHARACTER SET utf8mb4 DEFAULT NULL", "");
            cText = cText.Replace(" NOT NULL DEFAULT 0,", ",");
            cText = cText.Replace(" NOT NULL DEFAULT 0", "");
            cText = cText.Replace(" DEFAULT NULL,", ",");
            cText = cText.Replace(" DEFAULT NULL", "");
            cText = cText.Replace("ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_spanish_ci", "ENGINE=MyISAM");
            cText = cText.Replace("ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci", "ENGINE=MyISAM");
            cText = cText.Replace(" COLLATE utf8_spanish_ci,", ",");
            cText = cText.Replace(" COLLATE utf8_spanish_ci", "");
            cText = cText.Replace(" unsigned,", ",");
            cText = cText.Replace(" unsigned", "");
            cText = cText.Replace(" NOT NULL,", ",");
            cText = cText.Replace(" NOT NULL", "");
            cText = cText.Replace(" CHARACTER SET utf8,", ",");
            cText = cText.Replace(" CHARACTER SET utf8", "");
            cText = cText.Replace(" TEMPORARY", "");
            cText = cText.Replace(")mb4,", "),");
            cText = cText.Replace(")mb4", ")");
            return cText.ToString();
        }

[adriancs2]

Hi, thanks for the code sharing.
I do notice something, let's take this line as example:

cText = cText.Replace(" COLLATE utf8_spanish_ci DEFAULT NULL,", ",");

It seems that it only limited to

utf8_spanish_ci 

but there are many more other than "spanish"...
I think maybe this is not a universal solution.

[FranksDevRepo]

Hi, thank you for your good work work und efforts in maintaining this useful library.
Most likely I ran into the same problem.
You asked for an example to reproduce the dependency error.
Let's assume you have a database with 3 views.
create or replace view v1 as select * from v2; create or replace view v2 as select * from v3; create or replace view v3 as select * from t;

View v1 is dependent on v2, and v2 on v3, and v3 is dependent on table t.

If you export this database and try to import the backup file you'll experience this error.

The already suggested solution might have some drawbacks, when views are empty so that the temporary tables might not be created with correct column definitions.

I would suggest a recursive solution. While you try to create all views you remember all views which failed to create and try again until all views have been created or the list of failed reports does not decrease.

Best regards

[ikari84]

Hi!!

Yeah, was my mistake, tha section of replacement code is not necessary, because the sql code generated don't including it, only field definition. So, I thought that it's good solution.

string ReFormatTableView(string cViewName){
    StringBuilder cText = new StringBuilder();
    System.Data.DataTable hColumns = QueryExpress.GetTable(Command, string.Format("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='{0}' AND TABLE_SCHEMA='{1}'", cViewName,_database.Name));
    cText.AppendFormat("CREATE TABLE `{0}` (\r\n",cViewName);
    foreach(System.Data.DataRow rcol in hColumns.Rows)
    {
        cText.AppendFormat("`{0}` {1},\r\n", rcol["COLUMN_NAME"].ToString(), rcol["COLUMN_TYPE"].ToString());
    }
    cText.AppendLine(") ENGINE=MyISAM;");

    return cText.ToString();
}

Regards..

[kl1mm]

Hi together,
we ran in the same issue.
In our opinion the clearest way to fix this is to resolve the dependencies of a view and build the correct ordering for the export.

In principle, the solution is simple by just using "Topological Sort Algorithmen"
See: Wiki - Topological sorting

But how to figure out the dependent views:
Our solution is certainly not the best, but adequate for our current needs. We simply search the SQL for the occurrence of other view names and if we found any, that's the dependencies.

In code it looks like this:

void Export_Views()
{
	if (!ExportInfo.ExportViews || _database.Views.Count == 0)
		return;

	Export_WriteComment("");
	Export_WriteComment("Dumping views");
	Export_WriteComment("");
	textWriter.WriteLine();

	// ----------------------------------------------------
	// local func to determine the dependencies of one view
	IEnumerable<MySqlView> determineDependenciesFunc(MySqlView view)
	{
		return this._database.Views
			.Where(current => view.Name != current.Name) // filter ourself
			.Where(current => view.CreateViewSQLWithoutDefiner.Contains($"`{current.Name}`"));
	}
	// ----------------------------------------------------

	// Before: foreach (MySqlView view in this._database.Views)
	foreach (MySqlView view in this._database.Views.TopologicalSort(determineDependenciesFunc))
	{
		if (this.stopProcess)
			return;

		if (view.CreateViewSQL.Trim().Length == 0 ||
			view.CreateViewSQLWithoutDefiner.Trim().Length == 0)
			continue;

		this.Export_WriteLine(string.Format("DROP TABLE IF EXISTS `{0}`;", view.Name));
		this.Export_WriteLine(string.Format("DROP VIEW IF EXISTS `{0}`;", view.Name));

		if (this.ExportInfo.ExportRoutinesWithoutDefiner)
			this.Export_WriteLine(view.CreateViewSQLWithoutDefiner);
		else
			this.Export_WriteLine(view.CreateViewSQL);

		this.textWriter.WriteLine();
	}

	textWriter.WriteLine();
	textWriter.Flush();
}

For the topological sort we implement an extension method for IEnumerable (see Stackoverflow) :

internal static class EnumerableExtensions
{
	public static IEnumerable<T> TopologicalSort<T>(this IEnumerable<T> nodes, Func<T, IEnumerable<T>> connected)
	{
		var dictionary = nodes.ToDictionary(node => node, node => new HashSet<T>(connected(node)));
		while (dictionary.Count > 0)
		{
			var current = dictionary.FirstOrDefault(x => x.Value.Count == 0);
			if (current.Key == null)
				throw new ArgumentException("Cyclic connections are not allowed");
			
			dictionary.Remove(current.Key);
			
			yield return current.Key;

			foreach (var connections in dictionary.Values)
				connections.Remove(current.Key);
		}
	}
}

What do you think about this solution?
Best regards kl1mm

[adriancs2]

Hi, can you guys download and try this build and provide feedback. Is this build solve the VIEW dependencies problem?
This is compiled in .NET Standard 2.0
[MySqlBackup.NET_Issue_21_Debug1.zip]
(https://github.com/MySqlBackupNET/MySqlBackup.Net/files/2553718/MySqlBackup.NET_Issue_21_Debug1.zip)

[kl1mm]

Not working for us:

[adriancs2]

Hi, here is the test fix. You may download this version and see if the problem with VIEW dependencies solved or not.
In the build, I have included a test for creating VIEWs dependencies environment.
The sample SQL script can be found in MySqlBackupTestApp.exe
Run the [MySqlBackupTestApp.exe] > [Main Menu] > [Test] > [Export / Import VIEW with Dependencies within VIEWs]
It seems to be solved the VIEWs dependencies at my environment.
If this build unable to fix your problem, you may submit a SQL script that generates a database with dependency VIEWs that will generate the error.
This is compiled in .NET Standard 2.0
MySqlBackup.NET_Issue_21_Debug2.zip

[adriancs2]

In the build-debug2 (the DLL attached above), I have adopted FranksDevRepo suggestion:

I would suggest a recursive solution. While you try to create all views you remember all views which failed to create and try again until all views have been created or the list of failed reports does not decrease.

that performs loop to create all the the views one by one until all succeed or fail. Here is the code that I have modified:

The following block is the execution of SQL into database, but before executing the SQL, I have tried to capture any "CREATE VIEW" SQL command into a temporary DICTIONARY (_dicImportView).

void Import_AppendLineAndExecute(string line)
{
    _sbImport.AppendLine(line);
    if (!line.EndsWith(_delimiter))
        return;

    string _importQuery = _sbImport.ToString();

    bool skipexecute = false;
    if (_importQuery[0].ToString().ToUpper() == "C")
    {
        string _iq = _importQuery.ToLower();
        if (_iq.StartsWith("create "))
        {
            string[] splitoption = new string[] { " as " };
            string[] _saq = _iq.Split(splitoption, 1, StringSplitOptions.RemoveEmptyEntries);
            if (_saq[0].Contains(" view"))
            {
                _dicImportView[_importQuery] = false;
                skipexecute = true;
            }
        }
    }
            
    if(!skipexecute)
    {
        _mySqlScript.Query = _importQuery;
        _mySqlScript.Delimiter = _delimiter;
        _mySqlScript.Execute();
    }

    _sbImport.Clear();
    _sbImport = new StringBuilder();
    GC.Collect();
}

After all other SQL finished executing into database, then performs the creation of VIEW in a loop in attempt to create the VIEW one by one:

void Import_View()
{
    bool excutedOnce = true;

    Dictionary<string, Exception> _dicViewErr = new Dictionary<string, Exception>();

    while (excutedOnce)
    {
        excutedOnce = false;
        string lastExecutedView = "";

        foreach (var kv in _dicImportView)
        {
            if (kv.Value)
                continue;

            if (stopProcess)
            {
                processCompletionType = ProcessEndType.Cancelled;
                break;
            }

            _mySqlScript.Query = kv.Key;
            _mySqlScript.Delimiter = _delimiter;

            try
            {
                _mySqlScript.Execute();
                excutedOnce = true;
                lastExecutedView = kv.Key;
                if (_dicViewErr.ContainsKey(kv.Key))
                {
                    _dicViewErr.Remove(kv.Key);
                }
                break;
            }
            catch(Exception ex)
            {
                _dicViewErr[kv.Key] = ex;
            }
        }

        if (lastExecutedView.Length > 0)
        {
            _dicImportView[lastExecutedView] = true;
        }
    }

    if (_dicViewErr.Count > 0)
    {
        foreach(var kv in _dicViewErr)
        {
            _lastError = kv.Value;
            if (ImportInfo.IgnoreSqlError)
            {
                if (!string.IsNullOrEmpty(ImportInfo.ErrorLogFile))
                {
                    File.AppendAllText(ImportInfo.ErrorLogFile, Environment.NewLine + Environment.NewLine + kv.Value.ToString());
                }
            }
            else
            {
                StopAllProcess();
                throw kv.Value;
            }
        }
    }
}

Here is CS file of MySqlBackup.cs
MySqlBackup.zip

[adriancs2]

I think kl1mm suggestion is also good. The Topological sorting can be included in the next release so that the VIEW will be created in a better order.

[adriancs2]

Hi together,
we ran in the same issue.
In our opinion the clearest way to fix this is to resolve the dependencies of a view and build the correct ordering for the export.

In principle, the solution is simple by just using "Topological Sort Algorithmen"
See: Wiki - Topological sorting

But how to figure out the dependent views:
Our solution is certainly not the best, but adequate for our current needs. We simply search the SQL for the occurrence of other view names and if we found any, that's the dependencies.

In code it looks like this:

void Export_Views()
{
	if (!ExportInfo.ExportViews || _database.Views.Count == 0)
		return;

	Export_WriteComment("");
	Export_WriteComment("Dumping views");
	Export_WriteComment("");
	textWriter.WriteLine();

	// ----------------------------------------------------
	// local func to determine the dependencies of one view
	IEnumerable<MySqlView> determineDependenciesFunc(MySqlView view)
	{
		return this._database.Views
			.Where(current => view.Name != current.Name) // filter ourself
			.Where(current => view.CreateViewSQLWithoutDefiner.Contains($"`{current.Name}`"));
	}
	// ----------------------------------------------------

	// Before: foreach (MySqlView view in this._database.Views)
	foreach (MySqlView view in this._database.Views.TopologicalSort(determineDependenciesFunc))
	{
		if (this.stopProcess)
			return;

		if (view.CreateViewSQL.Trim().Length == 0 ||
			view.CreateViewSQLWithoutDefiner.Trim().Length == 0)
			continue;

		this.Export_WriteLine(string.Format("DROP TABLE IF EXISTS `{0}`;", view.Name));
		this.Export_WriteLine(string.Format("DROP VIEW IF EXISTS `{0}`;", view.Name));

		if (this.ExportInfo.ExportRoutinesWithoutDefiner)
			this.Export_WriteLine(view.CreateViewSQLWithoutDefiner);
		else
			this.Export_WriteLine(view.CreateViewSQL);

		this.textWriter.WriteLine();
	}

	textWriter.WriteLine();
	textWriter.Flush();
}

For the topological sort we implement an extension method for IEnumerable (see Stackoverflow) :

internal static class EnumerableExtensions
{
	public static IEnumerable<T> TopologicalSort<T>(this IEnumerable<T> nodes, Func<T, IEnumerable<T>> connected)
	{
		var dictionary = nodes.ToDictionary(node => node, node => new HashSet<T>(connected(node)));
		while (dictionary.Count > 0)
		{
			var current = dictionary.FirstOrDefault(x => x.Value.Count == 0);
			if (current.Key == null)
				throw new ArgumentException("Cyclic connections are not allowed");
			
			dictionary.Remove(current.Key);
			
			yield return current.Key;

			foreach (var connections in dictionary.Values)
				connections.Remove(current.Key);
		}
	}
}

What do you think about this solution?
Best regards kl1mm

Hi kl1mm, are you able to re-code this part to be compatible with .NET Framework 2.0 by taking out the LINQ?

[adriancs2]

A new version (v2.1) of this library is released. It's not only support dependencies within VIEWs, it supports dependencies among Procedures, Functions and Views.

Available in Nuget (.NET Standard 2.0)
and "Release Section" https://github.com/MySqlBackupNET/MySqlBackup.Net/releases