how to have "CREATE DATABASE IF NOT EXISTS database_name"

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
paul_huynh
Posts: 4
Joined: Fri 16 Dec 2011 19:04

how to have "CREATE DATABASE IF NOT EXISTS database_name"

Post by paul_huynh » Fri 06 Jan 2012 16:51

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
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Post by Pinturiccio » Mon 09 Jan 2012 15:22

Try using:

Code: Select all

mySqlDump.IncludeDatabase = true;
instead of

Code: Select all

mySqlDump.IncludeDatabase = false;

paul_huynh
Posts: 4
Joined: Fri 16 Dec 2011 19:04

this will drop the database and re-create the database again

Post by paul_huynh » Mon 09 Jan 2012 16:34

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
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Post by Pinturiccio » Tue 10 Jan 2012 12:52

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
Posts: 6
Joined: Mon 08 Nov 2010 16:29

Restore in different databse

Post by flafleur » Wed 18 Apr 2012 20:30

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
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Post by Pinturiccio » Mon 23 Apr 2012 14:23

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
Posts: 6
Joined: Mon 08 Nov 2010 16:29

Re: how to have "CREATE DATABASE IF NOT EXISTS database_nam

Post by flafleur » Fri 27 Apr 2012 14:25

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
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: how to have "CREATE DATABASE IF NOT EXISTS database_nam

Post by Pinturiccio » Thu 03 May 2012 10:43

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
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Re: how to have "CREATE DATABASE IF NOT EXISTS database_nam

Post by Shalex » Thu 03 May 2012 11:15

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.

Post Reply