how to have "CREATE DATABASE IF NOT EXISTS database_name"

how to have "CREATE DATABASE IF NOT EXISTS database_name"

Postby 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.
paul_huynh
 
Posts: 4
Joined: Fri 16 Dec 2011 19:04

Postby Pinturiccio » Mon 09 Jan 2012 15:22

Try using:
Code: Select all
mySqlDump.IncludeDatabase = true;

instead of
Code: Select all
mySqlDump.IncludeDatabase = false;
Pinturiccio
Devart Team
 
Posts: 1982
Joined: Wed 02 Nov 2011 09:44

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

Postby 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?
paul_huynh
 
Posts: 4
Joined: Fri 16 Dec 2011 19:04

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

Restore in different databse

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

Postby 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.
Pinturiccio
Devart Team
 
Posts: 1982
Joined: Wed 02 Nov 2011 09:44

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

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

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

Postby 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.
Pinturiccio
Devart Team
 
Posts: 1982
Joined: Wed 02 Nov 2011 09:44

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

Postby 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.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for MySQL