Mysqldump backup sql file with mysqlbackup.net restore will prompt an error:

[xintiandi]

Mysqldump backup sql file with mysqlbackup.net restore will prompt an error:

You have an error in your SQL syntax;check the manual that corresponds to your MySQL server version for the right syntax to use near "at line 3

Mysqldump backup sql can be restored with mysqldump
Mysqlbackup.net can also restore his own backup sql
I want to be a dual-engine software, I hope 2 backup and restore tools can be cross-mixed

Is there a way to solve?

Mysqldump backup command
Command = string.Format("mysqldump --quick --host={1} --default-character-set={2} -R -E --lock-tables --routines --force --port={3 } --user={4} --password={5} {6} -r "{0}"",
                            Directory, host, characterSet, port, user, password, databaseName);

Mysqlbackup.net restore command, no other parameters, keep the default

            characterSet = txtcharacterSet.Text.Trim();
            databaseName = cmbDBlist.Text.Trim();
            Host = txthost.Text.Trim();
            Password = txtpassword.Text;
            Port = txtport.Text.Trim();
            User = txtuser.Text.Trim();
            String ConnectString = String.Format("server={0};uid={1}; Port={2};pwd={3}; database={4};", host, user, port, password, databaseName) ;
            ConnectString += "charset=" + characterSet + "; " + "convertzerodatetime=true;";
            Try
            {
                Using (MySqlConnection myconn = new MySqlConnection(ConnectString))
                {
                    Using (MySqlCommand cmmd = new MySqlCommand())
                    {
                        Using (MySqlBackup backCmd = new MySqlBackup(cmmd))
                        {
 
                                cmmd.Connection = myconn;
                                    myconn.Open();
                                backCmd.ImportFromFile(strPath);

[adriancs2]

I wish to have a look at both SQL files created by MySqlDump and MySqlBackup.NET and see what are the differences between both files.

[xintiandi]

I wish to have a look at both SQL files created by MySqlDump and MySqlBackup.NET and see what are the differences between both files.

Backup sql file see attachment
I am using a remote mysql5.7.22 database backed up with msyql5.5.31 version mysqldump to generate a sql file.
In the mysqlbackup.net2.3 local restore of the generated sql file to the remote mysql5.7.22 database, it prompts an error.
Probably the operation process is like this.

mysqldump.zip

mysqlbackup.net.zip

[adriancs2]

I have found the bug. The error occurs at the "Procedure" block, where the delimeter is not handled correctly. You may try the following fixed version. I will patch the nuget version later.
MySqlBackup_issue_52_debug_v1.zip

[adriancs2]

Nuget version was submitted.
https://www.nuget.org/packages/MySqlBackup.NET

[xintiandi]

I have found the bug. The error occurs at the "Procedure" block, where the delimeter is not handled correctly. You may try the following fixed version. I will patch the nuget version later.
MySqlBackup_issue_52_debug_v1.zip

Thank you very much for this timely processing.
Can provide a compiled version of the release, I use the reference dll file method, you submit the source code, I use vs2015 to compile, however, you estimate that the version of vs is higher than me, the net version is not right, the environment is not the same . My program is a net4.0 environment, I hope to provide 2 compiled dll files.

[adriancs2]

Compiled dll released.
https://github.com/MySqlBackupNET/MySqlBackup.Net/releases
You're welcome.

[xintiandi]

Compiled dll released.
https://github.com/MySqlBackupNET/MySqlBackup.Net/releases
You're welcome.

Thank you very much, the problem has been solved. Ask a question, mysqldump backup table name in the database, after mysqlbackup.net restore, the letter case in the table name changed, there are no parameters to maintain uppercase and lowercase letters when restoring?or all converted to uppercase letters or lowercase letter?

[adriancs2]

This is affected by a server variable: lower_case_table_names

You can get the value by following SQL command:

show variables like 'lower_case_table_names';

On Unix, the value = 0
On Windows, the value = 1 (always convert to lowercase)
On MacOS, the value = 2

Read more about "Identifier Case Sensitivity" at MySQL documentation:
https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html

How to change lower_case_table_names=0 in Windows:
https://stackoverflow.com/questions/50453108/mysql-cant-set-lower-case-table-names-variable
http://www.jochenhebbrecht.be/site/2013-02-14/mac/mysql-table-names-always-converted-lowercase

[xintiandi]

This is affected by a server variable: lower_case_table_names

You can get the value by following SQL command:

show variables like 'lower_case_table_names';

On Unix, the value = 0
On Windows, the value = 1 (always convert to lowercase)
On MacOS, the value = 2

Read more about "Identifier Case Sensitivity" at MySQL documentation:
https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html

How to change lower_case_table_names=0 in Windows:
https://stackoverflow.com/questions/50453108/mysql-cant-set-lower-case-table-names-variable
http://www.jochenhebbrecht.be/site/2013-02-14/mac/mysql-table-names-always-converted-lowercase

Ok, then I will try again. What you mean is that the uppercase and lowercase letters of the mysql database name have nothing to do with mysqlbackup.net restore, only related to the configuration of mysql. My database is configured with case-sensitive letters, mysql5.5 lower_case_table_names=1,
Mysql5.7.22 lower_case_table_names=2 can be set to 1 can not be started, the data names I backed up during the test are all uppercase, some table names become lowercase after the restore. I will confirm that it is caused by mysqldump restore, or by mysqlbackup.net restore.
Finally, there is a suggestion to add a reference format to the textbox.Text of the Connection String in MySqlBackupTestApp.exe, or a mouseover prompt, so it is convenient to test.
Thank you again for providing such a good software.

[adriancs2]

Finally, there is a suggestion to add a reference format to the textbox.Text of the Connection String in MySqlBackupTestApp.exe, or a mouseover prompt, so it is convenient to test
Hi, thanks for the suggestion. I have applied your suggestion and include a connection string builder in the test app. However, I did not make another separate release for this. I only updated the repository. This feature will be available in the next release. For early access, one may compiles the current source code and run the testapp.

[adriancs2]

#53