MySQL Problems

Hello guys,

Im recently busy with MySQL, and i understand a lot of it. But not everything ofc.
So, i have made that if the player joined the game, he creates a table with the following information:

Code:

    public static void createAchievementConfig(Player p) throws SQLException {
        Statement statement = Main.c.createStatement();
        statement.executeUpdate("CREATE TABLE " + "'" + p.getUniqueId() + "'" + " (name TEXT, status TEXT);");
       
       
        ResultSet res = statement.executeQuery("SELECT * FROM " + p.getUniqueId() + " WHERE name = 'status';");
       
        if (res.getString("name") == null) {
            PreparedStatement ps = Main.c.prepareStatement("INSERT INTO " + p.getUniqueId() + " (name, status) VALUES ('total', '0');");
            ps.executeUpdate();
            return;
        }
       
        ResultSet infobot = statement.executeQuery("SELECT * FROM " + p.getUniqueId() + " WHERE name = 'infobot';");
       
        if (infobot.getString("name") == null) {
            PreparedStatement ps = Main.c.prepareStatement("INSERT INTO " + p.getUniqueId() + " (name, status) VALUES ('infobot', 'enabled');");
            ps.executeUpdate();
            return;
        }
       
        //PreparedStatement ps = Main.c.prepareStatement("INSERT INTO " + p.getUniqueId() + " (status, name) VALUES ('not reached', '" + SERVERJOINER + "');");

        }

But, he gives me a null pointer :

Code:

INFO Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''c1bbd6fc-542d-48cf-9f94-954896a18e2a' (name TEXT, status TEXT)' at line 1

I hope you guys can fix the problem and explane it. Thanks

 

@jusjus112
What happens when you change the single-quote to double-quotes before and after the table name, like so:

Code:

statement.executeUpdate("CREATE TABLE " + "\"" + p.getUniqueId() + "\"" + " (name TEXT, status TEXT);");

 

@MOMOTHEREAL
Thanks for your help.

But i have already tried it, and it came up with the same error:

Code:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"c1bbd6fc-542d-48cf-9f94-954896a18e2a" (name TEXT, status TEXT)' at line 1

I cant figure out whats wrong with my code. I have searched google and followed some tutorials and sqldocs say it that im doing it right. So, here is the whole error:

Error (open) Error (close)

