After mysqlbackup.net restores the sql file, the table names all become lowercase

[xintiandi]

I tested it again and confirmed that after mysqlbackup restores the sql file, there is a problem with the name of the table. My database is case-sensitive. The table names in my configured mysql5.7.22 are all uppercase letters.
After backing up with mysqldump, mysqldump restores and the table name does not change. But use mysqlbackup to restore the files backed up by mysqldump. After the restore, the table names all become lowercase.
I used mysqlbackup to back up the database, restore it with mysqldump, the table name is unchanged, or uppercase. This shows that mysqlbackup has problems with mysqldump compatibility, but mysqldump is perfectly compatible with mysqlbackup backup files.

This problem does not always appear, occasionally, may require multiple tests, to know how it is caused, will be related to spaces in the path, these times appeared 2-3 times.
When I tested it, I restored it to a different database. For example, the database name I backed up with mysqldump is test1. When I restore it, I use mysqlbackup to restore the backup database test1 to a new database test2.

[adriancs2]

Thanks for the info.

Actually in the coding of MySqlBackup.NET, it does not alter or interfere the lower case or upper case of the table names. It is all handled by MySqlCommand or MySqlScript from MySql.Data (developed by Oracle).

Take the following as example:

string ConnString = "server=127.0.0.1;user=root;pwd=1234;database=test4;convertzerodatetime=true;treattinyasboolean=true;charset=utf8;";
string sqlCreateTable = "";

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

        cmd.CommandText = "DROP TABLE IF EXISTS `ALM_Config`";
        cmd.ExecuteNonQuery();

        cmd.CommandText = @"
CREATE TABLE IF NOT EXISTS `ALM_Config` (
`f_id` int(11) NOT NULL AUTO_INCREMENT,
`AlarmText` varchar(100) NOT NULL,
`VarID` int(11) NOT NULL,
`AlarmValue` varchar(50) DEFAULT NULL,
`ReturnValue` varchar(50) DEFAULT NULL,
`AlarmMethod` varchar(50) DEFAULT NULL,
`ConfigStatus` varchar(50) DEFAULT NULL,
PRIMARY KEY (`f_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "SHOW CREATE TABLE `ALM_Config`;";
        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        sqlCreateTable = dt.Rows[0][1] + "";

        conn.Close();
    }
}

MessageBox.Show(sqlCreateTable);

I run above code on Windows.
The result: The table name is converted into lower case.

As per my understanding, MySqlDump only performs "Backup" and does not perform "Restore". It is more probably you're using MySql.exe to performs the "Restore".

I have tried to use MySql.exe to performs the "Restore", it also converted table name into lower case.

I'm unable to reproduce this effect (maintaining Upper Case with MySql.exe) at the moment.

But anyway, are you testing both restore operation of "MySqlDump" and "MySqlBackup.NET" on the same computer (same MySql server)?

If yes,
then most probably there is a missing step or option or parameter or switch to be configured in one of the following:

• Connection String Option
• MySqlConnection (from MySql.Data.DLL)
• MySqlCommand (from MySql.Data.DLL)
• MySql Server Variable (either session or global)

I have tried to Google around to find clues, but I'm unable to find any.
The best thing I can find at the moment is something related to lower_case_table_names.

I will come back to this issue in future if I found any clue or solution.

You are welcome to continue to post any findings if you have any clues on this issue, so that we can fix the problem together.

[adriancs2]

As a temporary solution, you may try to edit the value of lower_case_table_names at MySql config file.

You may try the following:

Open MySql config file, default location:

C:\ProgramData\MySQL\MySQL Server x.x\my.ini

under section [mysqld], find

lower_case_table_names=0

and change it to (if not found, then add the following line)

lower_case_table_names=2

Save the config file and restart MySql server.

With this change, the table names will maintain upper case.

If the MySql server is not administered by you, you may try to contact the admin of the MySql server.
This is because the value of lower_case_table_names cannot be changed after the MySql server starts running.

-- Edit/Update --
You can get the value lower_case_table_names 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]

lower_case_table_names=0

Backup mysqldump, restore with mysql, as long as mysqlbackup.net will not interfere with the size of the table name letters, I look for other reasons. The environment I tested was all done on a Windows computer. Sometimes it is a virtual machine test, sometimes a win7 x86 computer and my laptop, the computer I use is win10 x64. The server is my own. Make sure that lower_case_table_names=2 is set, this is not always the case. Have time to test slowly, and I will notify you if it is confirmed by what reason. Thank you for your answer.

[adriancs2]

You're welcome :)

[phil-w]

Just a comment on this, in my case. I'm exporting from a Windows based MariaDB instance and restoring onto a hosted Unix based system. In this circumstance the exported SQL file contains the evidence of the problem:

DROP TABLE IF EXISTS `AspNetRoles`;
CREATE TABLE IF NOT EXISTS `aspnetroles` (

For whatever reason, these two statements can only work if the system running this script ignores case, which is not always the case. For me, as there are only a handful of known and not-changing MS Identity tables, I'll just string replace the lower cased bits in the dump script with the correctly cased equivalents. Weird though.