Connection refused, i can't log in to my SQL-Server

[Markus Reis]

Hi all together.

I'm currently working on a web-application for university. First of all, the mySQL-Installation didnt work, which i fixed now. But as I try to run my code, i recieve the following exception:

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.net.SocketException
MESSAGE: java.net.ConnectException: Connection refused: connect

STACKTRACE:

java.net.SocketException: java.net.ConnectException: Connection refused: connect
	at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:156)
	at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:284)
	at com.mysql.jdbc.Connection.createNewIO(Connection.java:2569)
	at com.mysql.jdbc.Connection.<init>(Connection.java:1485)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at wochenplaner.ModelPlaner.<init>(ModelPlaner.java:28)
	at wochenplaner.ModelPlaner.main(ModelPlaner.java:333)


** END NESTED EXCEPTION **



Last packet sent to the server was 16 ms ago.
false
	at com.mysql.jdbc.Connection.createNewIO(Connection.java:2643)
	at com.mysql.jdbc.Connection.<init>(Connection.java:1485)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at wochenplaner.ModelPlaner.<init>(ModelPlaner.java:28)
	at wochenplaner.ModelPlaner.main(ModelPlaner.java:333)
java.lang.NullPointerException
	at wochenplaner.ModelPlaner.eventExists(ModelPlaner.java:300)
	at wochenplaner.ModelPlaner.main(ModelPlaner.java:334)

	package wochenplaner;
	import java.sql.*;

	public class ModelPlaner {
		String connectionURL = "jdbc:mysql://localhost:3310/wochenplaner";
		Connection connection = null;
		Statement statement = null;
			
		public ModelPlaner() {
			try {
				Class.forName("com.mysql.jdbc.Driver").newInstance();
			} catch (InstantiationException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				connection = DriverManager.getConnection(connectionURL, "root", "password");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		
		public String tableBuilder() {
			String table = "";
			
			String[] days = new String[7];
			days[0] = "Montag";
			days[1] = "Dienstag";
			days[2] = "Mittwoch";
			days[3] = "Donnerstag";
			days[4] = "Freitag";
			days[5] = "Samstag";
			days[6] = "Sonntag";
			
			int[] times = new int[14];
			int temp = 7;
			for (int i = 0; i < times.length; i++) {
				times[i] = temp;
				temp++;
			}
			
			for (int i = 0; i < times.length; i++) {
				table += "<tr><th>" +times[i] +":00</th>";
				for (int j = 0; j < days.length; j++) {
					table += "<td><a href=\"editDeleteEventPopup.jsp?tag=" +days[j] +"&zeit=" +times[i] +"\" target=\"_blank\" onclick=\"return popup(this.href);\">"+getVName(days[j], times[i]) +"</a></td>";
				}
				table += "</tr>";
			}
			
			return table;
		}
		
		
		public String getVName(String day, int time) { // gibt den Veranstaltungsnamen anhand Tag und Uhrzeit zurück
			String VName = null;
			try {
				ResultSet rs = null;
				statement = connection.createStatement();
				rs = statement.executeQuery("SELECT VName FROM veranstaltungen WHERE ID = " +getID(day, time));
				while(rs.next()) {
					VName = rs.getString("VName");
				}
				rs.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
			if (VName == null) {
				return "";
			}
			else {
				return VName;
			}
		}
		
		public String getVName(int ID) { // gibt den Veranstaltungsnummer anhand der ID zurück
			String VName = null;
			try {
				ResultSet rs = null;
				statement = connection.createStatement();
				rs = statement.executeQuery("SELECT VName FROM veranstaltungen WHERE ID = "+ID);
				while(rs.next()) {
					VName = rs.getString("VName");
				}
				rs.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
			return VName;
		}
		
		public String getDescrpt(String day, int time) { // gibt die Beschreibung einer Veranstaltung anhand Tag und Uhrzeit zurück
			String Descrpt = null;
			try {
				ResultSet rs = null;
				statement = connection.createStatement();
				rs = statement.executeQuery("SELECT Beschreibung FROM veranstaltungen WHERE ID = " +getID(day, time));
				while(rs.next()) {
					Descrpt = rs.getString("Beschreibung");
				}
				rs.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
			if (Descrpt == null) {
				return "";
			}
			else {
				return Descrpt;
			}
		}
		
		public boolean VNameExists(String name) { // findet heraus, ob der Veranstaltungsname bereits existiert
			String VName = null;
			try {
				ResultSet rs = null;
				statement = connection.createStatement();
				rs = statement.executeQuery("SELECT VName FROM veranstaltungen WHERE VName = '" +name +"'");
				while(rs.next()) {
					VName = rs.getString("VName");
				}
				rs.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
			if (VName == null) {
				return false;
			}
			else {
				return true;
			}
		}
		
		public int getLastID() { // gibt die zuletzt benutzte Veranstaltungs-ID zurück
			int maximum = 0;
			try {
				ResultSet rs = null;
				statement = connection.createStatement();
				rs = statement.executeQuery("SELECT ID FROM veranstaltungen WHERE ID = ( SELECT max(ID) FROM veranstaltungen )");
				while(rs.next()) {
					maximum = rs.getInt("ID");
				}
				rs.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
			return maximum;
		}
		
		public int getID(String day, int time) { // gibt die ID einer Veranstaltung anhand dem Tag und der Uhrzeit aus der Zuordnungstabelle zurück
			int ID = -1;
			try {
				ResultSet rs = null;
				statement = connection.createStatement();
				rs = statement.executeQuery("SELECT ID FROM zuordnung WHERE Tag = '" +day +"' AND Zeit =" +time);
				while(rs.next()) {
					ID = rs.getInt("ID");
				}
				rs.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
			return ID;
		}
		
		public int getID(String VName) { //overloading: gibt die ID einer Veranstaltung anhand des Veranstaltungsnamens zurück
			int ID = 0;
			try {
				ResultSet rs = null;
				statement = connection.createStatement();
				rs = statement.executeQuery("SELECT ID FROM veranstaltungen WHERE VName = '" +VName +"'");
				while(rs.next()) {
					ID = rs.getInt("ID");
				}
				rs.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
			return ID;
		}
		
		public void addEvent(String VName, String descr, String day, int time) { // fügt eine volle Veranstaltung neu in die Datenbank mit der Zuordnung ein
			int incrLastID = getLastID()+1;
			Statement statement;
			try {
				statement = connection.createStatement();
				int result = statement.executeUpdate("INSERT INTO veranstaltungen VALUES("+incrLastID +", '"+VName +"', '"+descr +"')");   
				statement.close();
				addHelp(incrLastID, day, time);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		public void addHelp (int ID, String day, int time) { // Hilfsfunktion zu addEvent
			Statement statement;
			try {
				statement = connection.createStatement();
				int result = statement.executeUpdate("INSERT INTO zuordnung VALUES("+ID +", '"+day +"', "+time +")");   
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		public int countEvent(int ID) { // zählt, wie oft die Veranstaltung zu einem Tag / einer Uhrzeit zugeordnet wurde
			int cnt = 0;
			try {
				ResultSet rs = null;
				statement = connection.createStatement();
				rs = statement.executeQuery("SELECT ID, COUNT(*) FROM zuordnung WHERE ID = " +ID +" GROUP BY ID");
				while(rs.next()) {
					cnt = rs.getInt("COUNT(*)");
				}
				rs.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
			return cnt;
		}

		public void deleteEventFromZuordnung (int ID, String day, int time) { // löscht einen Terin aus der Zuordungstabelle
			Statement statement;
			try {
				statement = connection.createStatement();
				int result = statement.executeUpdate("DELETE FROM zuordnung WHERE ID = " +ID +" AND Tag = '" +day +"' AND Zeit = " +time);   
				statement.close();
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		public void deleteEventFromVeranstaltungen (int ID) { // löscht eine komplette Veranstaltung aus der Veranstaltungstabelle
			Statement statement;
			try {
				statement = connection.createStatement();
				int result = statement.executeUpdate("DELETE FROM veranstaltungen WHERE ID = " +ID);   
				statement.close();
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		public void updateVeranstaltung(int ID, String VName, String descrpt) { // bearbeitet den VNamen und die Beschreibung einer Veranstaltung mithilfe der übergebenen ID und den vorherigen Daten
			Statement statement;
			try {
				statement = connection.createStatement();
				int result = statement.executeUpdate("UPDATE veranstaltungen SET VName = '" +VName +"', Beschreibung = '" +descrpt +"' WHERE ID = " +ID);   
				statement.close();
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		public void updateZuordnung(int ID, String day, int time, String alt_day, int alt_time) { // bearbeitet die Zuordnung einer ID zu einem neuen Tag und einer neuen Zeit
			Statement statement;
			try {
				statement = connection.createStatement();
				int result = statement.executeUpdate("UPDATE zuordnung SET Tag = '" +day +"', Zeit = " +time +" WHERE ID = " +ID +" AND Tag = '" +alt_day +"' AND Zeit = " +alt_time);   
				statement.close();
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		public boolean eventExists(String day, int time) { // gibt zurück, ob in der Zuordnungstabelle bereits eine Veranstaltung zu dieser Zeit existiert (für Fehlermeldungen)
			int cnt = 0;
			try {
				ResultSet rs = null;
				statement = connection.createStatement();
				rs = statement.executeQuery("SELECT ID, COUNT(*) FROM zuordnung WHERE Tag = '" +day +"' AND Zeit = " +time  +" GROUP BY ID");
				while(rs.next()) {
					cnt = rs.getInt("COUNT(*)");
				}
				rs.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
			if (cnt == 0) {
				return false;
			}
			else {
				return true;
			}
		}
		
		public static void main(String[] args) {
			ModelPlaner mp = new ModelPlaner();
			System.out.println(mp.eventExists("Freitag",15));
		}
		
	}

As I found out by checking different ports, this error always occurs, even if there's no server running, so I guess the server is not listening, but why?
Netstat looks like this, so I think the server is running:


Btw, if I'm trying via browser:


Moreover, if I start mySQL from xampp (i run it on 3306 there), EVERY single port I try says "access denied" (and yes I'm sure the password isnt wrong ;D), also the ones where is no server running as well as 3306 itself.

So first of all: How do I get on either the SQL-Workbench started server or the xampp server?
And second: Where is even the difference? Does xampp provide an own sql server or does it start one from the software i installed??

Would be awesome if someone could help, google didnt help me at all yet and I start to retire

best regards
markus

[K. Tsang]

Welcome to the Ranch

You mentioned you are using port 3310, but MySQL default port is 3306.

You mentioned you have XAMP, if XAMP started can you access MYSQL?

One way to check is indeed using netstat. If you don't use netstat you can use telnet <host> <port>

Another approach is use the command line to access MySQL.

[Markus Reis]

Hey there, thanks

I use port 3310 due to this Youtube Tutorial as I also had installation troubles. He then uses 3310 for fixing the problem. But on 3306 XAMPP now is running, and if I go to localhost on the browser xampp site opens, and I can also access to phpMyAdmin and create tables and databases etc, and the password there is the same as i try to use in my code. If I try to access 3306 (so the one from xampp) i get the error:

java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:798)
	at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3700)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1203)
	at com.mysql.jdbc.Connection.createNewIO(Connection.java:2572)
	at com.mysql.jdbc.Connection.<init>(Connection.java:1485)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at wochenplaner.ModelPlaner.<init>(ModelPlaner.java:28)
	at wochenplaner.ModelPlaner.main(ModelPlaner.java:333)
java.lang.NullPointerException
	at wochenplaner.ModelPlaner.eventExists(ModelPlaner.java:300)
	at wochenplaner.ModelPlaner.main(ModelPlaner.java:334)
false

(if i open localhost:3306 in browser i get "J���
5.5.27�š���tL^sJTIQ�ÿ÷�€����������<wep(q%`4%Wj�mysql_native_password�!��
ÿ„#08S01Got packets out of order)

So imo the server is running, isnt it? But why cant I contact it

[Markus Reis]

btw. if I test with telnet I get the same gibberish and native password stuff as if I connect via browser

[Paul Clapham]

This error message

java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)

confirms that you are connecting to the server, so you can stop testing for that. It is telling you that you have not configured MySQL to accept connections from the 'root' user, or that you have used the wrong password, or something like that.

[Markus Reis]

yep thats true. But the password and user are correct, because on shell they work.

So what else could it be? How can I accept connections?

[Paul Clapham]

You actually have to configure that in MySQL somehow. It's so many years ago that I did it, I forget how, but if I recall correctly, it isn't enough to just set up a password for the user ID. You also have to configure something which tells MySQL it's okay to accept connections from that user ID and from a particular host, or from all hosts (I think).

[Markus Reis]

Ok, I dont know exactly what I did wrong before, but I followed the steps by this Tutorial (using my Files) and now it runs the program on localhost. Whatever I did wrong before or whoever else has the same problem, check out that link