MySQL causing lagg?

Code:

package me.CraftBang;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
 
 
import org.bukkit.ChatColor;
import org.bukkit.entity.Player;
 
public class Sql {
private static Connection connection;
public static void disable(){
try {
if(connection != null && !connection.isClosed())
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
 
public synchronized static void openConnection(){
try{
connection = DriverManager.getConnection("jdbc:mysql://ip:3306/dbname" ,"login", "pw");
}catch(Exception e){
e.printStackTrace();
}
}
public synchronized static void closeConnection(){
try{
connection.close();
}catch(Exception e){
e.printStackTrace();
}
}
 
public static void registerdefaultacc(Player p){
try{
PreparedStatement newPlayer = connection.prepareStatement("INSERT INTO `player_data` values(?,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);");
newPlayer.setString(1, p.getName());
newPlayer.execute();
newPlayer.close();
}catch(Exception e){
e.printStackTrace();
}
}
 
public synchronized static boolean playerDataContainsPlayer (Player p){
try{
PreparedStatement sql = connection.prepareStatement("SELECT * FROM `player_data` WHERE player=?");
sql.setString(1, p.getName());
ResultSet resultSet = sql.executeQuery();
boolean containsPlayer = resultSet.next();
sql.close();
resultSet.close();
return containsPlayer;
}catch(Exception e){
e.printStackTrace();
return false;
}
}
 
public static int get(Player p, String what){
try{
if(playerDataContainsPlayer(p)){
PreparedStatement sql = connection.prepareStatement("SELECT " + what + " FROM `player_data` WHERE player=?");
sql.setString(1, p.getName());
 
ResultSet result = sql.executeQuery();
result.next();
return(result.getInt(what));
}
}catch (Exception e){
e.printStackTrace();
}
return 0;
 
}
 
public static void give(Player p, int i, String what){
openConnection();
if(what == "xp"){
SqlXP.giveXP(p, i);
return;
}
try{
if(playerDataContainsPlayer(p)){
int i2 = get(p, what) + i;
PreparedStatement updatewhat = connection.prepareStatement("UPDATE player_data SET " + what + "='" + i2 + "' WHERE player='" + p.getName() +"'");
updatewhat.executeUpdate();
updatewhat.close();
}else{
p.sendMessage(ChatColor.RED + "Use /regforum to register on our forums to save your Stats!");
p.sendMessage(ChatColor.RED + "If you did register than it's our fault!");
}
}catch (Exception e){
e.printStackTrace();
}
}
 
}

So on startup I do Sql.openConnection();
I use this code to give player like kills when they kill someone :

Sql.give(killer, 1, "killsbow");
This causes lagg. Why does it do this? Is this fixable?

SECOND PROBLEM :
My command /myxp after first restart it works fine, after like 1 min or something it gives an error :

Code:

 
The last packet successfully received from the server was 57,375 milliseconds ag
o.  The last packet sent successfully to the server was 0 milliseconds ago.
[16:54:31 WARN]:        at sun.reflect.GeneratedConstructorAccessor113.newInstan
ce(Unknown Source)
[16:54:31 WARN]:        at sun.reflect.DelegatingConstructorAccessorImpl.newInst
ance(Unknown Source)
[16:54:31 WARN]:        at java.lang.reflect.Constructor.newInstance(Unknown Sou
rce)
[16:54:31 WARN]:        at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
[16:54:31 WARN]:        at com.mysql.jdbc.SQLError.createCommunicationsException
(SQLError.java:1116)
[16:54:31 WARN]:        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.jav
a:3082)
[16:54:31 WARN]:        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.jav
a:2968)
[16:54:31 WARN]:        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:
3516)
[16:54:31 WARN]:        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
 
