MySqlBackupNET/MySqlBackup.Net

Can you use dependency on MySqlConnector.net (MIT License) instead of MySql.Data( GPL License)

himanshukodwani opened this issue · 13 comments

himanshukodwani commented

MySqlConnector.Net licensing seems to be a better choice for many, also performance-wise it has a slight edge.

adriancs2 commented

Thanks for the info. I'll look into this

Relax594 commented

I'd appreciate this as well.

adriancs2 commented

May I know how do you insert scripts?
The blocks of SQL statements for creating procedures, functions, etc?

In MySql.Data, the code will be something like this:

string text = @"DELIMITER |
CREATE PROCEDURE `proceduresample1`()
    DETERMINISTIC
    COMMENT 'A procedure'
BEGIN
SELECT 'Hello World !';
END |";

using (MySqlConnection conn = new MySqlConnection(ConnectionString))
{
	MySqlScript script = new MySqlScript(conn);
	script.Query = text;
	script.Execute();
}

What is the equivalent of above code if using MySqlConnector.net?

Relax594 commented

I've never done that so far sorry.

himanshukodwani commented
Memnarch commented

Can confirm you can just use a MySQLCommand in MySQLConnector to run multiple things. Using it and was looking for a backup-integration into my software and ended up here ;)

In my buildsystem I create different sql files for either full creating of my schema or patching a schema between to versions of my database-schema. I can simply load my file into a string, put it into a MySQLCommand and run it and it'll work as expected running all the create/alter statements in it.

From that discussion, MySQLScript from Oracle sounds like a hack to fix a designflaw in their MySQLCommand o.o

Flithor commented

Well, MySqlConnector.Net not support DELIMITER statement, DELIMITER it's only work with MySqlClient:
mysql-net/MySqlConnector#645
So, consider notify user do not use DELIMITER.

Your sql can remove DELIMITER and code will be:

string text = @"DROP PROCEDURE IF EXISTS`proceduresample1`;

-- DELIMITER |
CREATE PROCEDURE `proceduresample1`()
    DETERMINISTIC
    COMMENT 'A procedure'
BEGIN
    SELECT 'Hello World !';
-- END |
END;

CALL proceduresample1;
";

using (MySqlConnection conn = new MySqlConnection(ConnectionString))
using (MySqlCommand cmd = new MySqlCommand(text, conn))
{
    conn.Open();
    Debug.WriteLine(cmd.ExecuteScalar());
    //Output "Hello World !"
}
JeanRessouche commented

News ?

JeanRessouche commented

Hi @adriancs2, as i was stuck without it, i made a quick (& maybe dirty) fork supporting it, plus a nuget package.

https://github.com/souchprod/MySqlBackup.Net
https://www.nuget.org/packages/MySqlBackup.NET.MysqlConnector/2.3.4.2

It was mostly about changing the reference to MysqlConnector & using a clone of MysqlScript.
Let me know if you want a PR or if you prefer to implement it yourself your own way.
Once implemented, i will remove my package from Nuget.

adriancs2 commented

Hi @souchprod , nice work :)
I have included the 2 additional files ( MySqlScript.cs and MySqlTokenizer.cs ) that done by you and built the DLL. Nice workaround. and thanks.

along with additional for support of MySqlConnector.DLL, I have also added some updates, here are the summary:

[New]

  • ExportInformation.EnableComment = true. (new option)
  • Added support for MySqlConnector (MIT)
  • ExportToFile(), if the directory is not existed. MySqlBackup will attempt to create it.

[Improve]

  • ImportInfo.ErrorLogFile = true. Now will include the query that causes the error.
  • ExportToFile = Auto created directory if not exists.
  • For non delimeter changed query execution, using MySqlCommand in stead of MySqlScript, slightly increase overall import speed.
  • Minor clean up.

I did some benchmark between MySqlCommand and MySqlScript, and I found that MySqlCommand is slightly faster than MySqlScript. Therefore, for normal execution, I use MySqlCommand, and for routines creation (store procedures, functions, views, triggers, etc...), I use MySqlScript.

Below is the summary of the benchmark:

Database Size: 380MB
CPU: Intel Core i5
RAM: 12GB
Harddisk: Samsung SSD Evo 860

Time(Avr)   Class - DLL
---         ---
57s         mysql.exe (MySqlWorkbench)
1m 5s       MySqlCommand - MySql.Data.DLL
1m 5s       MySqlCommand - MySqlConnector.DLL
1m 5s       MySqlCommand - Devart.Express.MySql.DLL
1m 16s      MySqlScript  - MySql.Data.DLL

I have already released the binaries for MySqlConnector.DLL. You can download it at:
https://github.com/MySqlBackupNET/MySqlBackup.Net/releases

You can also download the specific files here for testing:
MySqlBackupNet.MySqlConnector.DLL(2.3.5).zip

  • 🎉1
himanshukodwani commented
  • 👍1
adriancs2 commented

Hi @souchprod I would like to upload the new nuget package for MySqlConnector, can you add me as the owner of the package?
https://www.nuget.org/packages/MySqlBackup.NET.MysqlConnector/

  • 👍1
JeanRessouche commented

HI @adriancs2 , yep i'll search how to do this now, nice to have the changes included in the original repo and glad to see the other improvements

  • 👍2