0

I'm trying to restore a MySQL database from a file generated with mysqldump. I do it with an ArrayList that contains each query of the restoration plan and I execute each one of them with a Statement.

But sometimes, it stops on some point of the proccess (it can be different on different executions). It doesn't show any error message; it just hangs (when this happens, I need to restart the Mysql service).

This is the code of the restoration:

    ArrayList<String> sql;
    int res;

    FileSQLCommandManager fichero = null;
    try {
        if (pass == null)
            conectar(conn);
        else
            conectar(conn, pass);

        Statement st = null;
        st = conn.createStatement();     

        st.executeUpdate("SET FOREIGN_KEY_CHECKS=0");

        PreparedStatement stConstraints = null;

        String cadenaSQL = null;
        String cadenaSQLConstraints = null;
        String cadenaConstraints;
        ResultSet rs;
        boolean ejecutar = false;

        fichero = new FileSQLCommandManager(fic);

        fichero.open();

        sql = fichero.read();

        cadenaSQL = "";

        for (int i = 0; i < sql.size(); i++) {

            cadenaSQL = sql.get(i);
            ejecutar = true;

            if (ejecutar) {                    

                st = null;
                st = conn.createStatement();
                res = st.executeUpdate(cadenaSQL);
                if (res == Statement.EXECUTE_FAILED) {
                    System.out.println("HA FALLADO LA CONSULTA " + cadenaSQL);
                }


            }

        }
                st.executeUpdate("SET FOREIGN_KEY_CHECKS=1");
                st.close();
        fichero.close();

        commit();
        desconectar();
        fichero = null;
        return true;

    } catch (Exception ex) {
        ex.printStackTrace();
        rollback();
        desconectar();
        return false;
    }

}

FileSQLCommandManager is a class that fills the ArrayList. This works, the ArrayList content is all right. It stops on executeUpdate of any query (not always, sometimes it works without problems WITH THE SAME SQL FILE).

First I disable the foreign key checks because it can drop a table with a reference (the order of recreation of tables is set by the SQL dump).

Any hint?

Thank's; I'm getting mad with this :(

davidrgh
  • 853
  • 2
  • 10
  • 17
  • Could be an out of memory issue. Check your task manager to see if the process uses a lot of memory. Increase the heap size of the JVM you are running with the -Xmx option. For example -Xmx2g sets the heap size to 2 Gb. – Adriaan Koster Dec 14 '12 at 10:20
  • Apparently there's no strange consumption of memory (nor cpu). – davidrgh Dec 14 '12 at 12:53
  • The 'ejecutar' variable has no effect on the execution. Also 'cadenaConstraints' and 'cadenaSQLConstraints' are not used anywhere. There is a mismatch between ResultSet 'rs' and 'res'. It looks like this is not the real code you are running and it is incomplete: what does FileSQLCommandManager look like? – Adriaan Koster Dec 14 '12 at 14:57
  • Yes, "ejecutar", "cadenaConstraints" and "cadenaSQLConstraints" are old variables that I don't use anymore, sorry. Same for "rs". Now I'm not at home, but I'll try to explain later how the FileSQLCommandManager reads the file and build the ArrayString. – davidrgh Dec 14 '12 at 15:39

2 Answers2

1

Why are you going through all that work, when a simple mysql < db_backup.dump will restore the whole thing for you?

hd1
  • 33,938
  • 5
  • 80
  • 91
  • I tried it (you can see it in http://stackoverflow.com/questions/13114282/restoring-mysql-dump-from-java-why-does-it-hang-the-process ) but the result was even worse (I'm executing it on Windows). – davidrgh Dec 14 '12 at 07:46
  • 1
    Did you try it from command line, instead of going through Java? – hd1 Dec 14 '12 at 07:47
  • From command line I've no problems (except if before I've done it by Java, because I need to restart the service), but I need to integrate it on a Java app. – davidrgh Dec 14 '12 at 08:00
  • Then, your problem is with Java and not SQL, @davidrgh. – hd1 Dec 14 '12 at 08:03
  • I guess that, but I'm not able to find any error on the code :S – davidrgh Dec 14 '12 at 12:54
  • Instead of `st = conn.createStatement();`, you might consider `st = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, *ResultSet.CONCUR_UPDATABLE*);` lest your statements only be read-only. If it works, leave a comment here, and I'll be happy to update my answer. – hd1 Dec 14 '12 at 14:52
  • All statements are "drop table", "create table" and "insert into"; so I guess that they mustn't be read-only (or am I wrong?). What about if I gather all statements of the file into a batch execution? Do you think would it solve anything? – davidrgh Dec 14 '12 at 15:37
  • @davidrgh per the [javadoc](http://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html): Result sets created using the returned Statement object will by default be type TYPE_FORWARD_ONLY and have a concurrency level of CONCUR_READ_ONLY – hd1 Dec 14 '12 at 15:41
  • Yes, but it doesn't returns a ResultSet, but a Integer (res is a int variable, not ResultSet). They're update queries. – davidrgh Dec 14 '12 at 15:44
  • Yes, but they still won't do anything unless the concurrency level is CONCUR_UPDATABLE. – hd1 Dec 14 '12 at 15:46
  • So, do you think that "conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, *ResultSet.CONCUR_UPDATABLE*);" could solve the problem? I can try it tonight – davidrgh Dec 14 '12 at 15:51
  • @davidrgh Please do so and report back – hd1 Dec 14 '12 at 15:52
  • It didn't work. However, I printed the sentences with System.out.println and I've seen that it stops while dropping a specific table; but didn't see nothing strange yet. Is there any way to see in that moment if any table is locked? – davidrgh Dec 15 '12 at 21:30
  • Mate, you run the command I provided from the cmd.exe prompt, not from Java. You say it works, what more do you need to do with the data? – hd1 Dec 15 '12 at 22:46
  • Maybe I didn't explain myself clearly. From cmd it worked fine, but I need to do that from a Java application (rather from a Struts web application) because it had to be one of the features of the application. However, I've found finally a pattern of the fail. By tracing it I discovered a previous proccess (on another part of the application) that opened a connection and didn't close it. I don't really see any relationship with the locked table, but when I added the disconnect command it started working. Thank's anywat for your time. – davidrgh Dec 16 '12 at 00:49
0

this really work for restore

 String comando = "C:\\MySQL\\bin\\mysql.exe  --host=localhost --port=3306 --user=root --password=123 < D:\\back.sql";
File f = new File("restore.bat");
FileOutputStream fos = new FileOutputStream(f);
fos.write(comando.getBytes());
fos.close();
Process run = Runtime.getRuntime().exec("cmd /C start restore.bat ");

and for backup

  DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd", Locale.forLanguageTag("ru"));
    java.util.Date currentDate = new java.util.Date();
    Process p = null;
    try {
        Runtime runtime = Runtime.getRuntime();
        p = runtime.exec("C:\\MySQL\\bin\\mysqldump.exe  --default-character-set=utf8 -uroot -p123 -c  -B shch2 -r " + "D:/" + dateFormat.format(currentDate) + "_backup" + ".sql");

//change the dbpass and dbname with your dbpass and dbname int processComplete = p.waitFor();

        if (processComplete == 0) {

            System.out.println("Backup created successfully!");

        } else {
            System.out.println("Could not create the backup");
        }


    } catch (Exception e) {
        e.printStackTrace();
    }

but you need to convey the exact path to mysql.exe and mysqldump.exe

Maik
  • 29
  • 1