[16:54:31 WARN]:        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:21
40)
[16:54:31 WARN]:        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.
java:2626)
[16:54:31 WARN]:        at com.mysql.jdbc.PreparedStatement.executeInternal(Prep
aredStatement.java:2111)
[16:54:31 WARN]:        at com.mysql.jdbc.PreparedStatement.executeQuery(Prepare
dStatement.java:2273)
[16:54:31 WARN]:        at me.CraftBang.SqlXP.playerDataContainsPlayer(SqlXP.jav
a:45)
[16:54:31 WARN]:        at me.CraftBang.SqlXP.getXP(SqlXP.java:58)
[16:54:31 WARN]:        at me.CraftBang.Stats.onCommand(Stats.java:18)
[16:54:31 WARN]:        at org.bukkit.command.PluginCommand.execute(PluginComman
d.java:44)
[16:54:31 WARN]:        at org.bukkit.command.SimpleCommandMap.dispatch(SimpleCo
mmandMap.java:196)
[16:54:31 WARN]:        at org.bukkit.craftbukkit.v1_7_R1.CraftServer.dispatchCo
mmand(CraftServer.java:542)
[16:54:31 WARN]:        at net.minecraft.server.v1_7_R1.PlayerConnection.handleC
ommand(PlayerConnection.java:932)
[16:54:31 WARN]:        at net.minecraft.server.v1_7_R1.PlayerConnection.a(Playe
rConnection.java:814)
[16:54:31 WARN]:        at net.minecraft.server.v1_7_R1.PacketPlayInChat.a(Packe
tPlayInChat.java:28)
[16:54:31 WARN]:        at net.minecraft.server.v1_7_R1.PacketPlayInChat.handle(
PacketPlayInChat.java:47)
[16:54:31 WARN]:        at net.minecraft.server.v1_7_R1.NetworkManager.a(Network
Manager.java:146)
[16:54:31 WARN]:        at net.minecraft.server.v1_7_R1.ServerConnection.c(Sourc
eFile:134)
[16:54:31 WARN]:        at net.minecraft.server.v1_7_R1.MinecraftServer.u(Minecr
aftServer.java:655)
[16:54:31 WARN]:        at net.minecraft.server.v1_7_R1.DedicatedServer.u(Dedica
tedServer.java:250)
[16:54:31 WARN]:        at net.minecraft.server.v1_7_R1.MinecraftServer.t(Minecr
aftServer.java:545)
[16:54:31 WARN]:        at net.minecraft.server.v1_7_R1.MinecraftServer.run(Mine
craftServer.java:457)
[16:54:31 WARN]:        at net.minecraft.server.v1_7_R1.ThreadServerApplication.
run(SourceFile:617)
[16:54:31 WARN]: Caused by: java.io.EOFException: Can not read response from ser
ver. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly l
ost.
[16:54:31 WARN]:        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2529)
[16:54:31 WARN]:        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.jav
a:2979)
[16:54:31 WARN]:        ... 24 more

LINES IN ERROR :
at me.CraftBang.SqlXP.playerDataContainsPlayer(SqlXP.jav
a:45)

Code:

ResultSet resultSet = sql.executeQuery();

[16:54:31 WARN]: at me.CraftBang.SqlXP.getXP(SqlXP.java:58)

Code:

