SQL-Dump nach MariaDB laden?

Hallo zusammen

Folgende Codes verursachen Fehler bei mir:

package main;

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class MySQLImport {
	public static void main(final String[] args) throws Exception {
		if (args == null || args.length == 0) {
			System.out.println("Please specify path");
			System.exit(0);
		} else {
			if (args == null || args.length == 1) {
				System.out.println("Please specify username");
				System.exit(0);
			} else {
				if (args == null || args.length == 2) {
					System.out.println("Please specify password");
					System.exit(0);
				} else {
					if (args == null || args.length == 3) {
						System.out.println("Please specify host");
						System.exit(0);
					} else {
						if (args.length == 4) {
							String path = args[0];
							String username = args[1];
							String password = args[2];
							String host = args[3];
							Connection connection = DriverManager.getConnection("jdbc:mariadb://" + host
									+ ":3306/mysql?user=" + username + "&password=" + password);

							try (BufferedReader br = new BufferedReader(new FileReader(path))) {
								StringBuilder sb = new StringBuilder();
								String line = br.readLine();

								while (line != null) {
									sb.append(line);
									sb.append(System.lineSeparator());
									line = br.readLine();
								}

								String everything = sb.toString();
								Statement stmt = connection.createStatement();

								// stmt.execute(everything);

								stmt.executeQuery(everything);

								stmt.close();
								connection.close();
							}
						} else {
							System.out.println("Too many arguments: " + args.length);
						}
					}
				}
			}
		}
	}
}

Ausgabe:

Exception in thread "main" java.sql.SQLSyntaxErrorException: (conn=24) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DROP DATABASE IF EXISTS relay;

	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:238)
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:171)
	at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:243)
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:332)
	at org.mariadb.jdbc.MariaDbStatement.executeQuery(MariaDbStatement.java:506)
	at main.MySQLImport.main(MySQLImport.java:50)
Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DROP DATABASE IF EXISTS relay;
CREATE DATABASE IF NOT EXISTS relay' at line 2
Query is: SET SQL_NOTES = 0;
DROP DATABASE IF EXISTS relay;
CREATE DATABASE IF NOT EXISTS relay;

java thread: main
	at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:121)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:233)
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:326)
	... 2 more

SQL, welches ich versuche reinzuladen:

SET SQL_NOTES = 0;
DROP DATABASE IF EXISTS relay;
CREATE DATABASE IF NOT EXISTS relay;

…scheinbar ist es nicht fähig mit SET SQL_NOTES = 0; richtig umzugehen. Warum?

HeidiSQL kann das ja schliesslich auch (libmariadb), wo sollte also das Problem liegen?

Problem gelöst…

@jmar83:
Da wäre es toll, wenn du noch schreibst, wie du es gelöst hast, denn es werden sicher Leute per Google oder anderweitig hier landen und sich freuen, wenn sie den richtigen Tipp erhalten.

1 Like
package main;

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class MySQLImport {

	private MySQLImport(final String[] args) throws Exception {
		if ((args == null) || (args.length == 0)) {
			System.out.println("Please set host");
			System.exit(-1);
		} else {
			if ((args == null) || (args.length == 1)) {
				System.out.println("Please set username");
				System.exit(-1);
			} else {
				if ((args == null) || (args.length == 2)) {
					System.out.println("Please set password");
					System.exit(-1);
				} else {
					if ((args == null) || (args.length == 3)) {
						System.out.println("Please set path");
						System.exit(-1);
					} else {
						if (args.length == 4) {
							final String host = args[0];
							final String username = args[1];
							final String password = args[2];
							final String path = args[3];
							final Connection conn = DriverManager
									.getConnection("jdbc:mariadb://" + host + ":3306/mysql?user=" + username
											+ "&password=" + password + "&allowMultiQueries=true");

							try (BufferedReader br = new BufferedReader(new FileReader(path))) {
								final StringBuilder sb = new StringBuilder();
								String line = br.readLine();

								while (line != null) {
									sb.append(line);
									sb.append(System.lineSeparator());
									line = br.readLine();
								}

								String everything = sb.toString();

								// Remove delimiters {
								everything = everything.replace("DELIMITER ||", "");
								everything = everything.replace("||", "");
								everything = everything.replace("DELIMITER //", "");
								everything = everything.replace("//", "");
								everything = everything.replace("DELIMITER ;", "");
								// } Remove delimiters

								final Statement stmt = conn.createStatement();

								try {
									conn.setAutoCommit(false);
									stmt.execute(everything);
									conn.commit();
								} catch (final SQLException se) {
									conn.rollback();
									System.out.println(se.getMessage());
									System.exit(-1);
								}

								stmt.close();
								conn.close();
							}
						} else {
							System.out.println("Too many arguments: " + args.length);
							System.exit(-1);
						}
					}
				}
			}
		}
	}

	public static void main(final String[] args) throws Exception {
		new MySQLImport(args);
	}
}

allowMultiQueries=true hat mir das Problem gelöst. (Das Programm mag strukturell evtl. nicht ganz perfekt sein, läuft aber problemlos…)

GrĂĽsse, Jan

1 Like