Dumping MySql database

[Sunesh Kumar]

Hi Friends..

I am developing an application which is used crete a dump file for specified tables in a database. I am writing a java program to implement that by using "mysqldump" command provided by the MySql.

Please See the code :

mysqldump -uroot -p accessory accessory_text customer instocknotification orderline orders ordershippingdetail websiteorder user_language user_securityrole ids > "C:\projects\hitachidigitalmediav2\hitachidigitalmediaV2_01-Mar-2006_13-29.dmp"

Note:here my database has no password.

The problem is i am unable to dump tables if they are more than 11.
upto 11 tables the dump file is generationg but after that its not...

can any one share ur ideas it is eating my brain

Bye waiting for ur repiles...

[Chetan Parekh]

Originally posted by SUNESH BAACHU kumar:
The problem is i am unable to dump tables if they are more than 11.
upto 11 tables the dump file is generationg but after that its not...

What error are you getting?

[Sunesh Kumar]

Hi,

I am getting no error, it doesnt creating me the dump file in the specified folder.
This is my log regarding to this action:

admin cmd is dumpDatabase
action is doDump
-->mysqldump -uroot -p accessory accessory_text customer instocknotification orderline orders ordershippingdetail websiteorder user_language user_securityrole ids > "C:\projects\hitachidigitalmediav2\build\web\admin\dumps\hitachidigitalmediaV2_01-Mar-2006_13-29.dmp"
cmd=[0]=native.cmd
cmd=[1]=
cmd=[2]=
nextPage...dumpProgress.jsp
admin site nextPage is...dumpProgress.jsp


can u say why it is happening like this...
Thanks in advance

[Chetan Parekh]

Post your code.

[Manish Hatwalne]

Check for disk quota for the user account.

- Manish

[Sunesh Kumar]

Hi Manish,

How can i check the disk space .I didnt set it any where i think so.

Bye waiting for your reply

[Chetan Parekh]

Originally posted by Manish Hatwalne:
Check for disk quota for the user account.

- Manish

Can be possible.

Another possibility is Array size, if you have array and the size is 11 or your are iterating from 0 to 10, it will create only 11 dumps.

[Sunesh Kumar]

Hi ,

I didnt set any constraints friends, just i am reading all the tables from my xml files they are upto 22.

can u solve that please for me,

Bye waiting for your reply

[Chetan Parekh]

Suneshjee, post your code.

[Sunesh Kumar]

Hi Chetan,

private String doDumpFullDatabase( Connection c, HttpServletRequest request )
throws Exception
{
try
{
// Generate a new file name
java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat( "dd-MMM-yyyy_H-mm" );
String dumpFilePrefix = InitServlet.hConfig.get( "db.dumpfileprefix" );
String dateString = "_" + sdf.format( new java.util.Date() );
String suffix = ".dmp";
String dumpFileName = dumpFilePrefix + dateString + suffix;

// Get the location of the dumpfile from the properties
String dumpFileLocation = InitServlet.hConfig.get( "db.dumpfilelocation" );
dumpFileLocation = InitServlet.context.getRealPath( dumpFileLocation )+"\\";
String dumpFileDirectory = dumpFileLocation;
dumpFileLocation = dumpFileLocation + dumpFileName;

// Determine the current operating system.
String osName = System.getProperty( "os.name" );

// Windows specific
if ( osName.indexOf( "Windows" ) != -1 )
{
// Build a windows specific mysqldump command from the properties
String fullCmd = buildWindowsCmd() + "\"" + dumpFileLocation + "\"";
System.out.println("-->-->"+fullCmd);
NativeCmd nativeCmd2 = new NativeCmd( NativeCmd.PLATFORM_WIN32, fullCmd, "cmd.out", "cmd.err" );


}
request.setAttribute( "DUMP_SUCCESS", "true" );
request.setAttribute( "DUMP_FILE_NAME", dumpFileName );

return "dumpProgress.jsp";
}
catch( Exception e )
{
e.printStackTrace();
request.setAttribute( "DUMP_SUCCESS", "false" );
return "dumpProgress.jsp";
}
}

private String buildWindowsCmd()
{
String winCmd = "mysqldump -u" +
InitServlet.hConfig.get( "db.user" ) +
" -p" + InitServlet.hConfig.get( "db.password" ) +
" " + InitServlet.hConfig.get( "db.url" ).substring( InitServlet.hConfig.get( "db.url" ).lastIndexOf( '/' ) + 1 ) +
+ InitServlet.hConfig.get( "db.livedumptables" ) + " > ";
return winCmd;
}


my xml file:

db.dumpfilelocation=/admin/dumps/
db.livedumpfilelocation=/admin/dumps/
# Filename prefix for database dump file produced from within admin tool. (Has date and time appended at runtime).
db.dumpfileprefix=hitachidigitalmediaV2
db.livedumpfileprefix=hitachidigitalmedia_live
# Only dump these tables
db.dumptables=aftersalescompany aftersalescountries authorised_locales brochure brochure_text comparisonwizard comparisonwizard_comparisonwizardlabel_product comparisonwizard_text comparisonwizardlabel comparisonwizardlabel_text country country_text customnews dealerimage dealerimage_text dealerpricelist distributor distributorcountries driver driver_text ecomm_producttype_contact faq faq_text feature feature_text idealpartner language locale_text manual manual_text platforms pressrelease pressrelease_text product product_aftersalescompany product_brochure product_dealerimage product_distributor product_drivers product_faq product_feature product_pressrelease product_techspec product_text productgroup productgroup_aftersalescompany productgroup_distributor productgroup_text producttemplates producttype producttype_ecommproductgroup producttype_text tax README review review_text techspec techspec_text
db.livedumptables=accessory accessory_text customer instocknotification orderline orders ordershippingdetail pincode_usage product_accessory users websiteorder user_language user_securityrole ids


i am using xml file to get the requires tables

[Sunesh Kumar]

Hi friends,

I think the problem may not of the 11 . I think as i am using like this

Runtime runtime=Runtime.getRuntime();
Process prc=runtime.exec(fullCmd);

the command prompt may restricts upto limited number of chars if so what can i do i have lot many tables to be dumped...

becoming by this

waiting for ur replies thax in advance

[Bear Bibeault]

Moved to the JDBC forum.

[Bear Bibeault]

"suneshkumar",

There aren't many rules that you need to worry about here on the Ranch, but one that we take very seriously regards the use of proper names. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Thanks!
bear
JavaRanch Sheriff

[Sunesh Kumar]

Hi Bear,

Sorry for that Bear . I didnt observe that thanks for ur remembrance.
I will update that bear.

Bye..

[Sunesh Kumar]

Hi Friends

At last I got known whats the error is..I am not sure but it may be the reason for not creating the dump file.

As i am using "Runtime" and "Process" the command executed on the command prompt...here the problem lies i think so..

Here If i give the tables which are short in length i can able to dump the tables upto 14 . I think the length of the whole string is becoming the problem what can i do for that ...

Bye waiting for ur replies...

[Manish Hatwalne]

I didn't realzie you were working on Windoze!! On unix, you can do "df" to find out about disk space, on windows it's simple right click on the directory an dyou can get this. But then as I realzie now, you don't have any allocated disk quota for the user.

This may not work, but you can try these --

Runtime runtime=Runtime.getRuntime();
Process prc=runtime.exec(fullCmd);

Insated fo single string "fullCmd", try the method which takes an array of commands as a string array. Alternatively, you can this -
runtime.exec(fullCmd, env);

where you can speify larger command-line arguments limit for the runtime process (is there such limit? You may have to google for it)

Better still, you can write a small program/batch script which will process these tabels one by one for a known file, in that case you can easily add/delete tables from thsi file as you want. I would suggest you use this approach -- this should work.

- Manish

[Manish Hatwalne]

See this discussion, seems interesting!!

- Manish

[Sunesh Kumar]

Hi Manish,

i didnt get u clearly Manish.I am using this class file to execute this.

NativeCmd nativeCmd2 = new NativeCmd( NativeCmd.PLATFORM_WIN32, fullCmd, "cmd.out", "cmd.err" );

the nativeCmd class is doing that runtime.exec(command)..here this command is an string array i splitted like that inside

waiting for ur reply

[Manish Hatwalne]

Have your nativeCmd class to run a batch file instead of "mysqldump", and write a batch file to do mysqldump by picking up tables from a file. Or you can refactor your nativeCmd class to dump/append an array of 0 tables at a time for MS Windows platofrmm. Once you know what the problem is, you are the best person to know which is the best solution for your parameters in your situations.

- Manish

[Sunesh Kumar]

Hi Manish,

This my NativeCmd code

 public class NativeCmd extends Thread
{
 
    public NativeCmd(int i, String s, String s1, String s2)
        throws Exception
    {
        process = null;
        outputFilename = "cmd.out";
        errorFilename = "cmd.err";
        outputFilename = s1;
        errorFilename = s2;
        String s3 = "";
        String s4 = "";
        String s5 = s;
        if(i == 1)
        {
            s3 = "native.cmd";
            s4 = "";
            s5 = "";
            try
            {
                FileWriter filewriter = new FileWriter("native.cmd");
                filewriter.write(s + "\n");
                filewriter.write("exit\n");
                filewriter.close();
            }
            catch(Exception exception)
            {
                exception.printStackTrace();
                throw exception;
            }
        } else
        if(i == 2)
        {
            s3 = "/bin/sh";
            s4 = "-c";
            s5 = s;
        } else
        {
            System.out.println("Warning: Unknown platform in NativeCmd: " + i);
        }
        Runtime runtime = Runtime.getRuntime();
        String as[] = null;
        as = (new String[] {
            s3, s4, s5
        });
        try
        {
            for(int j = 0; j < as.length; j++)
                System.out.println("cmd=[" + j + "]=" + as[j]);
 
            process = runtime.exec(as);
        }
        catch(IOException ioexception)
        {
            System.err.println("IOException while trying to execute command:" + ioexception);
        }
    }
 
