I'm having issues loading/storing MySQL data, this is my code
public void loadPlayer(Player p) {
if (isPlayerInDataBase(p)) {
Bukkit.getScheduler().runTaskAsynchronously(Main.getInstance(), new Runnable() {
@Override
public void run() {
Connection connection = sql.getConnection();
try {
PreparedStatement select = connection
.prepareStatement("SELECT * FROM `MurderData` WHERE playername='" + p.getName() + "'");
ResultSet result = select.executeQuery();
if (getPlayerData(p) != null) {
while (result.next()) {
getPlayerData(p).setdeaths(result.getInt("deaths"));
getPlayerData(p).setkills(result.getInt("kills"));
getPlayerData(p).setwins(result.getInt("wins"));
getPlayerData(p).setlose(result.getInt("loses"));
getPlayerData(p).setscore(result.getInt("score"));
getPlayerData(p).setcoins(result.getInt("coins"));
}
CloseResultSet(result);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
});
} else {
Connection connection = sql.getConnection();
Bukkit.getScheduler().runTaskAsynchronously(Main.getInstance(), new Runnable() {
@Override
public void run() {
try {
PreparedStatement insert = connection.prepareStatement(
"INSERT INTO `MurderData` (playername, wins, deaths, loses, kills, coins, score) VALUES (?, ?, ?, ?, ?, ?, ?)");
insert.setString(1, p.getName());
insert.setInt(2, 0);
insert.setInt(3, 0);
insert.setInt(4, 0);
insert.setInt(5, 0);
insert.setInt(6, 0);
insert.setInt(7, 0);
insert.executeUpdate();
ClosePreparedStatement(insert);
} catch (SQLException e) {
e.printStackTrace();
}
}
public boolean isPlayerInDataBase(Player p) {
Connection connection = sql.getConnection();
try {
PreparedStatement select = connection
.prepareStatement("SELECT * FROM `MurderData` WHERE playername='" + p.getName() + "'");
ResultSet result = select.executeQuery();
if (result.next()) {
CloseResultSet(result);
return true;
}
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
So basically the question is, should i close prepared statements after updating MySQL data? and should i make that have a delay like close it after 5 seconds? can i optimize this code to make it better?
The is player in data base is it alright?
this is my 2 closing methods:
public void CloseResultSet(ResultSet s) {
new BukkitRunnable() {
@Override
public void run() {
if (s != null) {
try {
s.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}.runTaskLater(this, 20 * getConfig().getInt("close-sql-statements-after"));
}
public void ClosePreparedStatement(PreparedStatement s) {
new BukkitRunnable() {
@Override
public void run() {
if (s != null) {
try {
s.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}.runTaskLater(this, 20 * getConfig().getInt("close-sql-statements-after"));
}
Update: this is the second problem, first problem is the small lag spikes second problem is storing data in line 252
public void setSQLData(Player p, int kills, int deaths, int loses, int wins, int coins, int score) {
Bukkit.getScheduler().runTaskAsynchronously(plugin, new Runnable() {
@Override
public void run() {
Connection connection = plugin.sql.getConnection();
try {
PreparedStatement insert = connection.prepareStatement(
"INSERT INTO `MurderData` (playername, wins, deaths, loses, kills, coins, score) VALUES (?, ?, ?, ?, ?, ?, ?)");
insert.setString(1, p.getName());
insert.setInt(2, wins);
insert.setInt(3, deaths);
insert.setInt(4, loses);
insert.setInt(5, kills);
insert.setInt(6, coins);
insert.setInt(7, score);
insert.executeUpdate();// error line 252
plugin.ClosePreparedStatement(insert);
} catch (SQLException e) {
e.printStackTrace();
}
}
});
}
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at java.lang.reflect.Constructor.newInstance(Unknown Source)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3082)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2968)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3516)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2111)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2407)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2325)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2310)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at me.joseph.murder.api.MurderAPI$1.run(MurderAPI.java:252)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at org.bukkit.craftbukkit.v1_8_R3.scheduler.CraftTask.run(CraftTask.java:71)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at org.bukkit.craftbukkit.v1_8_R3.scheduler.CraftAsyncTask.run(CraftAsyncTask.java:53)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at java.lang.Thread.run(Unknown Source)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2529)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2979)
First of all:
As far as I can see, you are closing the connection every time after you executet a Query/Update. This is unfortunately quite imperformant. It would be better if opened a MySQL connection when the Plugin starts and close it again when the plugin stops.
I personally made it this way:(Doesn't mean you have to do it this way)
The connection variable:
private static Connection connection;
The connect function:
public static void connect(String host, String user, String password, String database) {
Bukkit.getScheduler().runTaskAsynchronously(Main.getInstance(), () -> {
//close Connection if still active
if (connection != null) {
close();
}
//connect to database host
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://" + host + "/" + database, user, password);
} catch (SQLException e) {
System.out.println(e.getMessage());
}
});
}
My function to update/write entries into the database:
public void Update(final String qry) {
Bukkit.getScheduler().runTaskAsynchronously(Main.getInstance(), () -> {
try {
Statement stnt = connection.createStatement();
stnt.executeUpdate(qry);
} catch (SQLException e) {
e.printStackTrace();
}
});
}
My function to query information from the database:
As you can see is this function not asynchronous. I unfortunately didn't managed it by now to get this function asynchronous. But you can easily workaround this by just making the function call asynchronous. ex:
Bukkit.getScheduler().runTaskAsynchronously(Main.getInstance(), () -> { Query("your query");
});
public ResultSet Query(String qry) {
ResultSet rs = null;
try {
Statement stnt = connection.createStatement();
rs = stnt.executeQuery(qry);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
The close function:
public static void close() {
Bukkit.getScheduler().runTaskAsynchronously(Main.getInstance(), () -> {
try {
connection.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
});
}
A disadvantage of this way is that you can only connect to one Database at a Time(which was in my case just fine).
Hope it helps you. I also struggled a lot with weird MySQL Errors. Fortunately with this code everything works just fine.
To answer your question:
[22:31:18] [Craft Scheduler Thread - 71/WARN]: Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
As far as I know this means that the connection to the MySQL Server is closed even though the plugin tries to use it. As mentioned above opening a connection at the start and leaving it open until the plugin stops should fix this.