org.bukkit.event.EventException 29.02 13:52:14 [Server] INFO at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:297) ~[craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62) ~[craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at org.bukkit.plugin.SimplePluginManager.fireEvent(SimplePluginManager.java:501) [craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:486) [craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at net.minecraft.server.v1_8_R1.PlayerList.onPlayerJoin(PlayerList.java:249) [craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at net.minecraft.server.v1_8_R1.PlayerList.a(PlayerList.java:134) [craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at net.minecraft.server.v1_8_R1.LoginListener.b(LoginListener.java:81) [craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at net.minecraft.server.v1_8_R1.LoginListener.c(LoginListener.java:41) [craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at net.minecraft.server.v1_8_R1.NetworkManager.a(NetworkManager.java:150) [craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at net.minecraft.server.v1_8_R1.ServerConnection.c(SourceFile:151) [craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at net.minecraft.server.v1_8_R1.MinecraftServer.z(MinecraftServer.java:718) [craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at net.minecraft.server.v1_8_R1.DedicatedServer.z(DedicatedServer.java:284) [craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at net.minecraft.server.v1_8_R1.MinecraftServer.y(MinecraftServer.java:598) [craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at net.minecraft.server.v1_8_R1.MinecraftServer.run(MinecraftServer.java:506) [craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at java.lang.Thread.run(Thread.java:745) [?:1.8.0_66] 29.02 13:52:14 [Server] INFO Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"c1bbd6fc-542d-48cf-9f94-954896a18e2a" (name TEXT, status TEXT)' at line 1 29.02 13:52:14 [Server] INFO at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_66] 29.02 13:52:14 [Server] INFO at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_66] 29.02 13:52:14 [Server] INFO at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_66] 29.02 13:52:14 [Server] INFO at java.lang.reflect.Constructor.newInstance(Constructor.java:422) ~[?:1.8.0_66] 29.02 13:52:14 [Server] INFO at com.mysql.jdbc.Util.handleNewInstance(Util.java:407) ~[craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at com.mysql.jdbc.Util.getInstance(Util.java:382) ~[craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052) ~[craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593) ~[craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525) ~[craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986) ~[craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140) ~[craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620) ~[craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1662) ~[craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1581) ~[craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO at server.Achievements.Achievements.createAchievementConfig(Achievements.java:116) ~[?:?] 29.02 13:52:14 [Server] INFO at server.Events.JoinEvent.onPlayerJoin(JoinEvent.java:28) ~[?:?] 29.02 13:52:14 [Server] INFO at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_66] 29.02 13:52:14 [Server] INFO at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_66] 29.02 13:52:14 [Server] INFO at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_66] 29.02 13:52:14 [Server] INFO at java.lang.reflect.Method.invoke(Method.java:497) ~[?:1.8.0_66] 29.02 13:52:14 [Server] INFO at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:295) ~[craftbukkit25.jar:git-Bukkit-1092acb] 29.02 13:52:14 [Server] INFO ... 14 more

Line 116 from the class Achievements is:

Code:

statement.executeUpdate("CREATE TABLE " + "\"" + p.getUniqueId() + "\"" + " (name TEXT, status TEXT);");

 

@jusjus112

You really should not be running SQL queries on the main thread, and that code definitely should not be static. You should really get all that static out of your code as soon as possible, it honestly makes my eyes bleed.

In addition, you should not scale out tables with database size. Why would you need a table for each player? You should really figure out how to have a determinate number of tables, duplicity in rows is okay.

As for your error, the server does not like a number as the table name. Rearrange your database as described above to fix the issue.

 

@mythbusterma
But i have a achievement plugin wich creates 80 names for each user. So, i dont think i can create 80 TEXT rows for each UUID in a table. Or maybe 120 rows. So, how can i do this? Save 120 achievement for a user?

 

@jusjus112

Why do you need to create rows that represent achivements they don't have? Just have however many rows in a table that associate an integer (which represents the achivement ID) with the UUID of the player. That will give you much better results.

 

@mythbusterma
Yea, but its the same. IF you have a lot off players online. It creates a lot of rows.
So, it creates a lot and i dont thnk you cant stop it.

But from what you said, i dont understand you. Can you show me what you mean?

 

@jusjus112

MySQL is perfectly fine handling many many millions of rows. You should avoid unbounded table creation, however. Also, keep in mind most players won't earn many achievements, if any at all.

The table would look like this:

uuid | achievementid
c1bbd6fc-542d-48cf-9f94-954896a18e2a | 2
c1bbd6fc-542d-48cf-9f94-954896a18e2a | 5
2EA582D8-DF37-11E5-A449-4437069245D3 | 1
3AA8C0D6-DF37-11E5-AF0D-4437069245D3 | 7

In your code, you could resolve the achievementid to an achievement. This will speed up the database significantly, and reduce redundant information.

 

@mythbusterma
Oke, like what you said. I have done it, it was a great solution. But now i have another problem. Im sitting like 10 hours to fix it but nothing works. When i want to get a Integer from the Table it says :

Code:

Caused by: java.sql.SQLException: Column 'com.mysql.jdbc.JDBC4ResultSet@54ccb9fb' not found.

This is the code:

Code:

    public static Integer getAchievementFromUser(Player p) throws SQLException {
        Statement statement = Main.c.createStatement();
        ResultSet res = statement.executeQuery("SELECT total FROM achievements WHERE uuid = '" + p.getUniqueId() + "';");
       
        return res.getInt(res.toString());
    }

Also, when i try to set info in mysql. It works, it says. But when i want to see the info in MySql itself, i see nothing. So, it is very weird. And maybe this will fix the problem.


Also, this is working they say, but i cant see it. So, maybe it a strange way?

Code:

        ResultSet infobot = statement.executeQuery("SELECT uuid FROM infobot WHERE uuid = '" + p.getUniqueId() + "';");
       
        if (infobot == null) {
            PreparedStatement ps = Main.c.prepareStatement("INSERT INTO infobot (uuid, status) VALUES ('" + p.getUniqueId() + "', 'enabled');");
            ps.executeUpdate();
            ps.closeOnCompletion();
        }

 

@jusjus112
It should be mentioned you are using prepared statements incorrectly. The current method you are using is susceptible to SQL injection. Correctly executing SQL updates/queries as such will prevent this.

Code:

PreparedStatement p = conn.prepareStatement("INSERT INTO bukkit (name, level) VALUES (?,?);

p.setString(1,"UNST4BL3");
p.setInt(2,10);

p.executeUpdate();

I must stress how important it is for you not just to change this, but you understand SQL injection as a whole. Done incorrectly, it will allow a user to interact with your database however they please.

 

@Connor2weirdness

The only reason I didn't mention that is because he's using UUIDs, which can't be arbitrarily defined.

Other than though, you're right. There's just so much else wrong with his SQL.

@jusjus112

You should really stop with all the static stuff. Just rip that all out of your code. It will be much cleaner.

 

@mythbusterma
I'm not sure whether UUIDs can have the ' character in them. Although if they can, it would just simply break the statement, not cause any significant damage.

I just use prepared statements all the time, so I don't have to think about it. Better safe than sorry.

@jusjus112
Sorry for kinda going off on a tangent before looking at your problem.

Code:

res.getInt(columnName);

This is what gets a value from a row. Right now, you're passing a ResultSet to string. This is not what you are interested in. I want you to learn, so I'm not going to directly give the answer.

As for the second part of the issue. ResultSets aren't just 1 result. They can be, but as the datatype's name infers, it's a set. Like any set, their indexes begin at 0 - in this case before any rows that have been returned. You will need to perform:

Code:

res.next();

to move the set's "pointer" to the first index; the first row. Ideally you would do a check, but it's not always needed.

 

When I save UUID's, they're saved in a big table with the UUID field set as a primary key, varchar(32). Also, when you save UUIDs to a row, you should do toString() on it, since that plays nicer with MySQL. When you retrieve it, just do fromString() on the data. Maybe that should fix your problem with making a table name into a UUID.

 

I want to point out that saving UUIDs as strings (varchar or whtever you want it to be) will turn in slow queries once you have certain amount of rows in the table, to improve performance you should save them as binary(16)

http://stackoverflow.com/questions/10950202/how-to-store-uuid-as-number