    public NativeCmd(String s)
        throws Exception
    {
        this(2, s, "cmd.out", "cmd.err");
    }
 
    public void run()
    {
        Object obj = null;
        Object obj1 = null;
        Object obj2 = null;
        Object obj3 = null;
        Object obj4 = null;
        PrintWriter printwriter = null;
        PrintWriter printwriter1 = null;
        Object obj5 = null;
        try
        {
            String s = "";
            String s1 = "";
            String s2 = outputFilename;
            String s3 = errorFilename;
            try
            {
                File file = new File(s2);
                File file1 = new File(s3);
                printwriter = new PrintWriter(new FileWriter(file));
                printwriter1 = new PrintWriter(new FileWriter(file1));
            }
            catch(IOException ioexception1)
            {
                System.err.println("IO Exception processing files:");
                System.err.println(s2);
                System.err.println(s3);
                System.err.println(ioexception1.getMessage());
            }
            BufferedReader bufferedreader = new BufferedReader(new InputStreamReader(process.getErrorStream()));
            BufferedReader bufferedreader1 = new BufferedReader(new InputStreamReader(process.getInputStream()));
            String s4 = "";
            String s5 = "";
            while((s = bufferedreader.readLine()) != null || (s1 = bufferedreader1.readLine()) != null) 
            {
                if(s != null)
                    printwriter1.println(s);
                if(s1 != null)
                    printwriter.println(s1);
            }
            bufferedreader.close();
            bufferedreader1.close();
            printwriter.close();
            printwriter1.close();
            int i = process.waitFor();
        }
        catch(IOException ioexception)
        {
            System.err.println("IO Exception occurred in redirecting log output:" + ioexception);
            System.exit(1);
        }
        catch(InterruptedException interruptedexception)
        {
            System.err.println("InterruptedException occurred:" + interruptedexception);
            System.exit(1);
        }
    }
 
    public Process getProcess()
    {
        return process;
    }
 
    public void waitForCmdToFinish()
        throws InterruptedException
    {
        process.waitFor();
    }
 
    public static void main(String args[])
    {
        TestNativeCmd testnativecmd = new TestNativeCmd(1, "mysqldump -u<username> -p<password> <database>", "dump.out", "dump.err");
        testnativecmd.runit();
    }
 
    Process process;
    String outputFilename;
    String errorFilename;
    public static final int PLATFORM_WIN32 = 1;
    public static final int PLATFORM_LINUX = 2;
}

i call this class like this

NativeCmd nativeCmd2 = new NativeCmd( NativeCmd.PLATFORM_WIN32, fullCmd, "cmd.out", "cmd.err" );

here fullCmd is...

mysqldump -uroot -p hitachidigitalmediaV2 product_text productgroup productgroup_aftersalescompany productgroup_distributor productgroup_text producttemplates tax techspec_text techspec review_text > "c:\part3.dmp"

can u please help me by seeing this...the problemas u know its not creating the dump file if there are less tables then it is creating...i am just confused here i am ruuning under windows 2000 professinal

Waiting for reply..

Bye

[edited to add code tags]
[ March 02, 2006: Message edited by: Jeanne Boyarsky ]

[Sunesh Kumar]

Hi Friends

Can any body share my problem ...pls respond me it is eating my brain from these few days.

Please share ur ideas friends

[Adam Peck]

package ca.iais.control;
 
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
 
public class BackupServlet extends HttpServlet {
 
public static final String[] cmd = { "mysqldump.exe", "iais", "-u", "IAIS", "--password=password" };

public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {

if(request.getParameter("type").equals("backup")) {

//String path = request.getRealPath("/backup/");
File toWrite = new File("D:\\backup.sql");
Process p = Runtime.getRuntime().exec(cmd);
BufferedReader br = new BufferedReader(new InputStreamReader(p.getInputStream()));
BufferedWriter bw = new BufferedWriter(new FileWriter(toWrite));
String line = null;
String file = "";
while((line = br.readLine()) != null)
file += line+"\r\n";
try {
p.waitFor();
} catch(InterruptedException ie) {
ie.printStackTrace(System.out);
}
bw.write(file);
bw.close();
br.close();
}
}
}

[Sunesh Kumar]

HI

Sorry for late reply.
I have seen the given code Peck. But what is the package ca.iais.control.
I am aslo unable to know how the mysqldump.exe refers the exe file in "c:\mysql\bin"

i want to take a dump for specific tables in a database

can u please explain me