Fix for OdinMS database crash

[whoopdedoop]

how this works:
- at startup, load the user, pass and url for database
- set a default timeout (5 minutes)
- connect to db and send a request for wait_timeout
- set timeout to returned timeout
- when the method getconnection is called, the system validates the connection before returning

public class DatabaseConnection {
	private static final Logger log = LoggerFactory.getErrLogger();
	private static final HashMap<Integer, ConWrapper> connections =
			new HashMap();
    private static String dbDriver, dbUrl, dbUser, dbPass;
	private static boolean propsInited = false;
	private static long connectionTimeOut = 5 * 60 * 1000; // 5 minutes

    private DatabaseConnection() {}

    public static Connection getConnection() {
		Thread cThread = Thread.currentThread();
		int threadID = (int) cThread.getId();
		ConWrapper ret = connections.get(threadID);

		if (ret == null) {
			Connection retCon = connectToDB();
			ret = new ConWrapper(retCon);
			ret.id = threadID;
			connections.put(threadID, ret);
			log.info("[Database] Thread [" + threadID + "] has created a new Database Connection.");
		}

		return ret.getConnection();
    }

	private static long getWaitTimeout(Connection con) {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("SHOW VARIABLES LIKE 'wait_timeout'");
			if (rs.next()) {
				return Math.max(1000, rs.getInt(2) * 1000 - 1000);
			} else {
				return -1;
			}
		} catch (SQLException ex) {
			return -1;
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException ex) {
				} finally {
					if (rs != null) {
						try {
							rs.close();
						} catch (SQLException ex1) {}
					}
				}
			}
		}
	}

	private static Connection connectToDB() {
		if (!propsInited) {
			PropertyReader dbReader;

			try {
				dbReader = PropertyReader.load("db.cfg");
			} catch (IOException ex) {
				throw new DatabaseException(ex);
			}

			dbDriver = dbReader.getProperty("driver");
			dbUrl = dbReader.getProperty("url");
			dbUser = dbReader.getProperty("user");
			dbPass = dbReader.getProperty("password");
			try {
				connectionTimeOut = Long.parseLong(dbReader.getProperty("timeout"));
			} catch (Exception e) {
				log.warn("[Database] Cannot read Connection Timeout Information, using default: " + connectionTimeOut);
			}
		}

		try {
			Class.forName(dbDriver);    // touch the MySQL driver
		} catch (ClassNotFoundException e) {
			throw new DatabaseException(e);
		}

		try {
			Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPass);
			if (!propsInited) {
				long timeout = getWaitTimeout(con);
				if (timeout == -1) {
					log.error("[Database] Failed to read Wait_Timeout, using " + connectionTimeOut + " instead.");
				} else {
					connectionTimeOut = timeout;
					log.info("[Database] Timeout is " + connectionTimeOut + " milliseconds.");
				}
				propsInited = true;
			}
			return con;
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}
	}

	static class ConWrapper {
		private long lastAccessTime = 0;
		private Connection connection;
		private int id;

		public ConWrapper(Connection con) {
			this.connection = con;
		}

		public Connection getConnection() {
			if (expiredConnection()) {
				log.info("[Database] Connection #" + id + " has gone stale. Reconnecting..");
				try { // Assume that the connection is stale
					connection.close();
				} catch (Throwable err) {
					// Who cares
				}
				this.connection = connectToDB();
			}

			lastAccessTime = System.currentTimeMillis(); // Record Access
			return this.connection;
		}

		/**
		 * Returns whether this connection has expired
		 * @return
		 */
		public boolean expiredConnection() {
			if (lastAccessTime == 0) {
				return false;
			}
			try {
				return System.currentTimeMillis() - lastAccessTime >= connectionTimeOut || connection.isClosed();
			} catch (Throwable ex) {
				return true;
			}
		}
	}

    public static void closeAll() throws SQLException {
        for (ConWrapper con : connections.values()) {
            con.connection.close();
        }
		connections.clear();
    }
}

have fun..

edit: stop complaining about the import problems. if you can't fix it so there are no errors, you probably shouldnt be using it

[MentaL]

[CioNide]

Didn't see any exploits.
Looks good, what exactly would this be used for?

I've never heard of MySQL DB crashing.

[whoopdedoop]

this fixes the need to use ?autoReconnect=true in the database file, meaning servers can now stay up for months without the database connection timing out, as opposed to hours before.

[CioNide]

So it's just an modified DatabaseConnection.java?
:3

[whoopdedoop]

So it's just an modified DatabaseConnection.java?
:3

this is the same file that renoria's server uses, I just released it to help the ms community after being down in shit for so long, and of course i wouldnt put exploits in it, i have no interest in maplestory private servers.

[FateJiki]

So it's just an modified DatabaseConnection.java?
:3

Read the class name and there's your answer.

Basically what this does is that normally threads would time-out on the MySQL Server , thus eventually crashing the entire SQL(They build up over time). If I read correctly, this should remove any un-used active threads, without manually having to kill them or add a wait_timeout.

[whoopdedoop]

Read the class name and there's your answer.

Basically what this does is that normally threads would time-out on the MySQL Server , thus eventually crashing the entire SQL(They build up over time). If I read correctly, this should remove any un-used active threads, without manually having to kill them or add a wait_timeout.

no you're wrong, this system restarts any threads that did time out before returning the pointer to the active connection, in other words it makes sure the conneciton is valid before returning it

[FateJiki]

no you're wrong, this system restarts any threads that did time out before returning the pointer to the active connection, in other words it makes sure the conneciton is valid before returning it

Understood.

[King Grub]

Thank you David

I just released it to help the ms community

I really appreciate it and i'm sure others do too.

[rice]

Wow, a good release.

[LightPepsi]

opps....

[iChappie]

leeched, this is released months ago.

Got proof?

[wietse02]

Thank you!

[Sjogern93]

LightPepsi shut up. Don't bash anything into his face without providing some kind of proof at the same time. Your opinion doesn't matter without proof anyway.

Good release, won't this remove some minor lag issues? Seeing if the old method just stacks timeouts, that would create lag, correct?

[bagger]

thx for this... hopefully this will solve most or at least a lot of the database problems hehe