SQL-Dump nach MariaDB laden?

jmar83 · 29. Oktober 2019 um 08:43

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?

jmar83 · 29. Oktober 2019 um 09:02

Problem gelöst…

Crian · 30. Oktober 2019 um 08:25

@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.

jmar83 · 30. Oktober 2019 um 09:28

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

jmar83 · 9. Februar 2021 um 17:39

Hier noch eine überarbeitete Version, musste das Thema heute wieder mal aufgreifen…

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 {

private MySQLImport(final String[] caArgArr) throws Exception {
	if ((caArgArr == null) || (caArgArr.length == 0)) {
		System.out.println("No host set...");
		System.exit(-1);
	} else {
		if ((caArgArr == null) || (caArgArr.length == 1)) {
			System.out.println("No username set...");
			System.exit(-1);
		} else {
			if ((caArgArr == null) || (caArgArr.length == 2)) {
				System.out.println("No password set...");
				System.exit(-1);
			} else {
				if ((caArgArr == null) || (caArgArr.length == 3)) {
					System.out.println("No dump file path set...");
					System.exit(-1);
				} else {
					if (caArgArr.length == 4) {
						int lCounter = 0;
						final String clHost = caArgArr[lCounter++];
						final String clUsername = caArgArr[lCounter++];
						final String clPassword = caArgArr[lCounter++];
						final String clPath = caArgArr[lCounter++];
						Class.forName(org.mariadb.jdbc.Driver.class.getName());
						final Connection clConnection = DriverManager.getConnection("jdbc:mariadb://" + clHost + ":3306/mysql?user=" + clUsername + "&password=" + clPassword + "&allowMultiQueries=true");

						try (BufferedReader lBufferedReader = new BufferedReader(new FileReader(clPath))) {
							final StringBuilder clStringBuilder = new StringBuilder();
							String lLine = lBufferedReader.readLine();

							while (lLine != null) {
								clStringBuilder.append(lLine);
								clStringBuilder.append(System.lineSeparator());
								lLine = lBufferedReader.readLine();
							}

							String lEverything = clStringBuilder.toString();
							lEverything = lEverything.replace("DELIMITER ||", "");
							lEverything = lEverything.replace("||", "");
							lEverything = lEverything.replace("DELIMITER //", "");
							lEverything = lEverything.replace("//", "");
							lEverything = lEverything.replace("DELIMITER ;", "");

							final Statement clStatement = clConnection.createStatement();

							try {
								clConnection.setAutoCommit(false);
								clStatement.execute(lEverything);
								clConnection.commit();
							} catch (final Exception clException) {
								clConnection.rollback();
								System.out.println(clException.getClass().getName() + ": " + clException.getMessage());
								System.exit(-1);
							} finally {
								try {
									if (clConnection != null) {
										clConnection.close();
									}
									if (clStatement != null) {
										clStatement.close();
									}
									if (lBufferedReader != null) {
										lBufferedReader.close();
									}
								} catch (final Exception clException) {
									final Runtime clRuntime = Runtime.getRuntime();
									clRuntime.gc();
								}
							}
						}
					} else {
						System.out.println("Too many arguments: " + caArgArr.length);
						System.exit(-1);
					}
				}
			}
		}
	}
}

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

}