how to have "CREATE DATABASE IF NOT EXISTS database_name"

[paul_huynh]

how to have "CREATE DATABASE IF NOT EXISTS database_name" when using DUMP, below is my coding:

mySqlDump.Database = DatabaseName;
mySqlDump.IncludeDrop = true;
mySqlDump.GenerateHeader = true;
mySqlDump.IncludeUsers = true;
mySqlDump.IncludeLock = true;
mySqlDump.DisableKeys = true;
mySqlDump.IncludeDatabase = false;
mySqlDump.Mode = Devart.Common.DumpMode.All;
mySqlDump.ObjectTypes = MySqlDumpObjects.Events | MySqlDumpObjects.Functions |
MySqlDumpObjects.Procedures | MySqlDumpObjects.Tables |
MySqlDumpObjects.Triggers | MySqlDumpObjects.Udfs |
MySqlDumpObjects.Users | MySqlDumpObjects.Views;
mySqlDump.HexBlob = false;

With my codes above, it only have USE database_name on top of the dump file, but I need "CREATE DATABASE IF NOT EXISTS". Please help

Thanks very much.

[Pinturiccio]

Try using:

Code: Select all

mySqlDump.IncludeDatabase = true;

instead of

Code: Select all

mySqlDump.IncludeDatabase = false;

[paul_huynh]

With IncludeDatabase = true,
it will drop the database and create again. I don't want that, I'd prefer to have "CREATE Databases if not Exist", could you please help?

[Pinturiccio]

You also use mySqlDump.IncludeDrop = true; It will drop a database and create a new one with property mySqlDump.IncludeDatabase = true;

You can use:

Code: Select all

mySqlDump.IncludeDrop = false;
mySqlDump.IncludeDatabase = true;

In this case will be created a statement: CREATE DATABASE /*!32312 IF NOT EXISTS*/ DatabaseName.
But if DatabaseName already exists drop statements for database or tables will not be created, and when you try to restore the database you will get an error when the Dump class will try to create a table that already exists in this database.
So there are two ways:
1. Drop the database and create a new one. (mySqlDump.IncludeDrop = true; mySqlDump.IncludeDatabase = true;)
2. CREATE DATABASE /*!32312 IF NOT EXISTS*/ DatabaseName; but if DatabaseName already exists, then you will receive an error while restoring. (mySqlDump.IncludeDrop = false; mySqlDump.IncludeDatabase = true;)

As a workaround, you can use:

Code: Select all

mySqlDump.IncludeDrop = true;
mySqlDump.IncludeDatabase = false;

After this a row will be created in your backup file:

Code: Select all

USE DatabaseName;

You should open the file in your application and insert the following row before the row "USE DatabaseName;"

Code: Select all

CREATE DATABASE IF NOT EXISTS DatabaseName

[flafleur]

Is there a way to restore a Database in a diffrent database name from the original?

I want to use the MySqlDump Class to backup a database and restore it in a different database name in some cases.

Is it possible to do that with MySqlDump Class?

If yes, what is the way to do that?

Thanks!

Francois

[Pinturiccio]

There is no property implementing this feature. As a workaround, you can change the database name in MySqlDump.DumpText or in the file, where the data were saved when creating backup. Find the first occurrence of the "use " substring and replace the text after it to the semicolon with the new database name. If the new database don't exist yet, you need to add the corresponding create statement at the beginning of the file or the DumpText property.

[flafleur]

Is it a future option to implement this with the MySQLDump.Database? If we specify a database in this property the restore will be done in it.

I think that could be fine to have this option same as the command line mysqldump option database.

[Pinturiccio]

We cannot reproduce the issue with dotConnect for Oracle 6.80.350. Please send us a small test project with DDL/DML scripts for reproducing the issue.

[Shalex]

You can use the following command before restoring:

Code: Select all

MySqlDump dump = new MySqlDump(conn);
dump.Database = "test2";
MySqlCommand comm = new MySqlCommand("use "+dump.Database);

But if you do not change the backup file, this command will be overriden by the command from backup file.