if(playerDataContainsPlayer(p)){

[16:54:31 WARN]: at me.CraftBang.Stats.onCommand(Stats.java:18)

Code:

sender.sendMessage(ChatColor.DARK_PURPLE + "Your XP is : " + SqlXP.getXP((Player) sender);

sender.sendMessage(ChatColor.DARK_PURPLE + "Your XP is : " + SqlXP.getXP((Player) sender);

Code :

Code:

package me.CraftBang;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
 
 
 
import org.bukkit.ChatColor;
import org.bukkit.entity.Player;
 
public class SqlXP {
private static Connection connection;
public static void disable(){
try {
if(connection != null && !connection.isClosed())
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
 
public synchronized static void openConnection(){
try{
connection = DriverManager.getConnection("jdbc:mysql://ip:3306/dbname" ,"login", "pw");
}catch(Exception e){
e.printStackTrace();
}
}
public synchronized static void closeConnection(){
try{
connection.close();
}catch(Exception e){
e.printStackTrace();
}
}
 
public synchronized static boolean playerDataContainsPlayer (Player p){
try{
PreparedStatement sql = connection.prepareStatement("SELECT * FROM `xf_user` WHERE username=?");
sql.setString(1, p.getName());
ResultSet resultSet = sql.executeQuery();
boolean containsPlayer = resultSet.next();
sql.close();
resultSet.close();
return containsPlayer;
}catch(Exception e){
e.printStackTrace();
return false;
}
}
 
public static int getXP(Player p){
try{
if(playerDataContainsPlayer(p)){
String what = "credits";
PreparedStatement sql = connection.prepareStatement("SELECT " + what + " FROM `xf_user` WHERE username='" + p.getName() + "'");
ResultSet result = sql.executeQuery();
result.next();
return(result.getInt(what));
}
}catch (Exception e){
e.printStackTrace();
}
return 0;
 
}
 
public static void giveXP(Player p, int i){
openConnection();
try{
if(playerDataContainsPlayer(p)){
String what = "credits";
int i2 = getXP(p) + i;
PreparedStatement updatewhat = connection.prepareStatement("UPDATE xf_user SET " + what + "='" + i2 + "' WHERE username='" + p.getName() +"'");
updatewhat.executeUpdate();
updatewhat.close();
p.sendMessage(ChatColor.GREEN + "You received " + i + " XP!");
}else{
p.sendMessage(ChatColor.RED + "Use /regforum to register on our forums to save your Stats!");
p.sendMessage(ChatColor.RED + "If you did register than it's our fault!");
}
}catch (Exception e){
e.printStackTrace();
}
}
 
}
 

Please help me ,

Regards,

CB

 

It needs to connect to your mysql database, and pull data from it.
That costs time, and the longer it takes.
The more lagg you'll get.
Use a scheduled event to do this, it will take longer for the player you are pulling data for.
But the server will continue while you are doing this.

 

teunie75 like this ? :

Code:

 
 
public static void giveXP(final Player p, final int i){
Bukkit.getScheduler().scheduleSyncDelayedTask(Main.plugin, new Runnable(){
  public void run() {
openConnection();
try{
if(playerDataContainsPlayer(p)){
String what = "credits";
int i2 = getXP(p) + i;
PreparedStatement updatewhat = connection.prepareStatement("UPDATE xf_user SET " + what + "='" + i2 + "' WHERE username='" + p.getName() +"'");
updatewhat.executeUpdate();
updatewhat.close();
p.sendMessage(ChatColor.GREEN + "You received " + i + " XP!");
}else{
p.sendMessage(ChatColor.RED + "Use /regforum to register on our forums to save your Stats!");
p.sendMessage(ChatColor.RED + "If you did register than it's our fault!");
}
}catch (Exception e){
e.printStackTrace();
}
  }
  }
  , 0L); // 20L = 1
}

It'll make everything to final, does that really matter?
Thanks al ot I was also thinking about that, but than I came stuck somewhere:

This one

Code:

public static int getXP(Player p){
Bukkit.getScheduler().scheduleSyncDelayedTask(Main.plugin, new Runnable(){
  public void run() {
openConnection();
try{
if(playerDataContainsPlayer(p)){
String what = "credits";
PreparedStatement sql = connection.prepareStatement("SELECT " + what + " FROM `xf_user` WHERE username='" + p.getName() + "'");
ResultSet result = sql.executeQuery();
result.next();
return(result.getInt(what));
}
}catch (Exception e){
e.printStackTrace();
}
return 0;
  }
}
  , 0L); // 20L = 1
 
}

I get lots of errors :

DescriptionResourcePathLocationType
Void methods cannot return a valueSqlXP.java/CraftBang/src/me/CraftBangline 68Java Problem
Void methods cannot return a valueSqlXP.java/CraftBang/src/me/CraftBangline 73Java Problem
This method must return a result of type intSqlXP.java/CraftBang/src/me/CraftBangline 58Java Problem
Cannot refer to a non-final variable p inside an inner class defined in a different methodSqlXP.java/CraftBang/src/me/CraftBangline 65Java Problem
Cannot refer to a non-final variable p inside an inner class defined in a different methodSqlXP.java/CraftBang/src/me/CraftBangline 63Java Problem
If I change public void to public int it also gives error..

 

I'm not sure if this will work but:
You need to make value that you will return, and after the scheduler, you need to return that.
Also you can use, Bukkit.getScheduler().runTask.
This will run it instantly

 

teunie75 can you make an example please, just for one task, like send the player a message.
So just how to run the task, I can add the code than, I just can't find out what to type

 

Code:

public static int getXP(Player p){
int returnValue;
Bukkit.getScheduler().runTask(Main.plugin, new Runnable(){
  public void run() {
openConnection();
try{
if(playerDataContainsPlayer(p)){
String what = "credits";
PreparedStatement sql = connection.prepareStatement("SELECT " + what + " FROM `xf_user` WHERE username='" + p.getName() + "'");
ResultSet result = sql.executeQuery();
result.next();
returnValue = result.getInt(what);
}
}catch (Exception e){
e.printStackTrace();
}
returnValue = 0;
  }
}
  , 0L); // 20L = 1
return returnValue;
}

 

Ging to test that teunie75 just needed to add a little bit of code to yors ( i got returnvalue out of the void, so no errors than )

 

That's obvious, because voids don't return a value.

 

Now I get this error :

Code:

[18:30:24 ERROR]: Could not pass event BlockBreakEvent to CraftBang v1.0
org.bukkit.event.EventException
        at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.ja
va:427) ~[craftbukkit.jar:git-Bukkit-1.7.2-R0.2-b2974jnks]
        at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.jav
a:62) ~[craftbukkit.jar:git-Bukkit-1.7.2-R0.2-b2974jnks]
        at org.bukkit.plugin.SimplePluginManager.fireEvent(SimplePluginManager.j
ava:481) [craftbukkit.jar:git-Bukkit-1.7.2-R0.2-b2974jnks]
        at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.j
ava:466) [craftbukkit.jar:git-Bukkit-1.7.2-R0.2-b2974jnks]
        at net.minecraft.server.v1_7_R1.PlayerInteractManager.breakBlock(PlayerI
nteractManager.java:263) [craftbukkit.jar:git-Bukkit-1.7.2-R0.2-b2974jnks]
        at net.minecraft.server.v1_7_R1.PlayerInteractManager.dig(PlayerInteract
Manager.java:118) [craftbukkit.jar:git-Bukkit-1.7.2-R0.2-b2974jnks]
        at net.minecraft.server.v1_7_R1.PlayerConnection.a(PlayerConnection.java
:535) [craftbukkit.jar:git-Bukkit-1.7.2-R0.2-b2974jnks]
        at net.minecraft.server.v1_7_R1.PacketPlayInBlockDig.a(SourceFile:53) [c
raftbukkit.jar:git-Bukkit-1.7.2-R0.2-b2974jnks]
        at net.minecraft.server.v1_7_R1.PacketPlayInBlockDig.handle(SourceFile:8
) [craftbukkit.jar:git-Bukkit-1.7.2-R0.2-b2974jnks]
        at net.minecraft.server.v1_7_R1.NetworkManager.a(NetworkManager.java:146
) [craftbukkit.jar:git-Bukkit-1.7.2-R0.2-b2974jnks]
        at net.minecraft.server.v1_7_R1.ServerConnection.c(SourceFile:134) [craf
tbukkit.jar:git-Bukkit-1.7.2-R0.2-b2974jnks]
        at net.minecraft.server.v1_7_R1.MinecraftServer.u(MinecraftServer.java:6
55) [craftbukkit.jar:git-Bukkit-1.7.2-R0.2-b2974jnks]
        at net.minecraft.server.v1_7_R1.DedicatedServer.u(DedicatedServer.java:2
50) [craftbukkit.jar:git-Bukkit-1.7.2-R0.2-b2974jnks]
        at net.minecraft.server.v1_7_R1.MinecraftServer.t(MinecraftServer.java:5
45) [craftbukkit.jar:git-Bukkit-1.7.2-R0.2-b2974jnks]
        at net.minecraft.server.v1_7_R1.MinecraftServer.run(MinecraftServer.java
:457) [craftbukkit.jar:git-Bukkit-1.7.2-R0.2-b2974jnks]
        at net.minecraft.server.v1_7_R1.ThreadServerApplication.run(SourceFile:6
17) [craftbukkit.jar:git-Bukkit-1.7.2-R0.2-b2974jnks]
Caused by: java.lang.NullPointerException
        at me.CraftBang.NexusDamage.onBlockBreak(NexusDamage.java:233) ~[?:?]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.7.0
_45]
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[?:1.7.0
_45]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:1
.7.0_45]
        at java.lang.reflect.Method.invoke(Unknown Source) ~[?:1.7.0_45]
        at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.ja
