Solved MySQL: Data too long for column

Discussion in 'Spigot Plugin Development' started by Chaotisch3r, Aug 26, 2020.

  1. Hello i have this problem:
    Code (Java):
     Data truncation: Data too long for column 'Worldname' at row 1.
    but how can i fix it?

    Code (Java):
        public static void createTable_2() {
            if(Main.getInstance().getConfig().getString("SpawnFile").equals("MYSQL")) {
                try {
                    PreparedStatement ps = MySQL.getStatement("CREATE TABLE IF NOT EXISTS spawnLocation (X DOUBLE(0,0), Y DOUBLE(0,0), Z DOUBLE(0,0), " +
                            "Yaw FLOAT(0,0), Pitch FLOAT(0,0), Worldname VARCHAR(100))");
                    assert ps != null;
                    ps.executeUpdate();
                } catch (Exception e) {
                    Bukkit.getConsoleSender().sendMessage(Main.pr + "§cERROR: The table can't be created, becuase: " + e.getMessage() + "§7.");
                }
            }
        }
    Code (Java):
    public static void insert_2(Location location) {
            if(Main.getInstance().getConfig().getString("SpawnFile").equals("MYSQL")) {
                try {
                    PreparedStatement ps = MySQL.getStatement("INSERT INTO spawnLocation (X, Y, Z, Yaw, Pitch, Worldname)" +
                            " VALUES (?, ?, ?, ?, ?, ?)");
                    assert ps != null;
                    ps.setDouble(1, location.getX());
                    ps.setDouble(2, location.getY());
                    ps.setDouble(3, location.getZ());
                    ps.setFloat(4, location.getYaw());
                    ps.setFloat(5, location.getPitch());
                    ps.setString(6, location.getWorld().getName());
                    ps.executeUpdate();
                    ps.close();
                } catch (Exception ex) {
                    ex.printStackTrace();
                    Bukkit.getConsoleSender().sendMessage("§cERROR: Couldn't add any Datas, because: " + ex.getMessage() +"§7.");
                }
            }
        }
     
  2. My SQL knowledge is not the best, but I'm gonna try to logically help.

    You define the column type as `FLOAT(0,0)` (and `DOUBLE(0,0)`) when you're creating the table, that'd make the data type limited to 0 digits and 0 decimal point, which of course, makes any data input too big for it to hold. 0 might define it unlimited, but I'm not sure here.

    I'd try changing the table creation statement to:

    Code (Java):

    PreparedStatement ps = MySQL.getStatement("CREATE TABLE IF NOT EXISTS spawnLocation (X DOUBLE(32,4), Y DOUBLE(0,0), Z DOUBLE(32,4), " +
                            "Yaw FLOAT(32,4), Pitch FLOAT(32,4), Worldname VARCHAR(100))");
     
     
  3. I dont think, this will help me, because the Error means the "Worldname" and not the Double and Floats
    And i tried it, doesn Helped me
     
  4. The Worldname column allows for 100 character long strings... So how long is the name of the world you are inserting into the table?
     
  5. Oh I missread, sorray.

    The only reason why this would appear is that the data you're trying to insert is just too big. You could log it and check if it's the correct value.
     
  6. Use MongoDB it's easier to use, search for "thesourcecode mongodb bukkit" at youtube,hope i helped!
     
    • Funny Funny x 1
  7. Strahan

    Benefactor

    *facepalm*

    You don't choose a database system based on one being "easier" than the other, it depends on your use case. I don't see how MySQL is really any harder anyway.
     
    • Agree Agree x 4
  8. Have you verified that the table was properly created with your intended values? eg. using
    Code (SQL):
    DESCRIBE `spawnLocation`;
    Since you're using CREATE TABLE IF NOT EXISTS, if you update the Java code but don't touch the current SQL DB, your old table is not going to get overwritten.
     
    • Agree Agree x 1
    • Useful Useful x 1
  9. You don't need the assertion statement.
     
  10. Too many people use NoSQL because of trend instead of true reasons.
    Most of systems need a data relative-based DB like SQL Databases.

    as @Lyphiard said:
    So check your columns data and if it don't correspond to the expected values, use the ALTER TABLE statement.
     
    • Agree Agree x 1
  11. Soryy, it was my fault, i had to delet the old table, becuase it wasn't correct with the present Values. Thanks @Lyphiard