Database error

Discussion in 'Spigot Plugin Development' started by DotRar, Jun 7, 2016.

  1. I'm getting an error with my databases. I'm trying to cache UUIDs in one. Here is the table I am using:

    Code (Text):
    mysql> SHOW columns from nameCache;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | NAME  | varchar(16) | YES  |     | NULL    |       |
    | UUID  | varchar(32) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
     
    and when someone joins, a method to cache UUIDs with this code is called:
    Code (Text):
    PreparedStatement ps = conn.prepareStatement("REPLACE INTO nameCache(NAME, UUID) VALUES("+name+","+uuid+");");
    ps.execute();
    ps.close();
    My friend and I have both joined but the table has 0 rows of data.

    When someone joins, I get this error:

    Code (Text):
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'foallow' in 'field list'
    21:46:04 [SEVERE]     at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    21:46:04 [SEVERE]     at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    21:46:04 [SEVERE]     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    21:46:04 [SEVERE]     at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    21:46:04 [SEVERE]     at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
    21:46:04 [SEVERE]     at com.mysql.jdbc.Util.getInstance(Util.java:360)
    21:46:04 [SEVERE]     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
    21:46:04 [SEVERE]     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
    21:46:04 [SEVERE]     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
    21:46:04 [SEVERE]     at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
    21:46:04 [SEVERE]     at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
    21:46:04 [SEVERE]     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
    21:46:04 [SEVERE]     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
    21:46:04 [SEVERE]     at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:848)
    21:46:04 [SEVERE]     at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:742)
    21:46:04 [SEVERE]     at com.perkelle.dev.bungee.DB$7.run(DB.java:200)
    21:46:04 [SEVERE]     at net.md_5.bungee.scheduler.BungeeTask.run(BungeeTask.java:63)
    21:46:04 [SEVERE]     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    21:46:04 [SEVERE]     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    21:46:04 [SEVERE]     at java.lang.Thread.run(Thread.java:745)
     
    (Foallow is my friend)

    This is my first time working with mysql so sorry if this is a noob error. Thanks :D
     
  2. REPLACE INTO needs something to replace. If you don't insert (INSERT INTO) first, you won't ever be replacing anything. Also, that query doesn't make sense. You would think the UUID would remain constant, so why don't you do something like:
    Code (Text):
    UPDATE `nameCache` SET NAME='?' WHERE UUID='?';
    You should be using PreparedStatements like they're meant to be used by inserting the values through the setString, setInt, and other PreparedStatement methods.
     
  3. First problem is the command is "INSERT" not "REPLACE". Second problem is you need to surround the name and UUID in single quotes like this:

    conn.prepareStatement("REPLACE INTO nameCache(NAME, UUID) VALUES('"+name+"', '"+uuid+"');");

    Third a UUID is 36 characters when it has the dashes in it, not 32.

    Replace is not commonly used. Almost always use INSERT and UPDATE
     
    • Like Like x 1
  4. I'll change the table to be 36 characters now.

    Also, I'm guessing UPDATE only works when there is already a row there but how can I check if there is one there? Sorry for being a noob =/
     
  5. You need to run a query checking if it already exists in the table using a SELECT statement and a ResultSet. Do a quick Google search on how to do that exactly, I don't spoonfeed code unfortunately.
     
    • Like Like x 1
  6. I found this
    Code (Text):
    select 1
    from table
    where key = value;
     
    on stackoverflow, so would this something like this:
    Code (Text):
    SELECT 1 FROM nameCache WHERE name = INSERTplayerNAMEhere;
    return a value if the record existed and null if it did not?
     
  7. Ok I have written this:

    Code (Text):
    boolean exists;
                        PreparedStatement ps1 = conn.prepareStatement("SELECT 1 FROM nameCache WHERE uuid = '"+uuid+"';");
                        ResultSet rs = ps1.executeQuery();
                        ps1.close();
                        if(rs.first()) {
                            exists = true;
                        }
                        else {
                            exists = false;
                        }

                        if(!exists) {
                            PreparedStatement ps2 = conn.prepareStatement("INSERT INTO nameCache(NAME,UUID) VALUES('"+name+"','"+uuid+"');");
                            ps2.execute();
                            ps2.close();
                        }
                        else {
                            PreparedStatement ps3 = conn.prepareStatement("UPDATE 'nameCache' SET NAME='"+name+"' WHERE UUID='"+uuid+"';");
                            ps3.execute();
                            ps3.close();
                        }
    I think it will work but can you just see if it has any disastrous / database destroying mistakes before I run it please?
     
  8. It looks good to me...
     
  9. @Nex9 That works. However, it would be easier to just do "SELECT COUNT(*) FROM blah blah blah" and doing rs.next(), then rs.getBoolean(1) and checking that.

    (I don't have all those methods memorized but it's along those lines. I don't use SQL with Java as often I do in other languages)
     
    • Like Like x 1
  10. That's not how prepared statements work.
     
  11. @Nex9 3 statements in a row, that's not very performant .-.
    http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
    Code (SQL):
    INSERT INTO namecache(`UUID`, `NAME`) VALUES ('uuid', 'name') ON DUPLICATE KEY UPDATE `NAME`='name'
    OP note that UUIDs with hyphens have a length of 36. Unlees you are removing them, 32 is not long enough
     
    • Like Like x 1
  12. @Nex9 @BlizzardFyre use upserts (INSERT INTO ... ON DUPLICATE KEY UPDATE)

    And make your uuid column UNIQUE, otherwise upserts don't work
    [edit] tfw halfly ninja'd
     
    • Like Like x 1
  13. @megamichiel @DarkSeraphim I'm a SQL Server developer, not MySQL. Good to know that's a thing in MySQL though, I'm going to implement that in some plugins I use right now actually. Appreciate it!
     
    • Like Like x 1
  14. How do they work then?
     
  15. Thanks guys :D
     
  16. I implemented it like this:

    Code (Text):
    try {
                        PreparedStatement ps = conn.prepareStatement("INSERT INTO nameCache('UUID', 'NAME') VALUES('"+uuid+"', '"+name+"') ON DUPLICATE KEY UPDATE 'NAME' = '"+name+"';");
                        ps.execute();
                        ps.close();
                    } catch(SQLException e) {
                        e.printStackTrace();
                    }
    However, I am getting this error:

    Code (Text):
    19:08:02 [SEVERE] 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 ''UUID', 'NAME') VALUES('1f34f2db-61bc-4f22-ae4d-b58ca76b8766', 'nex_perkelle') O' at line 1
    19:08:02 [SEVERE]     at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    19:08:02 [SEVERE]     at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    19:08:02 [SEVERE]     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    19:08:02 [SEVERE]     at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    19:08:02 [SEVERE]     at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
    19:08:02 [SEVERE]     at com.mysql.jdbc.Util.getInstance(Util.java:360)
    19:08:02 [SEVERE]     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
    19:08:02 [SEVERE]     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
    19:08:02 [SEVERE]     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
    19:08:02 [SEVERE]     at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
    19:08:02 [SEVERE]     at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
    19:08:02 [SEVERE]     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530)
    19:08:02 [SEVERE]     at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907)
    19:08:02 [SEVERE]     at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1199)
    19:08:02 [SEVERE]     at com.perkelle.dev.bungee.DB$7.run(DB.java:210)
    19:08:02 [SEVERE]     at net.md_5.bungee.scheduler.BungeeTask.run(BungeeTask.java:63)
    19:08:02 [SEVERE]     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    19:08:02 [SEVERE]     at java.util.concurrent.ThreadPoolExecutor$
    What's up with my Syntax?
     
  17. @Nex9
    Did you make the table's UUID column have a length of 36?
     
    • Like Like x 1
  18. Yup:
    Code (Text):
    CREATE TABLE IF NOT EXISTS nameCache(UUID VARCHAR(36),NAME VARCHAR(16));
    I also deleted the table to let it regenerate with these settings
     
  19. @Nex9 did you delete the table? As it only creates if it doesn't exist
     
    • Like Like x 1
  20. The table, not the whole db