Search code examples
javamysqlresultset

Why does MySQL database return empty ResultSet although data should be correct

Context: I have 2 different tables in my database. First is actionblock (ActionBlock in Java) and the second one is location (PermanentLocation in Java). The ActionBlock class needs a location to work, so in order for the code to work it first loads the ActionBlock data which contains the location needed after which the code loads the necessary location. (yes I know the following code will spam load location query)

Problem: Loading the location gives empty ResultSet using the same DB connection as the ActionBlock loader

What I've tried:

  1. use different connection: no difference
  2. verify that the keys are same (id and uuid): they are
  3. test a query in the database: creating a select query in DBMS returns a 1 specific row but copying the exact same query code to Java still results in empty ResultSet (for this test PreparedStatement was changed to Statement)
  4. verify I'm using the correct database: the actionblock loads fine from the database and I'm using the same connection to load the location
  5. are other connections from the code closed?: yes
  6. what are the errors in your console?: there are no errors printed to console

"Could not load location" is always printed to console (since the loader returns null) ActionBlock load code

/**
 * Loads the blocks from the database. All registerClass calls must be called before this
 */
public void loadBlocksFromDatabase() {
    Connection conn = null;
    try {
        conn = KaranteeniCore.getDatabaseConnector().openConnection();
        PreparedStatement stmt = conn.prepareStatement(
                "SELECT uuid,permission,classtype FROM actionblock WHERE serverID = ?;");
        stmt.setString(1, KaranteeniCore.getServerIdentificator());
        ResultSet set = stmt.executeQuery();

        // loop all the blocks from the database
        while(set.next()) {
            String uuidStr = set.getString(1);
            String permission = set.getString(2);
            String classType = set.getString(3);

            UUID uuid = UUID.fromString(uuidStr);

            // check if loaded block is a block or a sign
            for(Class<? extends ActionBlock> clazz : subBlockClasses)
            if(clazz.getName().equals(classType)) {
                // load the location of the block
                PermanentLocation loc = PermanentLocation.loadLocation(conn, uuid);
                if(loc == null) {
                    Bukkit.getLogger().log(Level.WARNING, "Could not load location " + uuid.toString() + ". Has it been deleted from database?");
                    continue;
                }

                Block block = loc.getLocation().getBlock();
                try {
                    // try to create a new class out of this block
                    if(permission != null) {
                        Constructor<? extends ActionBlock> cstr = clazz.getConstructor(Block.class, UUID.class, String.class);
                        ActionBlock aBlock = cstr.newInstance(block, uuid, permission);
                        aBlock.onLoad();
                        registerBlock(aBlock); // register created class
                    } else {
                        Constructor<? extends ActionBlock> cstr = clazz.getConstructor(Block.class, UUID.class);
                        ActionBlock aBlock = cstr.newInstance(block, uuid);
                        aBlock.onLoad();
                        registerBlock(aBlock); // register created class
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

            // load all signs
            for(Class<? extends ActionSign> clazz : subSignClasses)
            if(clazz.getName().equals(classType)) {
                // load the location of the block
                Block block = PermanentLocation.loadLocation(uuid).getLocation().getBlock();
                try {
                    // try to create a new class out of this block
                    if(permission != null) {
                        Constructor<? extends ActionSign> cstr = clazz.getConstructor(Block.class, UUID.class, String.class);
                        ActionBlock aBlock = cstr.newInstance(block, uuid, permission);
                        aBlock.onLoad();
                        registerBlock(aBlock); // register created class
                    } else {
                        Constructor<? extends ActionSign> cstr = clazz.getConstructor(Block.class, UUID.class);
                        ActionBlock aBlock = cstr.newInstance(block, uuid);
                        aBlock.onLoad();
                        registerBlock(aBlock); // register created class
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    } catch(SQLException e) {
        e.printStackTrace();
        // ignored
    } finally {
        try {
            if(conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

PermanentLocation load code

/**
 * Loads a permanent location from database using the given uuid
 * @param conn Connection to the database
 * @param uuid uuid of the location
 * @return location loaded or null if none found
 */
public static PermanentLocation loadLocation(Connection conn, UUID uuid) {
    PermanentLocation location = null;
    try {
        PreparedStatement st = conn.prepareStatement("SELECT world, x, y, z, pitch, yaw FROM location WHERE id = ? AND serverID = ?;");
        st.setString(1, uuid.toString());
        st.setString(2, KaranteeniPlugin.getServerIdentificator());
        ResultSet set = st.executeQuery();
        if(set.next()) {
            String w = set.getString(1);
            double x = set.getDouble(2);
            double y = set.getDouble(3);
            double z = set.getDouble(4);
            float pitch = set.getFloat(5);
            float yaw = set.getFloat(6);

            World world = Bukkit.getWorld(w);
            if(world == null)
                return null;
            else
                Bukkit.getLogger().log(Level.SEVERE, "Could not find world " + w);

            Location loc = new Location(world, x,y,z,pitch,yaw);

            location = new PermanentLocation(uuid, loc);
        }
        else
            location = null;
    } catch(SQLException e) {
        Bukkit.getLogger().log(Level.SEVERE, e.getMessage());
    }

    return location;
}

sys.statements_with_errors_or_warnings (insert into actionblock was indev code which had a slight mistake) sys.statements_with_errors_or_warnings

query from (I assume) Java query received from Java

Contents from the location table database location tables contents

Contents from the actionblock table actionblock table contents

Need more info? This post originates from the Spigot forum post

Edit: Restarting database or system does not affect the outcome

Solution

  • Found answer: Location cannot be loaded whilst the server is starting as other classes can only find necessary information (World) only after full boot.