va:425) ~[craftbukkit.jar:git-Bukkit-1.7.2-R0.2-b2974jnks]
        ... 15 more
>

Caused by: java.lang.NullPointerException
at me.CraftBang.NexusDamage.onBlockBreak(NexusDamage.java:233)

Code : for(OfflinePlayer receivexp : team.getPlayers()){SqlXP.giveXP(receivexp.getPlayer(), 1);}
SqlXP :

Code:

package me.CraftBang;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.bukkit.Bukkit;
import org.bukkit.ChatColor;
import org.bukkit.entity.Player;
 
public class SqlXP {
static int returnValue = 0;
static Boolean returnBoolean = false;
private static Connection connection;
public static void disable(){
try {
if(connection != null && !connection.isClosed())
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
 
public synchronized static void openConnection(){
try{
connection = DriverManager.getConnection("thiscorrect.");
}catch(Exception e){
e.printStackTrace();
}
}
public synchronized static void closeConnection(){
try{
connection.close();
}catch(Exception e){
e.printStackTrace();
}
}
 
public synchronized static boolean playerDataContainsPlayer (final Player p){
Bukkit.getScheduler().runTask(Main.plugin, new Runnable(){
public void run() {
try{
PreparedStatement sql = connection.prepareStatement("SELECT * FROM `xf_user` WHERE username=?");
sql.setString(1, p.getName());
ResultSet resultSet = sql.executeQuery();
boolean containsPlayer = resultSet.next();
sql.close();
resultSet.close();
returnBoolean = containsPlayer;
}catch(Exception e){
e.printStackTrace();
returnBoolean = false;
}
}
});
return returnBoolean;
}
 
public static int getXP(final Player p){
Bukkit.getScheduler().runTask(Main.plugin, new Runnable(){
public void run() {
openConnection();
try{
if(playerDataContainsPlayer(p)){
PreparedStatement sql = connection.prepareStatement("SELECT credits FROM `xf_user` WHERE username='" + p.getName() + "'");
ResultSet result = sql.executeQuery();
result.next();
returnValue = result.getInt("credits");
}
}catch (Exception e){
e.printStackTrace();
}
returnValue = 0;
}
});
return returnValue;
}
 
 
public static void giveXP(final Player p, final int i){
Bukkit.getScheduler().runTask(Main.plugin, new Runnable(){
public void run() {
openConnection();
try{
if(playerDataContainsPlayer(p)){
String what = "credits";
int i2 = getXP(p) + i;
PreparedStatement updatewhat = connection.prepareStatement("UPDATE xf_user SET " + what + "='" + i2 + "' WHERE username='" + p.getName() +"'");
updatewhat.executeUpdate();
updatewhat.close();
p.sendMessage(ChatColor.GREEN + "You received " + i + " XP!");
}else{
p.sendMessage(ChatColor.RED + "Use /regforum to register on our forums to save your Stats!");
p.sendMessage(ChatColor.RED + "If you did register than it's our fault!");
}
}catch (Exception e){
e.printStackTrace();
}
}
});
}
 
}

 

I don't have all of your code, so I dont know what is going wrong.

 

teunie75 do you have skype? So I can send my TV (if you have time for that)

Or what code do you need more?

Nvm teunie75 it was my plugin that causes an error because my team was null.
Still need to do somethings because it's still lagging

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

I thought the event would help, it helped with my plugin's updater.
The console continued loading and didn't get stuck in the console for 5 seconds.
Because I saw this: http://forums.bukkit.org/threads/common-mistakes.100544/
Quote:

 

Wierd, does someone have a example for the mysql what will work perfectly?
Since mine doesn't work good

 

CraftBang Here's a nice little class I made to connect to MySQL databases and pull data from them. Eventually I will add more useful methods to it.
https://gist.github.com/bm/8506344

 

Compressions awesome!
Could you maybe also tell me how to use use it, like I just want to give kills on an event.
Do I just need to open the connection on Startup or everytime when the event is triggered?

And are you sure this won't lagg the server?

Thanks a lot already! Looking forward to testing and new methods (like maybe set data, and add data)

 

CraftBang As long as you the connection isn't closed, you should only need to instantiate it at startup.
The only reason this would lag the server is if 1) the server is slow or 2) the database is slow.
I would think about running the process asynchronously if it really becomes a lag issue.

 

I hosted server at my pc, quadcore 8 gb ram (used 4gb) so my pc is fine, I also guess my website hoster is good.
www.mcinfinite.net is the website, it got some high security I guess, dunno if this matters.(I guess I'm allowed to post this)

and about running the tasks async I guess that's the best, because what happens if website is down or whatever because of hoster, than server would be frozen?

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

CraftBang Right. You wouldn't want to interfere with the latency of the main thread if MySQL database access is slow.

 

Does the Java SQL do async by itself? It seems like a bad idea to connect to a server or access it in the main thread, but then it'll also take extra work to deal with problems that occur when the server is too slow (e.g. making sure your get and set commands are processed in the right order).

 

Well I read somehwere that you can update it like this (column kills) kills=kills+1
Somethnig like that I read somewhere